Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2008-10-15
13
501 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

838 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