|
-
Nov 24th, 2003, 01:50 PM
#1
Thread Starter
Lively Member
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
-
Nov 24th, 2003, 08:45 PM
#2
Hyperactive Member
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();
}
}
}
}
-
Nov 25th, 2003, 11:31 AM
#3
Thread Starter
Lively Member
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>
-
Nov 25th, 2003, 12:56 PM
#4
Thread Starter
Lively Member
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>
-
Nov 25th, 2003, 01:19 PM
#5
Hyperactive Member
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>
-
Nov 25th, 2003, 03:14 PM
#6
Thread Starter
Lively Member
I dont want to add all the buyers at a time
I want to add each buyers sum in to a seperate variable
-
Nov 25th, 2003, 03:40 PM
#7
Hyperactive Member
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>
-
Nov 25th, 2003, 05:14 PM
#8
Thread Starter
Lively Member
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote