|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SCOPE_IDENTITY()I was using this fine
sqlIDQuery = "SELECT SectionID FROM SECTIONS WHERE (SectionID=SCOPE_IDENTITY());" UNTIL. . . . I added a parameter, now it fails, it doesent matter if I use the parameter or not, when I add a param, it falls over, Any Ideas ? I know this is not the right place to post, but I know you guys are good !! -- Best Regards The Inimitable Mr Newbie º¿º Hi,
Could you please post the query with the parameter in it so we can help figure out the problem? Ken ------------------------ Show quoteHide quote "Mr Newbie" wrote: > I was using this fine > > sqlIDQuery = "SELECT SectionID FROM SECTIONS WHERE > (SectionID=SCOPE_IDENTITY());" > > UNTIL. . . . > > I added a parameter, now it fails, it doesent matter if I use the parameter > or not, when I add a param, it falls over, Any Ideas ? > > I know this is not the right place to post, but I know you guys are good !! > > > -- > Best Regards > > The Inimitable Mr Newbie º¿º > > > The following
Show quoteHide quote > code successfully inserts a record in the Sections table, but the > scope_identity() returns DbNull. If I remove the parameter > 'pSectionName' and replace it with dummy value, it works fine. > Alternatively, if I use @@IDENTITY with or withour the parameter, that > works fine too. > > It seems there is a problem with using scope_identity() when parameters > are involved, but I do need to use parameters. Does anyone know why this > would happen and how to circumvent it ? > > > Dim sqlConnection As New SqlConnection(getConnectionString()) > > Dim sqlString As String > Dim result As Integer > > Dim pSectionName As New SqlParameter("@pSectionName", > SqlDbType.NVarChar) > pSectionName.Value = sectionRow.SectionName > > sqlString = "INSERT INTO SECTIONS " & _ > "VALUES (" & _ > " '" & sectionRow.ArticleID.ToString & "'," & _ > " @pSectionName ," & _ > " '" & sectionRow.SectionNumber.ToString & "'," & _ > " '" & sectionRow.SectionFollowing.ToString & "'," & _ > " '" & sectionRow.Attachments.ToString & "'," & _ > " '" & sectionRow._Text & "'," & _ > " ''," & _ > " '" & sectionRow.pictureName & "'," & _ > " '" & sectionRow.pictureType & "'," & _ > " '" & sectionRow.pictureFilePath & "'," & _ > " '" & sectionRow.SectionType & "');" > > Dim sqlIDQuery As String > sqlIDQuery = "SELECT scope_identity();" > > Dim sqlCommand As New SqlCommand(sqlString) > sqlCommand.Connection = sqlConnection > > 'Add Parameters > sqlCommand.Parameters.Add(pSectionName) > > Dim SectionID As Integer > Try > sqlConnection.Open() > sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND > INSERTS RECORD. > > sqlCommand.CommandText = sqlIDQuery > SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' **** > FAILS HERE WITH AN EXCEPTION. > > Catch ex As Exception > SectionID = 0 > Finally > sqlConnection.Close() > End Try > > Return SectionID > > > -- > Best Regards > > The Inimitable Mr Newbie º¿º > -- Show quoteHide quoteBest Regards The Inimitable Mr Newbie º¿º "Ken Tucker [MVP]" <KenTucker***@discussions.microsoft.com> wrote in message news:654F521E-D47F-40C7-8AFA-0F0093950291@microsoft.com... > Hi, > > Could you please post the query with the parameter in it so we > can > help figure out the problem? > > Ken > ------------------------ > > "Mr Newbie" wrote: > >> I was using this fine >> >> sqlIDQuery = "SELECT SectionID FROM SECTIONS WHERE >> (SectionID=SCOPE_IDENTITY());" >> >> UNTIL. . . . >> >> I added a parameter, now it fails, it doesent matter if I use the >> parameter >> or not, when I add a param, it falls over, Any Ideas ? >> >> I know this is not the right place to post, but I know you guys are good >> !! >> >> >> -- >> Best Regards >> >> The Inimitable Mr Newbie º¿º >> >> >> Hi,
When you use sqlCommand.executenonquery you have a parameter defined and use it. At this line you change the command text for the sql command. sqlCommand.CommandText = sqlIDQuery The new command text does not have a parameter but you still have a parameter defined. TThis causes your error. Try clearing the parameters before you run the command the second time. sqlCommand.Parameters.Clear() SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' **** Ken -------------------- Show quoteHide quote "Mr Newbie" <h***@now.com> wrote in message news:uGCalFhGGHA.2696@TK2MSFTNGP14.phx.gbl... > The following >> code successfully inserts a record in the Sections table, but the >> scope_identity() returns DbNull. If I remove the parameter >> 'pSectionName' and replace it with dummy value, it works fine. >> Alternatively, if I use @@IDENTITY with or withour the parameter, that >> works fine too. >> >> It seems there is a problem with using scope_identity() when parameters >> are involved, but I do need to use parameters. Does anyone know why this >> would happen and how to circumvent it ? >> >> >> Dim sqlConnection As New SqlConnection(getConnectionString()) >> >> Dim sqlString As String >> Dim result As Integer >> >> Dim pSectionName As New SqlParameter("@pSectionName", >> SqlDbType.NVarChar) >> pSectionName.Value = sectionRow.SectionName >> >> sqlString = "INSERT INTO SECTIONS " & _ >> "VALUES (" & _ >> " '" & sectionRow.ArticleID.ToString & "'," & _ >> " @pSectionName ," & _ >> " '" & sectionRow.SectionNumber.ToString & "'," & _ >> " '" & sectionRow.SectionFollowing.ToString & "'," & _ >> " '" & sectionRow.Attachments.ToString & "'," & _ >> " '" & sectionRow._Text & "'," & _ >> " ''," & _ >> " '" & sectionRow.pictureName & "'," & _ >> " '" & sectionRow.pictureType & "'," & _ >> " '" & sectionRow.pictureFilePath & "'," & _ >> " '" & sectionRow.SectionType & "');" >> >> Dim sqlIDQuery As String >> sqlIDQuery = "SELECT scope_identity();" >> >> Dim sqlCommand As New SqlCommand(sqlString) >> sqlCommand.Connection = sqlConnection >> >> 'Add Parameters >> sqlCommand.Parameters.Add(pSectionName) >> >> Dim SectionID As Integer >> Try >> sqlConnection.Open() >> sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND >> INSERTS RECORD. >> >> sqlCommand.CommandText = sqlIDQuery >> SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' **** >> FAILS HERE WITH AN EXCEPTION. >> >> Catch ex As Exception >> SectionID = 0 >> Finally >> sqlConnection.Close() >> End Try >> >> Return SectionID >> >> >> -- >> Best Regards >> >> The Inimitable Mr Newbie º¿º >> > > > > -- > Best Regards > > The Inimitable Mr Newbie º¿º > "Ken Tucker [MVP]" <KenTucker***@discussions.microsoft.com> wrote in > message news:654F521E-D47F-40C7-8AFA-0F0093950291@microsoft.com... >> Hi, >> >> Could you please post the query with the parameter in it so we >> can >> help figure out the problem? >> >> Ken >> ------------------------ >> >> "Mr Newbie" wrote: >> >>> I was using this fine >>> >>> sqlIDQuery = "SELECT SectionID FROM SECTIONS WHERE >>> (SectionID=SCOPE_IDENTITY());" >>> >>> UNTIL. . . . >>> >>> I added a parameter, now it fails, it doesent matter if I use the >>> parameter >>> or not, when I add a param, it falls over, Any Ideas ? >>> >>> I know this is not the right place to post, but I know you guys are good >>> !! >>> >>> >>> -- >>> Best Regards >>> >>> The Inimitable Mr Newbie º¿º >>> >>> >>> > > Ive tried that ken. I have also tried creating a completely new sqlCommandID
sqlCommand object which shares the same connection but has its own SELECT scope_identity() string and that doesent work either, it's really odd.! Try it yourself with a single field table for brevity and you'll see what I mean. -- Show quoteHide quoteBest Regards The Inimitable Mr Newbie º¿º "Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message news:ONfsPqjGGHA.3036@tk2msftngp13.phx.gbl... > Hi, > > When you use sqlCommand.executenonquery you have a parameter > defined and use it. > > At this line you change the command text for the sql command. > > sqlCommand.CommandText = sqlIDQuery > > > The new command text does not have a parameter but you still have a > parameter defined. TThis causes your error. Try clearing the parameters > before you run the command the second time. > > sqlCommand.Parameters.Clear() > SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' **** > > Ken > -------------------- > "Mr Newbie" <h***@now.com> wrote in message > news:uGCalFhGGHA.2696@TK2MSFTNGP14.phx.gbl... >> The following >>> code successfully inserts a record in the Sections table, but the >>> scope_identity() returns DbNull. If I remove the parameter >>> 'pSectionName' and replace it with dummy value, it works fine. >>> Alternatively, if I use @@IDENTITY with or withour the parameter, that >>> works fine too. >>> >>> It seems there is a problem with using scope_identity() when parameters >>> are involved, but I do need to use parameters. Does anyone know why this >>> would happen and how to circumvent it ? >>> >>> >>> Dim sqlConnection As New SqlConnection(getConnectionString()) >>> >>> Dim sqlString As String >>> Dim result As Integer >>> >>> Dim pSectionName As New SqlParameter("@pSectionName", >>> SqlDbType.NVarChar) >>> pSectionName.Value = sectionRow.SectionName >>> >>> sqlString = "INSERT INTO SECTIONS " & _ >>> "VALUES (" & _ >>> " '" & sectionRow.ArticleID.ToString & "'," & _ >>> " @pSectionName ," & _ >>> " '" & sectionRow.SectionNumber.ToString & "'," & _ >>> " '" & sectionRow.SectionFollowing.ToString & "'," & >>> _ >>> " '" & sectionRow.Attachments.ToString & "'," & _ >>> " '" & sectionRow._Text & "'," & _ >>> " ''," & _ >>> " '" & sectionRow.pictureName & "'," & _ >>> " '" & sectionRow.pictureType & "'," & _ >>> " '" & sectionRow.pictureFilePath & "'," & _ >>> " '" & sectionRow.SectionType & "');" >>> >>> Dim sqlIDQuery As String >>> sqlIDQuery = "SELECT scope_identity();" >>> >>> Dim sqlCommand As New SqlCommand(sqlString) >>> sqlCommand.Connection = sqlConnection >>> >>> 'Add Parameters >>> sqlCommand.Parameters.Add(pSectionName) >>> >>> Dim SectionID As Integer >>> Try >>> sqlConnection.Open() >>> sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND >>> INSERTS RECORD. >>> >>> sqlCommand.CommandText = sqlIDQuery >>> SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' **** >>> FAILS HERE WITH AN EXCEPTION. >>> >>> Catch ex As Exception >>> SectionID = 0 >>> Finally >>> sqlConnection.Close() >>> End Try >>> >>> Return SectionID >>> >>> >>> -- >>> Best Regards >>> >>> The Inimitable Mr Newbie º¿º >>> >> >> >> >> -- >> Best Regards >> >> The Inimitable Mr Newbie º¿º >> "Ken Tucker [MVP]" <KenTucker***@discussions.microsoft.com> wrote in >> message news:654F521E-D47F-40C7-8AFA-0F0093950291@microsoft.com... >>> Hi, >>> >>> Could you please post the query with the parameter in it so we >>> can >>> help figure out the problem? >>> >>> Ken >>> ------------------------ >>> >>> "Mr Newbie" wrote: >>> >>>> I was using this fine >>>> >>>> sqlIDQuery = "SELECT SectionID FROM SECTIONS WHERE >>>> (SectionID=SCOPE_IDENTITY());" >>>> >>>> UNTIL. . . . >>>> >>>> I added a parameter, now it fails, it doesent matter if I use the >>>> parameter >>>> or not, when I add a param, it falls over, Any Ideas ? >>>> >>>> I know this is not the right place to post, but I know you guys are >>>> good !! >>>> >>>> >>>> -- >>>> Best Regards >>>> >>>> The Inimitable Mr Newbie º¿º >>>> >>>> >>>> >> >> > > 
Other interesting topics
Which database should I use?
vbexpress and sql2000 How to set the TextBox digital only? Formatting the ComboBox editing window help !!!! databinding with text box help urgent Wall Charts 2005 No Setup Project in 2005 Express Edition? Keep form on top of everything - even other apps ? Namespace not available... |
|||||||||||||||||||||||
