SQL Syntax error

[middletree]

What's wrogn with this query?
INSERT INTO Login(Name,Password,Pastor) VALUES ('Bob','whatever','y')

Seems pretty striaghtforward. Using Access 2003. My connection is good,
because I am using it to do all sort of other things, but this insert
statement is throwing me this message, where line 24 is the execute line:

Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/shape/addLogin.asp, line 24

 

[Evertjan.]

middletree wrote on 17 mei 2004 in microsoft.public.inetserver.asp.general:

What's wrogn with this query?
INSERT INTO Login(Name,Password,Pastor) VALUES ('Bob','whatever','y')

Seems pretty striaghtforward. Using Access 2003. My connection is good,
because I am using it to do all sort of other things, but this insert
statement is throwing me this message, where line 24 is the execute line:

Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/shape/addLogin.asp, line 24

Missing space after Login ????

Is Pastor a textfield ?

 

[Bob Barrows [MVP]]

What's wrogn with this query?
INSERT INTO Login(Name,Password,Pastor) VALUES ('Bob','whatever','y')

Seems pretty striaghtforward. Using Access 2003. My connection is
good, because I am using it to do all sort of other things, but this
insert statement is throwing me this message, where line 24 is the
execute line:

Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/shape/addLogin.asp, line 24

Both Name and Password are reserved keywords. I recommend that you rename
these fields, but if for some reason you can't, you will need to delimit
them with brackets [] whenever you use them in dynamic sql run via ADO. Of
course you will avoid this problem (and others) if you eschew dynamic sql
and use saved parameter queries instead.

Bob Barrows

 

[middletree]

Thanks. I didn't know those were reserved words. I'll try it out.

As for the "of course" sentence, I am not sure what you mean. Do you mean
like Stored Procedures?

 

[middletree]

Yes, I should have mentioned that. It's a text field. I took the lazy way
out instead of making it boolean.

 

[Bob Barrows [MVP]]

Thanks. I didn't know those were reserved words. I'll try it out.

As for the "of course" sentence, I am not sure what you mean. Do you
mean like Stored Procedures?

Yes.

Access (Jet) does not really have what I would think of as stored
procedures. The closest you can get to a stored procedure with a Jet
database is a saved query (parameterized or non-parameterized). Yes, I know
that in Jet 4.0, they started referring to saved queries as "stored
procedures" (see "CREATE PROCEDURE" in Access online help), but that is a
misnomer IMHO. A Jet saved query can contain only a single SQL statement. To
me, the "procedure" in stored procedure implies the ability to run one or
more SQL statements, along with the use of control-of-flow commands (IF,
WHILE, etc.).

Anyways, I've posted many times about using "saved parameter queries", so
you should be able to easily find them via Google.

Bob Barrows

 

[middletree]

Thanks for clarifying.

Although I understand the value of saved queries, I think that because I am
spending a lot of time late at night to get this to work, and it's free work
on my part, and because exactly one person will be using this page at any
given time, I will stay with the dynamic query in this case.

You did give me good info that I will file away for other projects, though.
Thanks.

 

[Bob Barrows [MVP]]

Thanks for clarifying.

Although I understand the value of saved queries, I think that
because I am spending a lot of time late at night to get this to
work, and it's free work on my part, and because exactly one person
will be using this page at any given time, I will stay with the
dynamic query in this case.

I don't think you realize how much time you are wasting using the dynamic
sql approach, but ... it sounds as if you can't be convinced.

 

[middletree]

I can be convinced, really. I want to learn this stuff. But my understanding
of this is that the dynamic SQL statement, because it's in ASP code, is
interpreted, not compiled, which makes it slower. But the slowness is best
seen when, say, several hundred people are trying to access the same
database at the same time.

In this case, one guy will be using this app. Possibly 3-5 others, but the
chances of them using it at the same time are pretty small.

So beyond that, what other advantages of using the compiled method are
there?

 

[Patrice]

Safety from a programming point of view :
- you pass real typed parameters instead of embedding these values as text
in, an sql string (could cause problem from types whose text represetnation
is not language independant mostly dates and numbers).
- promotes having the sql outside your app rather than sql code all over the
place
- better protecting against sql injection attacks
- and likely more ...

The key point is rather imo to have a distinct db layer whatever method you
choose...

Patrice


The key thing IMO is rahter to use a separate layer whatever method you
choose rather than having SQL statement created all over the code...

Patrice

 

[Bob Barrows [MVP]]

I can be convinced, really. I want to learn this stuff. But my
understanding of this is that the dynamic SQL statement, because it's
in ASP code, is interpreted, not compiled, which makes it slower.

That's only a minor advantage

So beyond that, what other advantages of using the compiled method are
there?

From a development standpoint, it's a real PITA to write dynamic sql code
compared to passing parameter values to a saved query. Look at all the time
you've wasted due to delimiter issues, literal quote issues, datatype
issues, now this reserved keyword issue ... All of these issues are avoided
when you pass parameters to a saved query/stored procedure.

Let's take your query for instance. Open your database in Access, click to
the Queries tab, and create a new query in Design View. Close the Choose
Table dialog without selecting a table. Switch to SQL View and paste in your
Insert query:
INSERT INTO Login(Name,Password,Pastor) VALUES ('Bob','whatever','y')

Replace the literal values with parameter placeholders:
INSERT INTO Login(Name,Password,Pastor)
VALUES ([P1],[P2],[P3])

You can use whatever parameter names you wish, as long as the names don't
match any of the field names in your table.
INSERT INTO Login(Name,Password,Pastor)
VALUES ([pName],[pPassword],[pPastor])

Run the query to test it. Access will prompt you for parameter values. When
you run this query from ASP, you will supply the values programmatically
(take note of the order in which Access prompts for the parameter values.
You will need to supply the values in the same order when you run it from
asp).

Anyways. supply values and verify that the query works. When it's doing what
you want it to do, same it. Give it a name such as qAddLogin.

Notice that I did not have to worry about datatypes or delimiters.

In ASP, create a connection object (cn) and simply do this to run the query:

cn.open
cn.qAddLogin "Bob","whatever","y"

What could be simpler?

If you have a SELECT query, simply do this to retrieve records from it:
cn.open
set rs=server.createobject("adodb.recordset")
cn.qAddLogin "Bob","whatever","y", rs

HTH,
Bob Barrows

 

[middletree]

Bob:

Thanks for this. Extremely educational. I'll not only put it to use now, but
I will save this for future use.

For many of us, ASP coding is only part of our job. Items like this, which
seem very elementary for all you full-timers, actually address reasons for
running SP's, reasons I didn't even know existed. I appreciate the quick
lesson. Have a great day!