|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
difficulty with SELECT SCOPE_IDENTITY();in vb/asp/sql-server, and trying to insert a record into a table, and retrieve the new index. Dim objDataConn Dim strSql Dim intNewKeyVal Set objDataConn = Server.CreateObject("ADODB.Connection") objDataConn.Open("Provider=SQLOLEDB;server=(local);Database=MY_DB;uid=sa;pwd=password") strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue); SELECT SCOPE_IDENTITY();" My problem is with retrieving the value. If I do the following: set intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected, adCmdText + adExecuteNoRecords ) It creates the new row, but intNewKeyVal is 'Nothing' after the call. If, instead, I do this: intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected, adCmdText + adExecuteNoRecords ) I get the following error: Microsoft VBScript runtime error: Object variable not set Any suggestions will be much appreciated. Thanks. Joe Joe,
How confident are you that the semi-colon delimiter that separates your two SQL statements does not put the second statement in a different scope to the first? I don't know the answer, but as you are passing a batch of statements pretty much straight to the server, you could just as easily try something like: strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue)" & vbLf & "SELECT SCOPE_IDENTITY()" -or- strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue)" & CHR(10) & "SELECT SCOPE_IDENTITY()" -or even just- strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue) SELECT SCOPE_IDENTITY()" In any event, given that you are programming in an ASP environment, you shouldn't really be taking these kind of shortcuts for performance reasons. Surely the more appropriate approach is to create a small "insert" Stored Procedure that takes FieldValue as an input parameter value & returns the generated Identity value as an output parameter value, and then use the ADODB.Command object to execute it? Let me know if you're not with me. Robert Show quote "Joe Befumo" <jbef***@impeachapalooza.org> wrote in message news:11usmg98dfl5p19@corp.supernews.com... > This has me stymied, but I'm betting it's something real simple. I'm > working in vb/asp/sql-server, and trying to insert a record into a table, > and retrieve the new index. > > > > Dim objDataConn > > Dim strSql > > Dim intNewKeyVal > > > > Set objDataConn = Server.CreateObject("ADODB.Connection") > objDataConn.Open("Provider=SQLOLEDB;server=(local);Database=MY_DB;uid=sa;pwd=password") > > > > strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue); SELECT > SCOPE_IDENTITY();" > > > > My problem is with retrieving the value. > > > > If I do the following: > > > > set intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected, > adCmdText + adExecuteNoRecords ) > > > > It creates the new row, but intNewKeyVal is 'Nothing' after the call. If, > instead, I do this: > > > > intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected, adCmdText + > adExecuteNoRecords ) > > > > I get the following error: > > > > Microsoft VBScript runtime error: Object variable not set > > > > Any suggestions will be much appreciated. > > > > Thanks. > > > > Joe > > Hi Robert,
I tried it both ways with the same results. I also tried the resultant SQL string in the SQL-Server query tool & it worked either way. Still head scratching. Joe Show quote "Robert Ellis" <robe_***@n0sp8m.hotmail.co.uk> wrote in message news:eGppDZ2LGHA.1536@TK2MSFTNGP11.phx.gbl... > Joe, > > How confident are you that the semi-colon delimiter that separates your > two SQL statements does not put the second statement in a different scope > to the first? I don't know the answer, but as you are passing a batch of > statements pretty much straight to the server, you could just as easily > try something like: > > strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue)" & vbLf & > "SELECT SCOPE_IDENTITY()" > -or- > strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue)" & CHR(10) & > "SELECT SCOPE_IDENTITY()" > -or even just- > strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue) SELECT > SCOPE_IDENTITY()" > > In any event, given that you are programming in an ASP environment, you > shouldn't really be taking these kind of shortcuts for performance > reasons. Surely the more appropriate approach is to create a small > "insert" Stored Procedure that takes FieldValue as an input parameter > value & returns the generated Identity value as an output parameter value, > and then use the ADODB.Command object to execute it? Let me know if you're > not with me. > > Robert > > > > > > > "Joe Befumo" <jbef***@impeachapalooza.org> wrote in message > news:11usmg98dfl5p19@corp.supernews.com... >> This has me stymied, but I'm betting it's something real simple. I'm >> working in vb/asp/sql-server, and trying to insert a record into a table, >> and retrieve the new index. >> >> >> >> Dim objDataConn >> >> Dim strSql >> >> Dim intNewKeyVal >> >> >> >> Set objDataConn = Server.CreateObject("ADODB.Connection") >> objDataConn.Open("Provider=SQLOLEDB;server=(local);Database=MY_DB;uid=sa;pwd=password") >> >> >> >> strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue); SELECT >> SCOPE_IDENTITY();" >> >> >> >> My problem is with retrieving the value. >> >> >> >> If I do the following: >> >> >> >> set intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected, >> adCmdText + adExecuteNoRecords ) >> >> >> >> It creates the new row, but intNewKeyVal is 'Nothing' after the call. If, >> instead, I do this: >> >> >> >> intNewKeyVal = objDataConn.Execute(strSql, intRecordsAffected, adCmdText >> + adExecuteNoRecords ) >> >> >> >> I get the following error: >> >> >> >> Microsoft VBScript runtime error: Object variable not set >> >> >> >> Any suggestions will be much appreciated. >> >> >> >> Thanks. >> >> >> >> Joe >> >> > > -- Posted via NewsDemon.com - Premium Uncensored Newsgroup Service ------->>>>>>http://www.NewsDemon.com<<<<<<------ Unlimited Access, Anonymous Accounts, Uncensored Broadband Access On Sat, 11 Feb 2006 16:40:33 -0500, Joe Befumo wrote:
(snip) >strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue); SELECT Hi Joe,>SCOPE_IDENTITY();" > > > >My problem is with retrieving the value. Probably because the INSERT returns a "1 row(s) affected" message, which is regarded as a seperate result set by the frontend. The easy fix is to include SET NOCOUNT ON at the beginning of your SQL string: strSql = "SET NOCOUNT ON; INSERT INTO myTable (fieldname) VALUES (fieldvalue); SELECT SCOPE_IDENTITY();" The good fix is to create a stored procedure that holds these three commands and call the stored procedure from your code. -- Hugo Kornelis, SQL Server MVP Hi Hugo,
I tried that, both with and without semicolons (see prior suggestion) with no change. I may have to bite the bullet and learn how to write stored procedures. Thanks, Joe Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message news:k3vsu1hmb57h40ie6g4vi62k10ao9edt9m@4ax.com... > On Sat, 11 Feb 2006 16:40:33 -0500, Joe Befumo wrote: > > (snip) >>strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue); SELECT >>SCOPE_IDENTITY();" >> >> >> >>My problem is with retrieving the value. > > Hi Joe, > > Probably because the INSERT returns a "1 row(s) affected" message, which > is regarded as a seperate result set by the frontend. > > The easy fix is to include SET NOCOUNT ON at the beginning of your SQL > string: > > strSql = "SET NOCOUNT ON; INSERT INTO myTable (fieldname) VALUES > (fieldvalue); SELECT SCOPE_IDENTITY();" > > The good fix is to create a stored procedure that holds these three > commands and call the stored procedure from your code. > > -- > Hugo Kornelis, SQL Server MVP -- Posted via NewsDemon.com - Premium Uncensored Newsgroup Service ------->>>>>>http://www.NewsDemon.com<<<<<<------ Unlimited Access, Anonymous Accounts, Uncensored Broadband Access GOT IT!
Thanks, Hugo -- Your mention of it returning 1 row jarred my thinking, so I tried this: objRSList.Open strSql, objDataConn and sure enough, the index was in objRSList.Fields.Item(0)! Kewl! Joe Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message news:k3vsu1hmb57h40ie6g4vi62k10ao9edt9m@4ax.com... > On Sat, 11 Feb 2006 16:40:33 -0500, Joe Befumo wrote: > > (snip) >>strSql = "INSERT INTO myTable (fieldname) VALUES (fieldvalue); SELECT >>SCOPE_IDENTITY();" >> >> >> >>My problem is with retrieving the value. > > Hi Joe, > > Probably because the INSERT returns a "1 row(s) affected" message, which > is regarded as a seperate result set by the frontend. > > The easy fix is to include SET NOCOUNT ON at the beginning of your SQL > string: > > strSql = "SET NOCOUNT ON; INSERT INTO myTable (fieldname) VALUES > (fieldvalue); SELECT SCOPE_IDENTITY();" > > The good fix is to create a stored procedure that holds these three > commands and call the stored procedure from your code. > > -- > Hugo Kornelis, SQL Server MVP -- Posted via NewsDemon.com - Premium Uncensored Newsgroup Service ------->>>>>>http://www.NewsDemon.com<<<<<<------ Unlimited Access, Anonymous Accounts, Uncensored Broadband Access  |
|||||||||||||||||||||||
Other interesting topics