[RESOLVED] problem retrieving data

[Pasvorto]

I am using VS2008 and .NET 3.5

This code does not seem to be getting any data. Yet, there are no errors.

        private void frmLog_Load(object sender, EventArgs e)
        {
            SqlDataReader rdr = null;

            SqlConnection conn = new SqlConnection("Data Source=PRIMERO;Initial Catalog=Techni;Persist Security Info=True;User ID=sa;Password=pacjo");

            SqlCommand sqlEmp = conn.CreateCommand();

            sqlEmp.CommandText = "SELECT empnum FROM Empfile ORDER BY Empnum";

            // open the connection
            conn.Open();

          // get an instance of the SqlDataReader
            rdr = sqlEmp.ExecuteReader();
            //if rdr.HasRows then {
                while (rdr.Read())
                {
                    int idxID = rdr.GetOrdinal("empnum");
                    string idx2 = Convert.ToString(idxID);
                    MessageBox.Show(idx2); //got a 0 here

                    string employee = (string)rdr["empnum"];

                    TS1.Text = (employee);
                    
                }
            //}
            // close the reader and connection
            rdr.Close();
            conn.Close();
        }

Any thoughts? All of the example code seems to be VS2005

TS1 is a status strip on the form.

[jmcilhinney]

The code for VB 2005 and 2008 would be exactly the same. 2008 adds new features, e.g. LINQ support, but you're just using straight ADO.NET, so there will be no difference whatsoever.

Either the query returns data and execution enters the 'while' loop or it doesn't. Which is it?

[Pasvorto]

The code for VB 2005 and 2008 would be exactly the same. 2008 adds new features, e.g. LINQ support, but you're just using straight ADO.NET, so there will be no difference whatsoever.

Either the query returns data and execution enters the 'while' loop or it doesn't. Which is it?

As the code shows, once in the loop, the message box shows an empnum of 0. There is no employee number of 0. Even if there were, I would expect to get the message over and over again. It only fires once. So, I am assuming the while loop only ran once, but did not retrieve any data.

I am trying to move the system from VB2005 to C# 2008. Corporate direction.

[Pasvorto]

When I runt he program, it stops at this line:

string employee = (string)rdr["empnum"];

with a troubleshooting box that says "When casting from a number, the value must be less than infinity"

I am assuming, that it does not understand rdr["empnum')

[Pasvorto]

OK, I've got the loop working. Now, to populate the grid...

[jmcilhinney]

If your aim is to populate a grid then don't use a loop at all. Populate a DataTable, either using a DataAdapter (if you intend to edit and save the data) or a DataReader (if the data is read-only), and bind that to the grid. Follow the CodeBank link in my signature and check out my Retrieving & Saving Data thread for examples.

[Pasvorto]

Well, if it were only that simple. technically, I need to loop through structure 1 and then get the last record for that 1 that is to be found in structure 2. I really need to do some reading in an SQL manual so that I can come up with one concise statement that will work.

[Pasvorto]

VS 2008 & SQL Server 20000

By way of further explanation:

I have an employee table and a table of time clock punches. I need to go through the employee file (only selecting active employees). For each employee selected, I need to go the Punches table and get the last punch for that employee. The grid will then display the employee #, employee name, date of punch, time of punch, type of punch (I or O).

I am not sure how to link the two tables together. Fortunately, my data base is fairly small and I have the luxury of doing a loop within a loop.

I plan to go to a books tomorrow and see if I can find a couple good C# 2008 books that I can reference. In the meantime, I would certainly appreciate any guidance provided.

My background is VB6. I converted this to VB.NET 2005 a while back, but I don't think I did it the "NET" way.

[GaryMazzone]

Try something like this.... but to get it right would need more information on database structure,

Select 
  employee.empNo,
  Employee.Name ,
  Punch.PunchType ,
  Max(Punch.PunchDate) ,
  Punch.Time,
Inner Join Employee.EmpNo 
    On Employee.EmpNo = Punch.EmpNo
Group By 
  employee.empNo,
  Employee.Name ,
  Punch.PunchType

[Pasvorto]

I will give that a try. Thanks. :-)

