ASP database issue

[John Peach]

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

 

[Bob Barrows [MVP]]

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 @@.

 

[Bob Barrows [MVP]]

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?

 

[Bob Barrows [MVP]]

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]

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

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','','','')

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

--
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]]

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&

 

[John Peach]

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

Regards

John