How to Use Asp Variables in SQL Statement

How to use ASP variables in SQL statement

Add a parameter to the SQL:

delCmd.CommandText="DELETE * FROM post WHERE (pos_ID = ?)"
delCmd.Parameters.Append delCmd.CreateParameter("posid", adInteger, adParamInput)   ' input parameter
delCmd.Parameters("posid").Value = postit

How to insert a variable in a query in asp.net c#?

Your method is open to SQL Injection. You should try this:

    protected void Button1_Click(object sender, EventArgs e)
    {
        string email = Request.QueryString["Email"];
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "INSERT INTO Job (Industry, JobPosition, ExactAddress, Region, Salary, JobDesc, EmployerID) VALUES (@industry, @jobPosition, @exactAddress, @region, @salary, @jobDesc, (Select employerid from employer where email = @email))";
        cmd.Parameters.Add("@industry", SqlDbType.VarChar, 255).Value = Industry.SelectedValue.ToString();
        cmd.Parameters.Add("@jobPosition", SqlDbType.VarChar, 255).Value = TextBox3.Text;
        cmd.Parameters.Add("@exactAddress", SqlDbType.VarChar, 255).Value = TextBox5.Text;
        cmd.Parameters.Add("@region", SqlDbType.VarChar, 255).Value = Region.SelectedValue.ToString();
        cmd.Parameters.Add("@salary", SqlDbType.VarChar, 255).Value = TextBox6.Text;
        cmd.Parameters.Add("@jobDesc", SqlDbType.VarChar, 255).Value = TextBox7.Text;
        cmd.Parameters.Add("@email", SqlDbType.VarChar, 255).Value = email.ToString();
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
        cmd.Parameters.Clear();
        Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Job Posted!');</script>");
        Response.Redirect("EmployerProfile.aspx");
    }

Also: Your method didn't work because your inner SELECT was filtered like this email = email

Pass C# variable and use it as a Column Value in SQL Statement Query

Because the value for amountfield comes from your code logic, you can safely do this:

var sql = new sqlCommand(String.Format("INSERT INTO Payments_TBL(column1,column2,{0},column3,....)Values(@value,@value,@value)", amountcolumn),new sqlConnection(conn));

But never do things like this with strings coming from your users because that would make you open for SQL injection.

Assigning variables in SQL Query c#

May be you expect something like this

var selectCommand = "SELECT TOP(10) * FROM mytable WHERE parent = @cats";
var cmd= new SqlCommand(selectCommand, yourconnetion);

cmd.Parameters["@cats"].Value = "white";

or

cmd.Parameters.AddWithValue("@cats","white");

---

Related Topics