error on insert statement

[tariqondego]

hi,
i would like to assign the value of a combobox to a variable and use this variable as a parameter on the insert statement.see code below,code 1 works ok,but code 2 brings an error relating to wrong datatype,i cant seem to find where the problem because i think i am doing everything right.

1.

 CmdinsertSite = New SqlCommand("insert into SiteStock(serialno,partno,technicianID,dispatcherID,ATMID,DateDispatched) values (@serialno,@partno,@technicianID,@dispatcherID,@ATMID,@DateDispatched)", AddConn)
        CmdinsertSite.Parameters.AddWithValue("@serialno", cbSARserialused1.SelectedValue)
        CmdinsertSite.Parameters.AddWithValue("@partno", cbSARpartused1.SelectedValue)
        CmdinsertSite.Parameters.Add("@technicianID", SqlDbType.VarChar).Value = cbSARtech.SelectedValue
        CmdinsertSite.Parameters.Add("@dispatcherID", SqlDbType.VarChar).Value = cbSARdispatcher.SelectedValue
        CmdinsertSite.Parameters.Add("@ATMID", SqlDbType.VarChar).Value = cbSARatm.SelectedValue
        CmdinsertSite.Parameters.Add("@DateDispatched", SqlDbType.SmallDateTime).Value = dtpSARdispatchdate.Text

         CmdinsertSite.ExecuteNonQuery()

2.

CmdinsertSite = New SqlCommand("insert into SiteStock(serialno,partno,technicianID,dispatcherID,ATMID,DateDispatched) values (@serialno,@partno,@technicianID,@dispatcherID,@ATMID,@DateDispatched)", AddConn)
        CmdinsertSite.Parameters.AddWithValue("@serialno", serialno)
        CmdinsertSite.Parameters.AddWithValue("@partno", partno)
        CmdinsertSite.Parameters.Add("@technicianID", SqlDbType.VarChar).Value = cbSARtech.SelectedValue
       CmdinsertSite.Parameters.Add("@dispatcherID", SqlDbType.VarChar).Value = cbSARdispatcher.SelectedValue
     CmdinsertSite.Parameters.Add("@ATMID", SqlDbType.VarChar).Value = cbSARatm.SelectedValue

     CmdinsertSite.Parameters.Add("@DateDispatched", SqlDbType.SmallDateTime).Value = dtpSARdispatchdate.Text

        dim partno as integer = cbSARpartused1.SelectedValue
         dim serialno as integer = cbSARserialused1.SelectedValue

            CmdinsertSite.ExecuteNonQuery()

please note that columns 'Serialno' and 'Partno' in table 'SiteStock' are both integer datatype.
code 2 brings error:
"The INSERT statement conflicted with the FOREIGN KEY constraint "FK__SiteStock__Seria__1367E606". The conflict occurred in database "ISIS", table "dbo.Stock", column 'SerialNo'."

i would like code 2 to work so that i can assign the variables different values at different scenarios.

column serialno is a foreign key from table Stock.

Please assist.

[M.Waqas Aslam]

this error comes when we try to insert the FK value which is not in it Parent table.

[tariqondego]

that was my first thought but its not the case.when i use code 1 its works perfectly,its only when i use variables in code 2 that the error comes up.the corresponding PK values in the parent table do exist,i have counter-checked on this many times..i can even insert the values in the childtable in sql server itself and it works ok..

any more ideas?

[G_Waddell]

Are you sure the combo boxes are returning integers? I can't remember if it is SQL Server or the Sql Native client, but it will try and convert something it doesn't recognise as an integer to one and I wouldn't rely on it.
Try something like this where you ensure they are integers first:

 dim partno as integer 
 dim serialno as integer


 if isnumeric(cbSARpartUsed1.SelectedValue) andalso isnumeric(cbSARserialUsed1.SelectedValue) then
     partno = cint(cbSARpartUsed1.SelectedValue)
     serialno = cint(cbSARpartUsed1.SelectedValue)   
     CmdinsertSite = New SqlCommand("insert into SiteStock(serialno,partno,technicianID,dispatcherID,ATMID,DateDispatched) values (@serialno,@partno,@technicianID,@dispatcherID,@ATMID,@DateDispatched)", AddConn)
     CmdinsertSite.Parameters.AddWithValue("@serialno", serialno)
     CmdinsertSite.Parameters.AddWithValue("@partno", partno)
     CmdinsertSite.Parameters.Add("@technicianID", SqlDbType.VarChar).Value = cbSARtech.SelectedValue
     CmdinsertSite.Parameters.Add("@dispatcherID", SqlDbType.VarChar).Value = cbSARdispatcher.SelectedValue
     CmdinsertSite.Parameters.Add("@ATMID", SqlDbType.VarChar).Value = cbSARatm.SelectedValue
     CmdinsertSite.Parameters.Add("@DateDispatched", SqlDbType.SmallDateTime).Value = dtpSARdispatchdate.Text
     CmdinsertSite.ExecuteNonQuery
Else
    msgbox("Invalid Values selected")
End if   

[tariqondego]

still same error,any more ideas?

[lolafuertes]

Maybe an old style can be used here, some thing like

CmdinsertSite = New SqlCommand(String.Format( "insert into SiteStock(serialno,partno,technicianID,dispatcherID,ATMID,DateDispatched) values ({0},{1}, {2},{3},{4},'{5:yyyy-MM-dd}')", serialno, partno, cbSARtech.SelectedValue, cbSARdispatcher.SelectedValue, cbSARatm.SelectedValue, Ctype(dtpSARdispatchdate.Text,Date) ), AddConn)

If any of the values is not numeric, you must surround the {} with '. IE: if the third parameter was string then '{2}'.
I would suggest that Dates are to be presented to SQL always in universal format, so no mistake between 6/7/2012 being June 7th or or July 6th 2012.
Also if a parameter is string, you should use the string replace function to replace each single ' with two consecituve ones in the string to avoid the code injection.

Hoipe this helps

[tariqondego]

i found where the issue might be,on debug mode cmdinsertinsert has the serialno=0 and partno=0.this means the variables are passed as 0's to the command.i wonder why this happens because when i view the values of partno and serialno still under debug they have been assigned the respective values.

CmdinsertSite = New SqlCommand(String.Format("insert into SiteStock(serialno,partno,technicianID,dispatcherID,ATMID,DateDispatched) values({0},{1},'{2}','{3}','{4}','{5:yyyy-MM-dd}')", serialno, partno, cbSARtech.SelectedValue, cbSARdispatcher.SelectedValue, cbSARatm.SelectedValue, CType(dtpSARdispatchdate.Text, Date)), AddConn)


partno=cbSARpartused1.selectedvalue
serialno=cbSARserialused1.selectedvalue
CmdinsertSite.ExecuteNonQuery()

in debug partno=12345678 and serialno=87654321 but CmdinsertSite has partno=0 and serialno=0.

any ideas?

[tariqondego]

still havent found a solution to this...any ideas?

[G_Waddell]

You look like you are setting the partno and the serialno after you have generated your SQL Insert string.... That is why you could be getting zero values

Try the following:

partno = cint(cbSARpartUsed1.selectedvalue)
serialNo = cint(cbSARserialused1.selectedvalue)

cmdInsertSite = New sqlCommand(String.Format("Insert Into SiteStock(serialno,partno,technicianID, dispatcherId,ATMID,DateDispatched) Values({0},{1},'{2}','{3}','{4}','{5:yyyy-MM-dd}')", serialno, partno, cbSARtech.SelectedValue, Ctype(dtpSARdispatchdate.Text, Date)), AddConn)

cmdInsertSite.ExecuteNonQuery

[tariqondego]

thanx,issue solved