10 Replies - 616 Views - Last Post: 29 July 2010 - 11:41 PM Rate Topic: -----

#1 feelblue  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 27-September 09

Got any idea about y so many problem in this statement?

Posted 28 July 2010 - 02:35 AM

i try by myself but a lot of problem.. can any1 help to solve pls...? thx

Class TestString
{
	int finalQuantity;
				int finalQuantity1;
				char lineid1;
				char datetime1;
				char lineid;				
				char datetime;

string strSQL= @" SELECT * FROM (SELECT Date_Time, Line_ID, Final_QTY
        FROM PICS_R  WHERE Date_Time IN (SELECT MAX(A.Date_Time)
        FROM (SELECT DISTINCT (Date_Time)
        FROM PICS_R) A)) DERIVEDTBL;                         
    
				sqlConn.Open();
				SqlCommand cmd=new SqlCommand(strSQL,sqlConn);
				SqlDataReader dr=cmd.ExecuteReader();

				while dr.Read();
				{
				finalQuantity=dr.(Final_QTY);
				lineid=dr(Line_ID);
				datetime=dr(Date_Time);						
				}
			sqlConn.Close();
	}

private void testing()
		{
			int finalQuantity1;
			char lineid1;
			char datetime1;
			string strSQL= @"
SELECT Date_Time, Line_ID, Final_QTY FROM PICS_R WHERE (Date_Time IN          (SELECT MIN(B.Date_Time) FROM (SELECT TOP 2 Date_Time FROM (SELECT DISTINCT(Date_Time) FROM PICS_R) A ORDER BY Date_Time DESC) B)/>);
                 
      sqlConn.Open();
				SqlCommand cmd=new SqlCommand(strSQL,sqlConn);
				SqlDataReader cd=cmd.ExecuteReader();
				
				while cd.Read();
				{
				finalQuantity1=cd(Final_QTY);
				lineid1=cd(Line_ID);
				datetime1=cd(Date_Time);				
				}
				sqlConn.Close();
		}

if (finalQuantity == finalQuantity1);
				{
					e.Item.Cells[6].ForeColor=Color.yellow;
				}




main problem i face is: sqlConn.open error

This post has been edited by eclipsed4utoo: 28 July 2010 - 05:24 AM
Reason for edit:: fixed closing code tag


Is This A Good Question/Topic? 0
  • +

Replies To: Got any idea about y so many problem in this statement?

#2 Adkins  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 66
  • View blog
  • Posts: 560
  • Joined: 27-October 09

Re: Got any idea about y so many problem in this statement?

Posted 28 July 2010 - 02:51 AM

first off please :code:

secondly what error message are you getting? What problem are you having? please expand.
Was This Post Helpful? 0
  • +
  • -

#3 Charles:)  Icon User is offline

  • D.I.C Regular

Reputation: 148
  • View blog
  • Posts: 357
  • Joined: 26-November 09

Re: Got any idea about y so many problem in this statement?

Posted 28 July 2010 - 04:33 AM

View Postfeelblue, on 28 July 2010 - 01:35 AM, said:

sqlConn.Open();				
SqlCommand cmd=new SqlCommand(strSQL,sqlConn);
SqlDataReader dr=cmd.ExecuteReader();

while dr.Read();
{
    finalQuantity=dr.(Final_QTY);
    lineid=dr(Line_ID);
    datetime=dr(Date_Time);						
}
sqlConn.Close();



These lines all need to be in a method - at the moment they're just sitting in the class itself.

This post has been edited by Charles:): 28 July 2010 - 04:41 AM

Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: Got any idea about y so many problem in this statement?

Posted 28 July 2010 - 05:26 AM

what is the actual error message you are getting? copy and paste it.
Was This Post Helpful? 0
  • +
  • -

#5 Searock  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 35
  • View blog
  • Posts: 259
  • Joined: 15-September 09

Re: Got any idea about y so many problem in this statement?

Posted 28 July 2010 - 11:30 AM

I am not sure if this is going to work, but give it a try.

Change your code from

string strSQL= @" SELECT * FROM (SELECT Date_Time, Line_ID, Final_QTY
        FROM PICS_R  WHERE Date_Time IN (SELECT MAX(A.Date_Time)
        FROM (SELECT DISTINCT (Date_Time)
        FROM PICS_R) A)) DERIVEDTBL;   



to

