scope_identity() issues.

M

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
 
G

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
 
O

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
Click to expand...

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

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

Otis Mukinfus said:
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
Click to expand...

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to get scope_identity here? 3
SCOPE_IDENTITY and parameters during insert 6
why is this code executed twice? 2
error msg help needed on stored procedure 4
Error Message could not find a non-generic method 6
Error: Object reference not set to an instance of an object 3
Need an OOP expert for some simple newie questions 4
Getting Autoincrement ID in ADO.NET 6

Top