Specifying function inside insert statement in OleDbCommand

Question

We have legacy data which is stored in MSSQL database as varchar, even though the data is really binary. In new application we're using OleDbCommand (because we are trying to use common code to access MSSQL+Oracle+Sybase) databases.

I am able to retrieve data from database by performing:

    DA.SelectCommand.CommandText = "select 
sequence_num, summary_num, line_num, CONVERT(varbinary, other_information) other_information 
from alex_test_bin";

However the insert command:

   DA.InsertCommand.CommandText = "insert into alex2_test_bin 
(sequence_num, summary_num, line_num, other_information) 
values(?, ?, ?, CONVERT(varchar, ?))";

throws this error (it happens during 'DA.Update(DT);'):

No value given for one or more required parameters.

Currently I'm just trying to read data from alex_test_bin and insert the data into alex2_test_bin. In between reading and writing, I'm using method 'SetAdded()' on each row from DT, so that all of those rows get inserted.

Is my insert statement wrong? Is there a way to correct it, and be able to use functions inside insert statement? I successfully ran the insert statement manually from sql manager.

There is nothing wrong with your `INSERT` syntax, as you verified yourself in SSMS. Have you checked the parameter values, as suggested by the error message? See [this question](http://stackoverflow.com/questions/2378763/no-value-given-for-one-or-more-required-parameters), for example. — Pondlife, Oct 03 '12 at 14:43

Lynn Crumbling · Accepted Answer · 2012-10-03T15:29:37.640

1

Without seeing your full code, it appears that you simply aren't adding the parameters to the DA.InsertCommand.

For each parameter denoted with a ?, you'll need to append an OleDbParameter:

DA.InsertCommand.Parameters.Parameters.Add("@sequence_num", OleDbType.Integer).Value = 42

You need to append these to the command in the same order that they are specified in the insert into statement.

edited Oct 03 '12 at 15:29

answered Oct 03 '12 at 14:41

Lynn Crumbling

12,985
8
57
95

How a function can be called inside a insert statement e.g. insert into my table values (myfunction, 1,2,3)? – bjan Nov 21 '12 at 13:40
@bjan Post this as a new question, and you'll probably get some answers. – Lynn Crumbling Nov 21 '12 at 13:42
http://stackoverflow.com/questions/13494743/how-a-function-procedure-can-be-called-inside-a-insert-statement – bjan Nov 21 '12 at 13:50

Specifying function inside insert statement in OleDbCommand

1 Answers1