How to Edit SQL Table Row via Textboxes?

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
Hi All,

I would appreciate your assistance with the following task: I'm trying to edit and save changes made to a row of a SQL table via Windows form textboxes. Right now when I run the app, I try to make a change to one of the textboxes, I click on the Save/Close button, but when I go to the database, the cell linked to the textbox is not showing the change.

Here is my code so far...please excuse the commented out code...I haven't gotten it to work correctly:

VB.NET:
Private Sub btnSaveClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveClose.Click 
 
        'When Save button is clicked, build SQL Update command, connect to db, 
        'update row's info in db 
        Dim updateCmd As String = "UPDATE Locations Set site = @site," _ 
        & "site_refnbr = @siteRefnbr, aac = @aac, telephone_nbr = @phoneNbr" _ 
        & "fax_nbr = @faxNbr, depot_refnbr = @depotRefnbr, freight_refnbr = @freightRefnbr" _ 
        & "dispatch_refnbr = @dispatchRefnbr, Email = @Email" 
 
        Dim myCommand As SqlCommand = New SqlCommand(updateCmd, conn) 
 
        myCommand.Parameters.Add(New SqlParameter("@site", SqlDbType.VarChar)) 
        myCommand.Parameters.Add(New SqlParameter("@siteRefnbr", SqlDbType.VarChar)) 
        myCommand.Parameters.Add(New SqlParameter("@aac", SqlDbType.VarChar)) 
        myCommand.Parameters.Add(New SqlParameter("@phoneNbr", SqlDbType.VarChar)) 
        myCommand.Parameters.Add(New SqlParameter("@faxNbr", SqlDbType.VarChar)) 
        myCommand.Parameters.Add(New SqlParameter("@depotRefnbr", SqlDbType.VarChar)) 
        myCommand.Parameters.Add(New SqlParameter("@freightRefnbr", SqlDbType.VarChar)) 
        myCommand.Parameters.Add(New SqlParameter("@dispatchRefnbr", SqlDbType.VarChar)) 
        myCommand.Parameters.Add(New SqlParameter("@Email", SqlDbType.VarChar)) 
 
        'Create array of column names 
        Dim cols() As String = {"@site", "@siteRefnbr", "@aac", "@phoneNbr", "@faxNbr", _ 
        "@depotRefnbr", "@freightRefnbr", "@dispatchRefnbr", "@Email"} 
 
        'Iterate through columns to check for empty values and display message 
        'box if empty value found and initialize sqlcommand parameter values 
        'Dim numCols As Integer = Me.Controls.Count 
        'Dim i As Integer 
        'Dim colvalue As String 
        'Dim txtBox As TextBox 
        ''For i = 1 To numCols - 1 
        '    txtBox = Me.Controls.Count(i) 
 
        'Next 
 
        'BindingContext(dsUpdateDb, "locations").EndCurrentEdit() 
 
        'Dim daUpdateDb As New SqlDataAdapter 
        'Dim dtLocations As New DataTable("Locations") 
 
        'Dim cmdUpdateCommandBuilder As New SqlCommandBuilder(daUpdateDb) 
        ''Dim dsUpdateDb As New DataSet 
        ''Dim dtLocations As New DataTable 
 
        'Try 
        '    daUpdateDb.Update(dsUpdateDb, "Locations") 
        'Catch ex As Exception 
        '    MessageBox.Show(ex.Message) 
        'End Try 
 
        'daUpdateDb.Update(dsUpdateDb, "Locations") 
        'daUpdateDb.Update(dsUpdateDb, dtLocations) 
        'daUpdateDb.Fill(dtLocations) 
 
         
        'Dim cmdUpdateDb As New SqlCommand 
        'MessageBox.Show("Do you want to save changes for this location?", "OK", MessageBoxButtons.YesNo) 
 
        'Try 
        '    daUpdateDb.Update(dtLocations) 
        '    Console.WriteLine("Location successfully updated") 
        'Catch ex As Exception 
        '    Console.WriteLine("Call to DataAdapter.Update " & _ 
        '    "threw exception:" & vbCrLf & ex.Message) 
        'End Try 
 
 
    End Sub
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,779
Location
Sydney, Australia
Programming Experience
10+
Did you add the database to your project or is it attached permanently to a SQL Server instance? If it's the former, try following the first link in my signature. It should shed some light on the situation.
 

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
Hi jmcilhinney,

