I think you've got several things wrong with your logic:
First, this line. Why do you call ExecuteScalar when setting the sql query?
string sql = "Select * From Products Where ProductID =" + objCmd.ExecuteScalar().ToString() + " ";
Second, why do you call ExecuteScalar inside the for loop? Calling the Fill method of the Adapter gets the job done.
Third, and most important, I can imply from your logic that you are dinamically adding parameters to your query according to the elements of "array". You're doing this by adding OR clauses to your query inside the for loop. If you want to do this, you'll
need several parameter objects, not just one like you have (objParam), because at the end of the loop, objParam.Value will have the value of the LAST index in the array. That's the reason you only get one item, cause at the end of the loop your query will
look something like this:
Select * From Products Where ProductID = 1 Or ProductID = 1 Or ProductID = 1 ...
1 is, in this case, the value of tha last element of "array".
What you need to do is create an OleDbParameter param object for each element of your array (this has to be donde inside the loop)
:. HUGONNE .:
Please mark as answer if the post helps.
Visit my coding blog. Any comments are well appreciated.
im setting the sql query at first because i need a base of the sq,second lets say i have a table with strings
every string i add to the array list,then i have to find the product id by the string,so then i do the for loop to find each product id and add it to the sql query to get the products that match the string value,
erezo9
0 Points
9 Posts
cmd execution wont run twice
Apr 09, 2012 02:33 PM|LINK
hi i have made an sql sentence in the code problem is that it wont run twice the execution
heres my code
OleDbCommand objCmd = new OleDbCommand("spGetProductsByAttirube", myConnection); objCmd.CommandType = CommandType.StoredProcedure; OleDbParameter objParam; objParam = objCmd.Parameters.Add("@Attirube", OleDbType.BSTR); objParam.Direction = ParameterDirection.Input; objParam.Value = array[0]; DataSet ds = new DataSet(); try { myConnection.Open(); string sql = "Select * From Products Where ProductID =" + objCmd.ExecuteScalar().ToString() + " "; for (int i = 1; i < array.Count; i++) { objParam.Value = array[i]; string obj = objCmd.ExecuteScalar().ToString(); sql = sql + "Or ProductID = "+obj+""; } objCmd.CommandText = sql; objCmd.CommandType = CommandType.Text; OleDbDataAdapter adapter = new OleDbDataAdapter(objCmd); adapter.Fill(ds, "Products"); } catch (Exception ex) { throw ex; } finally { myConnection.Close(); } return ds;what can be the problem?
it gives the same product id when it supose to be diferent
adamturner34
Contributor
3890 Points
971 Posts
Re: cmd execution wont run twice
Apr 09, 2012 03:05 PM|LINK
You have too many command objects. You should only have 1 for what you're doing.
Write it all out in T-SQL in the stored procedure and just pass parameters to it.
erezo9
0 Points
9 Posts
Re: cmd execution wont run twice
Apr 09, 2012 03:17 PM|LINK
but i have to do it like that ,i dont know how many times to do it thats why its in a loop
isnt there another way?
hugonne
Contributor
2112 Points
403 Posts
Re: cmd execution wont run twice
Apr 09, 2012 03:48 PM|LINK
I think you've got several things wrong with your logic:
First, this line. Why do you call ExecuteScalar when setting the sql query?
Second, why do you call ExecuteScalar inside the for loop? Calling the Fill method of the Adapter gets the job done.
Third, and most important, I can imply from your logic that you are dinamically adding parameters to your query according to the elements of "array". You're doing this by adding OR clauses to your query inside the for loop. If you want to do this, you'll need several parameter objects, not just one like you have (objParam), because at the end of the loop, objParam.Value will have the value of the LAST index in the array. That's the reason you only get one item, cause at the end of the loop your query will look something like this:
Select * From Products Where ProductID = 1 Or ProductID = 1 Or ProductID = 1 ...
1 is, in this case, the value of tha last element of "array".
What you need to do is create an OleDbParameter param object for each element of your array (this has to be donde inside the loop)
Please mark as answer if the post helps.
Visit my coding blog. Any comments are well appreciated.
erezo9
0 Points
9 Posts
Re: cmd execution wont run twice
Apr 09, 2012 05:48 PM|LINK
im setting the sql query at first because i need a base of the sq,second lets say i have a table with strings
every string i add to the array list,then i have to find the product id by the string,so then i do the for loop to find each product id and add it to the sql query to get the products that match the string value,
if u have an idea how to do it please tell me
tdmca
Contributor
2396 Points
661 Posts
Re: cmd execution wont run twice
Apr 09, 2012 05:52 PM|LINK
place this line out of loop
molly_c
Participant
1590 Points
401 Posts
Re: cmd execution wont run twice
Apr 11, 2012 09:55 AM|LINK
Totally agree. Why do you do that?
If you need to run two queries, Stored procedures will help you.
Molly
It's time to start living the life you are imagined.