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? 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!


Jade

Hi Jade,

I've had the odd issue with the newer insert behaviour in CS4 -, produces the same code as CS3. I normally work in Javascript but here goes. you might need to replace the & with + ...

The easiest way around I have found is to bypass Dreamweavers parameter section completely and set your own variables
ie

<%
dim var1
dim var2
var1 = Request.Form("yeshotf"
var2 = Request.Form("useridf"

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 (" & var1 & "," & var2 & ""
MM_editCmd.Prepared = true
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
End If
End If
%>

Thanks for the suggestion,

It was not really what I was hoping for but it did pass data in one of the form where there was only 2 fields. I tried the same thing on a different form with also 2 Insert variable and now I am getting:

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'

babe1 = username

I had built the site in the past using the ISO-8859-1 text set and it worked fine with access and still does if i return to access. It has been a horrible transition moving from Dreameavers ASP scripting using Access to MySql. Maybe you can shed some light on why that error is coming up? I also wonder if MySql needs to be set up using latin_swedish_ci character set as the default? Right now the default settings on the server seems to be Utf-8 Unicode. Since I am having my site hosted on a remote server...I can login to mysql management interface. Although on my MySql Localhost it says latina_sweden_ci as the character set...to the right side of the interface, under MySql, it still shows MySQL charset: UTF-8 Unicode (utf8). I asked the hosting service provider to change that for me but they said that should not affect my INSERT scripts. Is that true? Seems to me it is affecting it.

Any more light would be helpful....my 5th day down...Thanks!

Quote

Hi Jade,

I've had the odd issue with the newer insert behaviour in CS4 -, produces the same code as CS3. I normally work in Javascript but here goes. you might need to replace the & with + ...

The easiest way around I have found is to bypass Dreamweavers parameter section completely and set your own variables
ie

<%
dim var1
dim var2
var1 = Request.Form("yeshotf"
var2 = Request.Form("useridf"

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 (" & var1 & "," & var2 & ""
MM_editCmd.Prepared = true
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
End If
End If
%>

hi Jade,

Can you give me definition of the table you are doing the inserts to. When I last converted some MS access sites over to MS SQL i got lots of errors with field definitions and lengths. I believe there are different modes that MYSql can be in too dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
you may not be able to change these though if you are running on a shared host

Seb

Edited by - Seb Adlington on 27 Mar 2010  17:14:41

Hey Seb,

I went ahead and used your suggestion and modified some of the INSERT command using the "Dim var1......and the...VALUES ('"& var1 &"','"& var2 &"'....etc. It works perfectly...so thank-you for that tip! There is still one more issue that remains now...when a field contains a single quote like ' in it...it gives an error when trying to INSERT it. I have been reading around on the other forum and can't find the solution yet on how to include every "illegal" charater when using the INSERT from the modified Dreamweavwe ASP code into MySql. I never had that problem when inserting data into an access database. Is there a simple code for that to include before inserting and also when quering and displaying what is the best way to reverse that do it can display the single quote correctly. I notice some mention of stripping slashes and using the replace() options. Thank you and I appreciate your time!

Quote

hi Jade,

Can you give me definition of the table you are doing the inserts to. When I last converted some MS access sites over to MS SQL i got lots of errors with field definitions and lengths. I believe there are different modes that MYSql can be in too dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
you may not be able to change these though if you are running on a shared host

Seb

Edited by - Seb Adlington on 27 Mar 2010  17:14:41

Easiest way would be to just Escape the characters using the Replace function
So your string to insert might be "This is my 'String'. "
and you need to insert it as "This is my \'String\'. "

so once you've got your variable

var1 = Replace(var1,"'","\'")

I think that should work for you

Cheers

Edited by - Seb Adlington on 30 Mar 2010  14:42:40

Thanks for the reply. I had actually went ahead and did a replace("'","''" a few days ago to all my insert field. And then I had to use the replace("''","'" on all of the displayed results before displaying the data from that field. If I did the Escape method as you suggested, would I also have to use the replace ("\'","'" to remove the \ or it is just a temperary solution to allow the ' into the field without actually adding a \ into the data. And are there some other charater that you recommend me using the Escape for that is already a known issue in MySql? Thanks for your reply! Have a great day!

Jade

Quote

Easiest way would be to just Escape the characters using the Replace function
So your string to insert might be "This is my 'String'. "
and you need to insert it as "This is my \'String\'. "

so once you've got your variable

var1 = Replace(var1,"'","\'")

I think that should work for you

Cheers

Edited by - Seb Adlington on 30 Mar 2010  14:42:40

It shouldn't actually insert the \ into your tables so you don't have to mess around removing stuff when showing the data again. give it a try

Thanks you very much for your help...I apprec[][]iate it!!!

Have a great day!

Jade

Quote

It shouldn't actually insert the \ into your tables so you don't have to mess around removing stuff when showing the data again. give it a try

Pleasure - good luck [8D]