?
Solved

Getting this error when inserting record to a MySQL database

Posted on 2006-06-01
8
Medium Priority
?
487 Views
Last Modified: 2012-08-14



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

[MySQL][ODBC 3.51 Driver][mysqld-4.1.18-standard]Unknown system variable 'NOCOUNT'

page.asp, line 116


  If (Not TM_abortEdit) Then
    ' execute the insert
    Set TM_editCmd = Server.CreateObject("ADODB.Command")
    TM_editCmd.ActiveConnection = TM_editConnection
    TM_editCmd.CommandText = "SET NOCOUNT ON;" & TM_editQuery & ";SELECT myID = @@IDENTITY"
    Set rs = TM_editCmd.Execute
    if NOT rs.EOF Then
      Session("OrderIDNum") = rs.Fields.Item("myID").Value
    end if    
    TM_editCmd.ActiveConnection.Close

    If (TM_editRedirectUrl <> "") Then
      Response.Redirect(TM_editRedirectUrl)
    End If
  End If

End If
%>
0
Comment
Question by:morako
  • 4
  • 4
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 16811145
SET NOCOUNT is only for Microsoft SQL Server AFAIK.

If (Not TM_abortEdit) Then
    ' execute the insert
    Set TM_editCmd = Server.CreateObject("ADODB.Command")
    TM_editCmd.ActiveConnection = TM_editConnection
    TM_editCmd.CommandText = "" & TM_editQuery & ";SELECT myID = @@IDENTITY"
    Set rs = TM_editCmd.Execute
    if rs.state = 0 then
      set rs = rs.NextRecordset
    end if
    if NOT rs.EOF Then
      Session("OrderIDNum") = rs.Fields.Item("myID").Value
    end if    
    TM_editCmd.ActiveConnection.Close

    If (TM_editRedirectUrl <> "") Then
      Response.Redirect(TM_editRedirectUrl)
    End If
  End If

End If
%>
0
 

Author Comment

by:morako
ID: 16811190
Thanks for the info.

I am now getting this error.  I am trying to retrieve and autonumber after an insert.

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

[MySQL][ODBC 3.51 Driver][mysqld-4.1.18-standard]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 ';SELECT myID = @@IDENTITY' at line 1



For:

  If (Not TM_abortEdit) Then
    ' execute the insert
    Set TM_editCmd = Server.CreateObject("ADODB.Command")
    TM_editCmd.ActiveConnection = TM_editConnection
    TM_editCmd.CommandText = "" & TM_editQuery & ";SELECT myID = @@IDENTITY"
    Set rs = TM_editCmd.Execute
    if NOT rs.EOF Then
      Session("OrderIDNum") = rs.Fields.Item("myID").Value
    end if    
    TM_editCmd.ActiveConnection.Close

    If (TM_editRedirectUrl <> "") Then
      Response.Redirect(TM_editRedirectUrl)
    End If
  End If

End If
%>
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16811250
did you set the OPTION value in the connection string to allow multiple queries?

like the following:
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& "DATABASE=test;" _
& "UID=root;" _
& "PWD=12345;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:morako
ID: 16811278
This is the whole ASP script

<%@LANGUAGE="VBSCRIPT"%>
<!--#include virtual="/Connections/Order.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim TM_abortEdit
Dim TM_editQuery
Dim TM_editCmd

Dim TM_editConnection
Dim TM_editTable
Dim TM_editRedirectUrl
Dim TM_editColumn
Dim TM_recordId

Dim TM_fieldsStr
Dim TM_columnsStr
Dim TM_fields
Dim TM_columns
Dim TM_typeArray
Dim TM_formVal
Dim TM_delim
Dim TM_altVal
Dim TM_emptyVal
Dim TM_i

TM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  TM_editAction = TM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
TM_abortEdit = false

