Code below:
If Session("ID") = 1 Or Session("ID") = 2 Then
select case (request.form("Individual"))
case "Individual" sql = "Select V_Monthreport.Q_ID,
V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
V_Monthreport.PersID=" & session("Pers_ID") & " And
V_Monthreport.DeptID=" & request.form("SectID") & " And
V_Monthreport.Maand=" & request.form("Maand") & " And
V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
V_Monthreport.Q_ID"
case "Section"
case "Summary"
end select
End If
<% while not rstMain.eof %>
<tr>
<td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
%></font></td>
<td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Question") %></font></td>
<td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Answer") %></font></td>
</tr>
<% rstMain.movenext
wend%>
<% End If %>
I am experiencing a problem in that the data when displayed is
duplicated. The data in the db is not duplicated. So I dont have a clue
why the data is displayed in duplicates. Any comments on y?amatuer
How do you expect to get a right answer without posting some data to be
tested and an expected result.?
Take a look at DISTINCT , GROUP BY clause in the BOL
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||here's the table:
CREATE TABLE [dbo].[V_Monthreport] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PersID] [int] NULL ,
[DeptID] [int] NULL ,
[Datum] [datetime] NULL ,
[Maand] [int] NULL ,
[Jaar] [int] NULL ,
[Q_ID] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[V_Monthreport_Q] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AfdelingID] [int] NULL ,
[Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QNo] [int] NULL ,
[Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
& data looks like this:
V_Monthreport_Q:
14 1 Agricultural Service Agricultural Information Services 1 Marketing
of available new services of the Department (List Opportunities)
15 1 Agricultural Service Agricultural Information
Services 2 Opportunity/Involvement in identifying and development of
new clients; products; projects
16 1 Agricultural Service Agricultural Information
Services 3 Involvement in sustaining and improvement of the level of
expertise, as well as the knowledge and skills
17 1 Agricultural Service Agricultural Information Services 4 Intra
and inter-departmental co-operation (disposition/view)
18 1 Agricultural Service Agricultural Information
Services 5 Agricultural condition/State of Agriculture: Climate
(temperature, wind, etc)
19 1 Agricultural Service Agricultural Information
Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
nutritional value)
20 1 Agricultural Service Agricultural Information
Services 7 Agricultural condition/State of Agriculture: Moisture status
21 1 Agricultural Service Agricultural Information
Services 8 Agricultural condition/State of Agriculture: Livestock
(conditions)
22 1 Agricultural Service Agricultural Information
Services 9 Agricultural condition/State of Agriculture: Commodity
prices
23 1 Agricultural Service Agricultural Information
Services 10 Agricultural condition/State of Agriculture: Labour
24 1 Agricultural Service Agricultural Information
Services 11 Agricultural condition/State of Agriculture: Security /
Safe keeping
25 1 Agricultural Service Agricultural Information Services 12 Client
Relations
26 1 Agricultural Service Agricultural Information Services 13 Threats
/ Concerns
V_Monthreport:
53 4 1 6/21/2006 6 2006 1 Test1
54 4 1 6/21/2006 6 2006 2 Test2
55 4 1 6/21/2006 6 2006 4 Test4
56 4 1 6/21/2006 6 2006 5 Test5
57 4 1 6/21/2006 6 2006 6 Test6
58 4 1 6/21/2006 6 2006 8 Test8
59 4 1 6/21/2006 6 2006 9 Test9
60 4 1 6/21/2006 6 2006 10 Test10
61 4 1 6/21/2006 6 2006 11 Test11
62 4 1 6/21/2006 6 2006 12 Test12
63 4 1 6/21/2006 6 2006 13 Test13
Sorry but this was the best i could.see if you cn help Uri
Dimant.thanx.
Uri Dimant wrote:
> amatuer
> How do you expect to get a right answer without posting some data to be
> tested and an expected result.?
> Take a look at DISTINCT , GROUP BY clause in the BOL
>
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> > Code below:
> >
> > If Session("ID") = 1 Or Session("ID") = 2 Then
> > select case (request.form("Individual"))
> > case "Individual" sql = "Select V_Monthreport.Q_ID,
> > V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> > JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> > V_Monthreport.PersID=" & session("Pers_ID") & " And
> > V_Monthreport.DeptID=" & request.form("SectID") & " And
> > V_Monthreport.Maand=" & request.form("Maand") & " And
> > V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> > V_Monthreport.Q_ID"
> > case "Section"
> > case "Summary"
> > end select
> > End If
> >
> > <% while not rstMain.eof %>
> > <tr>
> > <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> > %></font></td>
> > <td><font face="Tahoma" size="-2" color="Black"><%=> > rstMain("Question") %></font></td>
> > <td><font face="Tahoma" size="-2" color="Black"><%=> > rstMain("Answer") %></font></td>
> > </tr>
> > <% rstMain.movenext
> > wend%>
> > <% End If %>
> >
> > I am experiencing a problem in that the data when displayed is
> > duplicated. The data in the db is not duplicated. So I dont have a clue
> > why the data is displayed in duplicates. Any comments on y?
> >|||No so much
INSERT INTO Table.. VALUES (...
Can you provide a full DDL?
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150883115.391130.319520@.p79g2000cwp.googlegroups.com...
> here's the table:
> CREATE TABLE [dbo].[V_Monthreport] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PersID] [int] NULL ,
> [DeptID] [int] NULL ,
> [Datum] [datetime] NULL ,
> [Maand] [int] NULL ,
> [Jaar] [int] NULL ,
> [Q_ID] [int] NULL ,
> [Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[V_Monthreport_Q] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AfdelingID] [int] NULL ,
> [Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QNo] [int] NULL ,
> [Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> & data looks like this:
> V_Monthreport_Q:
> 14 1 Agricultural Service Agricultural Information Services 1 Marketing
> of available new services of the Department (List Opportunities)
> 15 1 Agricultural Service Agricultural Information
> Services 2 Opportunity/Involvement in identifying and development of
> new clients; products; projects
> 16 1 Agricultural Service Agricultural Information
> Services 3 Involvement in sustaining and improvement of the level of
> expertise, as well as the knowledge and skills
> 17 1 Agricultural Service Agricultural Information Services 4 Intra
> and inter-departmental co-operation (disposition/view)
> 18 1 Agricultural Service Agricultural Information
> Services 5 Agricultural condition/State of Agriculture: Climate
> (temperature, wind, etc)
> 19 1 Agricultural Service Agricultural Information
> Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
> nutritional value)
> 20 1 Agricultural Service Agricultural Information
> Services 7 Agricultural condition/State of Agriculture: Moisture status
> 21 1 Agricultural Service Agricultural Information
> Services 8 Agricultural condition/State of Agriculture: Livestock
> (conditions)
> 22 1 Agricultural Service Agricultural Information
> Services 9 Agricultural condition/State of Agriculture: Commodity
> prices
> 23 1 Agricultural Service Agricultural Information
> Services 10 Agricultural condition/State of Agriculture: Labour
> 24 1 Agricultural Service Agricultural Information
> Services 11 Agricultural condition/State of Agriculture: Security /
> Safe keeping
> 25 1 Agricultural Service Agricultural Information Services 12 Client
> Relations
> 26 1 Agricultural Service Agricultural Information Services 13 Threats
> / Concerns
> V_Monthreport:
> 53 4 1 6/21/2006 6 2006 1 Test1
> 54 4 1 6/21/2006 6 2006 2 Test2
> 55 4 1 6/21/2006 6 2006 4 Test4
> 56 4 1 6/21/2006 6 2006 5 Test5
> 57 4 1 6/21/2006 6 2006 6 Test6
> 58 4 1 6/21/2006 6 2006 8 Test8
> 59 4 1 6/21/2006 6 2006 9 Test9
> 60 4 1 6/21/2006 6 2006 10 Test10
> 61 4 1 6/21/2006 6 2006 11 Test11
> 62 4 1 6/21/2006 6 2006 12 Test12
> 63 4 1 6/21/2006 6 2006 13 Test13
> Sorry but this was the best i could.see if you cn help Uri
> Dimant.thanx.
>
> Uri Dimant wrote:
>> amatuer
>> How do you expect to get a right answer without posting some data to be
>> tested and an expected result.?
>> Take a look at DISTINCT , GROUP BY clause in the BOL
>>
>>
>> "amatuer" <njoosub@.gmail.com> wrote in message
>> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
>> > Code below:
>> >
>> > If Session("ID") = 1 Or Session("ID") = 2 Then
>> > select case (request.form("Individual"))
>> > case "Individual" sql = "Select V_Monthreport.Q_ID,
>> > V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
>> > JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
>> > V_Monthreport.PersID=" & session("Pers_ID") & " And
>> > V_Monthreport.DeptID=" & request.form("SectID") & " And
>> > V_Monthreport.Maand=" & request.form("Maand") & " And
>> > V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
>> > V_Monthreport.Q_ID"
>> > case "Section"
>> > case "Summary"
>> > end select
>> > End If
>> >
>> > <% while not rstMain.eof %>
>> > <tr>
>> > <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
>> > %></font></td>
>> > <td><font face="Tahoma" size="-2" color="Black"><%=>> > rstMain("Question") %></font></td>
>> > <td><font face="Tahoma" size="-2" color="Black"><%=>> > rstMain("Answer") %></font></td>
>> > </tr>
>> > <% rstMain.movenext
>> > wend%>
>> > <% End If %>
>> >
>> > I am experiencing a problem in that the data when displayed is
>> > duplicated. The data in the db is not duplicated. So I dont have a clue
>> > why the data is displayed in duplicates. Any comments on y?
>> >
>|||> The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
Do you get duplicates when you run the same query from Query Analyzer? The
same rows will be returned multiple times when more than one row exists in
both tables with the same value in the joined columns.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||Thanx Dan, i sorted it out. The prob was that the inner join was not
unique.Anothr one had to be created in order not to yield more then the
required possible results.
Dan Guzman wrote:
> > The data in the db is not duplicated. So I dont have a clue
> > why the data is displayed in duplicates. Any comments on y?
> Do you get duplicates when you run the same query from Query Analyzer? The
> same rows will be returned multiple times when more than one row exists in
> both tables with the same value in the joined columns.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> > Code below:
> >
> > If Session("ID") = 1 Or Session("ID") = 2 Then
> > select case (request.form("Individual"))
> > case "Individual" sql = "Select V_Monthreport.Q_ID,
> > V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> > JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> > V_Monthreport.PersID=" & session("Pers_ID") & " And
> > V_Monthreport.DeptID=" & request.form("SectID") & " And
> > V_Monthreport.Maand=" & request.form("Maand") & " And
> > V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> > V_Monthreport.Q_ID"
> > case "Section"
> > case "Summary"
> > end select
> > End If
> >
> > <% while not rstMain.eof %>
> > <tr>
> > <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> > %></font></td>
> > <td><font face="Tahoma" size="-2" color="Black"><%=> > rstMain("Question") %></font></td>
> > <td><font face="Tahoma" size="-2" color="Black"><%=> > rstMain("Answer") %></font></td>
> > </tr>
> > <% rstMain.movenext
> > wend%>
> > <% End If %>
> >
> > I am experiencing a problem in that the data when displayed is
> > duplicated. The data in the db is not duplicated. So I dont have a clue
> > why the data is displayed in duplicates. Any comments on y?
> >
Showing posts with label below. Show all posts
Showing posts with label below. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
data convertion with dynamic sql
Hi, All,
I;m new to SQl server and having a problem with dynamic sql within an
procedure.
I got error(the error msg and procedure are below) when passing an smallint
value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
datatype, how to do it?
THanks a lot.
Here is the error message I got:
**********
0
ST_ccst_code_value
Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
Conversion failed when converting the nvarchar value 'Update
ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
Here is the procedure:
****
ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
@.BatchName nvarchar(30)
AS
Declare @.batch_ID smallint
select @.batch_ID = 0
Declare @.SQL VarChar(1000)
Declare @.ST_table nvarchar(30)
exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
print @.batch_ID
select @.ST_table = 'ST_'+ @.BatchName
print @.ST_table
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
Exec (@.SQL)
GOWhen you try to add (concatenate) the value for @.batch_ID to the query
string, SQL tries to implicitly convert the string to an integer, because in
t
has a higher data type precedence than char data types.
Try:
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ CAST(@.batch_ID
as nvarchar(10))
Exec (@.SQL)
"Jessie" wrote:
> Hi, All,
> I;m new to SQl server and having a problem with dynamic sql within an
> procedure.
> I got error(the error msg and procedure are below) when passing an smallin
t
> value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
> datatype, how to do it?
> THanks a lot.
> Here is the error message I got:
> **********
> 0
> ST_ccst_code_value
> Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
> Conversion failed when converting the nvarchar value 'Update
> ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
> Here is the procedure:
> ****
> ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
> @.BatchName nvarchar(30)
> AS
> Declare @.batch_ID smallint
> select @.batch_ID = 0
> Declare @.SQL VarChar(1000)
> Declare @.ST_table nvarchar(30)
>
> exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
> print @.batch_ID
> select @.ST_table = 'ST_'+ @.BatchName
> print @.ST_table
> SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
> Exec (@.SQL)
> GO
I;m new to SQl server and having a problem with dynamic sql within an
procedure.
I got error(the error msg and procedure are below) when passing an smallint
value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
datatype, how to do it?
THanks a lot.
Here is the error message I got:
**********
0
ST_ccst_code_value
Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
Conversion failed when converting the nvarchar value 'Update
ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
Here is the procedure:
****
ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
@.BatchName nvarchar(30)
AS
Declare @.batch_ID smallint
select @.batch_ID = 0
Declare @.SQL VarChar(1000)
Declare @.ST_table nvarchar(30)
exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
print @.batch_ID
select @.ST_table = 'ST_'+ @.BatchName
print @.ST_table
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
Exec (@.SQL)
GOWhen you try to add (concatenate) the value for @.batch_ID to the query
string, SQL tries to implicitly convert the string to an integer, because in
t
has a higher data type precedence than char data types.
Try:
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ CAST(@.batch_ID
as nvarchar(10))
Exec (@.SQL)
"Jessie" wrote:
> Hi, All,
> I;m new to SQl server and having a problem with dynamic sql within an
> procedure.
> I got error(the error msg and procedure are below) when passing an smallin
t
> value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
> datatype, how to do it?
> THanks a lot.
> Here is the error message I got:
> **********
> 0
> ST_ccst_code_value
> Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
> Conversion failed when converting the nvarchar value 'Update
> ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
> Here is the procedure:
> ****
> ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
> @.BatchName nvarchar(30)
> AS
> Declare @.batch_ID smallint
> select @.batch_ID = 0
> Declare @.SQL VarChar(1000)
> Declare @.ST_table nvarchar(30)
>
> exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
> print @.batch_ID
> select @.ST_table = 'ST_'+ @.BatchName
> print @.ST_table
> SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
> Exec (@.SQL)
> GO
Thursday, March 8, 2012
Data Binding-DataAdapter
Hi i'm a new to ASP.NET and for some reason when i click the Next button in the code below, the pageIndex does not change. Please assist, Basically what i'm trying to do is to use DataAdapter.fill but passing in the start index and the number of records to pull from the dataset table.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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;
using System.Data.OleDb;
publicpartialclassHome : System.Web.UI.Page
{
//Connection
OleDbConnection dbConn;//discount that can be change by user using a gui interface
//CurrentPage
int pageIndex = 0;double discount = 0.15 ;protectedvoid Page_Load(object sender,EventArgs e)
{
// homeGridView.Visible = true;
BindList();
}
protectedstring getSpecial(string price,object sale){
String special ="";if (sale.ToString().CompareTo("True") == 0){
special =String.Format("{0:C}",double.Parse(price) * (1-discount));}
return special;}
protectedvoid BindList()
{
//Creating an object for the 'PagedDataSource' for holding the data.
//PagedDataSource objPage = new PagedDataSource();
try
{
//open connection
openConnection();
//sql command
string columns ="*";string SqlCommand ="Select " + columns +" from Books";
//create adapters and DataSet
OleDbDataAdapter myAdapter =newOleDbDataAdapter(SqlCommand, dbConn);DataSet ds =newDataSet("bSet");
//create table
DataTable dt =newDataTable("Books");myAdapter.Fill(ds, pageIndex, 9,"Books");
Response.Write("Page Index: "+pageIndex);
//create table data view
DataView dv =newDataView(ds.Tables["bTable"]);booksDataList.DataSource = ds;
booksDataList.DataBind();
myAdapter.Dispose();
dbConn.Close();
}
catch (Exception ex){
Response.Write("Exception thrown in BindList()");
dbConn.Close();
throw ex;}
}
publicvoid openConnection()
{
string provider="Microsoft.Jet.OLEDB.4.0";string dataSource ="C:/Documents and Settings/Owner/My Documents/Visual Studio 2005/WebSites/E-BookOnline/App_Data/BooksDB.mdb";
dbConn =newOleDbConnection("Provider =" + provider +";" +"Data Source =" + dataSource);dbConn.Open();
}
protectedvoid nextClick(object sender,EventArgs e){
pageIndex=pageIndex+1;
Response.Write("In nextClick"+pageIndex);BindList();
}
protectedvoid prevClick(object sender,EventArgs e){
if (pageIndex > 0){
pageIndex=pageIndex-1;
BindList();
}
}
}
It is not advisable to use DataAdpater to page records... instead of this you need to use queries for that but anyway you can found this article useful.
http://msdn2.microsoft.com/en-us/library/tx1c9c2f(VS.80).aspx
Satya
Friday, February 17, 2012
Cyrstal Report count
Need help. I am new to crystal reports. I have the sql below that would run perfectly fine unders windows SQL 2000 server and give me the results that I needed. But I can't get the correct count from crystal report if I only want each distinct record to shown once.
SELECT TYPE, PART_NUM, COUNT(PART_NUM) AS Expr1
FROM dbo.ITEM
GROUP BY PART_NUM, TYPE
type part_num count
Monitor gx260 20
Monitor gx620 40You might need to use distinct count in Crystal.
GJ
SELECT TYPE, PART_NUM, COUNT(PART_NUM) AS Expr1
FROM dbo.ITEM
GROUP BY PART_NUM, TYPE
type part_num count
Monitor gx260 20
Monitor gx620 40You might need to use distinct count in Crystal.
GJ
CVS Export in ASCII format
For the CSV export problem, where excel opens data into one column, I
had to add a report link using the below code to fix the problem. Is
there a way to change the underlying export encoding in reporting
services?
="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"There is for RS 2005. Not for RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<slov1@.hotmail.com> wrote in message
news:1135297330.158158.120010@.g49g2000cwa.googlegroups.com...
> For the CSV export problem, where excel opens data into one column, I
> had to add a report link using the below code to fix the problem. Is
> there a way to change the underlying export encoding in reporting
> services?
> ="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"
>
had to add a report link using the below code to fix the problem. Is
there a way to change the underlying export encoding in reporting
services?
="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"There is for RS 2005. Not for RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<slov1@.hotmail.com> wrote in message
news:1135297330.158158.120010@.g49g2000cwa.googlegroups.com...
> For the CSV export problem, where excel opens data into one column, I
> had to add a report link using the below code to fix the problem. Is
> there a way to change the underlying export encoding in reporting
> services?
> ="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"
>
Subscribe to:
Posts (Atom)