|
| |
|
|
malky800
Posts: 116
Joined: 1/11/2006
Status: offline
 |
get record number in response - 12/20/2007 14:51:05
After I send an insert statement to the Sql SERVER, i need in response the record id number.
I was told it's something like "add @@ identity".
Any ideas?
i just ran this:
Insert_ansi.CommandType = 1
Insert_ansi.CommandTimeout = 0
Insert_ansi.Prepared = true
Insert_ansi.Execute()
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: get record number in response - 12/20/2007 16:08:36
Not sure exactly how you're using it but if you're using an open recordset, right after that execute, do something like this:
...
mySQL = "SELECT @@IDENTITY AS NewID"
Set myRS = conntemp.Execute(mySQL)
myNewID = myRS.Fields("NewID").value
...
be sure to change conntemp to your connection.
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
malky800
Posts: 116
Joined: 1/11/2006
Status: offline
|
RE: get record number in response - 12/23/2007 0:39:08
So I got the record number and now I am trying to update the record in the SQL table based on that record number.
I'm getting an error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. "
Set Insert_ansi = Server.CreateObject ("ADODB.Command")
Insert_ansi.ActiveConnection = MM_auctionConn2_STRING
Insert_ansi.CommandText = "UPDATE dbo.AuctionOnlineOrders SET DonorFirstName = " & (Request("first_name")) &", DonorLastName = " &(Request("last_name")) &", address = " &(Request("address_street"))&", city=" (Request("address_city")) &", [state]= " &(Request("address_state"))&", zip= " & (Request("address_zip"))&",Email=" &(Request("payer_email"))&", Country=" &request("address_country")&", approvalCode=" &request("verify_sign")&", transid=" &request("verify_sign") &" WHERE ID = 864 "
response.write(Insert_ansi.CommandText)
Insert_ansi.Execute()
Does this error have any connection to perhaps not having UPDATE permissions or is it something wrong in the coding?
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: get record number in response - 12/23/2007 15:02:48
quote:
Insert_ansi.CommandText = "UPDATE dbo.AuctionOnlineOrders SET DonorFirstName = " & (Request("first_name")) &", DonorLastName = " &(Request("last_name")) &", address = " &(Request("address_street"))&", city=" (Request("address_city")) &", [state]= " &(Request("address_state"))&", zip= " & (Request("address_zip"))&",Email=" &(Request("payer_email"))&", Country=" &request("address_country")&", approvalCode=" &request("verify_sign")&", transid=" &request("verify_sign") &" WHERE ID = 864 "
It looks like there may be quite a few syntax errors if those are text fields. Text fields need apostrophes as in:
Insert_ansi.CommandText = "UPDATE dbo.AuctionOnlineOrders SET DonorFirstName = '" & (Request("first_name")) &"', DonorLastName = '" &(Request("last_name")) &"', address = '" &(Request("address_street"))&"', ...
See them?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
malky800
Posts: 116
Joined: 1/11/2006
Status: offline
|
RE: get record number in response - 12/23/2007 22:25:08
I've but the update statement shorter to test it out.
I added the apostrophes, but I'm getting the same error.
Any other suggestions at the way it is now.
quote:
Insert_ansi.CommandText = "UPDATE dbo.AuctionOnlineOrders SET ApprovalCode='" &request("verify_sign")&"', transid='" &request("verify_sign") &"' WHERE ID = 864 "
| |
|
|
|
William Lee
Posts: 1176
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: get record number in response - 12/23/2007 22:41:57
quote:
ORIGINAL: malky800
I'm getting an error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. "
Insert_ansi.Execute()
Does this error have any connection to perhaps not having UPDATE permissions or is it something wrong in the coding?
Remove the (), try it like this:
Insert_ansi.Execute
_____________________________
William Lee
pǝssǝɟoɹd-ɟ1ǝs ʎɥʇɹoʍʇsnɹʇ ʇsoɯ ɹnoʎ
nɹnb ǝsɐqɐʇɐp & dsɐ ,ʍɹp ,ǝbɐdʇuoɹɟ
| |
|
|
|
malky800
Posts: 116
Joined: 1/11/2006
Status: offline
|
RE: get record number in response - 12/23/2007 22:51:00
No, it didn't help.
| |
|
|
|
William Lee
Posts: 1176
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: get record number in response - 12/23/2007 23:03:35
Is your database connection opened? Don't see it in your code.
_____________________________
William Lee
pǝssǝɟoɹd-ɟ1ǝs ʎɥʇɹoʍʇsnɹʇ ʇsoɯ ɹnoʎ
nɹnb ǝsɐqɐʇɐp & dsɐ ,ʍɹp ,ǝbɐdʇuoɹɟ
| |
|
|
|
malky800
Posts: 116
Joined: 1/11/2006
Status: offline
|
RE: get record number in response - 12/24/2007 12:16:48
I cut out all extra from the coding. I am just going to try to update 1 field in the record. I am pasting the entire code. My database programmer tells me I have permission to update the field.
quote:
Set Insert_ansi = Server.CreateObject ("ADODB.Command")
Insert_ansi.ActiveConnection = MM_auctionConn2_STRING
Insert_ansi.CommandText = "UPDATE dbo.AuctionOnlineOrders SET ApprovalCode = 'aaaaa' WHERE ID = '864' "
Insert_ansi.Prepared = true
response.write(Insert_ansi.CommandText)
Insert_ansi.Execute()
Insert_ansi.ActiveConnection.Close
response.write(Insert_ansi.CommandText)
Does the where statement need or not need quotes?
| |
|
|
|
William Lee
Posts: 1176
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: get record number in response - 12/25/2007 1:19:39
If the ID column is of numeric datatype, then no single quotes are required to enclose the value.
Does your page include another page that declares MM_auctionConn2_STRING ?
If you response.write MM_auctionConn2_STRING , does it point to your db?
_____________________________
William Lee
pǝssǝɟoɹd-ɟ1ǝs ʎɥʇɹoʍʇsnɹʇ ʇsoɯ ɹnoʎ
nɹnb ǝsɐqɐʇɐp & dsɐ ,ʍɹp ,ǝbɐdʇuoɹɟ
| |
|
|
|
malky800
Posts: 116
Joined: 1/11/2006
Status: offline
|
RE: get record number in response - 12/25/2007 12:53:21
Thank you all for your help.
I had an incorrect connection string.
| |
|
|
|
Joey
Posts: 172
Joined: 5/15/2002
From:
Status: offline
|
RE: get record number in response - 1/10/2008 17:31:41
I'm trying to do something simular. I need to submit the form results in 2 tables and add the record ID from the first table into the second. Not sure how
something like:
<%DIM conntemp, mySQL, myDSN
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../../fpdb/test.mdb")
'myDSN would be specific to your environment
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
mySQL = "INSERT INTO first_table (first_name, last_name, ) VALUES"
mySQL = mySQL & " ('" & Request("firstname") & "','" & Request("lastname") & "')"
mySQL = "INSERT INTO second_table (ID, partno, description, ) VALUES"
mySQL = mySQL & " ('" & Request("first_tableID") & "',''" & Request("partno") & "','" & Request("description") & "')"
conntemp.execute(mySQL)
conntemp.close
set conntemp=nothing
Response.write " "
%>
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: get record number in response - 1/11/2008 8:48:43
Something like this should work:
...
mySQL = "INSERT INTO first_table (first_name, last_name, ) VALUES"
mySQL = mySQL & " ('" & Request("firstname") & "','" & Request("lastname") & "')"
mySQL_ID = "SELECT Max(ID) AS NewID FROM first_table"
set rs_ID=conntemp.execute(mySQL_ID)
myNewIDArray = rs_ID.getrows
myNewID = myNewIDArray(0,0)
mySQL = "INSERT INTO second_table (ID, partno, description, ) VALUES"
mySQL = mySQL & " ('" & myNewID & "',''" & Request("partno") & "','" & Request("description") & "')"
....
Does that make any sense? Hope it helps.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joey
Posts: 172
Joined: 5/15/2002
From:
Status: offline
|
RE: get record number in response - 1/15/2008 0:21:20
Thanks Roger, saved me again!
| |
|
|
|
Joey
Posts: 172
Joined: 5/15/2002
From:
Status: offline
|
RE: get record number in response - 1/15/2008 21:57:24
The data for the customer table is not being inserted, the rows are being inserted in the listings table with the prior record :
mySQL = "INSERT INTO customers (first_name) VALUES"
mySQL = mySQL & " ('" & Request("first_name") & "')"
mySQL_ID = "SELECT Max(ID) AS NewID FROM customers"
set rs_ID=conntemp.execute(mySQL_ID)
myNewIDArray = rs_ID.getrows
myNewID = myNewIDArray(0,0)
for i = 1 to 4
mySQL = "INSERT INTO listings (cust_id) VALUES"
mySQL = mySQL & " ('" & myNewID & "')"
conntemp.execute(mySQL)
NEXT
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: get record number in response - 1/15/2008 22:46:53
quote:
...
mySQL = "INSERT INTO customers (first_name) VALUES"
mySQL = mySQL & " ('" & Request("first_name") & "')"
mySQL_ID = "SELECT Max(ID) AS NewID FROM customers"
set rs_ID=conntemp.execute(mySQL_ID)
myNewIDArray = rs_ID.getrows
...
Sorry, missed an execute. Try it this way:
...
mySQL = "INSERT INTO customers (first_name) VALUES"
mySQL = mySQL & " ('" & Request("first_name") & "')"
conntemp.execute(mySQL)
mySQL_ID = "SELECT Max(ID) AS NewID FROM customers"
set rs_ID=conntemp.execute(mySQL_ID)
myNewIDArray = rs_ID.getrows
...
That should be better.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Joey
Posts: 172
Joined: 5/15/2002
From:
Status: offline
|
RE: get record number in response - 1/16/2008 12:01:04
Thanks Roger, works perfectly!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
