0

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
9
Views
6 Years
Discussion Span
Last Post by Lusiphur
1

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: n/a

Votes + Comments
cleared me up!
1

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: n/a

Votes + Comments
cleared me up! thanks
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.