Errors when trying to INSERT Dreamweaver CS3 ASP scripts into MySql

Report · Mar 24, 2010

Greetings everyone,

I am hoping that someone can shed some light on an issue I have not been able to solve in the last few days. I have a website that I had built using Ultradev, then MX then CS3, using the default ASP scripts in dreamweaver and an odbc connection to an Access Database. Everything had been working fine for the last couple of years until recently where more people were visiting my site which resulted in the slowness because i was using access. I decided to move to a MySql database and that is when I ran into issues. I was able to solve most everything accept for one issue I am having. I was hoping it would be an easy transition by changing the ODBC connections but it has not been that easy. Any page I have with an INSERT command using the default Dreamweaver CS3 ASP script results in an error. Now I can query and delete and search with no problem...it is just the INSERT that is giving me issues. Here is just a general insertion script that CS3 does by default....

<%
If (CStr(Request("MM_insert")) = "form1")Then
If (Not MM_abortEdit) Then
' execute the insert
Dim MM_editCmd

Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_adsdata_STRING
MM_editCmd.CommandText = "INSERT INTO commenthot (yeshot, userhot) VALUES (?, ?)"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 2, 3, Request.Form("yeshotf")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 3, 30, Request.Form("useridf")) ' adVarWChar
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
End If
End If
%>

When I click to submit...the error i run into is...

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

[MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]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 ' mel)' at line 1

/fr/members/sexyoupasvote.asp, line 28

....Line 28 is the MM_editCmd.Execute. I notice if I put a single quotes around the ? next the the VALUE like this ('?', '?')...it does go through and the ? shows up in both fields as the data.

....If i put a single back quote around the column names like this (`yeshot`, `userhot`) VALUES (?, ?) I get this error and nothing gets inserted into the MySql.

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

[MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]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 ' mel)' at line 1

/fr/members/sexyoupasvote.asp, line 28

...by seeing 'mel)' show up, I know it is at least passing the data to that field.

....when I do (`yeshot`, `userhot`) VALUES ('?', '?')...
it goes through and inserts ? ...instead of the real data I want to use...in this case ...the number 1 and Mel.

...if I do (yeshot, userhot) VALUES (?, '?')...

I get this error...

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

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/fr/members/sexyoupasvote.asp, line 28

....any idea what to modify to make it insert the corect data? Has anyone experienced this? If I can get this fixed with just this page I can probablly fix the other pages that have teh same issue. Is there a special setting on the MySql that I should know about to allow data to be inserted? Any help would GREATLY appreciated!

Thank you!

Report · Mar 24, 2010

The error is telling you there is a problem with the parameter type. What is the datatype of the column(s) in your insert statement now that it is MySQL, and what was the MS Access datatype?

Also, it looks like you are specifying yeshotf as an output parameter, not input.

>instead of the real data I want to use...in this case ...the number 1 and Mel.

You've specified the parms as character strings, so they should not be used for numeric data columns.

Report · Mar 24, 2010

Thanks for your reply!

When it was in Access...the datatype was Text. I used a 3rd party software to convert Assess directly to my hosting MySql server. When I logged in to see the table in MySql, it is set to Varchar(3) for yeshot and Varchar(30) for userhot. There is also an extra field I used for the KEY called commentID which is set to int(11) and it is autonumbered. Under the Collation i have utf8_general_ci set on the yeshot and userhot field. Null is No on both and the Default is set to None and all 3 fields.

I have a form I am collecting that data from which is:

<% If (langname.Fields.Item("langname").Value)="FR" Then
         response.write("OUI!")
         else
         response.write("YES!")
         End if %>
<input name="yeshotf" type="radio" id="yeshotf" value="1" checked>
-
                            <span class="style10">
                            <input name="submit" type="submit" id="submit" value="VOTE!">
                            </span> -
<input name="yeshotf" type="radio" id="yeshotf" value="2">
                            <% If (langname.Fields.Item("langname").Value)="FR" Then
         response.write("NON!")
         else
         response.write("NO!")
         End if %>
                            <input name="useridf" type="hidden" id="useridf" value="<%=(hotornot.Fields.Item("membername").Value)%>">
                            <input type="hidden" name="MM_insert" value="form1">
                          <input type="hidden" name="hdwfail" id="hdwfail" value="sexyoupasvote.asp?langname=<%=(langname.Fields.Item("langname").Value)%>&membername=<%=(hotornot.Fields.Item("membername").Value)%>&hdwmsg=invalid">
</form>

....the form has 2 radio button and 1 hidden field where I collect the username. The radio value should either be a 1 or a 2 depending on the selection by the user. I also have a captha on the form but that does not seem the cause any problem since I am not collecting any data from it. Does this help? I left the yeshot field as Text but I can change that to Numeric data... again thanks for your assistance in helping me try to figure this out.

...The message I am getting now is:

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

[MySQL][ODBC 3.51 Driver][mysqld-5.0.67-community-nt-log]Unknown column 'Babe1' in 'field list'

/fr/members/sexyoupasvote.asp, line 28

....with this setting:

<%
If (CStr(Request("MM_insert")) = "form1") Then
If (Not MM_abortEdit) Then
' execute the insert
Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_adsdata_STRING
    MM_editCmd.CommandText = "INSERT INTO dsnrenms.commenthot (yeshot, userhot) VALUES (?, ?)"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 3, Request.Form("yeshotf")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 30, Request.Form("useridf")) ' adVarWChar
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
End If
End If
%>