473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

scope_identity() issues.

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

--
Best Regards

The Inimitable Mr Newbie º¿º
Jan 15 '06 #1
4 1550
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

"Mr Newbie" wrote:
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

--
Best Regards

The Inimitable Mr Newbie º¿º

Jan 16 '06 #2
Yes, and
(1) use parameterized queries
(2) stored procedures
Karl

--
http://www.openmymind.net/

"Sergey Poberezovskiy" <Se*****************@discussions.microsoft.com> wrote
in message news:8F**********************************@microsof t.com...
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

"Mr Newbie" wrote:
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

--
Best Regards

The Inimitable Mr Newbie º¿º

Jan 16 '06 #3
On Sun, 15 Jan 2006 19:26:18 -0000, "Mr Newbie" <he**@now.com> wrote:

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
Jan 16 '06 #4
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" <ph***@emailaddress.com> wrote in message
news:r7********************************@4ax.com...
On Sun, 15 Jan 2006 19:26:18 -0000, "Mr Newbie" <he**@now.com> wrote:

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 theSections 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 areinvolved, but I do need to use parameters. Does anyone know why this wouldhappen 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

Jan 16 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Scrappy | last post by:
I have an ASP front end on SQL 2000 database. I have a form that submits to an insert query. The entry field is an "identity" and the primary key. I have used scope_identity() to display the...
1
by: Lauren Quantrell | last post by:
I'm using an Access2K front end on a SQL Server2K backend. I use Scope_Identity() in a lot of stored procedures to pass the newwly inserted record's unique ID to the next select statement in the...
5
by: Larry | last post by:
I am seeing a problem with an ASP application, where I have 2 tables. In the first table, the ASP inserts just 1 row and retrieves the primary key of the new row using SCOPE_IDENTITY. It then uses...
6
by: Hardy Wang | last post by:
Hi all, I have the following codes, but SCOPE_IDENTITY() just returns NULL to me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it works fine!! Since I have a trigger on the...
4
by: Mr Newbie | last post by:
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...
1
by: Justyn | last post by:
Hi everyone, I hope someone can help me out! I have added a trigger that gets fired on INSERT and which itself inserts data into another table WITHOUT an identity I used to get back the...
2
by: needin4mation | last post by:
Hi, I have generated my queries and selected the refresh the table option. This creates a select after the insert to get the scope_identity() so that I can use that key value in another table,...
8
by: Martin Z | last post by:
INSERT INTO dbo.Transmission (TransmissionDate, TransmissionDirection, Filename, TransmittedData) VALUES (@TransmissionDate,@TransmissionDirection,@Filename,@TransmittedData); SELECT @retVal =...
10
by: Mike | last post by:
Sql Server, Scope_Identity(), Ado.NET: Which is better? Using an output parameter to return Scope_Identity through ExecuteNonQuery(), or adding Select Scope_Identity() to the end of the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.