Hello,



I am executing the following code against an Access

database. I keep getting the 'Syntax error in INSERT INTO

statement' error. Why??? What am I doing wrong???



<%

Set Con = Server.CreateObject("ADODB.Connection")

con.open "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=d:\mydbs\users.mdb"



strSQL = "INSERT INTO users(user,pass,name)

VALUES('joe','12345','Joe User');"



set ObjCmd = Server.CreateObject("ADODB.Command")

set ObjCmd.ActiveConnection = Con

objCmd.CommandText = strSQL

objCmd.Execute

%>



I know the solution is right in front of my eyes, but I

can't seem to find it.

Thanks in advance for any help.



Greg

I don't see anything wrong with your code.

But when I get an error such as this one,

I try to insert the data other ways.



for example, I may response.write the strSQL to the

screen and then scarpe and paste it into the

Access query designer, or I may resort to a

simple insert query without the command object.

such as conn.Execute(strSQL).

Anything to help me understand the problem.



Cheers





----- Original Message -----

From: "Greg Covey" <gecovey@s...>

To: "ASP Databases" <asp_databases@p...>

Sent: Sunday, January 21, 2001 7:12 PM

Subject: [asp_databases] Why is this causing an error???





> Hello,

>

> I am executing the following code against an Access

> database. I keep getting the 'Syntax error in INSERT INTO

> statement' error. Why??? What am I doing wrong???

>

> <%

> Set Con = Server.CreateObject("ADODB.Connection")

> con.open "Provider=Microsoft.Jet.OLEDB.4.0;Data

> Source=d:\mydbs\users.mdb"

>

> strSQL = "INSERT INTO users(user,pass,name)

> VALUES('joe','12345','Joe User');"

>

> set ObjCmd = Server.CreateObject("ADODB.Command")

> set ObjCmd.ActiveConnection = Con

> objCmd.CommandText = strSQL

> objCmd.Execute

> %>

>

> I know the solution is right in front of my eyes, but I

> can't seem to find it.

> Thanks in advance for any help.

>

> Greg

>

>

This looks like for an SQL database not access

create a secordset

and after

rs.fields("user")="joe"  could be a string instead of joe

etc etc for the rest values

rs.update

hope it helps







-----Original Message-----

From: Greg Covey [mailto:gecovey@s...]

Sent: Sunday, January 21, 2001 4:13 PM

To: ASP Databases

Subject: [asp_databases] Why is this causing an error???





Hello,



I am executing the following code against an Access

database. I keep getting the 'Syntax error in INSERT INTO

statement' error. Why??? What am I doing wrong???



<%

Set Con = Server.CreateObject("ADODB.Connection")

con.open "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=d:\mydbs\users.mdb"



strSQL = "INSERT INTO users(user,pass,name)

VALUES('joe','12345','Joe User');"



set ObjCmd = Server.CreateObject("ADODB.Command")

set ObjCmd.ActiveConnection = Con

objCmd.CommandText = strSQL

objCmd.Execute

%>



I know the solution is right in front of my eyes, but I

can't seem to find it.

Thanks in advance for any help.



Greg

Table name is missing in the insert statement





>From: "Greg Covey" <gecovey@s...>

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] Why is this causing an error???

>Date: Sun, 21 Jan 2001 16:12:42 -0800

>

>Hello,

>

>I am executing the following code against an Access

>database. I keep getting the 'Syntax error in INSERT INTO

>statement' error. Why??? What am I doing wrong???

>

><%

>Set Con = Server.CreateObject("ADODB.Connection")

>con.open "Provider=Microsoft.Jet.OLEDB.4.0;Data

>Source=d:\mydbs\users.mdb"

>

>strSQL = "INSERT INTO users(user,pass,name)

>VALUES('joe','12345','Joe User');"

>

>set ObjCmd = Server.CreateObject("ADODB.Command")

>set ObjCmd.ActiveConnection = Con

>objCmd.CommandText = strSQL

>objCmd.Execute

>%>

>

>I know the solution is right in front of my eyes, but I

>can't seem to find it.

>Thanks in advance for any help.

>

>Greg

>

>

Did you include the msado15.dll at the top of your page.If not do so.



<!--METADATA TYPE="typelib"

             FILE="C:\Program Files\Common Files\System\ado\msado15.dll"-->





Furthermore remove the commars on the interger value 12345





strSQL = "INSERT INTO users(user,pass,name)

VALUES('joe',12345,'Joe User');"









>From: "Dallas Martin" <dmartin@z...>

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] Re: Why is this causing an error???

>Date: Sun, 21 Jan 2001 20:05:08 -0500

>

>I don't see anything wrong with your code.

>But when I get an error such as this one,

>I try to insert the data other ways.

>

>for example, I may response.write the strSQL to the

>screen and then scarpe and paste it into the

>Access query designer, or I may resort to a

>simple insert query without the command object.

>such as conn.Execute(strSQL).

>Anything to help me understand the problem.

>

>Cheers

>

>

>----- Original Message -----

>From: "Greg Covey" <gecovey@s...>

>To: "ASP Databases" <asp_databases@p...>

>Sent: Sunday, January 21, 2001 7:12 PM

>Subject: [asp_databases] Why is this causing an error???

>

>

> > Hello,

> >

> > I am executing the following code against an Access

> > database. I keep getting the 'Syntax error in INSERT INTO

> > statement' error. Why??? What am I doing wrong???

> >

> > <%

> > Set Con = Server.CreateObject("ADODB.Connection")

> > con.open "Provider=Microsoft.Jet.OLEDB.4.0;Data

> > Source=d:\mydbs\users.mdb"

> >

> > strSQL = "INSERT INTO users(user,pass,name)

> > VALUES('joe','12345','Joe User');"

> >

> > set ObjCmd = Server.CreateObject("ADODB.Command")

> > set ObjCmd.ActiveConnection = Con

> > objCmd.CommandText = strSQL

> > objCmd.Execute

> > %>

> >

> > I know the solution is right in front of my eyes, but I

> > can't seem to find it.

> > Thanks in advance for any help.

> >

> > Greg

> >

>

Vijay,



that field is a varchar field, a password field, they just happen to be numeric. 

having to tick marks will surely throw an error.



just a note,



john





--- vijay das <anchovies76@h...> wrote:

> 

> Did you include the msado15.dll at the top of your page.If not do so.

> 

> <!--METADATA TYPE="typelib"

>              FILE="C:\Program Files\Common Files\System\ado\msado15.dll"-->

> 

> 

> Furthermore remove the commars on the interger value 12345

> 

> 

> strSQL = "INSERT INTO users(user,pass,name)

> VALUES('joe',12345,'Joe User');"

> 

> 

> 

> 

> >From: "Dallas Martin" <dmartin@z...>

> >Reply-To: "ASP Databases" <asp_databases@p...>

> >To: "ASP Databases" <asp_databases@p...>

> >Subject: [asp_databases] Re: Why is this causing an error???

> >Date: Sun, 21 Jan 2001 20:05:08 -0500

> >

> >I don't see anything wrong with your code.

> >But when I get an error such as this one,

> >I try to insert the data other ways.

> >

> >for example, I may response.write the strSQL to the

> >screen and then scarpe and paste it into the

> >Access query designer, or I may resort to a

> >simple insert query without the command object.

> >such as conn.Execute(strSQL).

> >Anything to help me understand the problem.

> >

> >Cheers

> >

> >

> >----- Original Message -----

> >From: "Greg Covey" <gecovey@s...>

> >To: "ASP Databases" <asp_databases@p...>

> >Sent: Sunday, January 21, 2001 7:12 PM

> >Subject: [asp_databases] Why is this causing an error???

> >

> >

> > > Hello,

> > >

> > > I am executing the following code against an Access

> > > database. I keep getting the 'Syntax error in INSERT INTO

> > > statement' error. Why??? What am I doing wrong???

> > >

> > > <%

> > > Set Con = Server.CreateObject("ADODB.Connection")

> > > con.open "Provider=Microsoft.Jet.OLEDB.4.0;Data

> > > Source=d:\mydbs\users.mdb"

> > >

> > > strSQL = "INSERT INTO users(user,pass,name)

> > > VALUES('joe','12345','Joe User');"

> > >

> > > set ObjCmd = Server.CreateObject("ADODB.Command")

> > > set ObjCmd.ActiveConnection = Con

> > > objCmd.CommandText = strSQL

> > > objCmd.Execute

> > > %>

> > >

> > > I know the solution is right in front of my eyes, but I

> > > can't seem to find it.

> > > Thanks in advance for any help.

> > >

> > > Greg

> > >

> 

Thanks, but the table name is there. It is 'users'.

Thanks for the tips. I'll try to paste it into the access query designer and

see what happens. That's a good idea.

John and Vijay,



Yes, John is correct. The field 'pass' is a text field, not integer or long.

Thus, I'm pretty sure the single quotes need to be there. Secondly, what is

this .dll that you're suggesting I need to include? I've never needed to use

it before. . . Please explain further.



Thanks for all your help,

	Greg

The DLL is a reference to ADO. If you use any ADO constant, like 

adOpenForwardOnly etc, ASP has to know what adOpenForwardOnly really means. 

