Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Standard insert record behavior cs3 not working for me (asp icm mysql dbase)

Posted on 2008-10-15
13
Medium Priority
?
527 Views
Last Modified: 2008-11-25
When I use standard insert record behavior with dw cs3 I get the following error:

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

[MySQL][ODBC 3.51 Driver][mysqld-5.0.51a]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 '@test.nl, test, test, test)' at line 1

If I make the exact same insert behavior in DW MX it works fine...

Is there something I need to change? I do not get it...

Attached is the cs3 generated insert code...
<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If
 
' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
If (CStr(Request("MM_insert")) = "Prijsvraag") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd
 
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_site_STRING
    MM_editCmd.CommandText = "INSERT INTO Abonnees (Nwsbr_Antwoord, Nwsbr_Naam, Nwsbr_Mailadres, Nwsbr_Adres, Nwsbr_Postcode, Nwsbr_Plaats) VALUES (?, ?, ?, ?, ?, ?)" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 255, Request.Form("Antwoord")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 255, Request.Form("Naam")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 255, Request.Form("Email")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 202, 1, 255, Request.Form("Adres")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 255, Request.Form("Postcode")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 202, 1, 255, Request.Form("Plaats")) ' adVarWChar
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
 
    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "prijsvraag_appel_entered.asp"
    If (Request.QueryString <> "") Then
      If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
        MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
      Else
        MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
      End If
    End If
    Response.Redirect(MM_editRedirectUrl)
  End If
End If
%>

Open in new window

0
Comment
Question by:Witheet
  • 7
  • 4
  • 2
13 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 22733500
If it works in the older version of DW but not the newer, you may need to delete the Connection file and rebuild it from CS3.  DW changed the syntax in that file slightly from MX to CS3 and the newer behavior may be expecting something in there.

After rebuilding the connection, I would also rebuild the Insert behavior just to be safe.
0
 

Author Comment

by:Witheet
ID: 22733644
Hello Jason,

TNX.. .sounds like a plan.. I'm going to do this and post back asap...

0
 

Author Comment

by:Witheet
ID: 22733876
I deleted connection file and remade in cs3... made a new page and new insert record...

this resulted in the same driver error, but with a different description...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.51a]Unknown column 'a' in 'field list'

INSTEAD OF BEFORE:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-5.0.51a]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 '@test.nl, test, test, test)' at line 1

I do not get it.. .the exact same insert works when made in mx...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 22733971
>> syntax to use near '@test.nl, test, test, test)'

Hmm.

Did you alter any part of the ASP code on the page?  What appears to be going on is that the query is choking up the MySQL server because of the @ sign in the email.  This means that the symbol is not being converted to a "safe" entity.

Let me ask someone better than me with ASP to look at this...
0
 

Author Comment

by:Witheet
ID: 22734009
No, I did not alter anything... is all straight out the box code...

Tanx!

0
 
LVL 25

Expert Comment

by:Rouchie
ID: 22738427
Hi there

Your code *looks* okay, so let's try resolving this one step at a time.  I wonder if for your chosen language that the @ symbol uses a different unicode reference.

Can you please try inserting an email address without the @ symbol ?  Try something like test_test.com

If that works then the problem is definitely the @ symbol as Jason suggests.  (You might have to remove page forms validation temporarily if you page checks for an actual email address before allowing the form to be submitted).
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 22738441
Ah, also another idea to make sure that your web page uses the same encoding as MySQL.  

So, if you MySQL email database field is Unidode, then you need to ensure that your web pages are also set to Unicode, like this:

  <head>
       <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
       <... remainder of page head contents here ....>
  </head>

Also use Dreamweaver's SAVE AS function to check the page save properties and ensure UTF8 (Unicode) option is being used.
0
 

Author Comment

by:Witheet
ID: 22738520
Hello Rouchie,

Ill be working outside the office today so no quick change to try your comments... I will do this as soon as I get back and post back asap!!!

Tanx for reply!!!!
0
 

Author Comment

by:Witheet
ID: 22756206
I tried it befor without the @ symbol but no change...
Encoding is all set the way cs3 sets it and like u stated...

Any other ideas? Is it something with " ' maybe? In mx u see this replace function in the insert statement in cs3 not???
0
 
LVL 25

Accepted Solution

by:
Rouchie earned 1500 total points
ID: 22756276
In the old MX code with the ' symbol, we do a technique to double-up single quotes, because single quotes can break a SQL statement.  The CS3 method uses parameterised queries, which are not affected by this reliability problem.

Clearly something is wrong but I don't see it in the code.  Do you have access to create Stored Procedures in MySQL?  If so, please try this method because it is much better than creating queries within ASP, and much more secure.

http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx
0
 

Author Comment

by:Witheet
ID: 22756293
I do and I will def. try this... Have been playing with this but no real result... This article seems very easy to follow...  
tnx for your time!
0
 

Author Comment

by:Witheet
ID: 22756297
I will leave the question open for a bit... If no other comments follow soon ill shut down and award the points...
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 22756312
Stored procedures are superb because they split the logic cleanly into two parts.  If a problem occurs, you can then execute stored procedures directly in MySQL without needing to use ASP/HTML, which makes these problems much easier to solve.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question