string strSQL= @" SELECT * FROM (SELECT Date_Time, Line_ID, Final_QTY
        FROM PICS_R  WHERE Date_Time IN (SELECT MAX(A.Date_Time)
        FROM (SELECT DISTINCT (Date_Time)
        FROM PICS_R) A)) DERIVEDTBL";   



and

string strSQL= @"
SELECT Date_Time, Line_ID, Final_QTY FROM PICS_R WHERE (Date_Time IN          (SELECT MIN(B.Date_Time) FROM (SELECT TOP 2 Date_Time FROM (SELECT DISTINCT(Date_Time) FROM PICS_R) A ORDER BY Date_Time DESC) B)/>);



to

string strSQL= @"
SELECT Date_Time, Line_ID, Final_QTY FROM PICS_R WHERE (Date_Time IN          (SELECT MIN(B.Date_Time) FROM (SELECT TOP 2 Date_Time FROM (SELECT DISTINCT(Date_Time) FROM PICS_R) A ORDER BY Date_Time DESC) B)/>)";




I think you you have started your string from here string strSQL= @" SELECT .....DERIVEDTBL; and forgot to close it here.

This post has been edited by Searock: 28 July 2010 - 12:08 PM

Was This Post Helpful? 0
  • +
  • -

#6 feelblue  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 27-September 09

Re: Got any idea about y so many problem in this statement?

Posted 29 July 2010 - 05:29 AM

View PostAdkins, on 28 July 2010 - 01:51 AM, said:

first off please :code:

secondly what error message are you getting? What problem are you having? please expand.


the problem is: sqlConn.open error but i solve it ad.
but now problem i face is this ...

	string strSQL= @"
		SELECT *
			FROM (SELECT Date_Time, Line_ID, Final_QTY
			FROM PICS_R
			WHERE Date_Time IN
			(SELECT MAX(A.Date_Time)
			FROM (SELECT DISTINCT (Date_Time)
			FROM PICS_R) A))DERIVEDTBL";

			
					sqlConn.Open();
					SqlCommand cmd=new SqlCommand(strSQL,sqlConn);
					SqlDataReader dr=cmd.ExecuteReader();
				
					dr.Read();
			
					fq = Convert.ToString(dr["Final_QTY"]);
					dt=Convert.ToString(dr["Date_Time"]);

					dr.Close ();
					sqlConn.Close();




In the database the
FINAL_QTY got 5 data they are: 1,2,3,4,5
Date_TIME got 5 data also.

But when i write like this, i only get 1 data.
Is that in c# got syntax like : ".movenext" in vb?

cos i want to get all the 5 data from this table.

This post has been edited by feelblue: 29 July 2010 - 05:29 AM

Was This Post Helpful? 0
  • +
  • -

#7 Rico Diesel  Icon User is offline

  • D.I.C Head

Reputation: 62
  • View blog
  • Posts: 122
  • Joined: 06-May 10

Re: Got any idea about y so many problem in this statement?

Posted 29 July 2010 - 05:38 AM

The dr.Read(); command will do this for you

from msdn

usage could be something like this (code borrowed from msdn):
SqlDataReader reader = command.ExecuteReader();

// Call Read before accessing data.
while (reader.Read())
{
  Console.WriteLine(String.Format("{0}, {1}",
    reader[0], reader[1]));
}



Hope this helps,

Rico
Was This Post Helpful? 0
  • +
  • -

#8 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1012
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: Got any idea about y so many problem in this statement?

Posted 29 July 2010 - 05:39 AM

View Postfeelblue, on 29 July 2010 - 03:29 AM, said:

					SqlDataReader dr=cmd.ExecuteReader();
				
					dr.Read();
			
					fq = Convert.ToString(dr["Final_QTY"]);
					dt=Convert.ToString(dr["Date_Time"]);

					dr.Close ();
					sqlConn.Close();


In the database the
FINAL_QTY got 5 data they are: 1,2,3,4,5
Date_TIME got 5 data also.

But when i write like this, i only get 1 data.
Is that in c# got syntax like : ".movenext" in vb?

cos i want to get all the 5 data from this table.

You only call the Read method once, so you only get one row of data. You need to put the dr.Read() method in a loop.
Was This Post Helpful? 0
  • +
  • -

#9 feelblue  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 27-September 09

Re: Got any idea about y so many problem in this statement?

Posted 29 July 2010 - 05:57 AM

View PostRico Diesel, on 29 July 2010 - 04:38 AM, said:

