SCOPE_IDENTITY()

[Mr Newbie]

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 !!

 

[Guest]

Hi,

Could you please post the query with the parameter in it so we can
help figure out the problem?

Ken

 

[Mr Newbie]

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

 

[Ken Tucker [MVP]]

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]

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.