Update existing info in db ?

pirate · March 31, 2003

[PLAIN]Update existing info in db ?[Resolved][/PLAIN]

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

Edited April 1, 2003 by pirate

Robby · March 31, 2003

use the ExcecuteNonQuery method of SqlCommand/OleDbCommand.

Then set the commandText = "Update myTable ...." query

pirate · March 31, 2003

Thanks ...That's actually what I'm in now.

Tell you what happens later...

pirate · March 31, 2003

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

Edited March 31, 2003 by pirate

Robby · March 31, 2003

Yeah it's very possible, I'll try and put a sample together. (or if anyone else wants to jump in)

pirate · April 1, 2003

That's very nice of you Robby :)

Robby · April 1, 2003

I can't post the entire project as it's too large, so here's the relevant stuff..

Don't 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 · April 1, 2003

Thank you so much , I appreciate that Robby (although I hate databinding but I'm sure I will profit it and adapt some lines to my needs .

Tell you what happened later...

Thank you again . Great demo... :)

pirate · April 1, 2003

Robby , Is it possible not using Update statement ??

Thanks in advance .

jfackler · April 1, 2003

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 who's 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" who's 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.

Edited April 1, 2003 by jfackler

pirate · April 1, 2003

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

pirate · April 1, 2003

Sorry I shoud've mentioned how ? I used datarow along with customizing the SQL Statement . No databinding at all !:D

Sign In

Update existing info in db ?

Recommended Posts

pirate

Robby

pirate

pirate

Robby

pirate

Robby

pirate

pirate

jfackler

pirate

pirate

Join the conversation

Browse

Activity