[GaryMazzone]

I think I missed on time. there also. Does PunchDate include the time in the column? Is PuchDate and PunchTime varchar Data or DateTime datatype?

I would need to change the select and group by

[Pasvorto]

I am getting an error

Incorrect syntax near the keyword "inner"

private void btnRefresh_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=PRIMERO;Initial Catalog=Techni;Persist Security Info=True;User ID=sa;Password=pacjo");
            SqlConnection conn2 = new SqlConnection("Data Source=PRIMERO;Initial Catalog=Timeclock;Persist Security Info=True;User ID=sa;Password=pacjo");

            // open the connection
            conn.Open();
            conn2.Open();
            SqlCommand sqlEmp = conn.CreateCommand();

           
            sqlEmp.CommandText = "SELECT EMPFILE.SHIFT, EMPFILE.EMPNUM, (EMPFILE.NAME1 + ' ' + EMPFILE.NAMEF) AS NAME, MAX(PUNCHES.TIME), PUNCHES.PUNCH, INNER JOIN EMPFILE.EMPNUM ON EMPFILE.EMPNUM = PUNCHES.EMPNUM GROUP BY EMPFILE.EMPNUM, NAME, PUNCHES.PUNCH";

 
                    // Create new DataAdapter
                    using (SqlDataAdapter a = new SqlDataAdapter(sqlEmp))
                    {
                         // Use DataAdapter to fill DataTable
                        DataTable t = new DataTable();
                        a.Fill(t);
                        // Render data onto the screen
                        dataGrid.DataSource = t;
                    }
 
        }

The error shows up on the line a.fill(t).
I'm not seeing the error (based on example post). Any thoughts?

[GaryMazzone]

Extra , after PunchTime

[GaryMazzone]

This should do better:

SELECT 
  EMPFILE.SHIFT, 
  EMPFILE.EMPNUM, 
  (EMPFILE.NAME1 + ' ' + EMPFILE.NAMEF) AS NAME, 
  MAX(PUNCHES.TIME), 
  PUNCHES.PUNCH 
INNER JOIN EMPFILE.EMPNUM 
  ON EMPFILE.EMPNUM = PUNCHES.EMPNUM 
GROUP BY 
  EMPFILE.EMPNUM, 
  EMPFILE.SHIFT, 
  EMPFILE.NAME1 ,
  EMPFILE.NAMEF ,
  PUNCHES.PUNCH

[Pasvorto]

I must be blind. I don't see and extra comma in the string.

[GaryMazzone]

Notice I changed the Group By also

[Pasvorto]

I get the same error , at the same spot

[GaryMazzone]

OK This time its me I'm messing up the SQL

SELECT 
  EMPFILE.SHIFT, 
  EMPFILE.EMPNUM, 
  (EMPFILE.NAME1 + ' ' + EMPFILE.NAMEF) AS NAME, 
  MAX(PUNCHES.TIME), 
  PUNCHES.PUNCH 
FROM PUNCHES 
INNER JOIN EMPFILE
  ON PUNCHES.EMPNUM = EMPFILE.EMPNUM 
GROUP BY 
  EMPFILE.EMPNUM, 
  EMPFILE.SHIFT, 
  EMPFILE.NAME1 ,
  EMPFILE.NAMEF ,
  PUNCHES.PUNCH