' query string to execute
TM_editQuery = ""
%>
<%
'TFM_Insert-Retrieve ID
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "AddOrder") Then
  TM_editConnection = MM_Order_STRING
  TM_editTable = "mediaorders"
  TM_editRedirectUrl = ""
  TM_fieldsStr  = "orderdate|value|parent|value"
  TM_columnsStr = "orderdate|',none,''|parent|',none,''"

  ' create the TM_fields and TM_columns arrays
  TM_fields = Split(TM_fieldsStr, "|")
  TM_columns = Split(TM_columnsStr, "|")
 
  ' set the form values
  For TM_i = LBound(TM_fields) To UBound(TM_fields) Step 2
    TM_fields(TM_i+1) = CStr(Request.Form(TM_fields(TM_i)))
  Next

  ' append the query string to the redirect URL
  If (TM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, TM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      TM_editRedirectUrl = TM_editRedirectUrl & "?" & Request.QueryString
    Else
      TM_editRedirectUrl = TM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

Dim TM_tableValues
Dim TM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

  ' create the sql insert statement
  TM_tableValues = ""
  TM_dbValues = ""
  For TM_i = LBound(TM_fields) To UBound(TM_fields) Step 2
    TM_formVal = TM_fields(TM_i+1)
    TM_typeArray = Split(TM_columns(TM_i+1),",")
    TM_delim = TM_typeArray(0)
    If (TM_delim = "none") Then TM_delim = ""
    TM_altVal = TM_typeArray(1)
    If (TM_altVal = "none") Then TM_altVal = ""
    TM_emptyVal = TM_typeArray(2)
    If (TM_emptyVal = "none") Then TM_emptyVal = ""
    If (TM_formVal = "") Then
      TM_formVal = TM_emptyVal
    Else
      If (TM_altVal <> "") Then
        TM_formVal = TM_altVal
      ElseIf (TM_delim = "'") Then  ' escape quotes
        TM_formVal = "'" & Replace(TM_formVal,"'","''") & "'"
      Else
        TM_formVal = TM_delim + TM_formVal + TM_delim
      End If
    End If
    If (TM_i <> LBound(TM_fields)) Then
      TM_tableValues = TM_tableValues & ","
      TM_dbValues = TM_dbValues & ","
    End If
    TM_tableValues = TM_tableValues & TM_columns(TM_i)
    TM_dbValues = TM_dbValues & TM_formVal
  Next
  TM_editQuery = "insert into " & TM_editTable & " (" & TM_tableValues & ") values (" & TM_dbValues & ")"

  If (Not TM_abortEdit) Then
    ' execute the insert
    Set TM_editCmd = Server.CreateObject("ADODB.Command")
    TM_editCmd.ActiveConnection = TM_editConnection
    TM_editCmd.CommandText = "" & TM_editQuery & ";SELECT myID = @@IDENTITY"
    Set rs = TM_editCmd.Execute
    if NOT rs.EOF Then
      Session("OrderIDNum") = rs.Fields.Item("myID").Value
    end if    
    TM_editCmd.ActiveConnection.Close

    If (TM_editRedirectUrl <> "") Then
      Response.Redirect(TM_editRedirectUrl)
    End If
  End If

End If
%>

<form method="POST" action="<%=MM_editAction%>" name="AddOrder">
<input type="text" name="orderdate" value="" size="32">

<input type="text" name="parent" value="" size="32">

</form>
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16811311
>This is the whole ASP script

except that the connection string is not in there, but in the following file:
<!--#include virtual="/Connections/Order.asp" -->
0
 

Author Comment

by:morako
ID: 16811325

<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_Order_STRING
MM_Order_STRING = "dsn=Order;"
%>
0
 

Author Comment

by:morako
ID: 16811466
Gonna go another direction.  Thanks ;-)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16813521
>MM_Order_STRING = "dsn=Order;"

this means you have a DSN Entry. Check if the OPTION is set over there, or add the OPTION in here:

MM_Order_STRING = "dsn=Order;" & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

864 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