[RESOLVED] update syntax

[Black Zero]

this is the first time I'm using this... -_-

I'm getting syntax error near where. What's wrong with my code?

Public Sub updateSupplier(ByVal supplier_code As String, ByVal supplier_name As String, ByVal supplier_address As String, ByVal con As SqlConnection)
        Try
            Dim updateSql As String = "UPDATE SupplierATK " & _
                   "SET Kode_Supplier = @supplier_code," & _
                   "Nama_supplier= @supplier_name," & _
                   "alamat_supplier= @supplier_address," & _
                   "WHERE kode_supplier = " & Val(txtcode.Text)

            Dim UpdateCmd As New SqlCommand(updateSql, con)

            UpdateCmd.Parameters.Add("@supplier_code", SqlDbType.NVarChar, 30, txtcode.Text)
            UpdateCmd.Parameters.Add("@supplier_name", SqlDbType.NVarChar, 30, txtname.Text)
            UpdateCmd.Parameters.Add("@supplier_address", SqlDbType.NVarChar, 50, txtaddress.Text)

            UpdateCmd.ExecuteNonQuery()

            MsgBox("Updated!", vbInformation)

            bersih()
            status_btn(0)
            isiDGrid()
        Catch ex As Exception
            MsgBox(ex.ToString)
            bersih()
            status_btn(1)
        End Try

    End Sub

[jmcilhinney]

This is a SQL question, not a VB.NET question, so it belongs in the Database Development forum.

The issue is that you have a comma before the WHERE keyword. You separate the assignments in the SET clause with commas but you don't put one after the last assignment.

[Black Zero]

I'm sorry but I really have no idea where to put another comma... I've been looking at the code for an hour already.

[jmcilhinney]

I didn't say that you need to add a comma. You need to REMOVE a comma; specifically the one before the WHERE clause. I find it difficult that you couldn't find an example of an UPDATE statement to compare.

[Black Zero]

Dim updateSql As String = "UPDATE SupplierATK" & _
                   "SET Kode_Supplier = @supplier_code," & _
                   "Nama_supplier= @supplier_name," & _
                   "alamat_supplier= @supplier_address" & _
                   "WHERE kode_supplier =" &val( txtcode.Text)

Thanks I omitted that comma.
...now incorrect syntax near Kode_Supplier?

[jmcilhinney]

You are building a string. It's not doing what it's supposed to. Have you actually looked at the string? I'll wager not.

[Black Zero]

Thanks for teaching me this.

Alright I tried it again and added few things so it's like this:

Dim updateSql As String = "UPDATE SupplierATK " & _
                   "SET Kode_Supplier = @supplier_code," & _
                   "Nama_supplier= @supplier_name," & _
                   "alamat_supplier= @supplier_address " & _
                   "WHERE kode_supplier =" & txtcode.Text

So far no the same error code.!

Now it gives me,

The parameterized query '(@supplier_code nvarchar(30),@supplier_name nvarchar(30),@suppli' expects the parameter '@supplier_code', which was not supplied.



But I gave the parameter already, didn't I?

UpdateCmd.Parameters.Add("@supplier_code", SqlDbType.NVarChar, 30, txtcode.Text)
UpdateCmd.Parameters.Add("@supplier_name", SqlDbType.NVarChar, 30, txtname.Text)
UpdateCmd.Parameters.Add("@supplier_address", SqlDbType.NVarChar, 50, txtaddress.Text)

[jmcilhinney]

You are misusing that Add method. Follow the CodeBank link in my signature and check out my Retrieving & Saving Data thread. It shows you when and how to use the Add method and the AddWithValue method.

[Black Zero]

Thanks jm, I head over there and read your article, looked at addwithvalue and decided to try it

UpdateCmd.Parameters.AddWithValue("@supplier_code", txtcode.Text)
UpdateCmd.Parameters.AddWithValue("@supplier_name", txtname.Text)
UpdateCmd.Parameters.AddWithValue("@supplier_address", txtaddress.Text)

I found it's working correctly now.

But I still don't understand what I did wrong. I'm sorry but I had no programming background, your explanation there was too difficult for me to understand. This solution is like trial and error without understanding what was wrong exactly.

Google searches suggested it's (addwithvalue) about implicit declaration e.g like in my previous codes as opposed to add which is explicit declaration, but that's all I could get.

My previous question still stands, why didn't it acknowledge the parameter I passed on to the program?

Thanks for the help but kindly help me a bit more.

[jmcilhinney]

When you use the Add method as you did in post #7, the fourth argument is not the parameter value. It is the name of a DataColumn in a DataTable that the parameter value will be taken from. You only use that overload of Add when you are using a DataAdapter to save multiple records from the rows of a DataTable. You specify the column to get the parameter value from and then the DataAdapter loops through the rows and sets the Value of the parameter from that column for each row. If you wanted to use Add rather than AddWithValue in your case then it would look like this:

UpdateCmd.Parameters.Add("@supplier_code", SqlDbType.NVarChar, 30).Value = txtcode.Text

As you can see, it's more complex than AddWithValue for no gain.

[Black Zero]

Thanks for the insight, I learned something valuable today.

Much appreciated!