SCOPE_IDENTITY() in c# and no stored procedure...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

ok, I've been on this for a long time...How can I get the most recent
identity from this insert into a string?(with using this code as the insert):

string strSQL5 = "INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " + //here is where we do the PeriodID lookup calculation
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "'); " +
"SELECT SCOPE_IDENTITY()";

cnn5.Execute(strSQL5, out recordsEffected, 0);

Thanks,
Trint
 
TrintCSD said:
ok, I've been on this for a long time...How can I get the most recent
identity from this insert into a string?(with using this code as the insert):

string strSQL5 = "INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " + //here is where we do the PeriodID lookup calculation
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "'); " +
"SELECT SCOPE_IDENTITY()";

cnn5.Execute(strSQL5, out recordsEffected, 0);
Click to expand...

By using an SqlCommand object with your query and parameters. You
shouldn't concat values directly into the query, as you then allow sql
injection attacks to happen. Always use parameters.

You then specify an output parameter and simply do SELECT @outputParam =
SCOPE_IDENTITY() to load the new identity into the output parameter. You
then read the value after the Sqlcommand has been executed. :)

Frans

--
 
You may want to us an output parameter to grab this value . Typically, if
you're using a proc to update a row and grab the value afterward using
SCOPE_IDENTITY you'll get a null back, I thought this wasn't the case with
dynamic sql as you are doing but I may be wrong. I know I've had problems
with this in the past and using an output param was the easy fix.

Also, as an aside, you are concatenating all of your sql and not using
parameters. This is begging for trouble. If you get an Irish surname for
instance, it would break the command. There are performance, security and
quality issues associated with using concatenated strings w/out parameters
so by using params, you'll fix your problem the whole way around.

I can point you to how to use params if you aren't familiar with them - if
not, let me know.

Cheers,

Bill
 
Thanks...but...
ok, how do i declair that variable and read it? Here is my code since I
read your reply:
"SELECT @outputParam = SCOPE_IDENTITY()";

cnn5.Execute(strSQL5, out recordsEffected, 0);
textBox1.Text = Convert.ToString(rs5.Fields["@outputParam"].Value);
Here is the error I get:
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in WindowsApplication1.exe

Additional information: Must declare the variable '@outputParam'.
Thanks,
Trint
 
Yes, i need to get this going, so any help is appreciated.
Thanks,
Trint

W.G. Ryan eMVP said:
You may want to us an output parameter to grab this value . Typically, if
you're using a proc to update a row and grab the value afterward using
SCOPE_IDENTITY you'll get a null back, I thought this wasn't the case with
dynamic sql as you are doing but I may be wrong. I know I've had problems
with this in the past and using an output param was the easy fix.

Also, as an aside, you are concatenating all of your sql and not using
parameters. This is begging for trouble. If you get an Irish surname for
instance, it would break the command. There are performance, security and
quality issues associated with using concatenated strings w/out parameters
so by using params, you'll fix your problem the whole way around.

I can point you to how to use params if you aren't familiar with them - if
not, let me know.

Cheers,

Bill
Click to expand...
 
I do appreciate your reply, but in both articles (maybe I am missing
something), I don't see a solution to my problem that I, with my limited
knowledge, can use.
 
If you declare an output parameter, and then set it using a select statement
= scope identity as frans mentioned above the output parameter value will be
the Identity you're looking for.
 
string strSQL5 = "INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " + //here is where we do the PeriodID lookup calculation
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "') "
cnn5.Execute(strSQL5);
Click to expand...
strSQL5 = "SELECT SCOPE_IDENTITY()"
cnn5.Execute(strSQL5);
Click to expand...

Using the same cnn5 connection you can retrieve your SCOPE_IDENTITY() from
your previous INSERT.
But it must be the same SqlConnection, don't create another SqlConnection,
use cnn5 on both Execute()'s.

I think someone pointed out that you should really be using a parameterized
sql query or even better, do this in a stored procedure and have the
identity return in an OUTPUT Parameter or as a RETURN from your stored
procedure.
 
