Parameterized query issue

Question

0.00/5 (No votes)

See more:

Hi,

I have a issue with parameterized query.

Here the case is I need to fetch the values from DB and display in gridview, but in all the times the no. of records are zero.

Can you help me?

What I did was:

C#

MySqlCommand cmd=new MySqlCommand("SELECT PRD.PRD_IDY,BAT_NME,bat_idy FROM BRD, PRD WHERE BRD.PRD_IDY = PRD.PRD_IDY AND PRD.PRD_IDY=@prdidy and bat_idy=@batchidy ",con);

cmd.Parameters.AddWithValue("@prdidy", prdidy);
cmd.Parameters.AddWithValue("@batchidy", batchidy);

reader = cmd.ExecuteReader();
if(reader.HasRows)
{
    reader.Read();
    MessageBox.Show(reader.GetValue(0).ToString());
}

Posted 2-Dec-10 21:16pm

krishna kishore58

Updated 2-Dec-10 23:06pm

Manfred Rudolf Bihy

v3

Add a Solution

Comments

Dalek Dave 3-Dec-10 4:23am

Edited for Grammar and Syntax.

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2010-12-02T21:23:00

Solution 1

Can you check in the database and make sure you have records available for the prdidy and batchidy values you are sending into the database.

Your query otherwise looks ok.

Posted 2-Dec-10 21:23pm

Abhinav S

Comments

krishna kishore58 3-Dec-10 3:45am

thanks 4 replying... ya the records are available n u know the query is getting executed in Mysql query browser

Abhinav S 3-Dec-10 7:37am

Can you please try
do
{
if (reader.HasRows)
{
reader.Read(); }
} while (reader.NextResult());

Mohd Wasif · Answer 2 · 2010-12-02T21:31:00

Solution 2

C#

MySqlCommand cmd=new MySqlCommand("SELECT PRD.PRD_IDY,BAT_NME,bat_idy FROM BRD, PRD WHERE BRD.PRD_IDY = PRD.PRD_IDY AND PRD.PRD_IDY=@prdidy and bat_idy=@batchidy ",con);

cmd.Parameters.AddWithValue("@prdidy", prdidy);
cmd.Parameters.AddWithValue("@batchidy", batchidy);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if(reader.Read)
{
GridView1.Datasource=reader ;
GridView1.DataBind();

reader .Close();
con.Close();
}

Posted 2-Dec-10 21:31pm

Mohd Wasif

Updated 2-Dec-10 22:24pm

v3

Comments

krishna kishore58 3-Dec-10 3:45am

thanks 4 replying...Dude cursor is not going in if(reader.Read()) and moreover how can i insert the records separetely in gridview without binding...

Dalek Dave 3-Dec-10 4:24am

I Code Blocked your answer.

Manfred Rudolf Bihy · Answer 3 · 2010-12-02T21:55:00

Solution 3

Hello Krishna,

since I see that you're joining tables BRD and PRD on their common field PRD_ID (I'm guessing that BRD.PRD_ID is a foreign key reference to table PRD), are you really sure that the join would return values?
Maybe the join of both tables is already empty.

Please try this:

C#

MySqlCommand cmd=new MySqlCommand("SELECT PRD.PRD_IDY,BAT_NME,bat_idy FROM BRD, PRD WHERE BRD.PRD_IDY = PRD.PRD_IDY", con);
//cmd.Parameters.AddWithValue("@prdidy", prdidy);
//cmd.Parameters.AddWithValue("@batchidy", batchidy);
reader = cmd.ExecuteReader();
if(reader.HasRows)
{
    reader.Read();
    MessageBox.Show(reader.GetValue(0).ToString());
}

Does this return results?

Modification:

Ok. You said code I pasted above does also return no records. This means the join of both tables on their common column PRD_ID is empty.
This means in effect that both tables do not have a common value in column PRD_ID.

End Modification

Cheers

Manfred

Posted 2-Dec-10 21:55pm

Manfred Rudolf Bihy

Updated 2-Dec-10 22:23pm

v6

Comments

krishna kishore58 3-Dec-10 4:01am

In the query the u didnot include @prdidy and @batchidy...k dats okay..and here theres no issue of foreign key.As i told the query is getting executed in query browser by the passing the direct values...Every thing is correct but i donno how to work with parameterized query...help me...same issue the control is not reaching if condition...

Manfred Rudolf Bihy 3-Dec-10 4:03am

Ok, so there you go. If the join is already empty adding parameters isn't going to get you anywhere. There has to be some PRD.PRD_ID value that exists in both tables PRD_ID column. Do you understand what I mean?

krishna kishore58 3-Dec-10 4:11am

ya prd_idy is available in both the tables...

Manfred Rudolf Bihy 3-Dec-10 4:15am

Yes the column is availabe in both tables. Thats what I already said. You need to check if the join WITHOUT the parameters would return records. So try what I posted in my answer and if no results are returned then you know that the columns PRD.PRD_ID and BRD.PRD_ID don't have any VALUES in common. Did I make myself clear now?

krishna kishore58 3-Dec-10 4:21am

