| Groups | Blog | Home |
vb.net upgrade : return @@identity not working
not working in .NET that was working in VB6 is returning the identity value. I'm getting the error message 'Cast from type DBnull to integer is not valid' with the code below: Public Function SaveclsParty() As Integer ptyID = 0 strsql = "exec sp_insert_PARTIES @ptyid = 0" strsql = strsql & ", @sysid = " & sysid strsql = strsql & ", @dmod = '" & dmod & "'" strsql = strsql & ", @modby = '" & modby & "'" strsql = strsql & ", @ptyname = '" & ptyname & "'" strsql = strsql & ", @reltype = '" & reltype & "'" strsql = strsql & ", @refattmt = '" & BoolStrg(refattmt) & "'" strsql = strsql & ", @comments = '" & comments & "'" strsql = strsql & ", @pstatus = " & pstatus Call cn.Execute(strsql) strsql = "select @@identity" Rs = New ADODB.Recordset Rs = cn.Execute(strsql) SaveclsParty = Rs.Fields(0).Value 'erroring here When I execute the insert statement in query analyzer, it inserts correctly and also returns the identity as well, just not working in .NET. What do I need to change?
Once SQL2k has executed your insert query, you then run another query to
retrieve the @@identity value that is long gone? It means what it says -- there is a null value being returned for the second query. You will need to retrieve the @@Identity as part of your iniitial query. You could use an output parameter to retrieve the @@Identity. [quoted text, click to view] "Joemanc" <jmanci@cl-law.com.donotspam> wrote in message news:08646D0B-B848-4948-B409-32AF4678FE41@microsoft.com... > I'm in the process of upgrading my app from VB6 to VB.net. One of the > things > not working in .NET that was working in VB6 is returning the identity > value. > I'm getting the error message 'Cast from type DBnull to integer is not > valid' > with the code below: > > Public Function SaveclsParty() As Integer > ptyID = 0 > strsql = "exec sp_insert_PARTIES @ptyid = 0" > strsql = strsql & ", @sysid = " & sysid > strsql = strsql & ", @dmod = '" & dmod & "'" > strsql = strsql & ", @modby = '" & modby & "'" > strsql = strsql & ", @ptyname = '" & ptyname & "'" > strsql = strsql & ", @reltype = '" & reltype & "'" > strsql = strsql & ", @refattmt = '" & BoolStrg(refattmt) & "'" > strsql = strsql & ", @comments = '" & comments & "'" > strsql = strsql & ", @pstatus = " & pstatus > Call cn.Execute(strsql) > > strsql = "select @@identity" > > Rs = New ADODB.Recordset > Rs = cn.Execute(strsql) > SaveclsParty = Rs.Fields(0).Value 'erroring here > > When I execute the insert statement in query analyzer, it inserts > correctly > and also returns the identity as well, just not working in .NET. What do I > need to change? >
[quoted text, click to view]
On Fri, 21 Apr 2006 11:42:01 -0700, Joemanc <jmanci@cl-law.com.donotspam> wrote: ¤ I'm in the process of upgrading my app from VB6 to VB.net. One of the things ¤ not working in .NET that was working in VB6 is returning the identity value. ¤ I'm getting the error message 'Cast from type DBnull to integer is not valid' ¤ with the code below: ¤ ¤ Public Function SaveclsParty() As Integer ¤ ptyID = 0 ¤ strsql = "exec sp_insert_PARTIES @ptyid = 0" ¤ strsql = strsql & ", @sysid = " & sysid ¤ strsql = strsql & ", @dmod = '" & dmod & "'" ¤ strsql = strsql & ", @modby = '" & modby & "'" ¤ strsql = strsql & ", @ptyname = '" & ptyname & "'" ¤ strsql = strsql & ", @reltype = '" & reltype & "'" ¤ strsql = strsql & ", @refattmt = '" & BoolStrg(refattmt) & "'" ¤ strsql = strsql & ", @comments = '" & comments & "'" ¤ strsql = strsql & ", @pstatus = " & pstatus ¤ Call cn.Execute(strsql) ¤ ¤ strsql = "select @@identity" ¤ ¤ Rs = New ADODB.Recordset ¤ Rs = cn.Execute(strsql) ¤ SaveclsParty = Rs.Fields(0).Value 'erroring here ¤ ¤ When I execute the insert statement in query analyzer, it inserts correctly ¤ and also returns the identity as well, just not working in .NET. What do I ¤ need to change? Sounds to me as if somewhere in your code you're attempting to assign a null value to an defined integer variable. Can you identify on which line the error occurs? Paul ~~~~
Paul - I'm erroring on this line:
SaveclsParty = Rs.Fields(0).Value 'erroring here And you are correct, the value of rs.fields(0).value is null when I step through. [quoted text, click to view] "Paul Clement" wrote:
> On Fri, 21 Apr 2006 11:42:01 -0700, Joemanc <jmanci@cl-law.com.donotspam> wrote: > > ¤ I'm in the process of upgrading my app from VB6 to VB.net. One of the things > ¤ not working in .NET that was working in VB6 is returning the identity value. > ¤ I'm getting the error message 'Cast from type DBnull to integer is not valid' > ¤ with the code below: > ¤ > ¤ Public Function SaveclsParty() As Integer > ¤ ptyID = 0 > ¤ strsql = "exec sp_insert_PARTIES @ptyid = 0" > ¤ strsql = strsql & ", @sysid = " & sysid > ¤ strsql = strsql & ", @dmod = '" & dmod & "'" > ¤ strsql = strsql & ", @modby = '" & modby & "'" > ¤ strsql = strsql & ", @ptyname = '" & ptyname & "'" > ¤ strsql = strsql & ", @reltype = '" & reltype & "'" > ¤ strsql = strsql & ", @refattmt = '" & BoolStrg(refattmt) & "'" > ¤ strsql = strsql & ", @comments = '" & comments & "'" > ¤ strsql = strsql & ", @pstatus = " & pstatus > ¤ Call cn.Execute(strsql) > ¤ > ¤ strsql = "select @@identity" > ¤ > ¤ Rs = New ADODB.Recordset > ¤ Rs = cn.Execute(strsql) > ¤ SaveclsParty = Rs.Fields(0).Value 'erroring here > ¤ > ¤ When I execute the insert statement in query analyzer, it inserts correctly > ¤ and also returns the identity as well, just not working in .NET. What do I > ¤ need to change? > > Sounds to me as if somewhere in your code you're attempting to assign a null value to an defined > integer variable. Can you identify on which line the error occurs? > > > Paul > ~~~~ > Microsoft MVP (Visual Basic)
Paul - I'm trying to return the identity, the autonumber, the id of the
record I just inserted. Sure, I could check for NULL, but why would I want to return 0? Something definitely changed from VB6 to VB.NET as this code below worked in VB6 and that is what I'm trying to figure out. [quoted text, click to view] "Paul Clement" wrote:
> On Mon, 24 Apr 2006 12:34:03 -0700, Joemanc <jmanci@cl-law.com.donotspam> wrote: > > ¤ Paul - I'm erroring on this line: > ¤ > ¤ SaveclsParty = Rs.Fields(0).Value 'erroring here > ¤ > ¤ And you are correct, the value of rs.fields(0).value is null when I step > ¤ through. > > Then you should first check the value of the field in order to determine whether it is Null > (IsDbNull). If it is Null then return zero (or whatever numeric value you choose). > > > Paul > ~~~~ > Microsoft MVP (Visual Basic)
[quoted text, click to view]
On Mon, 24 Apr 2006 12:34:03 -0700, Joemanc <jmanci@cl-law.com.donotspam> wrote: ¤ Paul - I'm erroring on this line: ¤ ¤ SaveclsParty = Rs.Fields(0).Value 'erroring here ¤ ¤ And you are correct, the value of rs.fields(0).value is null when I step ¤ through. Then you should first check the value of the field in order to determine whether it is Null (IsDbNull). If it is Null then return zero (or whatever numeric value you choose). Paul ~~~~
[quoted text, click to view]
On Tue, 25 Apr 2006 06:35:01 -0700, Joemanc <jmanci@cl-law.com.donotspam> wrote: ¤ Paul - I'm trying to return the identity, the autonumber, the id of the ¤ record I just inserted. Sure, I could check for NULL, but why would I want to ¤ return 0? ¤ ¤ Something definitely changed from VB6 to VB.NET as this code below worked in ¤ VB6 and that is what I'm trying to figure out. ¤ OK, I think I misunderstood the actual problem. If you're using a stored procedure then you should be able to handle retrieval of the identity value as well and return it to your app. See the following: http://www.aspfaq.com/show.asp?id=2174 Paul ~~~~
Looks like my best bet is to convert my code to the new ado.net object model.
Hopefully that will take care of the identity issue. [quoted text, click to view] "Paul Clement" wrote:
> On Tue, 25 Apr 2006 06:35:01 -0700, Joemanc <jmanci@cl-law.com.donotspam> wrote: > > ¤ Paul - I'm trying to return the identity, the autonumber, the id of the > ¤ record I just inserted. Sure, I could check for NULL, but why would I want to > ¤ return 0? > ¤ > ¤ Something definitely changed from VB6 to VB.NET as this code below worked in > ¤ VB6 and that is what I'm trying to figure out. > ¤ > > OK, I think I misunderstood the actual problem. > > If you're using a stored procedure then you should be able to handle retrieval of the identity value > as well and return it to your app. See the following: > > http://www.aspfaq.com/show.asp?id=2174 > > > Paul > ~~~~ > Microsoft MVP (Visual Basic)
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
March 2007
April 2007
May 2007
July 2007
August 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |