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!
electricd7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
electricd7Author Commented:
Yep..here's your points!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.