Re: SQL issue

RICK wrote:
Can someone please explain to me why the following code to insert two
numeric fields throws an error.

<%

str1SQL = "Insert into tblr (list1, list_2)"
str1SQL = str1SQL & "Select " & varl1 & ", "
str1SQL = str1SQL & " " & Varl2 & " "

%>

Here is the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.

/ASP/references/MN_ReferenceSpeciesAdded.asp, line 104

Access used to require a FROM clause in all SELECT statements - I don't
know if that is still the case. You should try using the native Jet OLE
DB provider instead of ODBC.
http://www.aspfaq.com/show.asp?id=2126

If that does not help, then replace the SELECT statement with a VALUES
clause:

str1SQL = "Insert into tblr (list1, list_2) " & _
"VALUES (" & varl1 & ", " & Varl2 & ")"

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl




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


.