Problem with database??

The following code doesn't retrieve the rows infact there are rows present in that. Can anyone help me out with this.
Ur help will be appreciated.

VB Code:

OleDbConnection conn = new OleDbConnection(pathinfo);
    String SQL,dr;
    void Page_Load()
    {
        Page.DataBind();
        SQL="Select Sum(Poamount) as amount from stats where Buyer='Brad' group by Poamount";
        dbCommand1 = new OleDbCommand(SQL,conn);
        conn.Open();
        OleDbDataReader dataRead = dbCommand1.ExecuteReader();
        dr = dataRead.GetString(dataRead.GetOrdinal("amount"));
//      dr = dataRead.GetInt32(0);
        dataRead.Close();
        conn.Close();
    }

The error says there are no rows/columns.

Thanks a lot

You need to call dataRead.Read() to get to the first row. If you're only bringing back a single row with a single column, I'd use ExecuteScalar:

PHP Code:

void Page_Load(object sender, System.EventArgs e) { double amount = 0.0; string cmdText = "select sum ( poamount ) as amount from [stats] where buyer = 'Brad' group by poamount"; string connString = "provider=sqloledb;user id=sa;password=sa;database=Scratch;server=DeathAngel;"; using (OleDbConnection cn = new OleDbConnection(connString)) using (OleDbCommand cmd = new OleDbCommand(cmdText, cn)) { try { cmd.Connection.Open(); object result = cmd.ExecuteScalar(); if (result != null) { amount = System.Convert.ToDouble(result); } } finally { if (cmd != null && cmd.Connection != null) { cmd.Connection.Close(); } } } }

But the amount is not displaying correctlyin my field

VB Code:

<%@ Page Language="C#" Debug="true"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
 
 
<SCRIPT LANGUAGE="C#" Runat="server">
    OleDbCommand cmd;
    static String pathinfo="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/InetPub/wwwroot/database/buyerStats.mdb";
    OleDbConnection conn = new OleDbConnection(pathinfo);
    int dr;
    void Page_Load(object sender, System.EventArgs e) 
    { 
    double amount = 0.0; 
    string cmdText = "select sum ( poamount ) as amount from [stats] where buyer = 'Brad' group by poamount"; 
    using (cmd = new OleDbCommand(cmdText, conn)) 
    { 
        try 
        { 
            cmd.Connection.Open(); 
            object result = cmd.ExecuteScalar(); 
            if (result != null) 
            { 
                amount = System.Convert.ToDouble(result); 
            } 
        } 
        finally 
        { 
            if (cmd != null && cmd.Connection != null) 
            { 
                cmd.Connection.Close(); 
            } 
        } 
    } 
    conn.Close();
    }
</SCRIPT>
<html>
<body>
<form runat="server">
    Number of Pos:<asp:Literal id="amount" runat="server" />
</form>
</body>
</html>

Is there anyproblem with the following code???

It says The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable at output[i] = dataRead.GetInt32(0).

VB Code:

<%@ Page Language="C#" Debug="true"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
 
 
<SCRIPT LANGUAGE="C#" Runat="server">
    OleDbCommand cmd;
    OleDbDataAdapter da1;
    DataSet ds;
    static String pathinfo="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/InetPub/wwwroot/database/buyerStats.mdb";
    OleDbConnection conn = new OleDbConnection(pathinfo);
    int dr,i=0;
    int[] output;
    void Page_Load(object sender, System.EventArgs e) 
    { 
    double amount = 0.0; 
    string cmdText = "select sum ( poamount ) as bradAmount from [stats] where buyer = 'Brad' group by Buyer"; 
    cmdText += " UNION select sum( poamount ) as judyAmount from [stats] where buyer = 'Judy' group by Buyer";
    cmdText += " UNION select sum( poamount ) as mikeAmount from [stats] where buyer = 'Mike' group by Buyer";
    cmdText += " UNION select sum( poamount ) as sueAmount  from [stats] where buyer = 'Sue' group by Buyer";
    cmdText += " UNION select sum( poamount ) as jeffAmount from [stats] where buyer = 'Jeff' group by Buyer";
    OleDbDataReader dataRead;
    using (cmd = new OleDbCommand(cmdText, conn)) 
    { 
        conn.Open();
        dataRead = cmd.ExecuteReader();
        try 
        { 
        while(dataRead.Read())
        {
            output[i]=dataRead.GetInt32(0);
            i++;
        }        
        } 
        finally 
        { 
            if (dataRead != null) 
            { 
                dataRead.Close();
            } 
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        } 
    } 
    conn.Close();
    }
</SCRIPT>
<html>
<body>
 
</body>
</html>

try something like this:

PHP Code:

<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="C#" runat="server"> protected override void OnLoad(System.EventArgs e) { double amount = 0.0; string mdbPath = @"C:\Documents and Settings\ccarter\My Documents\buyerStats.mdb"; string connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdbPath); string cmdText = "select sum(amount) from ( select sum(poamount) as amount from stats group by buyer )"; using (OleDbConnection cn = new OleDbConnection(connString)) using (OleDbCommand cmd = new OleDbCommand(cmdText, cn)) { try { cmd.Connection.Open(); object result = cmd.ExecuteScalar(); if (result != null) { amount = System.Convert.ToDouble(result); } } finally { if(cmd != null && cmd.Connection != null) { cmd.Connection.Close(); } } } lblAmount.Text = amount.ToString("c"); } </script> <html> <body> <form runat="server"> Amount:<asp:Label ID="lblAmount" Runat="server"/> </form> </body> </html>

I dont want to add all the buyers at a time
I want to add each buyers sum in to a seperate variable

PHP Code:

<%@ Import Namespace="System.Collections" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="C#" runat="server"> private struct Buyer { public double Amount; public string Name; } protected override void OnLoad(System.EventArgs e) { ArrayList buyerList = new ArrayList(); string mdbPath = @"C:\Documents and Settings\ccarter\My Documents\buyerStats.mdb"; string connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdbPath); string cmdText = "select buyer, sum(poamount) as amount from stats group by buyer"; using (OleDbConnection cn = new OleDbConnection(connString)) using (OleDbCommand cmd = new OleDbCommand(cmdText, cn)) { OleDbDataReader dr = null; try { cmd.Connection.Open(); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (dr != null) { while(dr.Read()) { Buyer buyer = new Buyer(); buyer.Name = dr["buyer"].ToString(); buyer.Amount = System.Convert.ToDouble(dr["amount"]); buyerList.Add(buyer); } } } finally { if (dr != null) { dr.Close(); } if(cmd != null && cmd.Connection != null) { cmd.Connection.Close(); } } foreach(Buyer buyer in buyerList) { lblAmount.Text += buyer.Name + " -> " + buyer.Amount.ToString("c") + "<br/>"; } } } </script> <html> <body> <form runat="server"> Amounts:<br/><asp:Label ID="lblAmount" Runat="server"/> </form> </body> </html>

The problem is with sum() command in sql.
I donno why its saysis data conversion problem.
If I use count() command instead of sum() it was working fine.

Thanx a lot for ur replies pvb