August 5, 2003 at 2:52 am
Hi all,
I walked into a n existing web site and one of the first things I did was turn all the connection strings into OLEDB connections to msSQL rather than the existing ODBC connections. This was fine except for one query that returns the ID of the last inserted record. This query refuses to return a value using the OLE connection string. Heres the code;
MM_RedstarRemote_STRING = "Provider=SQLOLEDB;Data Source=IP address;Initial Catalog=DB required;User ID=USERNAME;Password=PASSWORD;"
set cmdInsert = Server.CreateObject("ADODB.Command")
cmdInsert.ActiveConnection = MM_RemoteOLE_STRING
cmdInsert.CommandText = "INSERT INTO dbo.New_Accounts (cola, colb) VALUES (cola, colb) Select SCOPE_IDENTITY() AS ID"
cmdInsert.CommandType = 1
cmdInsert.CommandTimeout = 0
cmdInsert.Prepared = true
Set rsID = cmdInsert.Execute
If I run the code using an ODBC connection string everything is fine once again. I am assumming it is something about the cmdInsert properties but cant find an exhaustive list anywhere.
Any ideas greatly appreciated.
Thanks
Rolf
August 5, 2003 at 6:19 am
Im assuming that the missing ";" between the statements is a typo?
Andy
August 5, 2003 at 2:08 pm
Yes,
The same query works fine without any alteration if the connection string is altered.
Rolf
August 6, 2003 at 3:59 am
hi!
basically the code should work, except that i wouldn't assign a string to the Activeconnection property, which in fact is a ADODB.Connection object (this is just hidden by script-kiddy ASP).
try something like that (avoid using ADODB.Command where possible):
connStr = "Provider=SQLOLEDB.1;Data Source=your_server;Initial Catalog=your_db"
' Create connection object (client side cursor) and open it with sql server user
Set conn = Server.CreateObject("ADODB.Connection")
conn.CursorLocation = 3
conn.Open(connStr, "username", "password, 0)
' Execute insert statement and get new id into recordset
conn.Execute("INSERT INTO dbo.new_accounts ...")
Set rs = conn.Execute("SELECT SCOPE_IDENTITY() AS new_id")
Response.Write rs.Fields("new_id")
' Cleanup
Set rs = Nothing
Set conn = Nothing
best regards,
chris.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply