Question Need help about Paramaterized query...

black zero

Well-known member
Joined
Jan 15, 2009
Messages
53
Programming Experience
Beginner
Like.. I recently started to learn about it, having been using archaic sql syntax for a long time. Apologize too if this sub forum isn't the most appropriate place for this thread, kindly move it if it is.

Firstly, I've reread this topic
http://www.vbdotnetforums.com/database-general-discussion/18562-ins-outs-parameterized-queries.html

Still, not even understand the slightest part, so... need a guidance to learn this new thing for me.

VB.NET:
"INSERT INTO table1 VALUES(@param1, @param2, @param3)"

Assuming i have a table called 'Invoice'
And it has three columns - Invoice_number, invoice_date, total

And three textboxes on a form.

So the syntax would run like this?

VB.NET:
"INSERT INTO Invoice VALUES(@textbox1.text, @textbox2.text, @textbox3.text)"

Again, seems silly, but I really have no idea how it's working. Kindly explain it.
 
First up, after 47 posts we shouldn't have to ask you to post in the most appropriate forum for the topic of your thread. There is more than one forum that you could have posted to but VS.NET General is obviously not one of them. Thread moved.

As for the question, no that's not how the syntax goes. The syntax goes like this:
VB.NET:
Dim command As New SqlCommand("INSERT INTO table1 VALUES(@param1, @param2, @param3)", connection)

command.Parameters.AddWithValue("@param1", TextBox1.Text)
command.Parameters.AddWithValue("@param2", TextBox2.Text)
command.Parameters.AddWithValue("@param3", TextBox3.Text)
Think of the SQL code like a method declaration. When you declare a method you name the parameters, right, but those names are not the values. The values are substituted in for the parameters when the method is called. SQL/ADO.NET parameters are the same. You write the SQL code with a place-holder in each spot where you want a value to be inserted. You then add a parameter to the command for each place-holder in the SQL code. You specify the same name as is in the SQL code and you provide the value. When the SQL code is executed the value that you provided gets substituted for the name in the SQL code.
 
Sorry >.<
I am having problem about post placement. Will do better in the future.

And thanks for the answer, I'll read carefully your post, and will post again once I encounter a problem.
 
ok I've tried for weeks, but still can't get it right...

So, I created this sub

Private Sub saveLog(ByVal eventID As Integer, ByVal dateTime As String, ByVal userName As String, ByVal eventLog As String)

Dim sqlExecute As New SqlCommand("INSERT INTO LOG (eventId,date_time,username,eventAction) VALUES (@MyId, @myDate, @myUser, @myEvent)", con)

sqlExecute.Parameters.AddWithValue("@MyId", eventID)
sqlExecute.Parameters.AddWithValue("@myDate", dateTime)
sqlExecute.Parameters.AddWithValue("@myUser", userName)
sqlExecute.Parameters.AddWithValue("@myEvent", eventLog)

End Sub

And used this to fill the values

saveLog(TxtIdStock.Text, Date.Now, user, "save data harga solar")

When I debugged it, the values were inserted to the saveLog sub, and passed to the respective addwithvalue. However, the values weren't saved to the database.

Any pointer? I'm stuck...
 
Last edited:
All you're doing there is creating the command. That's no use if you don't execute it. Any ADO.NET tutorial will show you how to execute commands in various ways. cjard's thread was specifically about how to build the command using parameters instead of string concatenation, which is only part of the overall process. If you have used ADO.NET before, as it sounds like you have, then you already know how to execute a command. The fact that it was built using parameters instead of string concatenation makes no difference to that part.
 
Private Sub saveLog(ByVal eventID As Integer, ByVal dateTime As String, ByVal userName As String, ByVal eventLog As String)

Dim sqlExecute As New SqlCommand("INSERT INTO LOG (eventId,date_time,username,eventAction) VALUES (@MyId, @myDate, @myUser, @myEvent)", con)

sqlExecute.Parameters.AddWithValue("@MyId", eventID)
sqlExecute.Parameters.AddWithValue("@myDate", dateTime)
sqlExecute.Parameters.AddWithValue("@myUser", userName)
sqlExecute.Parameters.AddWithValue("@myEvent", eventLog)

End Sub


ah... just realized it after I read your post

I forgot this

sqlExecute.ExecuteNonQuery()

Now it's working :D

Thanks jmc!!!
YAYYYY my first!
 
Back
Top