ADO ole and SELECT SCOPE

  • 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

  • Im assuming that the missing ";" between the statements is a typo?

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Yes,

    The same query works fine without any alteration if the connection string is altered.

    Rolf

  • 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