Parameterized OleDB insert command for digital state

Discussion created by cjrancur on Jan 13, 2010
Latest reply on Jan 14, 2010 by andreas

I am trying to convert some code that worked with non-parameterized query language, to use a parameterized command in PIOleDB.  It works for non-digital tags.  For digital tags, the former non-parameterized command was:

'cmd.CommandText = "INSERT piarchive..picomp2 (tag, time, value, annotations) " & 
"VALUES ('" & TestResultsRow.Item("TagName").ToString & "', '" & PISampleDate & "',
    DIGCODE('" & strDIName & "','" & strDigitalSet & "'), " & strAnnotations & ")"

My current attempt at a parameterized command is not working.  How can I incorporate the DIGCODE function into parameterized SQL?  My plan for the next trial is to try this in 2 steps, first assigning a string variable to the result of the digcode function, and then setting that result equal to a varchar parameter.  I'm not sure if that will work, and I've been fiddling with this all day, so I thought I'd ask for some input from this forum.


The error message I receive says: System.Data.OleDb.OleDbException = {"Row insertion failed. Digital state code couldn't be converted to a digital state."}


Here's the code that generates the above error:

cmd.CommandText = "INSERT piarchive..picomp2 (tag,time,value,annotations) VALUES (?,?,?,?)"
cmd.Parameters.Add("@tag", OleDbType.VarChar).Value = TestResultsRow.Item("TagName").ToString
cmd.Parameters.Add("@time", OleDbType.Date).Value = PISampleDate
cmd.Parameters.Add("@value", OleDbType.VarChar).Value = "DIGCODE('" & strDIName & "','" & strDigitalSet & "')"
cmd.Parameters.Add("@annotations", OleDbType.VarChar).Value = strAnnotations