I left out the From clause and the INNER Join should just be the Table name (don't include the column on that part, it goes after the ON)

[Pasvorto]

I am getting 'invalid object name PUNCHES'.

Is that because they are in 2 difference connections?

[Lord_Rat]

SELECT A.EmpName, A.EmpId, B.LastPunch, C.PunchType 
FROM 
  EmpData A INNER JOIN
    (SELECT EmpId, Max(PunchDateTime) LastPunch FROM EmpPunches GroupBy EmpId) B
    ON A.EmpId = B.EmpId
    LEFT JOIN EmpPunches C
    ON CONCAT(B.EmpId, B.LastPunch) = CONCAT(C.EmpId, C.PunchDateTime)

That's from my head. You might have to play a bit with syntax.

Edit: Actually, Gary's is better. This one requires a concat, which is slower. I am a bit rusty. That's what happens when you're taken off doing database work for more than two years.

Sigh.

Edit2: Actually, Gary - won't yours cause multiple rows (for the same employee) when there is more than one "punch type" for each employee?

[GaryMazzone]

Are the two tables in the same database?

[Pasvorto]

No. Note the connection statements in the original posting.

SqlConnection conn = new SqlConnection("Data Source=PRIMERO;Initial Catalog=Techni;Persist Security Info=True;User ID=sa;Password=pacjo");
SqlConnection conn2 = new SqlConnection("Data Source=PRIMERO;Initial Catalog=Timeclock;Persist Security Info=True;User ID=sa;Password=pacjo");

[Lord_Rat]

You don't need two connections. You can join them, just put the schema in front of the table names.

Gary's becomes:

SELECT
EMPFILE.SHIFT,
EMPFILE.EMPNUM,
(EMPFILE.NAME1 + ' ' + EMPFILE.NAMEF) AS NAME,
MAX(PUNCHES.TIME),
PUNCHES.PUNCH
FROM Timeclock..PUNCHES AS PUNCHES
INNER JOIN Techni..EMPFILE AS EMPFILE
ON PUNCHES.EMPNUM = EMPFILE.EMPNUM
GROUP BY
EMPFILE.EMPNUM,
EMPFILE.SHIFT,
EMPFILE.NAME1 ,
EMPFILE.NAMEF ,
PUNCHES.PUNCH

[Pasvorto]

That works Lord_Rat. However, it is not giving me the information that I need. I would expect to see only one line per employee. Let me add this wrinkle. It gives me a syntax error neat the 'Wbhere'

            sqlEmp.CommandText = "SELECT ";
            sqlEmp.CommandText += " EMPFILE.SHIFT, ";
            sqlEmp.CommandText += " EMPFILE.EMPNUM, ";
            sqlEmp.CommandText += " (EMPFILE.NAMEl + ' ' + EMPFILE.NAMEF) AS NAME, ";
            sqlEmp.CommandText += " MAX(PUNCHES.TME), ";
            sqlEmp.CommandText += " PUNCHES.PUNCH ";
            sqlEmp.CommandText += " FROM TIMECLOCK..PUNCHES AS PUNCHES ";
            sqlEmp.CommandText += " INNER JOIN TECHNI..EMPFILE ";
            sqlEmp.CommandText += " ON PUNCHES.EMPNUM = EMPFILE.EMPNUM ";
            sqlEmp.CommandText += " GROUP BY ";
            sqlEmp.CommandText += " EMPFILE.EMPNUM, ";
            sqlEmp.CommandText += " EMPFILE.SHIFT, ";
            sqlEmp.CommandText += " EMPFILE.NAMEl , ";
            sqlEmp.CommandText += " EMPFILE.NAMEF , ";
            sqlEmp.CommandText += " PUNCHES.PUNCH ";
            sqlEmp.CommandText += " WHERE EMPFILE.STATUS =  'A' ";
            sqlEmp.CommandText += " AND EMPFILE.HS =  'H' ";
            sqlEmp.CommandText += " AND EMPFILE.EMPLOYER !=  'P' ";
            sqlEmp.CommandText += " ORDER BY EMPFILE.SHIFT, EMPFILE.EMPNUM";

[GaryMazzone]

I would work this out in SQL Query Analyzer and then paste it back into the C# app

[Pasvorto]

I have it returning data, but the data is not correct. It should be returning only one record (employee data + latest time clock punch). It is returning 2 records, one for Punch In and one for Punch Out. The date of the punch is correct, but the time seems bogus. I will continue to experiment with it. I will admit that I am like ADO much more.

            sqlEmp.CommandText = "SELECT ";
            sqlEmp.CommandText += " EMPFILE.SHIFT, ";
            sqlEmp.CommandText += " EMPFILE.EMPNUM, ";
            sqlEmp.CommandText += " (EMPFILE.NAMEL + ' ' + EMPFILE.NAMEF) AS NAME, ";
            sqlEmp.CommandText += " MAX(PUNCHES.DTE), ";
            sqlEmp.CommandText += " MAX(PUNCHES.TME), ";
            sqlEmp.CommandText += " PUNCHES.PUNCH, ";
            sqlEmp.CommandText += " EMPFILE.STATUS, ";
            sqlEmp.CommandText += " EMPFILE.HS, ";
            sqlEmp.CommandText += " EMPFILE.EMPLOYER ";
            sqlEmp.CommandText += " FROM TIMECLOCK..PUNCHES AS PUNCHES ";
            sqlEmp.CommandText += " INNER JOIN TECHNI..EMPFILE ";
            sqlEmp.CommandText += " ON PUNCHES.EMPNUM = EMPFILE.EMPNUM ";
            sqlEmp.CommandText += " WHERE EMPFILE.STATUS =  'A' ";
            sqlEmp.CommandText += " AND EMPFILE.HS =  'H' ";
            sqlEmp.CommandText += " AND EMPFILE.EMPLOYER !=  'P' ";
            sqlEmp.CommandText += " GROUP BY ";
            sqlEmp.CommandText += " EMPFILE.SHIFT, ";
            sqlEmp.CommandText += " EMPFILE.EMPNUM, ";
            sqlEmp.CommandText += " EMPFILE.NAMEL , ";
            sqlEmp.CommandText += " EMPFILE.NAMEF , ";
            //sqlEmp.CommandText += " NAME, ";
            sqlEmp.CommandText += " PUNCHES.PUNCH, ";
            sqlEmp.CommandText += " EMPFILE.STATUS, ";
            sqlEmp.CommandText += " EMPFILE.HS, ";
            sqlEmp.CommandText += " EMPFILE.EMPLOYER ";
            sqlEmp.CommandText += " ORDER BY EMPFILE.SHIFT, EMPFILE.EMPNUM, PUNCHES.PUNCH";

It did not like this line

//sqlEmp.CommandText += " NAME, ";

[GaryMazzone]

That is because NAME is not a field in the tables. It is a derived field. We have NAMEL and NAMEF in the group by to take care of that.

I could give another part (ROW_NUMBER() OVER()) but I don't think that is avail in SQL 2000.

[Pasvorto]

I really appreciate all of your help, guys. My inclination at this point is to use two data readers (one embedded within the other) to populate a dataset and try to attach the data grid to that dataset. At least, in my new to C# way, I can control what data gets retrieved.

[Lord_Rat]

Try the one I had posted further up, but with changing the table and column names as appropriate (don't forget to add the schemas in too)

Also - you have to put the WHERE before any ORDER or GROUP clauses.

Finally - this part:

//sqlEmp.CommandText += " NAME, ";

works like this:

sqlEmp.CommandText += " (EMPFILE.NAMEL + ' ' + EMPFILE.NAMEF), ";

(At least to my recollection)

[Pasvorto]

It is working now. It is just not giving me what I expect. The part that bothers me the most, is that this is one of the easiest queries that I would have to convert. I have some that have to go through 5 or 6 tables. It is not too difficult nesting ADO commands to get what I want. I guess I don't know that it is necessarily easier, but it is what I am used to. I am going to the bookstore tomorrow and look for some C# 2008 books that have plenty of examples in data manipulation using .NET.

[GaryMazzone]

I would suggest SQL books also or http://www.w3schools.com/sql/default.asp to get a full grasp of SQL

[Pasvorto]

I hear ya. I am comfortable enough with SQL until I get to the joins. That's when my SQL gets a bit squirrely.