birdseed birdseed - 1 month ago 7
SQL Question

Trying to get sql parameters to work

I am trying to build this site to use sql params. I thought I had followed the example I was given, but my sql data shows '@username' and 'number' instead of the values that have been put into the text boxes.

I'm stumped.

thanks for the help

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using System.Data;
using System.Data.SqlClient;

public partial class management_Default : System.Web.UI.Page
{

public void runsql(string sqlCmdTxt, bool adduserbool)
{
SqlConnection sqlConnection = new SqlConnection("Server=DELLXPS\\SQLEXPRESS; Initial Catalog=Warren_SEINDATASYSTEMS; Integrated Security=true;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = sqlCmdTxt;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;

//If statement uses adduserbool param to determine if the code needs to make sql params for users
if (adduserbool == true)
{
SqlParameter user = new SqlParameter();
user.ParameterName = "@username";
user.Value = CreateUserWizard1.UserName.Trim();
cmd.Parameters.Add(user);

if (txtboxNumberOfVisitors.Text != "")
{
SqlParameter number = new SqlParameter();
number.ParameterName = "@number";
number.Value = txtboxNumberOfVisitors.Text;
cmd.Parameters.Add(number);
}
else
{
// this else will send "2" if the text box is empty
SqlParameter number = new SqlParameter();
number.ParameterName = "@number";
number.Value = "2";
cmd.Parameters.Add(number);
}
}

sqlConnection.Open();

//cmd.ExecuteScalar();

reader = cmd.ExecuteReader();

sqlConnection.Close();

//if user was created, clear number of visitors text box
if (adduserbool == true)
{
txtboxNumberOfVisitors.Text = "";
}
}
protected void Page_Load(object sender, EventArgs e)
{

}
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
//Add residents to resident role
Roles.AddUserToRole(CreateUserWizard1.UserName, "resident");
runsql("INSERT INTO [dbo].[NumberOfVisitors] ([ResidentName],[NumberOfVisitors]) VALUES ('@username','@number')", true);

}

}

Answer

That's because you put quotes around the parameter names in your SQL INSERT statement. So SQL thinks it's a literal string, not a reference to a parameter.

Try this:

runsql("INSERT INTO [dbo].[NumberOfVisitors] ([ResidentName],[NumberOfVisitors]) VALUES (@username,@number)", true);
Comments