ok, I do not understand how to get a value into a stored procedure from my c#
string, int or whatever datatype. It's much easier for me to do it as you
can see in the "VALUES" part of the command with strings. What does the
VALUES look like when you create a stored procedure for insert if you don't
have the values yet.
When you call the stored procedure:
catDA.InsertCommand = new SqlCommand("GetParameterIdentity", strConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;
How do my values from textboxes, calculations and all over the place get in
there?
From all I've seen, values look like the field names with an @ sign in front
of them.
An example would probably help.
Thanks for any replies.
Trint

gabe garza said:
string strSQL5 = "INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " + //here is where we do the PeriodID lookup calculation
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "') "
cnn5.Execute(strSQL5);
Click to expand...
strSQL5 = "SELECT SCOPE_IDENTITY()"
cnn5.Execute(strSQL5);
Click to expand...

Using the same cnn5 connection you can retrieve your SCOPE_IDENTITY() from
your previous INSERT.
But it must be the same SqlConnection, don't create another SqlConnection,
use cnn5 on both Execute()'s.

I think someone pointed out that you should really be using a parameterized
sql query or even better, do this in a stored procedure and have the
identity return in an OUTPUT Parameter or as a RETURN from your stored
procedure.
Click to expand...
 
Start here
http://msdn.microsoft.com/library/d...y/en-us/vbcon/html/vboriIntegratingDataVB.asp

TrintCSD said:
ok, I do not understand how to get a value into a stored procedure from my
c#
string, int or whatever datatype. It's much easier for me to do it as you
can see in the "VALUES" part of the command with strings. What does the
VALUES look like when you create a stored procedure for insert if you
don't
have the values yet.
When you call the stored procedure:
catDA.InsertCommand = new SqlCommand("GetParameterIdentity", strConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;
How do my values from textboxes, calculations and all over the place get
in
there?
From all I've seen, values look like the field names with an @ sign in
front
of them.
An example would probably help.
Thanks for any replies.
Trint
Click to expand...
 
Ok.
This works, but I'm not sure it's exactly what I'm looking for:

scmd.CommandText ="CREATE PROCEDURE GetParameterIdentity " +
Environment.NewLine +
"@Identity bigint OUT" + Environment.NewLine +
"AS " +
" INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " +
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "') " +
"SET @Identity = SCOPE_IDENTITY()";

try
{
scmd.ExecuteNonQuery();
}
catch(SqlException expSql)
{
MessageBox.Show(expSql.ToString(), this.Text);
frmStatusMessage.Close();
return;
}

}
catch(Exception exp)
{

if (strConn == SQL_CONNECTION_STRING)
{
strConn = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
frmStatusMessage.Close();
MessageBox.Show("you must have SQL " +
"or MSDE ", this.Text);

Application.Exit();

}

}

}

frmStatusMessage.Close();

MessageBox.Show("The stored procedures were successfully added to the " +
"tsNess database.", this.Text);

HasCreatedSprocs = true;

///Start stored procedure here
strConn = SQL_CONNECTION_STRING5;
SqlConnection scnntsNess1 = new SqlConnection(strConn);
SqlCommand scmd1 = new SqlCommand("GetParameterIdentity", scnntsNess1);
SqlDataAdapter sda = new SqlDataAdapter(scmd1);
scmd1.CommandType = CommandType.StoredProcedure;

scmd1.Parameters.Add(new SqlParameter("@Identity",
SqlDbType.BigInt)).Direction = ParameterDirection.Output;

try
{
scnntsNess1.Open();

scmd1.ExecuteScalar();
}
catch(SqlException expSql)
{
MessageBox.Show(expSql.ToString(), this.Text);
return;
}
finally
{

scnntsNess1.Close();
}
// Display the results.
textBox1.Text = scmd1.Parameters["@Identity"].Value.ToString();

Thanks,
Trinity
 
