Trouble with parameter

[rcoco]

I'm having an error message sounding like :
Prepared statement '(@id text)SELECT * from isp_email.staff where @id
like +txtname.' expects parameter @id, which was not supplied.
This message appear when I press button to select a row at run time.
What could be the problem?

SqlCommand myCommand = new SqlCommand();
myCommand.Connection=con;
myCommand.CommandText="SELECT * from isp_email.staff where @id like
+txtname.Text";
SqlParameter myparam = new SqlParameter("@id",SqlDbType.Text);
myparam.Value=ID;
myCommand.Parameters.Add(myparam);
SqlDataAdapter myAdapter=new SqlDataAdapter(myCommand);
DataSet ds = new DataSet();
myAdapter.Fill(ds);
con.Open();
myCommand.ExecuteNonQuery();
dgupdate.DataSource=ds;
dgupdate.DataBind();
con.Close();
Thank you.

 

[DeveloperX]

first you've got +txtname.Text in the select string. It should be
outside.
Second, you don't need a variable in the select, as you're building the
sql this way (more on that shortly) just use the ID field name.
Really you should create a stored proc that takes a parameter @id then
set the commandtext ="storedprocname" and add the parameter as you've
done.
You might want to look at where your con.open statement is, I can't
remember if adapters automatically open and close the connection... And
why are you using executeNonQuery on sql that returns records? In fact
why are you calling that at all after the .Fill on the adapter?
Good luck.

 

[Code Monkey]

I'd never use inline SQL (too hard to manage - my preference is stored
procedures). Anyway!

Assuming that you want to search for

Smith
Smithy
Smithe

You'd set ID to 'Smith' and use the following

<code>
using (SqlConnection conn = new SqlConnection(con))
{
String myCommand="SELECT * from isp_email.staff where (id like
@ID+'%')";
SqlCommand cmd = new SqlCommand(myCommand, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value= ID;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
}
</code>

After that you've got a DataSet to play with. But do you really the
full DataSet? Surely a better solution would be the DataTable?

<code>
using (SqlConnection conn = new SqlConnection(con))
{
String myCommand="SELECT * from isp_email.staff where (id like
@ID+'%')";
SqlCommand cmd = new SqlCommand(myCommand, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value= ID;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
}
</code>

Hope that helps.

Dave.