scope_identity() issues.

[Mr Newbie]

The following code is comprises a function to create a section of a
documemt.
Each section is stored in a table. This 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

 

[Guest]

Your scope_identity () function falls out of the scope of the first (insert)
call , and therefore returns null. try to combine the two statements into one
call and you will get the result you want. Something similar to the following:

sqlString = "Insert ..." & ControlChars.CrLf & "SELECT scope_identity();"
....
SectionID = CType(sqlCommand.ExecuteScalar, Integer)

HTH

 

[Karl Seguin [MVP]]

Yes, and
(1) use parameterized queries
(2) stored procedures


Karl

 

[Otis Mukinfus]

Using Dynamic SQL is not a very good practice in .NET Here is an
example of a stored procedure that does what you are trying to do.

ALTER PROCEDURE [dbo].[sp_Author_Insert]
@LastName varchar(50),
@FirstName varchar(50),
@MiddleName varchar(50),
@Comment varchar(1000)
AS
INSERT INTO
Author(
LastName,
FirstName,
MiddleName,
Comment
)
VALUES(
@LastName,
@FirstName,
@MiddleName,
@Comment
)
RETURN SCOPE_IDENTITY()

There is a column in this table named ID which is an identity column.

The RETURN SCOPE_IDENTITY() statement gets the last generated identity
number.

Study your VB documentation regarding Parameterized Commands. In that
documentation there will be an example of how to get the return value
from a Parameterized query.

Good luck...

The following code is comprises a function to create a section of a
documemt.
Each section is stored in a table. This 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

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[Patrick.O.Ige]

To support Otis advice
Try looking at this article at:-
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
On how to return values
Hope that helps
Patrick

Using Dynamic SQL is not a very good practice in .NET Here is an
example of a stored procedure that does what you are trying to do.

ALTER PROCEDURE [dbo].[sp_Author_Insert]
@LastName varchar(50),
@FirstName varchar(50),
@MiddleName varchar(50),
@Comment varchar(1000)
AS
INSERT INTO
Author(
LastName,
FirstName,
MiddleName,
Comment
)
VALUES(
@LastName,
@FirstName,
@MiddleName,
@Comment
)
RETURN SCOPE_IDENTITY()

There is a column in this table named ID which is an identity column.

The RETURN SCOPE_IDENTITY() statement gets the last generated identity
number.

Study your VB documentation regarding Parameterized Commands. In that
documentation there will be an example of how to get the return value
from a Parameterized query.

Good luck...

The following code is comprises a function to create a section of a
documemt.
Each section is stored in a table. This 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

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com