Actually you'd create your stored procedure with Enterprise Manager first.
Once it's installed in the database you can call it anytime.

The way you have it now in code, that would work once but the second time
you call your code it would fail cause the stored procedure
GetParameterIdentity() already exists in the database because it was create
on your first ExecuteNonQuery() call.


Check out this link.
http://www.gbg-development.com/development.aspx
This describes how I simplified my process to call and get results from a
stored procdure. (This works with SELECT/UPDATE/DELETE statements too)

I generate an XML file that has my stored procedure parameter inputs, then I
use that XML file to create a SQLCommand so that I can use it to get my
results from a stored procedure.

So I have a T-SQL script that'll generate the XML, then I have C# class
library that will create the SQL Command, then I set the parameters before
executing the SQLCommand, then I get my result set. I can get a DataTable or
a SqlDataReader as my result set.

Check it out. Any questions let me know.


TrintCSD said:
Ok.
This works, but I'm not sure it's exactly what I'm looking for:

scmd.CommandText ="CREATE PROCEDURE GetParameterIdentity " +
Environment.NewLine +
"@Identity bigint OUT" + Environment.NewLine +
"AS " +
" INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " +
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "') " +
"SET @Identity = SCOPE_IDENTITY()";

try
{
scmd.ExecuteNonQuery();
}
catch(SqlException expSql)
{
MessageBox.Show(expSql.ToString(), this.Text);
frmStatusMessage.Close();
return;
}

}
catch(Exception exp)
{

if (strConn == SQL_CONNECTION_STRING)
{
strConn = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
frmStatusMessage.Close();
MessageBox.Show("you must have SQL " +
"or MSDE ", this.Text);

Application.Exit();

}

}

}

frmStatusMessage.Close();

MessageBox.Show("The stored procedures were successfully added to the " +
"tsNess database.", this.Text);

HasCreatedSprocs = true;

///Start stored procedure here
strConn = SQL_CONNECTION_STRING5;
SqlConnection scnntsNess1 = new SqlConnection(strConn);
SqlCommand scmd1 = new SqlCommand("GetParameterIdentity", scnntsNess1);
SqlDataAdapter sda = new SqlDataAdapter(scmd1);
scmd1.CommandType = CommandType.StoredProcedure;

scmd1.Parameters.Add(new SqlParameter("@Identity",
SqlDbType.BigInt)).Direction = ParameterDirection.Output;

try
{
scnntsNess1.Open();

scmd1.ExecuteScalar();
}
catch(SqlException expSql)
{
MessageBox.Show(expSql.ToString(), this.Text);
return;
}
finally
{

scnntsNess1.Close();
}
// Display the results.
textBox1.Text = scmd1.Parameters["@Identity"].Value.ToString();

Thanks,
Trinity

TrintCSD said:
Gabe,
adCmdStoredProc does not work in c#, only seen it working in vb.net.
Click to expand...
Click to expand...
 
Ok, let my try doing that and I will let you know.
Thanks,
Trint

gabe garza said:
Actually you'd create your stored procedure with Enterprise Manager first.
Once it's installed in the database you can call it anytime.

The way you have it now in code, that would work once but the second time
you call your code it would fail cause the stored procedure
GetParameterIdentity() already exists in the database because it was create
on your first ExecuteNonQuery() call.


Check out this link.
http://www.gbg-development.com/development.aspx
This describes how I simplified my process to call and get results from a
stored procdure. (This works with SELECT/UPDATE/DELETE statements too)

I generate an XML file that has my stored procedure parameter inputs, then I
use that XML file to create a SQLCommand so that I can use it to get my
results from a stored procedure.

So I have a T-SQL script that'll generate the XML, then I have C# class
library that will create the SQL Command, then I set the parameters before
executing the SQLCommand, then I get my result set. I can get a DataTable or
a SqlDataReader as my result set.

Check it out. Any questions let me know.


TrintCSD said:
Ok.
This works, but I'm not sure it's exactly what I'm looking for:

