Query string to call up row data from SCOPE_IDENTITY

Question

I've got a submission page for users to input data. Then I redirect to a view page so they can view their input. Sessions are out, since this would be a high volume site, but I used the scope identity method at the end of my submission command. How do I query the scope ID value so that the row data is displayed in my view page?

Code is below: it's a training project, and I was expressly forbidden to parameterize in the interest of simplicity. But yes, I know paramaterizing is the way to go.

protected void Button1_Click(object sender, EventArgs e)
{
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    String thisQuery = "INSERT INTO ProductInstance (CustId, CustName, SicNaic, CustAdd, CustCity, CustState, CustZip, BroId, BroName, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments) VALUES ('" + TextBox19.Text + "', '" + TextBox1.Text + "', '" + RadioButtonList1.SelectedItem + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + DropDownList1.SelectedItem + "', '" + TextBox4.Text + "', '" + TextBox18.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "', '" + TextBox7.Text + "', '" + DropDownList2.SelectedItem + "', '" + TextBox8.Text + "', '" + DropDownList3.SelectedItem + "','" + TextBox9.Text + "','" + TextBox10.Text + "','" + TextBox11.Text + "','" + TextBox12.Text + "','" + TextBox20.Text + "','" + TextBox14.Text + "','" + TextBox15.Text + "','" + TextBox16.Text + "'); SELECT SCOPE_IDENTITY() AS [lastInsertedProductId]";

    using (SqlConnection sqlConn = new SqlConnection(connectionString))
    {
        sqlConn.Open();

        using (SqlCommand command = new SqlCommand(thisQuery, sqlConn))
        {
            int lastInsertedProductId = Convert.ToInt32(command.ExecuteScalar());
        }
    }
    Response.Redirect("~/View.aspx");

and then the view page code is here:

protected void Page_Load(object sender, EventArgs e)
{
    string x = Request.QueryString["ProductId"];
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    string editQuery = "SELECT CustId, CustName, SicNaic, CustCity, CustAdd, CustState, CustZip, BroName, BroId, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments FROM ProductInstance WHERE ProductId =" + x;

    using (SqlConnection editConn = new SqlConnection(connectionString))
    {
        editConn.Open();

        using (SqlCommand command = new SqlCommand(editQuery, editConn))
        {
            SqlDataReader dr = command.ExecuteReader();
            dr.Read();
            Label6.Text = dr.GetInt32(0).ToString();

Stop everything and read this now: en.wikipedia.org/wiki/SQL_injection#Parameterized_statements — Michael Haren
– Michael Haren, Commented Apr 19, 2011 at 17:33
Hahaha yes, yes, I have read the article, and been referenced to it or something like it on just about all of my questions. The site is not for actual deployment, and as I mentioned in my question, my trainer expressly forbid me to parameterize because he wants me to focus on understanding the basics while still getting this project done. I really do appreciate how everyone jumps to point that out, though. It shows real goodwill. — Ace Troubleshooter
– Ace Troubleshooter, Commented Apr 19, 2011 at 17:39
You should consider having a look at an ORM such as EF or NHibernate. — Mikael Östberg
– Mikael Östberg, Commented Apr 19, 2011 at 17:40
I am having trouble understanding "forbidden to parameterize in the interest of simplicity" and "this would be a high volume site". If your trainer is actually trying to to give good advice but BASIC security and performance are being ignored as well as mixing the concerns of the presenter and data access, then I'm afraid you are going to have to gain your knowledge elsewhere. If there is an actual reason to use string concatenation then you MUST at least de-taint the user input strings — Adam Straughan
– Adam Straughan, Commented Apr 19, 2011 at 21:55

Bala R · Accepted Answer · 2011-04-19 17:44:11Z

1

Try moving lastInsertedProductId up in scope and

Response.Redirect("~/View.aspx?ProductId" + lastInsertedProductId );

But seriously, look into parameterized statements and http://bobby-tables.com/

edited Apr 19, 2011 at 17:44

answered Apr 19, 2011 at 17:39

Bala R

109k23 gold badges203 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ace Troubleshooter Over a year ago

Haha, I really like bobby tables. It was the first time I'd heard of sql injection, about which I have read much. Don't I need quotes around lastInsertProductId? VS spellchecks it and tells me I can't convert an int to a string, and in the redirect line it tells me it doesn't exist in current context.

Bala R Over a year ago

Try moveing the declaration of lastInsertedProductId before your using statement.

Peter Mortensen · Accepted Answer · 2012-03-20 20:44:48Z

0

You would make a stored procedure which does the work and in there you would get the SCOPE_IDENTITY.

Right after your insert.

edited Mar 20, 2012 at 20:44

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Apr 19, 2011 at 17:37

SQLMason

3,2851 gold badge32 silver badges40 bronze badges

Collectives™ on Stack Overflow

Query string to call up row data from SCOPE_IDENTITY

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related