Solved

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

Posted on 2008-10-15
13
485 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now