SQL Syntax error

Discussion in 'ASP General' started by middletree, May 17, 2004.

  1. middletree

    middletree Guest

    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
     
    middletree, May 17, 2004
    #1
    1. Advertising

  2. middletree

    Evertjan. Guest

    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 ?

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., May 17, 2004
    #2
    1. Advertising

  3. middletree wrote:
    > 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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], May 17, 2004
    #3
  4. middletree

    middletree Guest

    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?

    "Bob Barrows [MVP]" <> wrote in message >
    > 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
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
     
    middletree, May 17, 2004
    #4
  5. middletree

    middletree Guest

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


    "Evertjan." <> wrote in message
    news:Xns94EC694FD601Beejj99@194.109.133.29...
    > 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 ?
    >
    > --
    > Evertjan.
    > The Netherlands.
    > (Please change the x'es to dots in my emailaddress)
     
    middletree, May 17, 2004
    #5
  6. middletree wrote:
    > 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

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 17, 2004
    #6
  7. middletree

    middletree Guest

    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.
     
    middletree, May 17, 2004
    #7
  8. middletree wrote:
    > 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.
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 17, 2004
    #8
  9. middletree

    middletree Guest

    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?


    "Bob Barrows [MVP]" <> wrote in message news:eCD%

    > 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, May 17, 2004
    #9
  10. middletree

    Patrice Guest

    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

    "middletree" <> a écrit dans le message de
    news:%...
    > 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?
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message news:eCD%
    >
    > > 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.

    >
    >
     
    Patrice, May 17, 2004
    #10
  11. middletree wrote:
    > 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
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 17, 2004
    #11
  12. middletree

    middletree Guest

    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!
     
    middletree, May 17, 2004
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Balaji
    Replies:
    3
    Views:
    10,203
  2. ecoolone
    Replies:
    0
    Views:
    800
    ecoolone
    Jan 3, 2008
  3. Ken Bloom
    Replies:
    3
    Views:
    249
  4. Good Night Moon
    Replies:
    9
    Views:
    323
    Rick DeNatale
    Jul 25, 2007
  5. Mark Richards
    Replies:
    3
    Views:
    349
    Tad McClellan
    Nov 18, 2007
Loading...

Share This Page