0

Hi,
I have a problem with an insert into ms sql(web-based).
I keep getting an error

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ';'.

/admin/dataentry2.asp, line 147

the actual code the asp page is

function addRecord()
		v_Name = replace(Request.Form("Name"), "'", "\'")
		v_Category = replace(Request.Form("Category"), "'", "\'")
		v_Address1 = replace(Request.Form("Address1"), "'", "\'")
		v_Address2 = replace(Request.Form("Address2"), "'", "\'")
		v_Phone1 = replace(Request.Form("Phone1"), "'", "\'")
		v_Phone2 = replace(Request.Form("Phone2"), "'", "\'")
		v_Responsable1 = replace(Request.Form("Responsable1"), "'", "\'")
		v_Responsable2 = replace(Request.Form("Responsable2"), "'", "\'")
		v_Fax = replace(Request.Form("Fax"), "'", "\'")
		v_Description = replace(Request.Form("Description"), "'", "\'")
		v_Classification = replace(Request.Form("Classification"), "'", "\'")
		v_EMail = replace(Request.Form("EMail"), "'", "\'")
		v_Adds = replace(Request.Form("Adds"), "'", "\'")
		v_Url = replace(Request.Form("Url"), "'", "\'")
		v_Area = Request.Form("Area")
		v_booleanValue = Request.Form("booleanValue")

		if (v_Name <> "" and v_Category <> "") then
			sql = "INSERT INTO tblListing('Name', 'Category','Address1','Address2','Phone1';'Phone2','Responsable1','Responsable2','Fax','Description','Classification','EMail','Adds','Url','AreaId','booleanValue')" &_
								"Values('"&v_Name&"', '"&v_Category&"', '"&v_Address1&"', '"&v_Address2&"', '"&v_Phone1&"', '"&v_Phone2&"', '"&v_Responsable1&"', '"&v_Responsable2&"', '"&v_Fax&"', '"&v_Description&"', '"&v_Classification&"', '"&v_EMail&"', '"&v_Adds&"', '"&v_Url&"', "&v_Area&", "&v_booleanValue&")"
			openconnection
			conn.Execute sql
			conn.close
			set conn = nothing

if anyone can help me with this, I'd be very thankful

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by StuWer
0

Why are you using an OleDB driver for ODBC instead of connecting directly with OleDB, or better yet using the Native SQL Driver?

It's hard to tell because you're assembling a huge query, which is not a good idea, and is vulnerable to SQL Injection. If you use the OleDB Connection directly you can use named parameters, so your code would look like:

const string query = @"Insert Into Table (Col1,Col2) Values (@Val1, Val2)"

You would then add Sql Data Parameters to the command, and execute it. A tutorial for this can be found at: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx

ODBC can use parameters but I think it uses "?" instead of named parameters, you will need to look in to.

If you really want to do keep the approach you have then your best bet is to fire up the SQL Profiler and watch the TSQL execute on the SQL Server and see where the query is malformed. You will probably get used to doing this frequently if you build un-parameterized queries.

0

Thanks very much.
The code was actually made by a friend who has since disappeared.
This gives me something to work on

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.