Solved

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

Posted on 2008-10-15
13
514 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
[X]
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
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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…
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

636 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