Update existing info in db ?

[pirate]

Update existing info in db ?[Resolved]

I want to update my database with new values ? (not data binding)
I know how to save , search , delete record but missing only that one .
Thanks

 

[Robby]

use the ExcecuteNonQuery method of SqlCommand/OleDbCommand.
Then set the commandText = "Update myTable ...." query

 

[pirate]

Thanks ...Thats actually what Im in now.
Tell you what happens later...

 

[pirate]

That worked now . What I really need (if possible) is : load these changed data in dataset first and then push it back to the source .Is it possible ?
Thanks guys

 

[Robby]

Yeah its very possible, Ill try and put a sample together. (or if anyone else wants to jump in)

 

[pirate]

Thats very nice of you Robby

 

[Robby]

I cant post the entire project as its too large, so heres the relevant stuff..

Dont let the code in SetCommands() scare you.

Call SetCommands() when you bind the Datagrid.
Call daUpdate whenever you want to update the Datagrid.

All variables prefixed with m_ are class members.


    Friend Sub daUpdate(ByVal ds As DataSet, ByVal sTable As String)
        Dim tran As SqlTransaction

        m_da.ContinueUpdateOnError = True update whatever we can

        Try
            If m_con.State = ConnectionState.Closed Then m_con.Open()

            tran = m_con.BeginTransaction(IsolationLevel.ReadCommitted)
            m_da.UpdateCommand.Transaction = tran
            m_da.InsertCommand.Transaction = tran
            m_da.DeleteCommand.Transaction = tran
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        Try
            m_da.Update(ds.Tables(sTable))
            tran.Commit()
        Catch ex As SqlException
            Try
                MessageBox.Show(ex.ToString)
                tran.Rollback()
            Catch RollbackEx As SqlException

                MessageBox.Show(RollbackEx.ToString)
            End Try
        Finally
            If m_con.State = ConnectionState.Open Then m_con.Close()
        End Try
    End Sub

    Friend Sub SetCommands()
        This routine Sets the Commands for the Data Adapter
        Try
            Dim cmdInsert As New SqlCommand()
            Dim cmdUpdate As New SqlCommand()
            Dim cmdDelete As New SqlCommand()

            With cmdInsert
                .CommandText = "INSERT INTO CLients(FirstName, LastName, Tel, SinClient)" _
                & " VALUES (@FirstName, @LastName, @Tel, @SinClient); " _
                & "SELECT FirstName, LastName, Tel, SinClient FROM Clients" _
                & " WHERE (SinClient = @SinClient)"
                .Connection = m_con
                .Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar, 15, "FirstName"))
                .Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar, 20, "LastName"))
                .Parameters.Add(New SqlParameter("@Tel", SqlDbType.NVarChar, 12, "Tel"))
                .Parameters.Add(New SqlParameter("@SinClient", SqlDbType.NVarChar, 9, "SinClient"))
            End With

            With cmdUpdate
                .CommandText = "UPDATE Clients SET " & _
                    "SinClient = @SinClient, " & _
                    "FirstName = @FirstName, " & _
                    "LastName = @LastName, " & _
                    "Tel = @Tel " & _
                    "WHERE " & _
                    "(SinClient = @Original_SinClient) " & _
                    "AND (FirstName = @Original_FirstName) " & _
                    "AND (Tel = @Original_Tel) " & _
                    "AND (LastName = @Original_LastName " & _
                    "OR @Original_LastName IS NULL AND LastName IS NULL); " & _
                    "SELECT SinClient, FirstName, LastName, Tel " & _
                    "FROM Clients " & _
                    "WHERE (SinClient = @SinClient)"

                .Connection = m_con

                .Parameters.Add(New SqlParameter("@SinClient", SqlDbType.NVarChar, 9, "SinClient"))
                .Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar, 15, "FirstName"))
                .Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar, 20, "LastName"))
                .Parameters.Add(New SqlParameter("@Tel", SqlDbType.NVarChar, 12, "Tel"))

                .Parameters.Add(New SqlParameter("@Original_SinClient", SqlDbType.NVarChar, 9, _
                    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "SinClient", _
                    System.Data.DataRowVersion.Original, Nothing))
                .Parameters.Add(New SqlParameter("@Original_FirstName", SqlDbType.NVarChar, 15, _
                    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "FirstName", _
                    System.Data.DataRowVersion.Original, Nothing))
                .Parameters.Add(New SqlParameter("@Original_LastName", SqlDbType.NVarChar, 20, _
                    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "LastName", _
                    System.Data.DataRowVersion.Original, Nothing))
                .Parameters.Add(New SqlParameter("@Original_Tel", SqlDbType.NVarChar, 12, _
                    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Tel", _
                    System.Data.DataRowVersion.Original, Nothing))
            End With

            With cmdDelete
                .CommandText = "DELETE  " & _
                    "FROM CLients " & _
                    "WHERE ( " & _
                    "SinClient = @Original_SinClient) " & _
                    "AND (FirstName = @Original_FirstName) " & _
                    "AND (Tel = @Original_Tel) " & _
                    "AND (LastName = @Original_LastName " & _
                    "OR @Original_LastName IS NULL AND LastName IS NULL)"

                .Connection = m_con

                .Parameters.Add(New SqlParameter("@Original_SinClient", System.Data.SqlDbType.NVarChar, 9, _
                    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "SinClient", _
                    System.Data.DataRowVersion.Original, Nothing))
                .Parameters.Add(New SqlParameter("@Original_FirstName", System.Data.SqlDbType.NVarChar, 15, _
                    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "FirstName", _
                    System.Data.DataRowVersion.Original, Nothing))
                .Parameters.Add(New SqlParameter("@Original_LastName", System.Data.SqlDbType.NVarChar, 20, _
                    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "LastName", _
                    System.Data.DataRowVersion.Original, Nothing))
                .Parameters.Add(New SqlParameter("@Original_Tel", System.Data.SqlDbType.NVarChar, 12, _
                    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Tel", _
                    System.Data.DataRowVersion.Original, Nothing))

            End With

            With m_da
                .InsertCommand = cmdInsert
                .UpdateCommand = cmdUpdate
                .DeleteCommand = cmdDelete
            End With
        Catch

        End Try

    End Sub

 

