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 º¿º 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 º¿º
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 º¿º
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
by: Martin Z |
last post by:
INSERT INTO dbo.Transmission
(TransmissionDate, TransmissionDirection,
Filename, TransmittedData)
VALUES
(@TransmissionDate,@TransmissionDirection,@Filename,@TransmittedData);
SELECT @retVal =...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |