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 NewbieHave you considered using a relational design, which will never have
IDENTITY in its tables? These exposed physical locators have nothign to
do with RDBMS or a valid logical model.
Have you considered using a stored procedure inside the database instead
of building a query on the fly at run time? Why do you think that a row
and record are anything alike?
A spec that tells us what you are trying to do, long with some DDL would
be very helpful. Do you program from things this vague and imcomplete?
Us, neither.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||If you have nothing useful to say, just save it for someone who might care
that you try and make yourself feel good at the expense of others.
Especially those who are new like me.
Best Regards
The Inimitable Mr Newbie
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:OaEY7XgGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||I see you are reusing the same SqlCommand object but I don't see where you
are clearing the parameters collection from the first statement.
Consequently, the command will still contain the unneeded @.pSectionName
parameter when you execute SELECT SCOPE_IDENTITY(). You might try:
sqlCommand.Parameters.Clear()
sqlCommand.CommandText = sqlIDQuery
Also, consider parameterizing the entire INSERT statement or executing a
proc passing parameters. Parameterized values are more secure and avoid the
need to worry about things like embedded quotes in strings and date formats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mr Newbie" <here@.now.com> wrote in message
news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Sorry for double posting but I screwed the last one up. 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
>|||Thanks for your reply.
I tried clearing the Params, but that had no effect. so I created an
entirely new sqlCommandID object which used the same connection as the other
one for the scope_identity() but this had no effect.
I know I should parameterise the other parts of the sql query, but they dont
really need it because the contain no user input. Only the section title
carries this on creation.
Any other ideas. This is really bugging me.
Best Regards
The Inimitable Mr Newbie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OyzuingGGHA.2040@.TK2MSFTNGP14.phx.gbl...
>I see you are reusing the same SqlCommand object but I don't see where you
>are clearing the parameters collection from the first statement.
>Consequently, the command will still contain the unneeded @.pSectionName
>parameter when you execute SELECT SCOPE_IDENTITY(). You might try:
> sqlCommand.Parameters.Clear()
> sqlCommand.CommandText = sqlIDQuery
> Also, consider parameterizing the entire INSERT statement or executing a
> proc passing parameters. Parameterized values are more secure and avoid
> the need to worry about things like embedded quotes in strings and date
> formats.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mr Newbie" <here@.now.com> wrote in message
> news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
Have you consider going one step further than the 'logical model' and create
an implementation or do you just deal with theory?
The IDENTITY property is a very useful way to create surrogate keys and gain
good performance and is a solution for the really big problem of when a
primary key value changes.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
How do you think report builders work? Do you have a stored procedure with
1000 parameters and 1000 if else.
Actually, let me bring you up on this point, you posted a w
or two agothat you shouldn't use IF ELSE in the database.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:OaEY7XgGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You need to run it as a single statement, it would be better if you used a
stored procedure if possible.
Anyway, the problem lies in that SCOPE_IDENTITY() is only for the
connection, your connection will have been reset between calls.
You can concatenate sqlString and sqlIDQuery just put a semi column ; after
the first statement.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mr Newbie" <here@.now.com> wrote in message
news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Sorry for double posting but I screwed the last one up. 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
>|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
>
No, there is no spec needed. Instead of preteding like you are a bad
AI program let loose, learn how ADO .Net works and you will be answer
the question without any further spec.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Mr Newbie (here@.now.com) writes:
> Sorry for double posting but I screwed the last one up. 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 ?
scope_idenity() returns the most recently generated identity value
in the current scope. Scope here is a stored procedure, or the top-
level scope. @.@.identity, on the other hand, returns the most recently
generated identity value for the connection, independent on scope.
When you don't use parameters, the INSERT command is submitted as-is,
and thus in the same scope as you later fetch scope_identity().
But when you use parameters, SqlClient submits the command through
sp_executesql. This is because SqlClient does not build the expanded
command string, but instead passes the parameters in an RPC call.
This is usually good for performance. The side effect is that the
INSERT statement no longer is in the top-level scope, and thus you
can get the identity value with scope_identity().
There are two possible ways to do:
o Use @.@.identity. This is fine as long as the table you are inserting
to does not have a trigger which in its turn insert into a table
with an identity column. In this case, @.@.identity will report the
value generated for that table. (It is to avoid this trap, that
scope_identity() was introduced.)
o As Tony suggested, add the SELECT on scope_identity() to the
batch with the INSERT statement.
The latter has the advantage of saving you a network roundtrip, which
is usally good for performance.
In fact, I would like to take you even a step further and do this:
sqlString = "INSERT INTO SECTIONS " & _
"(ArticleID, SectionName, SectionNumber, ... ) " & _
"VALUES (@.ArticleID, @.pSectionName, @.SectionNumber, ...) " & _
"SELECT @.id = scope_identity()"
1) Include the columns you are inserting into. If you leave out the column
list, and the table is later changed, you query will blow up.
2) Pass all values as parameters, don't embed values in the SQL String
(constants are OK). This protects you against SQL injection, and
other problems that could occur if the value includes an '. It also
saves you from hassle when using datetime values.
3) Make the value from scope_identity() an output parameter from
the batch. For this you need to specify the direction as InputOuput.
(SQL Server does not have any output-only parameters.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> If you have nothing useful to say, just save it for someone who might car
e that you try and make yourself feel good at the expense of others. Especia
lly those who are new like me. <<
So when I tell you think about stored procedures, it is a bad thing.
But when other people mention stored procedures as an answer, it is
good thing. Interesting.
No comments:
Post a Comment