insert command OLE parameterized

Question

CURRENT PROGRESS TOWARD SOLUTION:

This is the updated code:

sql = "INSERT INTO Strings (String_Name, Long_Text, Short_Text, Alternate_Text, Multi_String_ID, Lang_ID) VALUES (?,?, ?,?,?,?)"

Dim command = New OleDbCommand(sql, pConn)

command.Parameters.AddWithValue("@webStringName", "String_Name")
command.Parameters.AddWithValue("@webLang_String", "Long_Text")
command.Parameters.AddWithValue("@ptsShortText", "Short_Text")
command.Parameters.AddWithValue("@webAltText", "Alternate_Text")
command.Parameters.AddWithValue("@webMultiStringID", "Multi_String_ID")

Changed above line to this... command.Parameters.AddWithValue("@webMultiStringID", OleDbType.Integer).Value = webMultiStringID

command.Parameters.AddWithValue("@webLang_Code", "Lang_ID")

command.ExecuteNonQuery()

ORIG POST:

I am trying to create and INSERT statement with and OLE adapter. I had it working without paramtersm but now I am trying to add parms and am having issues with the syntax. Ha

Here is the code so far...

command = New OleDbCommand(sql, pConn)

sql = "INSERT INTO Strings (String_Name, Long_Text, Short_Text) VALUES (?,?, """ & ptsShortText & """)"


command.Parameters.Add("@webStringName", OleDbType.VarChar, 250, "String_Name")
command.Parameters.Add("@webLang_String", OleDbType.VarChar, 250, "Long_Text")

command = New OleDbCommand(sql, pConn)
command.ExecuteNonQuery()

Was just trying to get the first two variables parameterized.

Any suggestions would be greatly appreciated.

EDIT: Fixed SQL

Why not go all the way and parameterize all three parameters... — Meta-Knight
– Meta-Knight, Commented Feb 6, 2012 at 17:50

Heinzi · Accepted Answer · 2012-02-06 17:46:36Z

2

You are doing something wrong. You first add the parameters:

command.Parameters.Add("@webLang_String", OleDbType.VarChar, 250, "Long_Text")

and then replace the command with a new one (with no parameters):

command = New OleDbCommand(sql, pConn)

Thus, you remove all your existing parameters in this line. That's why it doesn't work.

You have to do it in the correct order:

sql = "INSERT INTO Strings (String_Name, Long_Text, Short_Text) " & _
      "VALUES (?,?, """ & ptsShortText & """)"

Dim command = New OleDbCommand(sql, pConn)  ' First create the command

' Then add the parameters to the command
command.Parameters.AddWithValue("@webStringName", "String_Name")
command.Parameters.AddWithValue("@webLang_String", "Long_Text")

' Then execute the command. (DON'T recreate it with New OleDbCommand here,
' that would throw away all you've done so far.)
command.ExecuteNonQuery()

edited Feb 6, 2012 at 17:46

answered Feb 6, 2012 at 17:39

Heinzi

173k61 gold badges386 silver badges555 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Heinzi Over a year ago

@marc11h: Yes, and I'm trying to tell you what you did wrong and why it doesn't work. ;-) I've expanded my answer to show you a working solution.

htm11h Over a year ago

I am getting only one error msg now: Data type mismatch in criteria expression. There is only one integer in the list. I will update my original code.

Heinzi Over a year ago

@marc11h: If you declare a parameter as an Integer, you cannot assign a String to it (at least not without converting the string into an integer first). Maybe it would be easiest if you specified in your question: (a) what data types your database fields have and (b) what .NET data types your variables (e.g. webMultiStringID) have. The data types must match.

htm11h Over a year ago

I fixed it, thank you for your help. I had to assigned the value to the variables. I update my post to reflect the fix.

Collectives™ on Stack Overflow

insert command OLE parameterized

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related