SQL Update

[computermat]

Hi, im writing an application and have hit a snag with SQL. This is probably a noob question but how do you use the UPDATE function to save data to the database. I have tried adding data to my database in a deployed application but the data does not get committed. I am using a binding navigator with the stanard add new record button but when I close the app the data is lost. Am i on the right lines with the following code.

 UPDATE    Customers
SET
Title = @title,
Initial = @int,
Surname = @surname,
[House No] = @house,
[Street Name] = @street,
[Address Line 2] = @add2,
Town = @town,
County = @county,
[Post Code] = @post,
Telephone = @phone,
WHERE     ([Customer ID] = @customerID)

If im barking up the wrong tree here can someone point me in the right direction.

 

[computermat]

##Urgently Need Help##

Hi, after getting very fustrated with my database not updating i really need some help from someone who knows what they are doing with SQL. I have red cjards DNU, but this doesn't really answer what i need.

I have a dedicated SQL server and this is where my database i stored. I have got the connections working 'ok' so i can view all the data already saved on it.

My problem is that when i want to update any changes to the database i am using the standard binding navigator + button typing in the new data then closing the form. If i am correct does this just edit the local copy of the data saved in the dataset memory, if so the code in the above post does not send the data to the SQL Server.

Can someone tell me how i can code it so it sends the changes back to the database. I have tried using

 me.TO_CUSTOMERS_FULL.AcceptChanges

but have had no joy.

Thanks in advance for replies

 

[Canopy High]

Do you have a DataAdapter and tried this:

DataAdapter.Update(DatasetName, TableName)

I will post my code to give you an idea.

Public Function SaveRecord(ByVal dataset As DataSet, ByVal sqlStatement As String, ByVal tableName As String) As Boolean
        Dim connection As SqlConnection = New SqlConnection("Your Connection String")
        Dim dataAdapter As New SqlDataAdapter(sqlStatement, connection)
        Dim command As New SqlCommandBuilder(dataAdapter)

        Try
            If dataset.HasChanges Then
                dataAdapter.Update(dataset, tableName)
                dataset.AcceptChanges()
            End If
            dataAdapter.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error Saving Record", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End Try
        Return True
    End Function

Hope this one will help you.

 

[computermat]

Hi, i have tried your code and im gettin a problems with my SQL connection

SQL Database Location: "JNKSRV1\JNKSQL"
Database Name: "Tour Operator"
Table Name: "Customers"

I want to use the following to logon to my sql server in the connection string. (copied as pasted this from my dataset connection)

 Dim connection As SqlConnection = New SqlConnection("JNKSRV1\JNKSQL;Initial Catalog = "Tour Operator"; Persist Security Info=True;User ID=user;Password=pass")

Error - Argument exception was unhandled - Keyword not supported: 'jnksrv1\jnksql;persist security info'

Anyone know how i can resolve this tiny issue

 

[TechGnome]

Your connectionstring is botched. 1) Missing the Server keyword, 2) you've got quotes in the middle of the string.

try www.connectionstrings.com for the correct format.

-tg

 

[Canopy High]

Try this.

Dim connection As SqlConnection = New SqlConnection("Data Source = JNKSRV1\JNKSQL;.....")

 

[cjard]

If im barking up the wrong tree here can someone point me in the right direction.

Totally the wrong tree, i'm afraid. Youre embarking on the process of learning the very old way of using databases, like we did before .NET 2.0 came along.. At least youre parameterising your statements which is a great start..

Read the DW2 link in my sig, start with "creating a simple data app" and go from there.. You'll learn a very smart, sensible way of data access that take you away from setting up db conenctions manually and tediously interacting with them

 

[computermat]

Hi, thanks for the replys here. I have managed to sort out my problem with help form MSDN.

This post i found as answer two got it working straight away.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=85470&SiteID=1

This is the working code i now use

        Dim connection As SqlConnection = New SqlConnection("Data Source=JNKSRV1\JNKSQL;Initial Catalog=Tour_Operator;Persist Security Info=True;User ID=userid;Password=password")


        Try
            If Me.TO_CUSTOMERS_FULL.HasChanges Then
                connection.Open()
                CustomersTableAdapter.Update(Me.TO_CUSTOMERS_FULL)
                Me.TO_CUSTOMERS_FULL.AcceptChanges()
                MsgBox("Changes saved to the sql database server")
                connection.Close()
            Else
                MsgBox("Error, Data not saved to SQL Server")
            End If
            CustomersTableAdapter.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error Saving Record", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

Thanks for posting

 

[cjard]

you dont need to acceptchanges() after update() - update() performs acceptchanges for you. you also dont need the code that creates or opens the connection - the tableadapters manage their own connections (comment it out and your code will still work) and you dont need to dispose of the customers tableadapter after youre done, becaquse youll only have to make it again. youre suppoosed to leave the dataset and tableadapters in place, have the TAs fill the datatables in the set, use and edit the data and use the same TA to send the data back; if youre constantly creating and destroying them all the time, that's like re-plumbing your house every time you swithc the central heating on, and ripping it out every time you switch it off

you might additionally want to check that the form's validate() has been called and that any bindingsources in use have had endedit called, otherwise the most recent edits made might not appear in the dataset. see the DW2 link in my sig, do the simple example (it will literally take about 10 minutes) and shows you a lot of advanced stuff; relationships, loading and saving data, table adapter creation and best practices. If you follow it, learn it and do your data access that way, you will never encounter problems like this again