Error with Parameter: the sqlparametercollection only accepts non-null sqlparameter type objects not sqlparameter[] objects

Question

I have the below code and I am facing a hard time with the parameter... I'm getting the below error:

the sqlparametercollection only accepts non-null sqlparameter type objects not sqlparameter[] objects

When I insert a msgbox to check the value of param(0).value I get MARC as an example.

Should I create a parameter for every rowData.item(i) too?

Dim con As New SqlConnection
Dim cmd As New SqlCommand
con.ConnectionString = "Data Source=USER\SQLEXPRESS;Initial Catalog=DBName;Integrated Security=SSPI"
con.Open()
cmd.Connection = con
Try
Dim InsertCommand As New SqlCommand
Dim param(0) As SqlClient.SqlParameter
param(0) = New SqlParameter("abrev", SqlDbType.VarChar, 50)
param(0).Value = UCase(abrev)

InsertCommand.Parameters.Add(param)

cmd.CommandText = "INSERT INTO allData (ABRV, Times, Num1, Num2, Num3, Num4, Num5) VALUES (param,rowData.Item(0),rowData.Item(1),rowData.Item(2),rowData.Item(3),rowData.Item(4),rowData.Item(5))"
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
                Finally
                    con.Close()
                End Try

Khanh TO · Accepted Answer · 2013-06-15 12:53:14Z

1

Check that you're using 2 different variables: InsertCommand and cmd

InsertCommand.Parameters.Add(param)

cmd.CommandText = "INSERT INTO allData (ABRV, Times, Num1, Num2, Num3, Num4, Num5) VALUES (param,rowData.Item(0),rowData.Item(1),rowData.Item(2),rowData.Item(3),rowData.Item(4),rowData.Item(5))"
cmd.ExecuteNonQuery()

Another problem is the parameter does not match. You use the parameter named abrev

param(0) = New SqlParameter("abrev", SqlDbType.VarChar, 50)

In your insert statement, you should have the parameter @abrev

cmd.CommandText = "INSERT INTO allData (ABRV, Times, Num1, Num2, Num3, Num4, Num5) VALUES (@abrev,rowData.Item(0),rowData.Item(1),rowData.Item(2),rowData.Item(3),rowData.Item(4),rowData.Item(5))"

Should i create a parameter for every rowData.item(i) too ?

Yes, you should. Do it similarly to the abrev parameter. Hope you get it work soon.

edited Jun 15, 2013 at 12:53

answered Jun 15, 2013 at 12:47

Khanh TO

49k13 gold badges101 silver badges117 bronze badges

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

2 Comments

Marc Chemali Over a year ago

You pointed it out to me... I fixed it with the below code:

Dim InsertCommand As New SqlCommand cmd.Parameters.Add("@abrev", SqlDbType.VarChar).Value = abrev cmd.CommandText = "INSERT INTO allData "INSERT INTO allData (ABRV, Times, Num1, Num2, Num3, Num4, Num5) VALUES (@abrev,rowData.Item(0),rowData.Item(1),rowData.Item(2),rowData.Item(3),rowData.Item(4),rowData.Item(5))" cmd.ExecuteNonQuery()

Marc Chemali Over a year ago

Creating now the parameters for rowdata.item(i), Thanks a lot !

Collectives™ on Stack Overflow

Error with Parameter: the sqlparametercollection only accepts non-null sqlparameter type objects not sqlparameter[] objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related