There are a couple of ways to do it:



1. Declare them yourself: Const adOpenForwardOnly = 0

Not recommended, cause you have to do this on every page you use the 

constant, or include your own file.





2. Include the adovbs.inc file, (found in Program Files\Common 

Files\System\Ado by default) with this code (copy it to your virtual root, 

or modify the statement:

<!--#include virtual="/adovbs.inc"-->





3. Add a reference to the ADO dll in your global.asa. For this to work, you 

need to know the exact location of the DLL which can be a problem if you 

use an ISP or can't / don't use the global.asa.

However, imo this is the recommend way, because all constants are included 

directly from the source. If anything ever changes, there's no risk you 

have an old include file:

<!--METADATA TYPE="typelib"

FILE="D:\Program Files\Common Files\SYSTEM\ADO\msado15.dll"

NAME="ADODB Type Library" -->

Modify the above path to match your location of msado15.dll





HtH



Imar





At 09:46 AM 1/22/2001 -0800, you wrote:

>John and Vijay,

>

>Yes, John is correct. The field 'pass' is a text field, not integer or long.

>Thus, I'm pretty sure the single quotes need to be there. Secondly, what is

>this .dll that you're suggesting I need to include? I've never needed to use

>it before. . . Please explain further.

>

>Thanks for all your help,

>         Greg

Imar,



Thanks for the heads up. Yes, I am familiar with the ADO constants, as well

as adovbs.inc. I've just never heard about including the .dll as meta data.

But, now I know! Thanks again,



Greg

What we see here is two lines. The questions becomes is there 2 lines in 

your code or only one. Presumability only one or you would get a syntax 

error. Since you are getting an sql error, it must have to do with the 

string. We assume you have a table named users (in lower case) that has 3 or 

more fields (user, pass, name) and the field names are in lower case. In 

addition, the three fields are text fields in the access database. If there 

are other fields they are not required. Therefore there could be a problem 

with the string that does not have a space between the parts: 

",name)<nospace>VALUES(..." should be:

",name)<space>VALUES(..."



strSQL = "INSERT INTO users(user,pass,name) VALUES('joe','12345','Joe 

User');"



I guess

Wally Burfine

Wally,



All of your assumptions are correct: table names 'users',

text fields 'user', 'pass', and 'name', etc. And yes, my SQL

string is on one line, and has the space as you specify

below. I'm still stumped. This is really driving me crazy.

Thanks for your help.



Greg

To all that have offered help: I think I just figured out my

problem. This is so simple and stupid, but very easy to

overlook. The word 'user' is a SQL reserved word! Since one

of my field names is 'user', access saw this as an error. I

changed it to 'username' in both the database and my code,

and it works just fine!



Thanks again to all that offered their help!

Imar,



I'd like to learn a little more about this subject. You mentioned (in option

3) using the METADATA.  What are the side effects of using this on the

server?  Memory, overhead, etc. I'm curious. We have a large file with all

the constants that are defined in the objects we created, but have to

duplicate these in ASP.  What is to be gained or lost when using METADATA

object references versus just creating an include with all your constants?

(Aside from the obvious that object updates will update the referenced

constants using the METADATA method.)

Seeing as this is a global application reference, can I assume that it would

be beneficial not having a file of constants loading for every single page?



Curious to learn more. Thanks.



-Peter





-----Original Message-----

From: Imar Spaanjaars [mailto:Imar@S...]

Sent: Monday, January 22, 2001 1:07 PM

To: ASP Databases

Subject: [asp_databases] Re: Why is this causing an error???





The DLL is a reference to ADO. If you use any ADO constant, like

adOpenForwardOnly etc, ASP has to know what adOpenForwardOnly really means.

There are a couple of ways to do it:



1. Declare them yourself: Const adOpenForwardOnly = 0

Not recommended, cause you have to do this on every page you use the

constant, or include your own file.





