Add Parameters to Adapter

Question

I need to populate datagridview2 after selecting a value in datagridview1. I do this by pulling key values from dgv1 and adding them to a SQL script that is fed to an adapter that fills a dataset which in turn shows in dgv2.

I have code that works 99.9% of the time. However, there are occasional values that get passed that break the code. Therefore, I want to improve my SQL query creation by adding parameters rather just dumping the values directly into the script. The added security is a nice side effect of this task.

I believe I am almost there, I am just unable to get the parameters to pass into my script.

Here is my current code which works fine, except when a bad value gets passed as one of the varValues...

string sql = @"select distinct Value1, Value2, Value3 from vSourceView where value1 = " + varValue1 + " and value2 = " + varValue2;

SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);

DataSet da = new DataSet();
da.Tables.Add(sql);
adapter.Fill(da, sql);

dataGridView2.DataSource = da;
dataGridView2.DataMember = sql;

Here is my "new" code that is not working...

string sql = "select distinct Value1, Value2, Value3 from vSourceView where Value1 = @value1 and Value2 = @value2";

SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);

adapter.SelectCommand.Parameters.AddWithValue("@value1", varValue1);
adapter.SelectCommand.Parameters.AddWithValue("@value2", varValue2);

//added for testing
textBox1.Text = sql;

DataSet da = new DataSet();
da.Tables.Add(sql);
adapter.Fill(da, sql);

dataGridView2.DataSource = da;
dataGridView2.DataMember = sql;

The parameters are not passing into the script. When I run the app, textBox1 shows "select distinct Value1, Value2, Value3 from vSourceView where Value1 = @value1 and Value2 = @value2" and dgv2 is not filled.

What am I missing?

Thanks in advance.

PS

The actual value that is "breaking" my current code is "VBA.MEDICAL". I suspect that "VBA." is a reserved string...

`da.Tables.Add(sql);` `dataGridView2.DataMember = sql;` These lines make no sense. sql is an sql string, not a name of something. Most programmers would use "ds" as a variable name for a DataSet, "da" sounds like an adapter. — LarsTech, Jun 14 '19 at 19:16
You probably just want `dataGridView2.DataSource = da.Tables[0];` — LarsTech, Jun 14 '19 at 19:17
`the added security is a nice side affect` -ha! Really? Security should be your *first* priority here. Especially when it's often considered [the most dangerous vulnerability](https://www.netsparker.com/blog/web-security/sql-injection-vulnerability-history/). — mason, Jun 14 '19 at 19:20
Possible duplicate of [Trying to pass SqlCommand in SqlDataAdapter as parameters](https://stackoverflow.com/questions/44402669/trying-to-pass-sqlcommand-in-sqldataadapter-as-parameters) — Jim G., Jun 14 '19 at 19:24
@LarsTech My existing works great using the values I posted. Except when the bad value gets passed. Changing the values in both my new test code as well as my original code lead to errors ("Child list for field [sql] cannot be created"). Either way, this address my original code, not the problem with passing parameters. — kevin, Jun 14 '19 at 19:33
@mason I realize that. I was just trying to avoid the comments about bad security with passing the values directly vs parameters. I know parameters are better. I am trying to get everything there. — kevin, Jun 14 '19 at 19:35
@JimG. That question does not address parameters, which is the crux of my problem. — kevin, Jun 14 '19 at 19:48

score 0 · Answer 1 · answered Jun 14 '19 at 19:30

0

string sql = "select distinct Value1, Value2, Value3 from vSourceView where Value1 = @value1 and Value2 = @value2";

SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);

adapter.SelectCommand.Parameters.AddWithValue("@value1", varValue1);
adapter.SelectCommand.Parameters.AddWithValue("@value2", varValue2);

//added for testing
textBox1.Text = sql;

/* DataSet da = new DataSet();
   da.Tables.Add(sql);
*/
var dt = new Datatable();
adapter.Fill(dt);

dataGridView2.DataSource = dt;
// dataGridView2.DataMember = sql; // you don't need this.

answered Jun 14 '19 at 19:30

Jim G.

15,141
22
103
166

2

`AddWithValue` ought to be avoided - it makes the provider guess at the datatype – Ňɏssa Pøngjǣrdenlarp Jun 14 '19 at 19:36
@NatPongjardenlarp I agree with you. I was trying to avoid changing too much of the OP's code. – Jim G. Jun 14 '19 at 19:41
I rather think the OP and future readers would be better served with a whole and compleat answer – Ňɏssa Pøngjǣrdenlarp Jun 14 '19 at 19:42
dgv2 does not populate at all without the "dataGridView2.DataMember = sql;" line. No error is produced, but no return either. – kevin Jun 14 '19 at 19:56
1

@kevin That's probably because you are using a DataSet, so you need to specify the table name of the DataTable in the DataSet. That's where your "sql" name comes into play. This answer skips that by filling a DataTable directly and using that as a DataSource. – LarsTech Jun 14 '19 at 20:02

Add Parameters to Adapter

1 Answers1