Member Avatar for AngelicOne

How to put parameters within dataadapter?

Here's an example of non-parameter query..

SqlDataAdapter da = new SqlDataAdapter ("SELECT * FROM Table WHERE column='"+textBox1.Text+"'",con);
  • 3 Contributors
  • 2 Replies
  • 2K Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Lusiphur
Member Avatar for saravind84

Hi,

Please refer the example from MSDN given below:

string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?";
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlDataAdapter custDA = new SqlDataAdapter();        

SqlCommand selectCMD = new SqlCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;

// Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London";

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
Edited by saravind84 because: n/a
commented: cleared me up! +1
Member Avatar for Lusiphur

If I understand your question correctly (you didn't give a lot to work with) you're looking for something like this:

SqlDataAdapter da = new SqlDataAdapter ("SELECT * FROM Table WHERE column='@varNameHere'",con);
da.SelectCommand.Parameters.AddWithValue(textBox1.Text);

Essentially, you'd need to modify the correct command type within the adapter with the parameter you wanted to add... I am not 100% sure because I haven't tried to manually override the default adapter methods recently but you may also need to actually set your SELECT statement with the use of the following instead of in the actual adapter declaration in order for the SelectCommand methods to work as well:

da.SelectCommand.CommandText = "SELECT * FROM Table WHERE column='@varNameHere'"

Hope this helps (and hope I'm not butchering it at the same time) :)

EDIT: As I thought (and as saravind pointed out before I could finish typing my reply) you do need to set the CommandText separately instead of in the declaration of the adapter when using parameters :)

Edited by Lusiphur because: n/a
commented: cleared me up! thanks +1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.