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

--
Best Regards

The Inimitable Mr Newbie º¿º

[Ken Tucker [MVP]]

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

>
>
> --
> Best Regards
>
> The Inimitable Mr Newbie º¿º
>

--
Best Regards

The Inimitable Mr Newbie º¿º

"Ken Tucker [MVP]" <KenTuc...@discussions.microsoft.com> wrote in message
news:654F521E-D47F-40C7...@microsoft.com...

[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" <he...@now.com> wrote in message
news:uGCalFhG...@TK2MSFTNGP14.phx.gbl...

[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.

--
Best Regards

The Inimitable Mr Newbie º¿º

"Ken Tucker [MVP]" <vb...@bellsouth.net> wrote in message
news:ONfsPqjG...@tk2msftngp13.phx.gbl...