-
Oct 19th, 2010, 04:06 AM
#1
Thread Starter
Lively Member
[RESOLVED] update syntax
this is the first time I'm using this... -_-
I'm getting syntax error near where. What's wrong with my code?
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
-
Oct 19th, 2010, 04:25 AM
#2
Re: update syntax
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.
-
Oct 19th, 2010, 04:38 AM
#3
Thread Starter
Lively Member
Re: update syntax
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.
-
Oct 19th, 2010, 05:07 AM
#4
Re: update syntax
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.
-
Oct 19th, 2010, 05:18 AM
#5
Thread Starter
Lively Member
Re: update syntax
Code:
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?
-
Oct 19th, 2010, 05:59 AM
#6
Re: update syntax
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.
-
Oct 19th, 2010, 06:37 AM
#7
Thread Starter
Lively Member
Re: update syntax
Thanks for teaching me this.
Alright I tried it again and added few things so it's like this:
vb Code:
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?
vb Code:
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)
-
Oct 19th, 2010, 07:14 AM
#8
Re: update syntax
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.
-
Oct 19th, 2010, 07:47 AM
#9
Thread Starter
Lively Member
Re: update syntax
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.
-
Oct 19th, 2010, 06:03 PM
#10
Re: update syntax
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:
vb.net Code:
UpdateCmd.Parameters.Add("@supplier_code", SqlDbType.NVarChar, 30).Value = txtcode.Text
As you can see, it's more complex than AddWithValue for no gain.
-
Oct 19th, 2010, 06:08 PM
#11
Thread Starter
Lively Member
Re: update syntax
Thanks for the insight, I learned something valuable today.
Much appreciated!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|