2. Include the adovbs.inc file, (found in Program Files\Common

Files\System\Ado by default) with this code (copy it to your virtual root,

or modify the statement:

<!--#include virtual="/adovbs.inc"-->





3. Add a reference to the ADO dll in your global.asa. For this to work, you

need to know the exact location of the DLL which can be a problem if you

use an ISP or can't / don't use the global.asa.

However, imo this is the recommend way, because all constants are included

directly from the source. If anything ever changes, there's no risk you

have an old include file:

<!--METADATA TYPE="typelib"

FILE="D:\Program Files\Common Files\SYSTEM\ADO\msado15.dll"

NAME="ADODB Type Library" -->

Modify the above path to match your location of msado15.dll





HtH



Imar





At 09:46 AM 1/22/2001 -0800, you wrote:

>John and Vijay,

>

>Yes, John is correct. The field 'pass' is a text field, not integer or

long.

>Thus, I'm pretty sure the single quotes need to be there. Secondly, what is

>this .dll that you're suggesting I need to include? I've never needed to

use

>it before. . . Please explain further.

>

>Thanks for all your help,

>         Greg

Hi Peter,



I don't have any hard evidence on this, but I think it will perform faster 

when you use the metadata option.

My adovbs.inc is currently over 500 lines which all need to be included in 

every page I use the include. This seems a lot of overhead to me.

Maybe naming your own constants per page might be faster than the metadata 

option, but compared to the extra amount of work to build and maintain it, 

I myself rather go for the metadata.



Comparing the complete includefile with your own named constants per page 

will show a 23 percent decrease in performance for the complete file. 

(based on performance tests by Wayne Plourde)

Wayne Plourde wrote an excellent article on this subject on ASPToday. Check 

it out at http://www.asptoday.com/articles/20000426.htm

He has investigated many of these subjects like:



         Should I use the ADOVBS.inc include file?

         Should I create a separate Connection object when using a Recordset?

         What is the best way to retrieve a Recordset?

         What are the most efficient Cursor and Lock Types?

         Should I use Disconnected Recordsets?

         What is the best way to set Recordset Properties?



All based with relative hard evidence.



HtH



Imar





At 04:10 PM 1/22/2001 -0500, you wrote:

>Imar,

>

>I'd like to learn a little more about this subject. You mentioned (in option

>3) using the METADATA.  What are the side effects of using this on the

>server?  Memory, overhead, etc. I'm curious. We have a large file with all

>the constants that are defined in the objects we created, but have to

>duplicate these in ASP.  What is to be gained or lost when using METADATA

>object references versus just creating an include with all your constants?

>(Aside from the obvious that object updates will update the referenced

>constants using the METADATA method.)

>Seeing as this is a global application reference, can I assume that it would

>be beneficial not having a file of constants loading for every single page?

>

>Curious to learn more. Thanks.

>

>-Peter

Couldn't one use an 'INCLUDE file" statement at the top of every page

with the file containing just the needed constant declarations?





----- Original Message -----

From: "Imar Spaanjaars" <Imar@S...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, January 23, 2001 8:42 AM

Subject: [asp_databases] Re: Why is this causing an error???





> Hi Peter,

>

> I don't have any hard evidence on this, but I think it will perform faster

> when you use the metadata option.

> My adovbs.inc is currently over 500 lines which all need to be included in

> every page I use the include. This seems a lot of overhead to me.

> Maybe naming your own constants per page might be faster than the metadata

> option, but compared to the extra amount of work to build and maintain it,

> I myself rather go for the metadata.

>

> Comparing the complete includefile with your own named constants per page

> will show a 23 percent decrease in performance for the complete file.

> (based on performance tests by Wayne Plourde)

> Wayne Plourde wrote an excellent article on this subject on ASPToday.

Check

> it out at http://www.asptoday.com/articles/20000426.htm

> He has investigated many of these subjects like:

>

>          Should I use the ADOVBS.inc include file?

>          Should I create a separate Connection object when using a

Recordset?

>          What is the best way to retrieve a Recordset?

>          What are the most efficient Cursor and Lock Types?

>          Should I use Disconnected Recordsets?

>          What is the best way to set Recordset Properties?

>

> All based with relative hard evidence.

>

> HtH

>

> Imar

>

>

> At 04:10 PM 1/22/2001 -0500, you wrote:

> >Imar,

> >

> >I'd like to learn a little more about this subject. You mentioned (in

option

> >3) using the METADATA.  What are the side effects of using this on the

> >server?  Memory, overhead, etc. I'm curious. We have a large file with

all

> >the constants that are defined in the objects we created, but have to

> >duplicate these in ASP.  What is to be gained or lost when using METADATA

> >object references versus just creating an include with all your

constants?

> >(Aside from the obvious that object updates will update the referenced

> >constants using the METADATA method.)

> >Seeing as this is a global application reference, can I assume that it

would

> >be beneficial not having a file of constants loading for every single

page?

> >

> >Curious to learn more. Thanks.

> >

> >-Peter

>

>

Yes, you could. But I think the effect will be the same. You still have to 

include / read that file on every page that uses it.



Also, as an application tends to grow, you'll end up adding all kinds of 

constants to this file. In the end, you end up with a copy of ....... 

adovbs.inc ;-)



Imar



At 07:14 PM 1/23/2001 -0500, you wrote:

>Couldn't one use an 'INCLUDE file" statement at the top of every page

>with the file containing just the needed constant declarations?