471,893 Members | 2,084 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

Problem returning identity from SQL Server when string contains semicolon

Dan
I'm writing a record from an asp.net page to SQL Server. After the insert
I'm selecting @@identity to return the ID of the record that I just wrote.
It worked fine until I typed a semicolon into one of the string fields to be
inserted. The string fields are inside single quotes in the INSERT command.
With the semicolon in the string, the record is written correctly including
the semicolon, but the identity is not returned. Without a semicolon in the
string, the identity is returned correctly. The code is below, any
suggestions would be appreciated. The field with the semicolon is
ContractNumber. Thanks.

lsSQL = "INSERT INTO tblVPContracts (NonContracted, ContractNumber,
POReqNumber, ForInfoOnly, ImmediateActionReq, ModifiedBy) VALUES (" _
+ NonContracted.ToString + ", '" + tbContractNum.Text + "', '" +
TextBox1.Text + "', " + ForInfoOnly.ToString + ", " +
ImmediateAction.ToString + ", " + UserID.ToString + " )"
Dim MyCommand As SqlCommand = New SqlCommand(lsSQL, conn)
MyCommand.ExecuteNonQuery()
Dim sSelect As String = "SELECT @@IDENTITY as NewID"
Dim DataSet As New DataSet
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(sSelect, conn)
adapter.Fill(DataSet, "Identity")
cookie.Values.Add("VPContractID",
DataSet.Tables("Identity").Rows(0)(0))
Nov 20 '05 #1
3 4382
> I'm writing a record from an asp.net page to SQL Server. After the insert
I'm selecting @@identity to return the ID of the record that I just wrote.
It worked fine until I typed a semicolon into one of the string fields to be
inserted. The string fields are inside single quotes in the INSERT command.
With the semicolon in the string, the record is written correctly including
the semicolon, but the identity is not returned. Without a semicolon in the
string, the identity is returned correctly. The code is below, any
suggestions would be appreciated. The field with the semicolon is
ContractNumber. Thanks.

lsSQL = "INSERT INTO tblVPContracts (NonContracted, ContractNumber,
POReqNumber, ForInfoOnly, ImmediateActionReq, ModifiedBy) VALUES (" _
+ NonContracted.ToString + ", '" + tbContractNum.Text + "', '" +
TextBox1.Text + "', " + ForInfoOnly.ToString + ", " +
ImmediateAction.ToString + ", " + UserID.ToString + " )"
Dim MyCommand As SqlCommand = New SqlCommand(lsSQL, conn)
MyCommand.ExecuteNonQuery()
Dim sSelect As String = "SELECT @@IDENTITY as NewID"
Dim DataSet As New DataSet
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(sSelect, conn)
adapter.Fill(DataSet, "Identity")
cookie.Values.Add("VPContractID",
DataSet.Tables("Identity").Rows(0)(0))

If that Contract Number contained a single quote ('), then your sql
statement would fail. Look up "SQL Injection Attack".
So use Parameters to pass those values! See MSDN for details.
This might also solve your semicolon problem.

Second remark: instead of filling an entire dataset with that single
identity value, use ExecuteScalar (and cast the "object" result to an
integer). This will return just the first column in the first row of
the first resultset.

Hans Kesting
Nov 20 '05 #2
there are a lot issues with your code:

@@IDENTITY returns the last identity assigned in the sql batch. your select
@@IDENTITY is in its own batch, so it returns null. you need to switch to
one batch.

@@identity does not return the correct value if a trigger is used which also
creates an identity. you should use scope_ideneity() instead.

your code allows sql injection, you should switch to parameters.

string sql = @"set nocount on
INSERT INTO tblVPContracts (
NonContracted, ContractNumber,POReqNumber,
ForInfoOnly, ImmediateActionReq, ModifiedBy
)
VALUES (
@NonContracted,@ContractNum,@textbox,
@ForInfoOnly,@ImmediateAction,@userid
)
select scope_identity as newid";

SqlCommand cmd = new SqlCommand(sql, conn)
cmd.Parameters.Add(@NonContracted,SqlDbType.Int,0) ;
....
int newId = cmd.ExecuteScaler();
-- bruce (sqlwork.com)

"Dan" <da**********@po.state.ct.us> wrote in message
news:ef****************@TK2MSFTNGP09.phx.gbl...
I'm writing a record from an asp.net page to SQL Server. After the insert
I'm selecting @@identity to return the ID of the record that I just wrote.
It worked fine until I typed a semicolon into one of the string fields to
be
inserted. The string fields are inside single quotes in the INSERT
command.
With the semicolon in the string, the record is written correctly
including
the semicolon, but the identity is not returned. Without a semicolon in
the
string, the identity is returned correctly. The code is below, any
suggestions would be appreciated. The field with the semicolon is
ContractNumber. Thanks.

lsSQL = "INSERT INTO tblVPContracts (NonContracted, ContractNumber,
POReqNumber, ForInfoOnly, ImmediateActionReq, ModifiedBy) VALUES (" _
+ NonContracted.ToString + ", '" + tbContractNum.Text + "', '" +
TextBox1.Text + "', " + ForInfoOnly.ToString + ", " +
ImmediateAction.ToString + ", " + UserID.ToString + " )"
Dim MyCommand As SqlCommand = New SqlCommand(lsSQL, conn)
MyCommand.ExecuteNonQuery()
Dim sSelect As String = "SELECT @@IDENTITY as NewID"
Dim DataSet As New DataSet
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(sSelect, conn)
adapter.Fill(DataSet, "Identity")
cookie.Values.Add("VPContractID",
DataSet.Tables("Identity").Rows(0)(0))

Nov 20 '05 #3
Dan
Both of these suggestions were a big help.
Thanks

"Hans Kesting" <ne***********@spamgourmet.com> wrote in message
news:mn***********************@spamgourmet.com...
I'm writing a record from an asp.net page to SQL Server. After the insert I'm selecting @@identity to return the ID of the record that I just wrote. It worked fine until I typed a semicolon into one of the string fields to be inserted. The string fields are inside single quotes in the INSERT command. With the semicolon in the string, the record is written correctly including the semicolon, but the identity is not returned. Without a semicolon in the string, the identity is returned correctly. The code is below, any
suggestions would be appreciated. The field with the semicolon is
ContractNumber. Thanks.

lsSQL = "INSERT INTO tblVPContracts (NonContracted, ContractNumber, POReqNumber, ForInfoOnly, ImmediateActionReq, ModifiedBy) VALUES (" _
+ NonContracted.ToString + ", '" + tbContractNum.Text + "', '" +
TextBox1.Text + "', " + ForInfoOnly.ToString + ", " +
ImmediateAction.ToString + ", " + UserID.ToString + " )"
Dim MyCommand As SqlCommand = New SqlCommand(lsSQL, conn)
MyCommand.ExecuteNonQuery()
Dim sSelect As String = "SELECT @@IDENTITY as NewID"
Dim DataSet As New DataSet
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(sSelect, conn)
adapter.Fill(DataSet, "Identity")
cookie.Values.Add("VPContractID",
DataSet.Tables("Identity").Rows(0)(0))

If that Contract Number contained a single quote ('), then your sql
statement would fail. Look up "SQL Injection Attack".
So use Parameters to pass those values! See MSDN for details.
This might also solve your semicolon problem.

Second remark: instead of filling an entire dataset with that single
identity value, use ExecuteScalar (and cast the "object" result to an
integer). This will return just the first column in the first row of
the first resultset.

Hans Kesting

Nov 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Andrew | last post: by
4 posts views Thread by Chris Gatto | last post: by
4 posts views Thread by Jeff B | last post: by
18 posts views Thread by jslowery | last post: by
9 posts views Thread by =?Utf-8?B?dHBhcmtzNjk=?= | last post: by
reply views Thread by YellowAndGreen | last post: by

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.