WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WIL Extenders
ODBC
plus
Sample Code

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Syntax for Insert Into Access

Question:

I cannot figure out the command to insert a record into an Access DB.

I know it must be something like: 

INSERT INTO table ?????

When I execute this from Access itself it works fine but when I execute it from Winbatch I get the error "invalid bracketing of name DATA3"

Here is the statement that is failing in Winbatch: 

INSERT INTO TABLENAME (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6) SELECT "DATA1" AS Expr2,1 as Expr1, "\DATA2" AS Expr3, "DATA3" AS Expr4, "DATA4" AS Expr5, "DATA5" AS Expr6;

Here is my code: 

one = arrayone[1]
two = arrayone[2]
three = arrayone[3]
four = arrayone[4]
five = arrayone[5]
image = StrCat(arrayone[0],"\%filename%")

cSQL1stpart = ("INSERT INTO TABLENAME(FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6) SELECT ""%one%"" AS Expr2,")
cSQL2ndpart = ('1 as Expr1,"%image%" AS Expr3, "%three%" AS Expr4, "%four%" AS Expr5, "%five%" AS Expr6;')
cSQL = Strcat (cSQL1stpart, cSQL2ndpart)
;cSQL =StrCat("SELECT * FROM ",TABLE)

retcode = qExecDirect(hstmt,cSQL)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Display(10,"SQL Operation Status",qError(hstmt,2))
sqlerror = qError(hstmt,2)
exit

Here is the final sql statement... 

INSERT INTO tbl_test (name, address, state, zipcode, wrkphone, homephone) SELECT "Sinjin Smith" AS Expr2,1 as Expr1, "\1204 Cali Way" AS Expr3, "CA" AS Expr4, "90210" AS Expr5, "5555555555" AS Expr6;

Answer:

Are you sure this is a valid SQL Statement? It seems to include both an INSERT and a SELECT command....

Maybe see: http://sqlcourse.com/insert.html

For example, the code might look something like this: 

tablename = 'tbl_test'
filename = 'test.bmp'
arrayone=ArrDimension(6,0,0,0,0)
ArrInitialize(arrayone, 'testvalue')
one = arrayone[1]
two = arrayone[2]
three = arrayone[3]
four = arrayone[4]
five = arrayone[5]
image = StrCat(arrayone[0],'\%filename%')


cSQL = StrCat('INSERT INTO ',tablename,' (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6) VALUES (',image,',',one,',',two,',',three,',',four,',',five,')')
Pause('SQL statment',cSQL)

retcode = qExecDirect(hstmt,cSQL)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Display(10,'SQL Operation Status',qError(hstmt,2))
sqlerror = qError(hstmt,2)
exit

Article ID:   W15848

File Created: 2004:03:30:15:41:08
Last Updated: 2004:03:30:15:41:08