[pirate]

Thank you so much , I appreciate that Robby (although I hate databinding but Im sure I will profit it and adapt some lines to my needs .
Tell you what happened later...
Thank you again . Great demo...

 

[pirate]

Robby , Is it possible not using Update statement ??
Thanks in advance .

 

[jfackler]

If you employee a dataadapter object and dataset object filled with your entire data base
table then use a command builder to
create your insert, delete and update commands,
you can use the following to access a single row in that database(represented by the dataset),
change the individual fields and
update the database.

Another way perhaps.

      Dim dr As DataRow

        Dim key As Integer = 0    in your database, the column that holds 
                          the key value whos row your looking to edit

        Dim int as integer =0

         dim bc as integer = 0

        For Each dr In dataset1.Tables("MyTable").Rows   array of datarows

         If CStr(i.Item(key)) Is "SomeValue" Then  interogates each datarow for the 
                                            key value "SomeValue" whos row you want to edit


         bc = int    sets binding context to row that holds key value


         End If

         int  += 1   counter for rows...next row

        Next

        Dim drCurrent As DataRow

        drCurrent = dataset1.Tables("MyTable").Rows.Item(bc)   set current row database to edit 

          drCurrent.BeginEdit() begins edit of dataset row representing database row

          drCurrent("datacolumn") = textbox1.Text  obviously setting the value of the field

          multiple changes here from different control sources not necessarily databound

           drCurrent.EndEdit()

            oledbdataadapter1.Update(dataset1, "MyTable") updates database with new values

Let me know if it works for you.

 

[pirate]

Sorted out ! Thanks both of you so much . I really appreciate what you have done guys . Thank you

 

[pirate]

Sorry I shoudve mentioned how ? I used datarow along with customizing the SQL Statement . No databinding at all !