Setting parameters for an Insert 1

[qwert231]

Okay, here is what I have:

cmd.CommandText = "INSERT INTO someTable (OrderID, OrigOrder,CustID,ProgID,CustRef) values (?,?,?,?,?)"
Line 115:  cmd.Parameters(0).Value = nowJob.Text
Line 116:  cmd.Parameters(1).Value = oldJob.Text
Line 117:  cmd.Parameters(2).Value = Request.Cookies("id").Value()

But I get an error:
System.IndexOutOfRangeException: Invalid index 0 for this OleDbParameterCollection with Count=0.

I figure this is a simple Faux Pau on my part... coffee... not enough... need to get to blood stream!..

 

[Custom24]

Hi
You need to add the parameters to the collection - you've not done that.
You can do this either manually using the add method. You can then reference them by a string name rather than just ordinal position
Choose whichever overload suits you
cmd.parameters.add....

or there is a way to generate the parameters collection automatically at run time using the command builder object, I think

If this is not enough, let me know
Mark

 

[qwert231]

Did this:

Dim cmd As New OleDb.OleDbCommand()
  cmd.CommandText = "INSERT INTO MyTable (OrderID, OrigOrder,CustID,CustRef) values (?,?,?,?)"
  cmd.Parameters.Add("@OrderID", OleDb.OleDbType.VarChar).Value = nowJob.Text
  cmd.Parameters.Add("@OrigOrder", OleDb.OleDbType.VarChar).Value = oldJob.Text
  cmd.Parameters.Add("@CustID", OleDb.OleDbType.VarChar).Value = Request.Cookies("id").Value()
  cmd.Parameters.Add("@CustRef", OleDb.OleDbType.VarChar).Value = custRef.Text

Thanks

 

[qwert231]

K, I got this:

Dim objConn As New OleDb.OleDbConnection(dbAccess)
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = objConn
cmd.CommandText = "INSERT INTO tblLongRollOrders (OrderID,OrigOrder,CustID,ProgID,CustRef,Date) values (?,?,?,?,?,?)"
cmd.Parameters.Add("@OrderID", OleDb.OleDbType.VarChar).Value = nowJob.Text
cmd.Parameters.Add("@OrigOrder", OleDb.OleDbType.VarChar).Value = oldJob.Text
cmd.Parameters.Add("@CustID", OleDb.OleDbType.VarChar).Value = Session("id")
cmd.Parameters.Add("@ProgID", OleDb.OleDbType.VarChar).Value = progNum.SelectedItem.Value.ToString.Trim
cmd.Parameters.Add("@CustRef", OleDb.OleDbType.VarChar).Value = custRef.Text
cmd.Parameters.Add("@Date", OleDb.OleDbType.VarChar).Value = DateTime.Now.ToLongDateString

errMsg.Text = errMsg.Text & cmd.CommandText.ToString() & &quot;<br />&quot; 'This line gives me ??????
objConn.Open()
cmd.ExecuteNonQuery()
objConn.Close()

It says there is a syntax error. HOW CAN I SEE WHAT IT IS TRYING TO SUBMIT? I want to see the what it is trying to enter into the Database... I can't! I am frustrated...

 

[Zarcom]

stop using ???? I don't think VS likes it. An Access thing as far as I know
You need to say which parameter goes where.


cmd.CommandText = "INSERT INTO tblLongRollOrders (OrderID,OrigOrder,CustID,ProgID,CustRef,Date) values (@OrderID, @OrigOrder, @CustID, @ProgID, @CustRef, @Date)"


I think that should fix it.
That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[qwert231]

Well... this Insert is an Access one, and I got it to work without the Date... so I gotta figure out what it doesn't like with that, tho I think it's the commas that ToLongDateString puts in there.

 

[Zarcom]

Could be though I consider it good practice to include the names. I try to be explicit rather than implicit in my coding.

Anyway, it is an Access database but you are connecting using Access your using oledb right?

my thoughts That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[qwert231]

So, here's my code. I agree, it is better to be specific...

Dim objConn As New OleDb.OleDbConnection(dbAccess) 'A public variable.
  Dim cmd As New OleDb.OleDbCommand()
  cmd.Connection = objConn
  cmd.CommandText = "INSERT INTO tblLongRollOrders (OrderID,OrigOrder,CustID,ProgID,CustRef, Date) values (@OrderID,@OrigOrder,@CustID,@ProgID,@CustRef,@Date)"
  cmd.Parameters.Add("@OrderID", OleDb.OleDbType.VarChar).Value = nowJob.Text
  cmd.Parameters.Add("@OrigOrder", OleDb.OleDbType.VarChar).Value = oldJob.Text
  cmd.Parameters.Add("@CustID", OleDb.OleDbType.VarChar).Value = Session("id")
  cmd.Parameters.Add("@ProgID", OleDb.OleDbType.VarChar).Value = progNum.SelectedItem.Value.ToString.Trim
  cmd.Parameters.Add("@CustRef", OleDb.OleDbType.VarChar).Value = custRef.Text
  cmd.Parameters.Add("@Date", OleDb.OleDbType.DBTimeStamp).Value = DateTime.Now.Date.ToString

  errMsg.Text = errMsg.Text & &quot;'&quot; & DateTime.Now.Date.ToString & &quot;'<br />&quot;
  objConn.Open()
  cmd.ExecuteNonQuery()
  objConn.Close()

Still get an error with the Date... Any ideas?

 

[Zarcom]

What is the error That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[qwert231]

Nevermind, thanks tho.
I changed this:

cmd.Parameters.Add("@Date", OleDb.OleDbType.DBTimeStamp).Value = DateTime.Now.Date.ToString

To this:

cmd.Parameters.Add("@Date", OleDb.OleDbType.DBDate).Value = DateTime.Now

 

[Custom24]

Hi
Sorry - I've been away.
The ? placeholder is the correct one for oledb
You might want to check out my post in thread855-379869

This shows you how to build the command object and params collection visually. But also, you can look at the code to see what it has done. The overload that the vs designer chooses in your case for the add method would look like this

cmd.Parameters.Add("Date", OleDb.OleDbType.DBDate, 1, "Date&quot

The first string "Date" is the name of the parameter. The second is the name of the column it refers to. In your case, these could be the same, or you could stick to your "@Date" for the name of the parameter.

In the SQL string, though, by default, ? is the placeholder used for oledb.

Finally, I agree that troubleshooting with parameters is not as easy as it is with straightforward SQL strings, but check out the post in the thread for the advantages I listed.

Hope this helps.
Mark