not getting the data in ds

Question

0.00/5 (No votes)

See more:

.NET3.0

Heloo guys!
may i know whats wrong with the following code. i am not getting the data in the data set if i am using the following code.

thanks

C#

string Bagquery;

try
{
    string dtfrom = this.datefrom.Value.ToShortDateString();
    string dateto = this.Dateto.Value.ToShortDateString();
    SqlConnection con;
    SqlDataAdapter da;
    DataSet ds;
    DataConnection data = new DataConnection();
    con = data.GetConnection();
    clsBagData bagdata = new clsBagData();
    Bagquery = bagdata.Bagdata(dtfrom, dateto, txtFname.Text, txtSName.Text, cmbEmailID.Text, CmbSubmode.Text);
    con.Open();
    ds = new DataSet();
    da = new SqlDataAdapter(Bagquery, con);
    da.SelectCommand.Parameters.Add("@dtfrom", SqlDbType.VarChar, 11);
    da.SelectCommand.Parameters["@dtfrom"].Value = dtfrom;
    da.SelectCommand.Parameters.Add("@dtto", SqlDbType.VarChar, 11);
    da.SelectCommand.Parameters["@dtto"].Value = dateto;
    da.SelectCommand.Parameters.Add("@RFname", SqlDbType.VarChar, 11);
    da.SelectCommand.Parameters["@RFname"].Value = txtFname.Text;
    da.SelectCommand.Parameters.Add("@RSurname", SqlDbType.VarChar, 11);
    da.SelectCommand.Parameters["@RSurname"].Value = txtSName.Text;
    da.SelectCommand.Parameters.Add("@cmbemailid", SqlDbType.VarChar, 11);
    da.SelectCommand.Parameters["@cmbemailid"].Value = cmbEmailID.Text;
    da.SelectCommand.Parameters.Add("@cmbmode", SqlDbType.VarChar, 11);
    da.SelectCommand.Parameters["@cmbmode"].Value = CmbSubmode.Text;
    da.Fill(ds);
    dataGridView2.DataSource = ds.Tables[0];
    con.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Posted 3-May-13 21:32pm

Rajeshkrathor

Updated 3-May-13 21:37pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

1 solution

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)

OriginalGriff · Answer 1 · 2013-05-03T21:40:00

Solution 1

We can't give you an absolute answer from that code: it misses out far, far too much. For example, your query itself is assembled using a method "bagdata.Bagdata" which you don't show, and since the query string is probably going to be fairly fundamental to why you don't get the data you want, you need to look at that.

So the first thing to do is to use the debugger to breakpoint the line:

C#

con.Open();

And examine the actual content of the Bagquery string - any WHERE clause will be significant.

However, give that you have datefrom and Dateto variables in your code, I suspect that your query is something along the lines of:

SQL

SELECT ... FROM ... WHERE dateColumn BETWEEN @dtfrom AND @dtto

And that it is probably significant that you convert dates to strings before passing them through. If you are also storing dates as strings in your DB, that is very likely to be the cause of the problem - string comparisons do not work well with date related data. Check your DB and see what datatype the columns are declared as.

Posted 3-May-13 21:40pm

OriginalGriff

Comments

[no name] 4-May-13 3:47am

dear sir its my class from where i am creating the string of query

class clsBagData
{

public string Bagdata(string dtfrom, string dateto, string Rfname, string Rlastname, string cmbEmailid, string cmbsubmode )
{

String Bagquery = "";
if (Rfname != "")
{
Rfname = "and RFname=@RFname";
}
if (Rlastname != "")
{
Rlastname = "and RSurname=@RSurname";
}
if (cmbEmailid != "")
{
cmbEmailid = "and REmail=@cmbemailid";
}
if (cmbsubmode != "")
{
cmbsubmode = "and SubMode=@cmbmode";

}

Bagquery = "select RFname,RSurname,InboundType,In_Date,In_Time,REmail,RMobile,SFname,SSurname,SAddress,Mode,SubMode,TypeofMail,TypeofMail,BagDate,fstrmn,MainconsigNo,Weekday,Weekendmail,Weekenddate,Remarks from tblinbound where DATEDIFF(d,In_Date,getdate())< = 15 and DATEDIFF(d,In_Date,getdate())>5 and delivered is null and Shred is null and BagDate>=@dtfrom and BagDate<=@dtto" + Rfname + " " + Rlastname + " " + cmbEmailid + " " + cmbsubmode + "";
return Bagquery;

}

OriginalGriff 4-May-13 3:56am

And BagFate is stored as what datatype? Because you pass the elements you compare it against ast strings in your system's default date format and culture - which could be very different!

[no name] 4-May-13 4:02am

sir all are string

OriginalGriff 4-May-13 4:09am

So change it. Use the appropriate data type of the appropriate data. If you store everything as a string there are two problems:
1) It takes vastly more space than it needs to - and with DB's that adds up fast because each row needs the same amount of extra space.
2) Comparisons do not work, or they work very badly. Consider integers:
1
2
10
Stored as integers, that is the ascending order.
stored as strings, a string ordering is used:
1
10
2
and the same ordering is used for comparisons. So if you say WHERE x > 7 you will get no results.
It's even worse for dates because the default format that you pass through is not necessarily comparison friendly at all: it could be 4/5/2013, or 2013 May 4, or 5/4/13 or... and all of those will order differently. If you store integers as integers, and dates as dates, then SQL knows what it is doing and can order things correctly, independent of what your culture and language, and locale are set to on the two or more computers that are involved in the data!

[no name] 4-May-13 4:06am

ohhh its a silly mistake by meself..datatype parameter is varchar 50 and i have passed varchar 11.

[no name] 4-May-13 4:08am

ohhh i have passed the datatype varchar 11 its actually varchar 50

thanks OriginalGriff..