InsertCommand???

[Darryn Ross]

Hi,

I am running an insert statement into an access database, but i don't want
to add information to every column in my table... when i run my
insertcommand the following exception is thrown...

"Number of query values and Destination fields are not the same"...

How do i stop it from caring wether i insert a record for all my fields or
just one? and the fields that i am missing out are not required so i don't
understand the problem??

Any help would be appreciated.

Regards

Darryn

 

[Morten Wennevik]

Hi Darryn,

I suspect you aren't specifying which columns you are updating, so it expects you to pass it a value for all columns in the order they are in the table. Or, you are specifying columns, but do not pass the same number of values.

INSERT INTO SomeTable (Column1, Column2, Column3) VALUES ('Value1', 'Value2', 'Value3')

 

[Jon Skeet [C# MVP]]

I am running an insert statement into an access database, but i don't want
to add information to every column in my table... when i run my
insertcommand the following exception is thrown...

"Number of query values and Destination fields are not the same"...

How do i stop it from caring wether i insert a record for all my fields or
just one? and the fields that i am missing out are not required so i don't
understand the problem??

Please post the command you're using.

 

[Darryn Ross]

here is my command...

try {

//Building the insert command for the addition of new records

InsertDtlsCommand.CommandText = "INSERT INTO tblGLBatch VALUES (@TransNum,
@Source, @Type, @TDate," +

"@Reference, @Code, @Note, @GSTMode, @Debit, @Credit, @GST_Debit,
@GST_Credit," +

"@Amount)" ;


//Setting the connnection object for the update command

InsertDtlsCommand.Connection = JournalConnection ;

//For each field that will be getting a value added to it, there will need
to be a parameter

//added and filled in with the appropriate information

InsertDtlsCommand.Parameters.Add("@TransNum", OleDbType.Integer, 9,
"TransNum") ;

InsertDtlsCommand.Parameters["@TransNum"].Value = GetNextTransNum() ;

InsertDtlsCommand.Parameters.Add("@Source", OleDbType.VarChar, 14, "Source")
;

InsertDtlsCommand.Parameters["@Source"].Value = "General Ledger" ;

InsertDtlsCommand.Parameters.Add("@Type", OleDbType.Integer, 9, "Type") ;

InsertDtlsCommand.Parameters["@Type"].Value = 40 ;

InsertDtlsCommand.Parameters.Add("@TDate", OleDbType.Date, 10, "TDate") ;

InsertDtlsCommand.Parameters["@TDate"].Value = txtDate.Value ;

InsertDtlsCommand.Parameters.Add("@Reference", OleDbType.VarChar, 50,
"Reference") ;

InsertDtlsCommand.Parameters["@Reference"].Value = txtReference.Text ;

InsertDtlsCommand.Parameters.Add("@Code", OleDbType.VarChar, 9, "Code") ;

InsertDtlsCommand.Parameters["@Code"].Value = txtCode.Text ;

InsertDtlsCommand.Parameters.Add("@Note", OleDbType.VarChar, 250, "Note") ;

InsertDtlsCommand.Parameters["@Note"].Value = txtNote.Text ;

InsertDtlsCommand.Parameters.Add("@GSTMode", OleDbType.VarChar, 1,
"GSTMode") ;

InsertDtlsCommand.Parameters["@GSTMode"].Value =
txtTaxCode.Text.Substring(0, 1) ;


InsertDtlsCommand.Parameters.Add("@Debit", OleDbType.Double, 10, "Debit") ;

InsertDtlsCommand.Parameters["@Debit"].Value =
Convert.ToDouble(txtDebit.Text) ;

InsertDtlsCommand.Parameters.Add("@Credit", OleDbType.Double, 10, "Credit")
;

InsertDtlsCommand.Parameters["@Credit"].Value =
Convert.ToDouble(txtCredit.Text) ;

InsertDtlsCommand.Parameters.Add("@GST_Debit", OleDbType.Double, 10,
"GST_Debit") ;

InsertDtlsCommand.Parameters["@GST_Debit"].Value =
Convert.ToDouble(txtGSTDebit.Text) ;

InsertDtlsCommand.Parameters.Add("@GST_Credit", OleDbType.Double, 10,
"GST_Credit") ;

InsertDtlsCommand.Parameters["@GST_Credit"].Value =
Convert.ToDouble(txtGSTCredit.Text) ;

InsertDtlsCommand.Parameters.Add("@Amount", OleDbType.Double , 10, "Amount")
;

InsertDtlsCommand.Parameters["@Amount"].Value = 100.00 ;

//Allocating the insert command defined above to the JournalAdapter.

JournalAdapter.InsertCommand = InsertDtlsCommand ;

}

catch(Exception e) {

MessageBox.Show(e.Message, "JournalInsertCommand", MessageBoxButtons.OK,
MessageBoxIcon.Error) ;

}

There are two more fields in the database but i don't want to update them...
if i leave them out it has a shit?? if i put tham all in it works fine??

 

[Jon Skeet [C# MVP]]

here is my command...

try {

//Building the insert command for the addition of new records

InsertDtlsCommand.CommandText = "INSERT INTO tblGLBatch VALUES (@TransNum,
@Source, @Type, @TDate," +

"@Reference, @Code, @Note, @GSTMode, @Debit, @Credit, @GST_Debit,
@GST_Credit," +

"@Amount)" ;

Right - you haven't specified which columns you're trying to insert
into, which means you *do* have to have all of the columns, in the
order in which they're in the schema. If you want to only specify some
columns, you need to use

INSERT INTO tblGLBATCH (Column1, Column2, ...) VALUES (...)

There are two more fields in the database but i don't want to update them...
if i leave them out it has a shit?? if i put tham all in it works fine??

I don't think I'd characterise "telling you that your SQL is invalid"
as "having a shit".

 

[Guest]

hi,
For insert command you need to supply correct no of parameters which
match the database no of feilds.

Suppose if you want to put it as blank use System.DBNull.Value to check
whether it is empty. Then pass this System.DBNull.Value to your command