Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have the below code and I am facing a hard time with the parameter... I get 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 ?

Help needed :) thank you

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
share|improve this question

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
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 28 mins ago
Creating now the parameters for rowdata.item(i), Thanks a lot ! – Marc Chemali 25 mins ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.