scmd.CommandText ="CREATE PROCEDURE GetParameterIdentity " +
Environment.NewLine +
"@Identity bigint OUT" + Environment.NewLine +
"AS " +
" INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " +
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "') " +
"SET @Identity = SCOPE_IDENTITY()";

try
{
scmd.ExecuteNonQuery();
}
catch(SqlException expSql)
{
MessageBox.Show(expSql.ToString(), this.Text);
frmStatusMessage.Close();
return;
}

}
catch(Exception exp)
{

if (strConn == SQL_CONNECTION_STRING)
{
strConn = MSDE_CONNECTION_STRING;
frmStatusMessage.Show("Connecting to MSDE");
}
else
{
frmStatusMessage.Close();
MessageBox.Show("you must have SQL " +
"or MSDE ", this.Text);

Application.Exit();

}

}

}

frmStatusMessage.Close();

MessageBox.Show("The stored procedures were successfully added to the " +
"tsNess database.", this.Text);

HasCreatedSprocs = true;

///Start stored procedure here
strConn = SQL_CONNECTION_STRING5;
SqlConnection scnntsNess1 = new SqlConnection(strConn);
SqlCommand scmd1 = new SqlCommand("GetParameterIdentity", scnntsNess1);
SqlDataAdapter sda = new SqlDataAdapter(scmd1);
scmd1.CommandType = CommandType.StoredProcedure;

scmd1.Parameters.Add(new SqlParameter("@Identity",
SqlDbType.BigInt)).Direction = ParameterDirection.Output;

try
{
scnntsNess1.Open();

scmd1.ExecuteScalar();
}
catch(SqlException expSql)
{
MessageBox.Show(expSql.ToString(), this.Text);
return;
}
finally
{

scnntsNess1.Close();
}
// Display the results.
textBox1.Text = scmd1.Parameters["@Identity"].Value.ToString();

Thanks,
Trinity

TrintCSD said:
Gabe,
adCmdStoredProc does not work in c#, only seen it working in vb.net.

:

Start here
http://msdn.microsoft.com/library/d...y/en-us/vbcon/html/vboriIntegratingDataVB.asp

ok, I do not understand how to get a value into a stored procedure
from my
c#
string, int or whatever datatype. It's much easier for me to do it
as you
can see in the "VALUES" part of the command with strings. What does
the
VALUES look like when you create a stored procedure for insert if you
don't
have the values yet.
When you call the stored procedure:
catDA.InsertCommand = new SqlCommand("GetParameterIdentity",
strConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;
How do my values from textboxes, calculations and all over the place
get
in
there?
From all I've seen, values look like the field names with an @ sign
in
front
of them.
An example would probably help.
Thanks for any replies.
Trint

:


string strSQL5 = "INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " + //here is where we do the PeriodID lookup
calculation
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "') "
cnn5.Execute(strSQL5);

strSQL5 = "SELECT SCOPE_IDENTITY()"
cnn5.Execute(strSQL5);

Using the same cnn5 connection you can retrieve your
SCOPE_IDENTITY()
from
your previous INSERT.
But it must be the same SqlConnection, don't create another
SqlConnection,
use cnn5 on both Execute()'s.

I think someone pointed out that you should really be using a
parameterized
sql query or even better, do this in a stored procedure and have the
identity return in an OUTPUT Parameter or as a RETURN from your
stored
procedure.


ok, I've been on this for a long time...How can I get the most
recent
identity from this insert into a string?(with using this code as
the
insert):

string strSQL5 = "INSERT INTO tblTravelDetail(MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " + //here is where we do the PeriodID lookup
calculation
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "'); " +
"SELECT SCOPE_IDENTITY()";

cnn5.Execute(strSQL5, out recordsEffected, 0);

Thanks,
Trint
Click to expand...
Click to expand...
Click to expand...
 
while all of the other suggestions are good suggestions and better methods of
handling the queries, the actual answer to your question is:

int NewID=(int) cnn5.ExecuteScalar(strSQL5)
 
Back
Top