Thanks for your reply. The database is actually attached to a SQL server. What would you suggest in this case?

Thanks in advance.
 

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
Hi jmcilhinney,

Okay, so I've uncommented out the part I think is behind the save, but now I'm receiving the error, "Update unable to find TableMapping['Locations'] or DataTable 'Locations'." Any clue as to what's causing this? :confused: Here is my code for this as of right now:

VB.NET:
Private Sub btnSaveClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveClose.Click

        'When Save button is clicked, build SQL Update command, connect to db,
        'update row's info in db
        Dim updateCmd As String = "UPDATE Locations Set site = @site," _
        & "site_refnbr = @siteRefnbr, aac = @aac, telephone_nbr = @phoneNbr" _
        & "fax_nbr = @faxNbr, depot_refnbr = @depotRefnbr, freight_refnbr = @freightRefnbr" _
        & "dispatch_refnbr = @dispatchRefnbr, Email = @Email"


        'checks to make sure the connection is open
        If Not conn.State = ConnectionState.Open Then conn.Open()

        Dim myCommand As SqlCommand = New SqlCommand(updateCmd, conn)

        myCommand.Parameters.Add(New SqlParameter("@site", SqlDbType.VarChar))
        myCommand.Parameters.Add(New SqlParameter("@siteRefnbr", SqlDbType.VarChar))
        myCommand.Parameters.Add(New SqlParameter("@aac", SqlDbType.VarChar))
        myCommand.Parameters.Add(New SqlParameter("@phoneNbr", SqlDbType.VarChar))
        myCommand.Parameters.Add(New SqlParameter("@faxNbr", SqlDbType.VarChar))
        myCommand.Parameters.Add(New SqlParameter("@depotRefnbr", SqlDbType.VarChar))
        myCommand.Parameters.Add(New SqlParameter("@freightRefnbr", SqlDbType.VarChar))
        myCommand.Parameters.Add(New SqlParameter("@dispatchRefnbr", SqlDbType.VarChar))
        myCommand.Parameters.Add(New SqlParameter("@Email", SqlDbType.VarChar))

        'Create array of column names
        Dim cols() As String = {"@site", "@siteRefnbr", "@aac", "@phoneNbr", "@faxNbr", _
        "@depotRefnbr", "@freightRefnbr", "@dispatchRefnbr", "@Email"}

        Dim daUpdateDb As New SqlDataAdapter
        Dim dtLocations As New DataTable("Locations")

        Dim cmdUpdateCommandBuilder As New SqlCommandBuilder(daUpdateDb)
        Dim dsUpdateDb As New DataSet

        Try
            daUpdateDb.Update(dsUpdateDb, "Locations")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        daUpdateDb.Update(dsUpdateDb, "Locations")
        daUpdateDb.Fill(dtLocations)

       End Sub
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,779
Location
Sydney, Australia
Programming Experience
10+
You're creating a brand new, and therefore empty, DataSet and then immediately calling Update to save the changes it contains. It can't contain any changes if it doesn't contain anything at all. The sequence goes like this:

1. Create your data access objects, e.g. connection, adapter, etc.
2. Retrieve the data from the database, e.g. call Fill on a DataAdapter to populate a DataTable.
3. Edit the data, e.g. through a bound DataGridView.
4. Save the changes, e.g. call Update on the same DataAdapter.

If Fill gets the data and Update saves it, obviously Fill must come before Update. Also, there must be some separation between them, i.e. some time to edit the data.
 

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
jmcilhinney,

Thanks for the advice. I will give it a try. Regarding the 3rd step you've suggested, what if I'm not using databinding?
 
Top Bottom