By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,077 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,077 IT Pros & Developers. It's quick & easy.

Problem returning identity from SQL Server when string contains semicolon

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
> 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

P: n/a
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

P: n/a
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.