Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

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

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
Witheet
Asked:
Witheet
  • 7
  • 4
  • 2
1 Solution
 
Jason C. LevineNo oneCommented:
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
 
WitheetAuthor Commented:
Hello Jason,

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

0
 
WitheetAuthor Commented:
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jason C. LevineNo oneCommented:
>> 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
 
WitheetAuthor Commented:
No, I did not alter anything... is all straight out the box code...

Tanx!

0
 
RouchieCommented:
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
 
RouchieCommented:
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
 
WitheetAuthor Commented:
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
 
WitheetAuthor Commented:
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
 
RouchieCommented:
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
 
WitheetAuthor Commented:
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
 
WitheetAuthor Commented:
I will leave the question open for a bit... If no other comments follow soon ill shut down and award the points...
0
 
RouchieCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now