Parameters passed to Stored Procedure not working

[vncntj]

I keep getting "The best overloaded method match for
'System.Data.SqlClient.SqlParameter.SqlParameter".....

protected void Button1_Click(object sender, EventArgs e)
{
SqlDataReader rdr = null;
SqlConnection con = null;
SqlCommand cmd = null;

try
{
// Open connection to the database
string ConnectionString =
@"server=localhost;uid=;pwd=;trusted_connection=true;database=PMAEvents";
con = new SqlConnection(ConnectionString);
con.Open();

// Set up a command with the given query and associate
// this with the current connection.

string CommandText = "SELECT Locations, EventTitle FROM
specialevents WHERE Locations LIKE '@Find'";

cmd = new SqlCommand(CommandText);
cmd.Connection = con;

// Add LastName to the above defined parameter @Find
cmd.Parameters.Add(new SqlParameter("@Find",
System.Data.SqlDbType.NVarChar, "Locations")); // The name of the
source column

// Fill the parameter with the value retrieved
// from the text field
cmd.Parameters["@Find"].Value = txtFind.Text;

// Execute the query
rdr = cmd.ExecuteReader();

// Fill the list box with the values retrieved
lbFound.Items.Clear();
while (rdr.Read())
{
lbFound.Items.Add(rdr["EventTitle"].ToString() +
" " + rdr["Locations"].ToString());
}
}
catch(Exception ex)
{
// Print error message
MessageBox.Show(ex.Message);
}
finally{
// Close data reader object and database connection
if(rdr != null)
rdr.Close();

if(con.State == ConnectionState.Open)
con.Close();
}
}

 

[David Browne]

I keep getting "The best overloaded method match for
'System.Data.SqlClient.SqlParameter.SqlParameter".....

protected void Button1_Click(object sender, EventArgs e)
{
SqlDataReader rdr = null;
SqlConnection con = null;
SqlCommand cmd = null;

try
{
// Open connection to the database
string ConnectionString =
@"server=localhost;uid=;pwd=;trusted_connection=true;database=PMAEvents";
con = new SqlConnection(ConnectionString);
con.Open();

// Set up a command with the given query and associate
// this with the current connection.

string CommandText = "SELECT Locations, EventTitle FROM
specialevents WHERE Locations LIKE '@Find'";

Should be

string CommandText = "SELECT Locations, EventTitle FROM
specialevents WHERE Locations LIKE @Find";
When you use a parameter, you don't use the quotes. Otherwise you will be
searching for a location called "@Find".

cmd = new SqlCommand(CommandText);
cmd.Connection = con;

// Add LastName to the above defined parameter @Find
cmd.Parameters.Add(new SqlParameter("@Find",
System.Data.SqlDbType.NVarChar, "Locations")); // The name of the
source column
. . ..

You are passing (String, SqlDbType, String) to the SqlParameter
constructor. There is just no constructor for SqlParameter matching that
signature.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.sqlparameter.aspx


David

 

[vncntj]

David -
Thanks! Can I ask one last question??!!
my syntax below works perfectly. (Thanks to you) But, if I wanted to
get the data from stored procedure named "ps_events" and pass @Find,
instead of using my own Select statement, how would I alter syntax?

Regards,
vj



// Open connection to the database
string ConnectionString =
@"server=localhost;uid=;pwd=;trusted_connection=true;database=PMAEvents";
con = new SqlConnection(ConnectionString);
con.Open();

// Set up a command with the given query and associate
// this with the current connection.

string CommandText = "SELECT Locations, EventTitle FROM
specialevents WHERE Locations LIKE @Find";

cmd = new SqlCommand(CommandText);
cmd.Connection = con;

// Add LastName to the above defined parameter @Find
//cmd.Parameters.Add(new SqlParameter("@Find",
System.Data.SqlDbType.NVarChar, "Locations")); // The name of the
source column
cmd.Parameters.Add(new SqlParameter("@Find",
System.Data.SqlDbType.VarChar));

// Fill the parameter with the value retrieved
// from the text field
cmd.Parameters["@Find"].Value = txtFind.Text;

// Execute the query
rdr = cmd.ExecuteReader();