• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 846
  • Last Modified:

How to get @@IDENTITY field from MySQL Insert statement like Set NOCOUNT ON for MS SQL

I have an INSERT statement I am using to input data into a MySQL database.  I have used Dreamweaver to construct the page and have the following code for the insert:

<%
If (CStr(Request("MM_insert")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_CONN_STRING
    MM_editCmd.CommandText = "INSERT INTO videovault.users (joindate, lastpwchange, fname, lname, username, password, email, localonly) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 135, 1, -1, MM_IIF(Request.Form("joindate"), Request.Form("joindate"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 135, 1, -1, MM_IIF(Request.Form("lastpwchange"), Request.Form("lastpwchange"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 201, 1, 45, Request.Form("fname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 201, 1, 45, Request.Form("lname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 201, 1, 45, Request.Form("username")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 201, 1, 20, Request.Form("password")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 201, 1, 45, Request.Form("email")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 201, 1, 1, MM_IIF(Request.Form("localonly"), "Y", "N")) ' adLongVarChar
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "blank.htm"
    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
%>

I need to be able to get the unique identity field "ID" from the record which is created in that query.  I tried using SET NOCOUNT ON, but apparently that is only a MSSQL command.  Can someone tell me what to add to the code to get the ID i need?  Thanks!
0
electricd7
Asked:
electricd7
  • 8
  • 6
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes:




MM_editCmd.CommandText = "SET NOCOUNT ON;   INSERT  INTO videovault.users (joindate, lastpwchange, fname, lname, username, password, email, localonly) VALUES (?, ?, ?, ?, ?, ?, ?, ?); SELECT SCOPE_IDENTITY() NEW_ID"  
... 
dim r 
set r = MM_editCmd.Execute
response.write r.fields("new_id").value

Open in new window

0
 
electricd7Author Commented:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Unknown system variable 'NOCOUNT'

/adduser.asp, line 63

LINE 63 = MM_editCmd.Execute
0
 
hieloCommented:
MM_editCmd.CommandText = "SET NOCOUNT ON;   INSERT  INTO videovault.users (joindate, lastpwchange, fname, lname, username, password, email, localonly) VALUES (?, ?, ?, ?, ?, ?, ?, ?); SELECT LAST_INSERT_ID() NEW_ID"  
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
electricd7Author Commented:
nm..forgot to take out the original MM_editCmd.Execute...so I took that out and now I have error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Unknown system variable 'NOCOUNT'

/adduser.asp, line 64

Line 64 = set r = MM_editCmd.Execute
0
 
hieloCommented:
I meant:
SELECT LAST_INSERT_ID() AS NEW_ID
0
 
electricd7Author Commented:
Tried both..still getting "Unknown system variable 'NOCOUNT' ".  guessing you can't use NOCOUNT with mySQL?
0
 
hieloCommented:
Get ID of Newest Inserted Record

MS SQL: SET NOCOUNT ON; INSERT INTO...; SELECT id=@@IDENTITY; SET NOCOUNT OFF;
MyQL: Two step process:
1. Execute your statement: objConn.Execute("INSERT INTO...")
2. Set objRS = objConn.Execute("SELECT LAST_INSERT_ID() AS ID")
0
 
hieloCommented:
>> guessing you can't use NOCOUNT with mySQL?
Correct
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I indeed overlooked that it was for MySQL, sorry.
hielo has given the correct answer, I think, with the 2 queries to be run.
0
 
electricd7Author Commented:
OK, so now I have this:

<%
If (CStr(Request("MM_insert")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_CONN_STRING
    MM_editCmd.CommandText = "INSERT INTO videovault.users (joindate, lastpwchange, fname, lname, username, password, email, localonly) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 135, 1, -1, MM_IIF(Request.Form("joindate"), Request.Form("joindate"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 135, 1, -1, MM_IIF(Request.Form("lastpwchange"), Request.Form("lastpwchange"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 201, 1, 45, Request.Form("fname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 201, 1, 45, Request.Form("lname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 201, 1, 45, Request.Form("username")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 201, 1, 20, Request.Form("password")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 201, 1, 45, Request.Form("email")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 201, 1, 1, MM_IIF(Request.Form("localonly"), "Y", "N")) ' adLongVarChar
    MM_editCmd.Execute
      Set objRS = MM_editCmd.Execute("SELECT LAST_INSERT_ID() AS ID")
      Response.Write objRS("ID")
    MM_editCmd.ActiveConnection.Close

    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "blank.htm"
    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
%>

and I get this error:
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/adduser.asp, line 65

LINE 65 = Response.Write objRS("ID")
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

      Set objRS = MM_editCmd.Execute("SELECT LAST_INSERT_ID() AS ID")

needs to be

    Set MM_cnn = Server.CreateObject ("ADODB.Connection")
    MM_cnn.Open MM_CONN_STRING

     Set objRS = MM_cnn.Execute("SELECT LAST_INSERT_ID() AS ID")

 
note: as you will need the connection object anyhow, you can also change:

MM_editCmd.ActiveConnection = MM_CONN_STRING
to
MM_editCmd.ActiveConnection = MM_cnn

but you need to "move up" the 2 lines that create an open the connection to above that part...


0
 
electricd7Author Commented:
Ok that kinda works...I now the code below and it returned 0 for the record id?

<%
If (CStr(Request("MM_insert")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd
 
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_CONN_STRING
    MM_editCmd.CommandText = "INSERT INTO videovault.users (joindate, lastpwchange, fname, lname, username, password, email, localonly) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 135, 1, -1, MM_IIF(Request.Form("joindate"), Request.Form("joindate"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 135, 1, -1, MM_IIF(Request.Form("lastpwchange"), Request.Form("lastpwchange"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 201, 1, 45, Request.Form("fname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 201, 1, 45, Request.Form("lname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 201, 1, 45, Request.Form("username")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 201, 1, 20, Request.Form("password")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 201, 1, 45, Request.Form("email")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 201, 1, 1, MM_IIF(Request.Form("localonly"), "Y", "N")) ' adLongVarChar
    MM_editCmd.Execute
	Set MM_cnn = Server.CreateObject ("ADODB.Connection")
    MM_cnn.Open MM_CONN_STRING
    Set objRS = MM_cnn.Execute("SELECT LAST_INSERT_ID() AS ID")
 
	Response.Write objRS("ID")
    MM_editCmd.ActiveConnection.Close
 
    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "blank.htm"
    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
 
hieloCommented:
Try this:
<%
If (CStr(Request("MM_insert")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd
 
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_CONN_STRING
    MM_editCmd.CommandText = "INSERT INTO videovault.users (joindate, lastpwchange, fname, lname, username, password, email, localonly) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 135, 1, -1, MM_IIF(Request.Form("joindate"), Request.Form("joindate"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 135, 1, -1, MM_IIF(Request.Form("lastpwchange"), Request.Form("lastpwchange"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 201, 1, 45, Request.Form("fname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 201, 1, 45, Request.Form("lname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 201, 1, 45, Request.Form("username")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 201, 1, 20, Request.Form("password")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 201, 1, 45, Request.Form("email")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 201, 1, 1, MM_IIF(Request.Form("localonly"), "Y", "N")) ' adLongVarChar
    MM_editCmd.Execute
      Set MM_cnn = Server.CreateObject ("ADODB.Connection")
	 MM_cnn.Mode=3
    MM_cnn.Open MM_CONN_STRING
    Set objRS = Server.CreateObject ("ADODB.Recordset")
    objRS.Open ( "SELECT LAST_INSERT_ID() AS newId",MM_cnn,1, 3 )
 
      Response.Write objRS("newId")
    MM_editCmd.ActiveConnection.Close
 
    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "blank.htm"
    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
 
electricd7Author Commented:
Still returns 0 hielo?  I just don't get it?
0
 
electricd7Author Commented:
BTW, I had to take the ( ) out of the objRS.Open command because it said I cant use parentheses when calling a Sub.
0
 
hieloCommented:
No, wait. You cannot use a different connection object. So update your code to comment these:
'Set MM_cnn = Server.CreateObject ("ADODB.Connection")
       'MM_cnn.Mode=3
    'MM_cnn.Open MM_CONN_STRING

AND then on this line:
objRS.Open ( "SELECT LAST_INSERT_ID() AS newId",MM_cnn,1, 3 )

the second argument must be the connection object you used to insert your data. On the code you posted I don't see anything similar to:
Set MM_nn = Server.CreateObject("ADODB.Connection")
I guess it is somewhere else on your code. From what you have posted my guess would be:
objRS.Open ( "SELECT LAST_INSERT_ID() AS newId",MM_editCmd.ActiveConnection,1, 3 )

These changes are reflected below
<%
If (CStr(Request("MM_insert")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the insert
    Dim MM_editCmd
 
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_CONN_STRING
    MM_editCmd.CommandText = "INSERT INTO videovault.users (joindate, lastpwchange, fname, lname, username, password, email, localonly) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 135, 1, -1, MM_IIF(Request.Form("joindate"), Request.Form("joindate"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 135, 1, -1, MM_IIF(Request.Form("lastpwchange"), Request.Form("lastpwchange"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 201, 1, 45, Request.Form("fname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 201, 1, 45, Request.Form("lname")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 201, 1, 45, Request.Form("username")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 201, 1, 20, Request.Form("password")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 201, 1, 45, Request.Form("email")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 201, 1, 1, MM_IIF(Request.Form("localonly"), "Y", "N")) ' adLongVarChar
    MM_editCmd.Execute
    
      'Set MM_cnn = Server.CreateObject ("ADODB.Connection")
	 'MM_cnn.Mode=3
    'MM_cnn.Open MM_CONN_STRING 
    Set objRS = Server.CreateObject ("ADODB.Recordset")
    objRS.Open ( "SELECT LAST_INSERT_ID() AS newId",MM_editCmd.ActiveConnection ,1, 3 )
 
      Response.Write objRS("newId")
    MM_editCmd.ActiveConnection.Close
 
    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "blank.htm"
    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
 
electricd7Author Commented:
Yep..here's your points!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 8
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now