Update table using SqlCommand in asp.net

Question

I'm trying to update a SQL Server table using SqlCommand, however, the table never gets updated when I run the code

My ASPX:

<label id="lblEmail">Email:</label>
<asp:TextBox id="txtEmail" runat="server"/>
<label id="lblcode">Code:</label>
<asp:TextBox id="txtCode" runat="server"/>
<asp:ImageButton ID="btnSubmit" runat="server" ImageUrl="Images/Submit.gif" OnClick="btnSubmit_Click" Width="80px"/>
<asp:ImageButton ID="btnReset" runat="server" ImageUrl="Images/Reset.gif" OnClick="btnReset_Click" Width="80px"/>

This is my aspx.cs:

protected void btnSubmit_Click(object sender, EventArgs e)
{
    string Email = txtEmail.Text.ToString();
    string AttendingCode = txtRegCode.Text.ToString();

    SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=bkdb;User ID=sa;Password=sa");

    SqlCommand cmd = new SqlCommand("UPDATE Registration SET [Attending] = 1 WHERE [Email] = '@Email' and [AttendingCode] LIKE '%@AttendingCode%'", con);

    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email;
    cmd.Parameters.Add("@AttendingCode", SqlDbType.VarChar).Value = AttendingCode;

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

Grant Winney · Accepted Answer · 2013-12-31 03:50:07Z

4

You don't need single apostrophes around your parameters. I suspect your parameters names (i.e. @Email) might be getting interpreted as literals.

Try removing the single apostrophes from around your parameters:

SqlCommand cmd = new SqlCommand("UPDATE Registration SET [Attending] = 1 WHERE [Email] = @Email and [AttendingCode] LIKE '%' + @AttendingCode + '%'", con);

answered Dec 31, 2013 at 3:50

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

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

2 Comments

user3148149 Over a year ago

when I run the code now the table gets update Thank

marc_s Over a year ago

@user3148149: if this answer helped you solve your problem, then please accept this answer. This will show your appreciation for the people who spent their own time to help you.

Dimitar Dimitrov · Accepted Answer · 2013-12-31 03:50:50Z

2

It seems that you're not passing the parameters correct - you don't really need the quotes in your query, give this a shot:

using (var con = new SqlConnection("Data Source=localhost;Initial Catalog=bkdb;User ID=sa;Password=sa"))
using (var cmd = new SqlCommand(
    "UPDATE Registration SET [Attending] = 1 WHERE [Email] = @Email and [AttendingCode] LIKE @AttendingCode",
    con))
{
    cmd.Parameters.AddWithValue("@Email", Email);
    cmd.Parameters.AddWithValue("@AttendingCode", string.Format("%{0}%", AttendingCode));

    con.Open();
    cmd.ExecuteNonQuery();
}

answered Dec 31, 2013 at 3:50

Dimitar Dimitrov

15.2k9 gold badges55 silver badges82 bronze badges

Comments

Steve Wellens · Accepted Answer · 2013-12-31 03:45:30Z

0

Try just selecting the records using your WHERE clause. If the WHERE clause doesn't find any records nothing will be updated.

There may be trailing spaces or a case sensitivity issue that causes the WHERE to fail.

For more info: http://weblogs.asp.net/stevewellens/archive/2009/10/16/why-sql-updates-fail-three-reasons.aspx

Of course, you could mistakenly be updating a different database than you think.

answered Dec 31, 2013 at 3:45

Steve Wellens

20.6k2 gold badges31 silver badges70 bronze badges

Collectives™ on Stack Overflow

Update table using SqlCommand in asp.net

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related