Syntax error creating an SQL query dynamically

Question

I'm using VB6 and MS Access. my sql command is

insert into BatchInfo (BName,BDate,Currency) values('" & Me.txtBatchName.Text & "','" & Me.dtpBatchDate.Value & "','" & Me.cboCurrency.Text & "')

the output of the command at run time is

"insert into BatchInfo (BName,BDate,Currency) values('batch1','8/2/2012','AED')"

here is the schema of the BatchInfo Table

BatchID   AutoNumber
BName     Text
BDate     Date/Time
Currency  Text

I cannot find any syntax error. Please help.

If you're going to build up SQL strings, PLEASE PLEASE PLEASE sanitise your database input otherwise you WILL get problems. — Deanna
– Deanna, Commented Aug 3, 2012 at 16:00

Alex K. · Accepted Answer · 2012-08-03 15:27:23Z

6

Currency is a reserved word, escape it thusly;

insert into BatchInfo (BName, BDate, [Currency]) values (...

answered Aug 3, 2012 at 15:27

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Taryn · Accepted Answer · 2012-08-03 15:26:23Z

2

MS Access typically likes # signs around its dates:

insert into BatchInfo (BName,BDate,Currency) 
values('" & Me.txtBatchName.Text & "','#" & Me.dtpBatchDate.Value & "#','" & Me.cboCurrency.Text & "')

answered Aug 3, 2012 at 15:26

Taryn

249k57 gold badges374 silver badges409 bronze badges

9 Comments

Fionnuala Over a year ago

@Deanna I would strongly suggest year, month, day formats (yyyy-mm-dd, yyyy/mm/dd) rather than American, especially for people in non-US locales.

Deanna Over a year ago

@Remou So would I wth any other RDBMS. Access (and VB6, and VBA, etc) explicitly expect #mm/dd/yyyy#.

Fionnuala Over a year ago

I am sorry but Allen Browne is not telling the whole story in this case, a very rare thing for Allen Browne. Access does indeed use American dates as default which leads to problems for people based in other locales, but what Access needs is an unambiguous date, and 4 digit year, month, day is the most unambiguous date that you can get.

Deanna Over a year ago

Sadly MSDN is failing me and I can;t find a definitive reference, but this page on query criteria uses #mm/dd/yyyy# in the examples examples.

Fionnuala Over a year ago

One of the problems for people working in non-US locales is that Access does not always need a US date, for example, in the query design window (v.2010), if you type #2012/07/05# Access itself it will change it to #05/07/2012#, not #07/05/2012#, so people working outside US locale are generally best using year,month,day. It is far less confusing for everyone.

|

PowerUser · Accepted Answer · 2012-08-03 15:25:15Z

0

You are using single quotes, not double quotes.

Try this

insert into BatchInfo (BName,BDate,Currency) values(""" & Me.txtBatchName.Text & """,#" & Me.dtpBatchDate.Value & #",""" & Me.cboCurrency.Text & """)

answered Aug 3, 2012 at 15:25

PowerUser

11.8k20 gold badges67 silver badges103 bronze badges

3 Comments

Krishanu Dey Over a year ago

but this statement works fine.

"insert into Admission_Master values('" & id & "','" & Me.txtstdcode.Text & "','" & Me.cmbcourse.Text & "','" & modules & "','" & Me.dtpadmtd.Value & "','" & cfees1 & "','" & Me.dtpsdt.Value & "','" & Me.dtpedt.Value & "','N/A'," & CBool(False) & ",'" & cfees1 & "')"

Deanna Over a year ago

Some RDBMS accept both ' and " delimeters. Others (e.g. mysql) only likes one of them

PowerUser Over a year ago

I've always just used double quotes "" in Access. Good to know there's another way.

Collectives™ on Stack Overflow

Syntax error creating an SQL query dynamically

3 Answers 3

Comments

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related