problems with sql string when running Connection.Execute(SQLString)

[martin123123]

hi everyone.

there seems to be a problem when i try to execute this sentence:
'InsertSql = "insert into abolagre values('" + TXT1.Text + "'," + DateTime.Date$ + &quot"

Now the sql sentence will be something like this:

InsertSql =
"insert into abolagre values('hello','07-07-2003')"

But when i try to run
myConnection.Execute(InsertSql)
i get the error:
syntax error converting datetime from character string

i've also tried to remove the quote marks on the date, but it still doesn't work.

what am I doing wrong?

 

[MarkSweetland]

Unless you specify the column name order in the insert clause, the order in which your values lie must match the column types defined in the table. It looks as though you may have the values transposed. Try:

InsertSql =
"insert into abolagre values('07-07-2003', 'hello')"


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[martin123123]

no, the order is not the answer. my querystring is much bigger and every value is in right order. I just shortened the string down when I posted it here.
The problem is the datetime value.

 

[CCLINT]

What dbms are you using?

 

[aalmeida]

Try to add the single quote arround the date as follows:
"insert into abolagre values('" + TXT1.Text + "','" + DateTime.Date$ + "')"

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[CCLINT]

Single quotes:
This is what I was assumming was happening:

>Now the sql sentence will be something like this:

>InsertSql =
>"insert into abolagre values('hello','07-07-2003')"

But looking close at
>'InsertSql = "insert into abolagre values('" + TXT1.Text + "'," + DateTime.Date$ + &quot"

I see that this isn't the case.

This is often a problem in determing where and error is when a question isn't straight forward and completly correct.

 

[vbkris]

yup as CCLINT said the problem is the single qoutes, also try this:
"insert into abolagre values('" + TXT1.Text + "',#" + DateTime.Date$ + "#)"


Known is handfull, Unknown is worldfull

 

[aalmeida]

I tryed using the extra single quotes and works for SQL 92 and yes the # signs will work on MS Access

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"