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

Page 1 of 1

## 10 Replies - 894 Views - Last Post: 29 July 2010 - 11:41 PMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'http://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=183513&amp;s=69b23bcae1e59dc0e520318818788151&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 feelblue

Reputation: 0
• 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);

{
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);

{
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?

Reputation: 66
• 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

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

### #3 Charles:)

• D.I.C Regular

Reputation: 149
• Posts: 359
• Joined: 26-November 09

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

Posted 28 July 2010 - 04:33 AM

feelblue, on 28 July 2010 - 01:35 AM, said:

```sqlConn.Open();
SqlCommand cmd=new SqlCommand(strSQL,sqlConn);

{
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

### #4 eclipsed4utoo

Reputation: 1533
• Posts: 5,972
• 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.

### #5 Searock

• D.I.C Regular

Reputation: 35
• 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.

```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

### #6 feelblue

Reputation: 0
• 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

Adkins, on 28 July 2010 - 01:51 AM, said:

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

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

### #7 Rico Diesel

Reputation: 63
• 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.
{
Console.WriteLine(String.Format("{0}, {1}",
}

```

Hope this helps,

Rico

### #8 Momerath

• D.I.C Lover

Reputation: 1020
• Posts: 2,463
• Joined: 04-October 09

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

Posted 29 July 2010 - 05:39 AM

feelblue, on 29 July 2010 - 03:29 AM, said:

```					SqlDataReader dr=cmd.ExecuteReader();

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.

### #9 feelblue

Reputation: 0
• 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

Rico 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.
{
Console.WriteLine(String.Format("{0}, {1}",
}

```

Hope this helps,

Rico

thx a lot

### #10 baavgai

• Dreaming Coder

Reputation: 7118
• Posts: 14,835
• 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)
";

```

```dt = null;
List<String> fqList;
SqlCommand cmd=new SqlCommand(strSQL,sqlConn);
try {
sqlConn.Open();

if (dt==null) {
dt = Convert.ToString(dr["Date_Time"]);
fqList = new List<String>();
}
}
} finally {
sqlConn.Close();
}

```

### #11 feelblue

Reputation: 0
• 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);

// Call Read before accessing data.
{
Console.WriteLine(String.Format("{0}, {1},  {2}, {3},{4}, {5}",
//{
// 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);

// Call Read before accessing data.
{
Console.WriteLine(String.Format("{0}, {1},  {2}, {3},{4}, {5}",
//{
// e.Item.Cells[6].ForeColor=Color.Pink;
//}
}
mycon.Close();
}
}

```