ASP database issue

Discussion in 'ASP General' started by John Peach, Jun 4, 2007.

  1. John Peach

    John Peach Guest

    not sure if this is the right place to ask but, when i run the following
    query in ASP i get the error

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Invalid column name '2 UK lighting turnover from Indoor lighting'.

    /qbrowse/forms/New Starter Feedback Form.asp, line 65

    where line 65 executed the following sql statement


    INSERT INTO tblNewStarter ([FormName], [Name], [Company], [1 UK turnover
    from lighting products £], [Percentage], [2 UK lighting turnover from Indoor
    lighting], [2 UK lighting turnover from Outdoor Lighting], [2 UK lighting
    turnover from Loose lamps], [3 Route to Market Wholesale - Stock], [3 Route
    to Market Wholesale - Project], [3 Route to Market Direct / End User], [3
    Route to Market Retail], [3 Route to Market Other], [3 Route to Market
    (please specify)], [4 UK Headcount External Salespeople - Stock], [4 UK
    Headcount External Salespeople - Project], [4 UK Headcount Internal
    Salespeople - Stock], [4 UK Headcount Internal Salespeople - Project], [4 UK
    Headcount Other Salespeople], [5 How many UK regional sales offices ?],
    [Where ?], [6Lighting Schemes], [6a Products Manufactured in UK], [6a
    Products Manufactured in Central Europe], [6a Products Manufactured in
    Eastern Europe], [6a Products Manufactured in Far East], [6a Products
    Manufactured in Other], [6a Products Manufactured in (Please specify)], [6b
    For UK Market UK], [6b For UK Market Central Europe], [6b For UK Market
    Eastern Europe], [6b For UK Market Far East], [6b For UK Market Other],
    [7Preferred ballast type], [8Component Supplier], [9 Are products
    distributed from a central point or are there outlying distribution
    centres?], [Where?], [10Product Return Policy], [11Samples], [12 Is the
    company able to provide a mock-up for a large project ?], [Is this provided
    free of charge ?], [13Sponsorship], [14 What is the company spend on
    entertainment?], [What form does this take ?], lastupdateddate) VALUES ('New
    Starter Feedback Form','John Peach','Peas ltd
    ','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',
    '6/4/2007')

    Can anyone advise why this won't run in ASp but runs without it in SQL
    itself



    Thanks in anticipation



    John
    John Peach, Jun 4, 2007
    #1
    1. Advertising

  2. John Peach wrote:
    > not sure if this is the right place to ask but, when i run the
    > following query in ASP i get the error
    >
    > Microsoft OLE DB Provider for SQL Server error '80040e14'


    What version of SQL Server is this?
    >
    > Invalid column name '2 UK lighting turnover from Indoor lighting'.


    I suspect that using a numeric character as the first character in the
    field name is causing the problem. IIRC, you should not have even been
    allowed to do that.
    From BOL:
    a..
    1.. The first character must be one of the following:
    2.. A letter as defined by the Unicode Standard 2.0. The Unicode
    definition of letters includes Latin characters a-z and A-Z, in addition
    to letter characters from other languages.
    a.. The _ (underscore), @ (at sign), or # (number sign) symbol.
    Certain symbols at the beginning of an identifier have special meaning
    in SQL Server. An identifier beginning with @ denotes a local variable
    or parameter. An identifier beginning with # denotes a temporary table
    or procedure. An identifier beginning with double number signs (##)
    denotes a global temporary object.

    Some Transact-SQL functions have names that start with double at signs
    (@@). To avoid confusion with these functions, it is recommended that
    you do not use names that start with @@.



    --
    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], Jun 4, 2007
    #2
    1. Advertising

  3. John Peach wrote:
    > I took your advice and stripped out the illegal characters, but still
    > get an error :
    >


    By "stripping out", do you mean you changed the names of the fields in
    the database? or did you merely remove the characters from your sql
    statement?

    --
    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], Jun 4, 2007
    #3
  4. I know this could be construed as dodging the question, but I have to
    ask: is there a reason you have not encapsulated this long sql statement
    into a stored procedure?

    I'd like to try to reproduce your problem, so can you let me know what
    version of SQL Server you are using? Also, can you verify if the
    following statement reproduces the problem:

    INSERT INTO tblNewStarter ([FormName], [Name], [Company],
    [UK turnover from lighting products £], [Percentage],
    [UK lighting turnover from Indoor lighting])
    VALUES ('New Starter Feedback Form',
    'John Peach','Pea Soup Ltd','','','')

    John Peach wrote:
    > LOL, no the fields in the database match the names in the SQL
    > statement, as stated the SQL statement runs prefectly from the
    > Servera and the record gets inserted into the database, just will not
    > run from ASP
    >
    > Regards
    >
    > John
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> John Peach wrote:
    >>> I took your advice and stripped out the illegal characters, but
    >>> still get an error :
    >>>

    >>
    >> By "stripping out", do you mean you changed the names of the fields
    >> in the database? or did you merely remove the characters from your
    >> sql statement?
    >>



    --
    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], Jun 4, 2007
    #4
  5. John Peach

    John Peach Guest

    The insert statement you put forward ran without issue

    Will go the stored procedure route if i can't get this sql statement to work

    Running on SQL 2000

    Regards

    John

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > I know this could be construed as dodging the question, but I have to
    > ask: is there a reason you have not encapsulated this long sql statement
    > into a stored procedure?
    >
    > I'd like to try to reproduce your problem, so can you let me know what
    > version of SQL Server you are using? Also, can you verify if the
    > following statement reproduces the problem:
    >
    > INSERT INTO tblNewStarter ([FormName], [Name], [Company],
    > [UK turnover from lighting products £], [Percentage],
    > [UK lighting turnover from Indoor lighting])
    > VALUES ('New Starter Feedback Form',
    > 'John Peach','Pea Soup Ltd','','','')
    >
    > John Peach wrote:
    >> LOL, no the fields in the database match the names in the SQL
    >> statement, as stated the SQL statement runs prefectly from the
    >> Servera and the record gets inserted into the database, just will not
    >> run from ASP
    >>
    >> Regards
    >>
    >> John
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >>> John Peach wrote:
    >>>> I took your advice and stripped out the illegal characters, but
    >>>> still get an error :
    >>>>
    >>>
    >>> By "stripping out", do you mean you changed the names of the fields
    >>> in the database? or did you merely remove the characters from your
    >>> sql statement?
    >>>

    >
    >
    > --
    > 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.
    >
    >
    John Peach, Jun 5, 2007
    #5
  6. John Peach wrote:
    > The insert statement you put forward ran without issue
    >


    Really? So that means the problem lies elsewhere. Add fields in
    one-at-a-time until you discover the actual culprit.

    > Will go the stored procedure route if i can't get this sql statement
    > to work
    > Running on SQL 2000


    Frankly, I would stop wasting time and convert it to a stored procedure
    which can easily be executed from asp. Aee:
    http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&

    --
    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], Jun 5, 2007
    #6
  7. John Peach

    John Peach Guest

    Thanks for the advice, eventually went forthe route of a Stored procedure
    and all is working well now

    Regards

    John

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > John Peach wrote:
    >> The insert statement you put forward ran without issue
    >>

    >
    > Really? So that means the problem lies elsewhere. Add fields in
    > one-at-a-time until you discover the actual culprit.
    >
    >> Will go the stored procedure route if i can't get this sql statement
    >> to work
    >> Running on SQL 2000

    >
    > Frankly, I would stop wasting time and convert it to a stored procedure
    > which can easily be executed from asp. Aee:
    > http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&
    >
    > --
    > 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"
    >
    John Peach, Jun 6, 2007
    #7
    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. =?Utf-8?B?dGUgZ29vZHk=?=
    Replies:
    0
    Views:
    2,164
    =?Utf-8?B?dGUgZ29vZHk=?=
    Feb 8, 2006
  2. Steve
    Replies:
    0
    Views:
    289
    Steve
    Feb 24, 2006
  3. Peter

    Database design issue

    Peter, Aug 12, 2004, in forum: Java
    Replies:
    5
    Views:
    432
    Peter
    Aug 20, 2004
  4. Replies:
    4
    Views:
    2,486
  5. Srini
    Replies:
    11
    Views:
    951
    Arne Vajhøj
    Jun 1, 2008
Loading...

Share This Page