The dr.Read(); command will do this for you

from msdn

usage could be something like this (code borrowed from msdn):
SqlDataReader reader = command.ExecuteReader();

// Call Read before accessing data.
while (reader.Read())
{
  Console.WriteLine(String.Format("{0}, {1}",
    reader[0], reader[1]));
}



Hope this helps,

Rico


thx a lot
Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5907
  • View blog
  • Posts: 12,813
  • Joined: 16-October 07

Re: Got any idea about y so many problem in this statement?

Posted 29 July 2010 - 06:19 AM

I have a problem with the SQL, this would be less wasteful:
string strSQL= @"
	SELECT Date_Time, Final_QTY
		FROM PICS_R
		WHERE Date_Time = ( SELECT MAX(Date_Time) FROM PICS_R)
";



For your data, maybe:
dt = null;
List<String> fqList;
SqlCommand cmd=new SqlCommand(strSQL,sqlConn);
try {
	sqlConn.Open();
	SqlDataReader dr = cmd.ExecuteReader();
	
	while (dr.Read()) {
		if (dt==null) { 
			dt = Convert.ToString(dr["Date_Time"]);
			fqList = new List<String>();
		}
		fqList.Add(Convert.ToString(dr["Final_QTY"]));
	}
} finally {
	sqlConn.Close();
}


Was This Post Helpful? 2
  • +
  • -

#11 feelblue  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 27-September 09

Re: Got any idea about y so many problem in this statement?

Posted 29 July 2010 - 11:41 PM

thx a lot ... i get it.. now i face new problem

can help me see why this have error?
ERROR="oledbException was unhandled by user code-no value given for one or more required parameters"
 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class Default2 : System.Web.UI.Page
{
    private OleDbConnection mycon;
    protected void Page_Load(object sender, EventArgs e)
    {
        mycon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\My Documents\Visual Studio 2005\db2.mdb;Persist Security Info=False
");
       // InitializeCulture();
        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        
        
        string mySQL = @"SELECT * FROM (SELECT Date_Time, Line_ID, Final_QTY FROM PICS_R WHERE Date_Time IN (SELECT MAX(A.Date_Time) FROM (SELECT DISTINCT (Date_Time) FROM PICS_R) A)) N INNER JOIN (SELECT Date_Time, Line_ID, Final_QTY FROM PICS_R WHERE Date_Time IN (SELECT MIN(B.Date_Time) FROM (SELECT TOP 2 Date_Time FROM (SELECT DISTINCT (Date_Time) FROM PICS_R) A ORDER BY Date_Time DESC) B)/>) O ON N.Line_ID = O.Line_ID AND N.Final_QTY = O.Final_QTY";
    mycon.Open();        

        OleDbCommand cmd = new OleDbCommand(mySQL, mycon);
        OleDbDataReader reading; 
            reading = cmd.ExecuteReader();
       
        // Call Read before accessing data.
        while (reading.Read())
        {
            Console.WriteLine(String.Format("{0}, {1},  {2}, {3},{4}, {5}",
reading[0], reading[1], reading[2], reading[3], reading[4], reading[5]));
            //, reader[3], reader[4], reader[5]
            // if (reader[1]==reader[4])
            //{
            // e.Item.Cells[6].ForeColor=Color.Pink;
            //}
        }
        mycon.Close();
    }
}



why with this no problem but no output...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class Default2 : System.Web.UI.Page
{
    private OleDbConnection mycon;
    protected void Page_Load(object sender, EventArgs e)
    {
        mycon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Documents and Settings\My Documents\Visual Studio 2005\db2.mdb;Persist Security Info=False
");
       // InitializeCulture();
        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        
        
              string mySQL = @"select * from PICS_R";
mycon.Open();
         

        OleDbCommand cmd = new OleDbCommand(mySQL, mycon);
        OleDbDataReader reading; 
            reading = cmd.ExecuteReader();
       
        // Call Read before accessing data.
        while (reading.Read())
        {
            Console.WriteLine(String.Format("{0}, {1},  {2}, {3},{4}, {5}",
reading[0], reading[1], reading[2], reading[3], reading[4], reading[5]));
            //, reader[3], reader[4], reader[5]
            // if (reader[1]==reader[4])
            //{
            // e.Item.Cells[6].ForeColor=Color.Pink;
            //}
        }
        mycon.Close();
    }
}

 


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1