how can i read data from my database

Question

2.00/5 (1 vote)

See more:

The problem i have here is that when i select a file from the the open dialog box, i am able to display is contents as described in the code but i can seem to open any other file besides this one file. for example. i open file 'A', after displaying its contents i try to open file 'B' but it wont open. the code is detailed below.

OpenFileDialog dlg = new OpenFileDialog();
DialogResult dlgrslt = dlg.ShowDialog();

String connection, sql;

if (dlgrslt == DialogResult.OK)
{

    connection = Properties.Settings.Default.cvmanagerConnectionString;

    filename = dlg.FileName;


                    sql = "select A.Fname, A.Lname, A.dept, A.role, A.rating, A.notes, B.firstdegA, B.firstdegB, C.secdegA, C.secdegB, D.thirddegA, D.thirddegB, E.dipA, E.dipB, F.hndA, F.hndB, G.phone_one from apps as A left join firstdeg as B on A.FileLoc = B.FileLoc left join secdeg as C on A.FileLoc = C.FileLoc left join thirddeg as D on A.FileLoc = D.FileLoc left join dip as E on A.FileLoc = E.FileLoc left join hnd as F on A.FileLoc = F.FileLoc left join contacts as G on A.FileLoc = G.FileLoc where A.FileLoc = '" + filename + "'";

    MessageBox.Show(sql);
    SqlDataReader reader = null;
    SqlConnection conn = new SqlConnection(connection);
    SqlCommand cmd = new SqlCommand(sql, conn);

    try
    {
        conn.Open();
        reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {

                tbappname.Text = reader["Fname"].ToString() + " " + reader["Lname"].ToString();
                tbfirstdegA.Text = reader["firstdegA"].ToString();
                tbfirstdegB.Text = reader["firstdegB"].ToString();

                tbsecdegA.Text = reader["secdegA"].ToString();
                tbsecdebB.Text = reader["secdegB"].ToString();

                tbthirddegA.Text = reader["thirddegA"].ToString();
                tbthirddegB.Text = reader["thirddegB"].ToString();

                tbdipA.Text = reader["dipA"].ToString();
                tbdipB.Text = reader["dipB"].ToString();

                tbhndA.Text = reader["hndA"].ToString();
                tbhndB.Text = reader["hndB"].ToString();

                tbcontact.Text = reader["phone_one"].ToString();

                plcmntdept.Text = reader["dept"].ToString();
                plcmntrole.Text = reader["role"].ToString();
                plcmntrating.Text = reader["rating"].ToString();
                plcmntnotes.Text = reader["notes"].ToString();


            }
        }

        plcmntedit.Enabled = true;

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        reader.Close();
        conn.Close();
    }
}

Posted 6-Dec-10 6:32am

Member 7250221

Updated 7-Dec-10 5:19am

v2

Add a Solution

Comments

AspDotNetDev 6-Dec-10 12:55pm

I don't really feel like answering your question, but I thought I'd recommend that you use command parameters rather than concatenating strings to form queries, as you have done with the filename. See here: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx

[no name] 6-Dec-10 13:12pm

that doesnt solve the problem

wizardzz 6-Dec-10 13:37pm

He did not submit it as an answer, so the fact that it does not solve the problem should be understood.

2 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)

JOAT-MON · Accepted Answer · 2010-12-06T07:47:00

It looks like there are a couple things to look at in your query:

1. You do not need to select FileLoc from each table, but if you choose to, then you need to apply an alias to it...

C#

sql = "select A.FileLoc, A.Fname, A.Lname, A.dept, A.role, A.rating, A.notes, B.FileLoc AS FileLocB, B.firstdegA, B.firstdegB, C.FileLoc AS FileLocC, C.secdegA, C.secdegB, ..."

... so that it has a unique name.

2. Your tables are joined with inner joins, which means that the query will only return rows if there is a match in ALL the tables listed (A,B,C,D,E,F, and G). If even one of the tables does not have a matching FileLoc column then no rows will be returned. You might consider using LEFT JOIN instead. This will allow you to retrieve partial information...

C#

sql = "... from apps as A left join firstdeg as B on A.FileLoc = B.FileLoc left join secdeg as C on A.FileLoc = C.FileLoc ..."

Hope this helps.

William Winner · Accepted Answer · 2010-12-06T09:34:00

First of all, your question doesn't make any sense (at least not to me). You talk about opening files, but nowhere in that code are you opening a file (as in

C#

using (StreamReader sr = new StreamReader("TestFile.txt"))
{
     string line;
     // Read and display lines from the file until the end of
     // the file is reached.
     while ((line = sr.ReadLine()) != null)
     {
          Console.WriteLine(line);
     }
}

You're selecting a database record.

Secondly, you don't need to include A.FileLoc, B.FileLoc, C.FileLoc, etc... in your Select. You're not using them at all.

Finally, I'm not sure how you're getting any information at all. You call reader.Read() twice. I assume that your database should only have one record for each file. So, here's what you are doing logically.

You set up your SQL Select.
You set up the SQLDataReader.
You execute the DataReader.
You read the first record.
You create firstname, lastname and fullname variables.
You read the next record.

*At this point you are already on the second record.

Now you fill in all of your TextBoxes.

If you are only expecting one row to be returned, you need to get rid of the while(reader.Read()).

And as an FYI, you are aware that instead of using memory to hold firstname, lastname and fullname, you could also just as easily write:

C#

tbappname.Text = reader["Fname"].ToString() + " " + reader["Lname"].ToString();

how can i read data from my database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0