ya ur clear...the both the fields have few common values and lemme tel u 1 thing is that the qurey is getting executed in query browser and not in app...think there might be wrong usage of parameterized query...Dont mind i just wanna conform...does the answer which u posted is absolutely correct?

Dalek Dave 3-Dec-10 4:24am

Good Answer!

Manfred Rudolf Bihy 3-Dec-10 4:33am

@Dave: Thanks!
@Krishna: So you tried the code I posted in my answer? What was the result?
(Please review again, in a previous version the "reader = cmd.ExecuteReader();" had slipped into the comment line)

krishna kishore58 3-Dec-10 4:38am

ya i reviewed but the control is not at all entering in "if(reader.HasRows)"....i donno y...

Manfred Rudolf Bihy 3-Dec-10 4:44am

Ok, in a next step try selecting data from one of the tables only and see it that works:
1. "SELECT PRD.PRD_IDY FROM PRD"
2. "SELECT BRD.PRD_IDY FROM BRD"
Does either of these two selects yield any results, if substituted in my example snippet?

krishna kishore58 3-Dec-10 5:00am

Hey in the query which u suggested doesnot include the parameter...is it so...

Manfred Rudolf Bihy 3-Dec-10 5:04am

Yes. What I'm trying the whole time with you is to narrow down where the real problem is.
You said you retried the statement WITH the join and WITHOUT parameters and it didn't work.
The next step would be to try to get records from only one of these tables.
This is the way you debug, you try to find out where the root of the problem lies.
Sometimes to find the root of a problem you have to climb a tree :) .
Bear with me and please try out what I suggested.

krishna kishore58 3-Dec-10 5:17am

When i doesnot include the parameters in the query its fetching all the records...its wrong...in the sense its not matching the parameter with the column value in table....and fetching all the records... when i include the parameters in the query control is not moving to the if cond...surfed in net but clumsy...k thanks 4 spending...if u hav any changes lemme know...

Manfred Rudolf Bihy 3-Dec-10 5:23am

Ok, I'm no longer wasting my time on this. When I asked you to retry what I posted in my answer this is what you replied:

ya i reviewed but as i told u the control is not at all entering in "if(reader.HasRows)"....i donno y...

So it seems you must have been a bit untruthful in your reply. If what you stated in your last comment is correct I'd even say it was an outright lie.
Why are treating people who are trying to help you in such a way?
You are very rude.

krishna kishore58 3-Dec-10 5:37am

u completely misunderstood me...wat im saying is i tried with ur suggestion also...i got partial result... and i told that there might be minute mistake...this is wat i mean to say...

fjdiewornncalwe 3-Dec-10 14:48pm

If it makes you feel better, Manfred, I understand you.... :) Tschues!

Manfred Rudolf Bihy 3-Dec-10 18:31pm

@Marcus:Thanks for your understanding! :)

sandipapatel · Answer 4 · 2010-12-03T21:23:00

C#

using MySql.Data.MySqlClient;

// Download MySQLConnectorfor VS2010 and add rederence of the DLL named
// MySql.Data.dll

String strcon = "Your connection string";
MySqlConnection con = new MySqlConnection(strcon);
String strQuery = "Your SQL Query";
MySqlCommand cmd = new MySqlCommand(strQuery,con);

MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();

try
{
con.Open();
da.Fill(ds, "TableName");
// OOOORRRR
da.Fill(ds);
}
catch (Exception err)
{
//show error
}
finally
{
con.Close();
}

sandipapatel · Answer 5 · 2010-12-03T06:18:00

The best easy and more modifiable & even more readable n understandable code sample ever : same can be done for select query also.

C#

constr = "Your connection string";
con = new OleDbConnection(constr);
cmd = new OleDbCommand();

cmd.Connection = con;
OleDbParameter IDp, Namep, Semesterp, Collegep;
IDp = new OleDbParameter();
Namep = new OleDbParameter();
Semesterp = new OleDbParameter();
Collegep = new OleDbParameter();

IDp.OleDbType = OleDbType.Integer;
Namep.OleDbType = OleDbType.VarChar;
Semesterp.OleDbType = OleDbType.VarChar;
Collegep.OleDbType = OleDbType.VarChar;

IDp.Value = id;
Namep.Value = name;
Semesterp.Value = sem;
Collegep.Value = college;

cmd.Parameters.Clear();
cmd.Parameters.Add(IDp);
cmd.Parameters.Add(Namep);
cmd.Parameters.Add(Semesterp);
cmd.Parameters.Add(Collegep);

cmd.Connection = con;
cmd.CommandText = "INSERT INTO StudentInfo([ID],[Name],[Semester],[College]) VALUES([@IDp],[@Namep],[@Semesterp],[@Collegep]);";
    cmd.CommandType = CommandType.Text;

try
{
    con.Open();
    cmd.ExecuteNonQuery();
    ResultLabel.Text = "Data Successfully Inserted Into DataBase";
}
catch (Exception err)
{
    ResultLabel.Text = err.Message.ToString();
}

Parameterized query issue

5 solutions

Solution 1

Solution 2

Solution 3

Solution 5

Solution 4

Add your solution here

Preview 0