Going slightly mad - Insert sql not working from code but does in Access?

[JamesB]

I am trying to insert a row to an Access database:

INSERT INTO tblTest (Val1, Val2, Val3, Val4, Val5) VALUES ('blah', 0, '',
'', 'foo');

If I run this in my code:

String insStr = "INSERT INTO INSERT INTO tblTest (Val1, Val2, Val3, Val4,
Val5) VALUES ('blah', 0, '', '', 'foo');
OleDbCommand insCmd = new OleDbCommand(in sStr, DBConn);
try
{
insCmd.ExecuteS calar();
}

I get an error "Syntax error in INSERT statement". Yet if I create a new
query in the Access db and paste the insert statement in there, it works
fine! All fields are text apart from Val2, which is a Yes/No field.

Anyone spot what I'm doing wrong here?

[sloan]

Re: Going slightly mad - Insert sql not working from code but does in Access?


A.

You want to run
..ExecuteNonQue ry

and not the scalar.

2. You need to provide the datatypes of Va1-Val5.





"JamesB" <james@comwro te in message
news:47cc74b5$0 $21861$db0fefd9 @news.zen.co.uk ...

>I am trying to insert a row to an Access database:
>
INSERT INTO tblTest (Val1, Val2, Val3, Val4, Val5) VALUES ('blah', 0, '',
'', 'foo');
>
If I run this in my code:
>
String insStr = "INSERT INTO INSERT INTO tblTest (Val1, Val2, Val3, Val4,
Val5) VALUES ('blah', 0, '', '', 'foo');
OleDbCommand insCmd = new OleDbCommand(in sStr, DBConn);
try
{
insCmd.ExecuteS calar();
}
>
I get an error "Syntax error in INSERT statement". Yet if I create a new
query in the Access db and paste the insert statement in there, it works
fine! All fields are text apart from Val2, which is a Yes/No field.
>
Anyone spot what I'm doing wrong here?

[sloan]

Re: Going slightly mad - Insert sql not working from code but does in Access?

Check

http://www.4guysfromrolla.com/webtech/chapters/ASPNET/ch06.2.shtml

Listing 6.2.4 Adding a Record to Access


PS

YOu have a DOUBLE "insert into insert into"




"JamesB" <james@comwro te in message
news:47cc74b5$0 $21861$db0fefd9 @news.zen.co.uk ...

>I am trying to insert a row to an Access database:
>
INSERT INTO tblTest (Val1, Val2, Val3, Val4, Val5) VALUES ('blah', 0, '',
'', 'foo');
>
If I run this in my code:
>
String insStr = "INSERT INTO INSERT INTO tblTest (Val1, Val2, Val3, Val4,
Val5) VALUES ('blah', 0, '', '', 'foo');
OleDbCommand insCmd = new OleDbCommand(in sStr, DBConn);
try
{
insCmd.ExecuteS calar();
}
>
I get an error "Syntax error in INSERT statement". Yet if I create a new
query in the Access db and paste the insert statement in there, it works
fine! All fields are text apart from Val2, which is a Yes/No field.
>
Anyone spot what I'm doing wrong here?

[Liz]

Re: Going slightly mad - Insert sql not working from code but does in Access?

"JamesB" <james@comwro te in message
news:47cc74b5$0 $21861$db0fefd9 @news.zen.co.uk ...

>>I am trying to insert a row to an Access database:
>>
>INSERT INTO tblTest (Val1, Val2, Val3, Val4, Val5) VALUES ('blah', 0, '',
>'', 'foo');
>>
>If I run this in my code:
>>
>String insStr = "INSERT INTO INSERT INTO tblTest (Val1, Val2, Val3, Val4,
>Val5) VALUES ('blah', 0, '', '', 'foo');
>OleDbCommand insCmd = new OleDbCommand(in sStr, DBConn);

Did you really this code with the two "INSERT INTOs" :

String insStr = "INSERT INTO INSERT INTO tbltest ..."

?

>{
> insCmd.ExecuteS calar();
>}
>>
>I get an error "Syntax error in INSERT statement". Yet if I create a new
>query in the Access db and paste the insert statement in there, it works
>fine! All fields are text apart from Val2, which is a Yes/No field.
>>
>Anyone spot what I'm doing wrong here?

>
>