Insert failing

[dudeshouse]

Hi,
I'm developing in ASP using MySQL as the database.
I've inherited a database that I need to work with, both in structure and data, so please bear with me a little while I try to explain...
I'm adding a listing to a pub directory, then need to insert records in to other tables using the unique ID created.
I'm coming up against an error, which looks like the data is being passed weirdly. I hope that this is the right forum to be posting this in as I think it's data related.
The error I'm getting is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 5.1 Driver][mysqld-4.1.22-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[long], address, town_id, postcode) VALUES (1.57000000000000000e+002, 5.14345799' at line 1

/inflight/pub-add.asp, line 106

The ASP code is:
<%
If (CStr(Request("MM_insert")) = "add_pub_form") Then
If (Not MM_abortEdit) Then
' execute the insert
Dim MM_editCmd

Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_dbWLD_STRING
MM_editCmd.CommandText = "INSERT INTO pub (inactive, name, expiry_date, phone, email, website, contact_info, short_description, long_description, pub_chain_id, drink_info, food_info, entertainment_info, other_info, dart_info, dart_team_phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 5, 1, -1, MM_IIF(Request.Form("active"), Request.Form("active"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 255, Request.Form("pub_name")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 135, 1, -1, MM_IIF(Request.Form("expiry_date"), Request.Form("expiry_date"), null)) ' adDBTimeStamp
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 202, 1, 255, Request.Form("phone")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 255, Request.Form("email")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 202, 1, 255, Request.Form("website")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 202, 1, 16777215, Request.Form("contact_names")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 202, 1, 255, Request.Form("desc_short")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param9", 202, 1, 16777215, Request.Form("desc_long")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param10", 5, 1, -1, MM_IIF(Request.Form("pub_chain"), Request.Form("pub_chain"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param11", 202, 1, 16777215, Request.Form("info_drink")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param12", 202, 1, 16777215, Request.Form("info_food")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param13", 202, 1, 16777215, Request.Form("info_ents")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param14", 202, 1, 16777215, Request.Form("info_other")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param15", 202, 1, 16777215, Request.Form("darts_info")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param16", 202, 1, 16777215, Request.Form("darts_phone")) ' adVarWChar
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
End If
End If
%>
<%
If (CStr(Request("MM_insert")) = "add_pub_form") Then
Set newPub_cmd = Server.CreateObject ("ADODB.Command")
newPub_cmd.ActiveConnection = MM_dbWLD_STRING
newPub_cmd.CommandText = "SELECT id FROM pub ORDER BY id DESC"
newPub_cmd.Prepared = true

Set newPub = newPub_cmd.Execute

varPubID = newPub.Fields.Item("id").Value

newPub.Close()
Set newPub = Nothing
End If
Response.Write varPubID
%>
<%
If (CStr(Request("MM_insert")) = "add_pub_form") Then
If (Not MM_abortEdit) Then
' execute the insert

Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_dbWLD_STRING
MM_editCmd.CommandText = "INSERT INTO pub_location (id, lat, [long], address, town_id, postcode) VALUES (?, ?, ?, ?, ?, ?)"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 5, 1, -1, CInt(varPubID)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 5, 1, -1, Request.Form("pub_lat")) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, -1, Request.Form("pub_long")) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 202, 1, 255, Request.Form("pub_address")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 5, 1, -1, MM_IIF(Request.Form("pub_town"), Request.Form("pub_town"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 202, 1, 10, Request.Form("postcode")) ' adVarWChar
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
End If
End If
%>

There is more, but it's more of the same to be honest, so once I figure out this error, I should be able to apply the fix to the other inserts as well.
Line 106 is the MM_editCmd.Execute line near the bottom.
I'm not really up to speed with MySQL database, so am wondering if it's something to do with how the database has been set up. Either that or something to do with the data types. I don't understand why the numbers being passed are being formatted like that in the SQL. The first one showing is an integer and the second is 51.4345799 (note the difference in the decimal placing).
Any help would be grealy appreciated as I've been on this for a while now and haven't fixed it.
I've tried making sure that the ID is being passed, which it is, as I can write it to the screen. I've tried truncating the data being passed.
Thanks.

[tomws]

The error you've posted is complaining about this query:

MM_editCmd.CommandText = "INSERT INTO pub_location (id, lat, [long], address, town_id, postcode) VALUES (?, ?, ?, ?, ?, ?)"


I'd guess it (MySQL) doesn't like the square brackets in the query. Does the fieldname really have brackets? In MySQL, you can work around screwy fieldnames by wrapping them in backticks/graves in the query string. Yours might look like this:
MM_editCmd.CommandText = "INSERT INTO pub_location (id, lat, `[long]`, address, town_id, postcode) VALUES (?, ?, ?, ?, ?, ?)"

[dudeshouse]

Thanks for the reply.
The square brackets have been added by Dreamweaver - I'm guessing that this is because long is a reserved word.
I will try and remove the square brackets and use back ticks instead.

[dudeshouse]

Diamond geezer. Perfect. I removed the square brackets, added in back ticks and it's on to the next error! I've now got the whole page working, so thank you for your suggestion.
Someone else here came up with the same suggestion as I was trying it, so there you go.
Thanks again.

[Old Pedant]

Yet another case of DumbWaster's excellent coding capabilities.

[...] is used for *ACCESS* databases.
`...` is used for MySQL.
"..." is used for SQLServer (assuming that a certain flag is set the normal way; SQL Server can use [...] if it is not)

When you told DripWater that you were using MySQL, it *should* have used `...` but this is kind of par for the DoofusWacko course.

[dudeshouse]

Haha, thanks for that Old Pedant.

I know, I know... I tend to use DillWeed to build frameworks fairly quickly, then go into code view, breaking it terribly in "designer" view... Please rest assured that I'm not a DeadWallaby lover, I just find it helps to speed up the initial parts of a project.

Thanks again for the help guys.