Hi everybody!
Need to create report according to example below
User will input start & end date
From Date:______ ToDate:________
Then the report should create output like this:
CustomerID: Number of orders:
John 5
Jim 12
Aileen 21
I tried to accomplish that with Northwind / Orders, had no success.
Thank you in advance.
Alex
It would help to tell us what you've tried; that might give us a clue why you've not been successful.
Don|||
Hi Don,
my question is about how to write the SELECT statement to retrive the data
Thanks for the reply
|||You're going to keep what you've tried a close secret, eh? Heh.
This does what you want, except that it doesn't include the join to get the customer name:
SELECT [CustomerID]Don|||
,COUNT(*)AS [Numberof orders]
FROM [Orders]
WHERE OrderDateBETWEEN'6/1/1997'AND'6/30/1997'GROUP BY CustomerID
Thanks again Don,
I did tried to use that Select string, I have no errors but also no results.
I am using datagrid to display the result of the query.
This is the code that I use as ASP.NET, please take a pick.
I must to admit that I'm very green in SQL area.
<%@. Page Language="VB" %><%@. import Namespace="System.Data.SqlClient" %><%@. import Namespace="System.Data.SqlTypes" %><script runat="server"> shared public dim startDate as date shared public dim endDate as date sub load_page() startDate = "6/1/1997" endDate = "6/30/1997" dgOrders.DataSource = orders(startDate, endDate ) dgOrders.DataBind() end sub Function orders(ByVal fromDate As Date, ByVal toDate As Date) As System.Data.IDataReader Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Northwind'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim orderNumb as integer Dim queryString As String queryString = "SELECT [Orders].[CustomerID] ,COUNT(*) AS [orderNumb] FROM [Orders] WHERE OrderDate BETWEEN startDate AND endDate GROUP BY CustomerID" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_fromDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_fromDate.ParameterName = "@.fromDate" dbParam_fromDate.Value = fromDate dbParam_fromDate.DbType = System.Data.DbType.DateTime dbCommand.Parameters.Add(dbParam_fromDate) Dim dbParam_toDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_toDate.ParameterName = "@.toDate" dbParam_toDate.Value = toDate dbParam_toDate.DbType = System.Data.DbType.DateTime dbCommand.Parameters.Add(dbParam_toDate) dbConnection.Open Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Return dataReader End Function</script><html><head></head><body> <form runat="server"> <p> </p> <p> <asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundColumn DataField="orderNumb" HeaderText="CustomerID"></asp:BoundColumn> <asp:BoundColumn DataField="Orders" HeaderText="Number of Orders"></asp:BoundColumn> </Columns> </asp:DataGrid> </p> </form></body></html>|||
try this:
BETWEEN @.startDate AND @.endDate
|||Actually, it would beBETWEEN @.fromDateAND @.toDatebased on what are apparently the parameter names.
Don|||
Thank you guys for helping me here,
i tried that:BETWEEN @.fromDateAND @.toDate
still get empti web page, no errors though.
I probably missing somthing here, but i don't where to look cause there is no compilation error of any kind.
Thanks again
|||Can you try this:
<asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="True"> </asp:DataGrid>
You don't have the orders column in your data source.
Thank Limno
did tried that - get the same result - blank web page.
may be using the datagrid isn't such a good idea for this case?
just don't know how to display the query result in any other way.
Alex
|||The datareader for the datagrid looks fine to me. Can you confirm that you can run the sql query in your database with the results you are expecting? (hard code your dates in the query).
If you can get something from your database, we will be close to get something in the code.
|||
Hi Limno,
I did try this Select statement in SQL Query Analiser:
SELECT [CustomerID]
,COUNT(*)AS [Numberof orders]
FROM [Orders]
WHERE OrderDateBETWEEN'6/1/1997'AND'6/30/1997'
GROUP BY CustomerID
and did get the following result:
ANTON 1
AROUT 1
BERGS 1
BLAUS 1
BLONP 3
BSBEV 1
ERNSH 1
FAMIA 1
FOLKO 1
...
I went through my code again and again but dont find the problem.
Please advice
Here you go:
<%@. Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Public Shared startDate As Date
Public Shared endDate As Date
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
startDate = "6/1/1997"
endDate = "6/30/1997"
dgOrders.DataSource = orders(startDate, endDate)
dgOrders.DataBind()
End Sub
Function orders(ByVal fromDate As Date, ByVal toDate As Date) As System.Data.IDataReader
Dim connectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("Personal").ConnectionString
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String
queryString = "SELECT [Orders].[CustomerID] ,COUNT(*) AS [orderNumb] FROM [Orders] WHERE OrderDate BETWEEN @.startDate AND @.endDate GROUP BY CustomerID"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_fromDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_fromDate.ParameterName = "@.startDate"
dbParam_fromDate.Value = fromDate
dbParam_fromDate.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_fromDate)
Dim dbParam_toDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_toDate.ParameterName = "@.endDate"
dbParam_toDate.Value = toDate
dbParam_toDate.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_toDate)
dbConnection.Open()
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundColumn DataField="CustomerID" HeaderText="CustomerID"></asp:BoundColumn>
<asp:BoundColumn DataField="orderNumb" HeaderText="Number of Orders"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</div>
</form>
</body>
</html>
You are great Limno!
That works right from the beginning.
Didn't had the chance to examine the code yet, I'm curies how did you find the problem?
Oh…, is this two lines Must have in my code?
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
Thank a lotLimno
Alex
|||Hi Alex:
Actually, your page load event syntax is not right. It would save you a lot of headaches to use the IDE's intellisence. Pick the Page from left dropdown list , then find the event you want from right dropdown list.
Not the two lines you think. you can test them out.
BobP - BIM wrote: