Best way to copy database: bind

Showing posts with label bind. Show all posts

Tuesday, March 27, 2012

Binding WebService as Datasource

I've painstakingly managed to get a XmlDocument from a webservice and run this to retrieve data.

However, i cannot bind any of the returned elements to anything. Any help appreciated.

Are you using the XML data processing extension? What is the Query that you are using? More specifically, what is the Element Path for the query?

For more information, take a look at the following article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/RepServXMLDS.asp

Ian

binding textbox with SqlDataSource

Hi,

I wants to bind textbox with sqldatasource in c#.net so I am using following code and has following error...

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 22:
Line 23: System.Data.DataView dv = (DataView) SqlDataSource1.Select(System.Web.UI.DataSourceSelectArguments.Empty);
Line 24: TextBox1.Text = dv[0]["Proid"].ToString();
Line 25:
Line 26: }

Please, anybody knows solution tell me

Show all your code here, please. Do you have the column "Proid" in your datasource and a TextBox1 in your ASPX page?

binding textbox text to a database field

hello

is it possible to to bind a single field from a database table to a textbox.

I am trying to open a web form with five textboxes on it. When it loads is it possible to have the textboxes filled with a different field from a row in a database, selected with a sql statment.

Thanks in advance NubNub

Wrong Forum.|||

Yes, that is quite possible.

But you will get better assistance if you post your question in one of the .NET ASP related forums.

sql

Binding Render output to ReportViewer control?

I'm trying to take the output of the Render method and bind it to the ReportViewer control. First of all, is this possible? If so, what is the best way to render the report (format: XML,CSV, NULL, etc), and then how to I 'bind' the result to the control?

I'm proving some code to demonstrate how I'm rendering the report:
...
string format = "XML";
string devInfo = @."<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
ReportExecutionService rs = new ReportExecutionService();
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
byte[] result= null;

rs.ExecutionHeaderValue = execHeader;
execInfo = rs.LoadReport(path, null);
string SessionId = rs.ExecutionHeaderValue.ExecutionID;
result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

Thanks, any help would be greatly appreciated.
Peter*bump*

Just trying to get a feel for what everyone else is using this feature/method for?

Thanks,
P

Binding last row in table to a label

Hi all. I have a label on my page and I want to bind it to a field in a table. The catch is that I want to bind it to the last row in the table. I think I can use the custom binding, but I don't know how to bind to the last row. Any Suggestions ?

p.s. The page is tied to an SqlDataSource that retrieves the data from the above table.

Thanks in advance.

Could you not change the datasource to only return the last row? Or did you want all the other rows as well?

What about

Label1.text = dt.Rows(dt.Rows.Count-1)("FieldName").ToString()

where dt is the datatable containing the data you are binding to.

|||

Thanks for the reply GavDraper,

but what is the type of dt, how can I use a sql server table in my code ? and what is the meaning of "FieldName" ?

|||

the type of dt is datatable. You would need to replace fieldname with the name of the field you want to pull data from. below is a very rough quick example although I'm unable to test this as im not at my development machine, you would also want to include some error handling

dim dt as new datatable()dim sqlCon as new sqlConnection(strCon)dim sqlAdp as new SqlDataAdapter()sqlAdp.SelectCommand.Connection = sqlCon()

sqlAdp.Selectcommand.CommandText ("SELECT * FROM tablename")sqlAdp.fill(dt)lbl1.Text = dt.Rows(dt.Rows.Count-1)("FieldName")

|||

I'll give it a try,

but is it possible to do it without the code behind ?

I have the label and I use databinding to bind the fields in the table using the sqldatasource, but somehow it gives me the first row in the table.

I use a stored procedure to select the data from the table and use parameters in that stored procedure. So is it possible to bind to these parameters declaratively so they would give me the fields in the last row ?

The stored procedure:

CREATE PROCEDURE dbo.createPage@.PageTitlenvarchar(300)OUTPUT, @.PageMetaDescnvarchar(300)OUTPUT,@.PageMetaWordsnvarchar(300)OUTPUT,@.PageDescnvarchar(300)OUTPUT,@.PageTemplateint OUTPUTASDECLARE @.pIdAS intSELECT @.pId =cast(SCOPE_IDENTITY()AS int)--selecting the id of the last row inserted--selecting the data from that rowSELECT PageTitle,PageMetaDesc,PageMetaWords,PageDesc,PageHTML, PageTemplate, PageSummaryHTMLFROM PagesWHERE PageId = @.pIdGO

|||

that stored procedure is returning the data in no particular order how can you be sure its always the last record you want? You could change the stored procedure to only return 1 record by using the TOP keyword but to make this accurate the data would have to be ordered to guarentee you will always get the correct row.

Binding Datatable to Report

Hi,

I have created a datatable which is filled by the result of stored procedure. So I need to know like how to bind this datatable with the report using report viewer.

Apoorva

First you have to make sure you have a dataset. Also that the data set is connecting to this table.

If this is your first time creating a report I would suggest finding a walk through on a simple report creation

Here is a pretty good wak through

http://www.codeproject.com/dotnet/HowToReport.asp

good luck

Binding dataset to a reportviewer control

Hi,

I need to bind my custom dataset, which i retrieve by executing some query to a reportviewer control. I don't want to create a typed dataset in the application. Is there any way to do it? The dataset that i bind will be built in a seperate class. If I plan to use a Object Data source, what are the steps to follow. Pls help.

thanks,

Saravanan

www.gotreportviewer.com provides information about how to use object data sources specifically at: http://www.gotreportviewer.com/objectdatasources/index.html

-- Robert

|||

Hi Robert,

Thanks for the reply. I already read through that article. What i really need is to bind a dataset ( which i create in my application) to be bound to the report viewer. The dataset will have some columns from the database ( thro a select query), some columns may be added by me. I want to bind this custom dataset to the report viewer control (local mode processing). Please let me know if this can be done and also some steps to do it.

Thanks in advance,

Saravanan.

Binding data to text box

I want to bind some data to a text box from sql server db. but when i run the page i get an error. here is my code.

<formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacsConn %>"SelectCommand="SELECT Reportnumber FROM [SummaryBlue] WHERE REPORTNUMBER = @.REPORTNUMBER"><SelectParameters><asp:QueryStringParameterName="REPORTNUMBER"QueryStringField="REPORTNo"Type="String"/>

</SelectParameters></asp:SqlDataSource><asp:TextBoxID="TextBox1"runat="server"Columns="<%$ ConnectionStrings:imacsConn %>"></asp:TextBox></div></form>

Error:

Exception Details:System.FormatException: Input string was not in a correct format.

Source Error:

Line 25: </SelectParameters>Line 26: </asp:SqlDataSource>Line 27: <asp:TextBox ID="TextBox1" runat="server" Columns="<%$ ConnectionStrings:imacsConn %>"></asp:TextBox></div>Line 28: </form>

The Columns property of a text box is how many characters wide it should be (an integer property). You are trying to assign it a string.

<asp:TextBoxID="TextBox1"runat="server"Columns="<%$ ConnectionStrings:imacsConn %>">

|||

Hi xbhavesh,

From your description, I understand you want to bind data to single TextBox. Base on my experience and research, SqlDataSource can be binded to List data-bound controls, but TextBox is not belong to them. So I think we cannot simply bind data to TextBox from SqlDataSource.

I find two solutions and hope they are helpful to you.

1. Bind the DataSource to a GridView, and then bind the GridView cell value to TextBox. Don't forget set GridView to not visible and call DataBind method when Page Load. Here is the sample code:

<asp:TextBoxID="TextBox1"runat="server"Text='<% #GridView1.Rows[0].Cells[0].Text%>'></asp:TextBox>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:testConnectionString%>"

SelectCommand="SELECT [id] FROM [Table1] WHERE ([id] = 2)">

</asp:SqlDataSource>

<asp:GridViewID="GridView1"Visible="false"runat="server"DataSourceID="SqlDataSource1">

</asp:GridView>

protectedvoid Page_Load(object sender,EventArgs e)

{

TextBox1.DataBind();

}

2. Create your custom control to inherit DataBoundControl:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.databoundcontrol.aspx

P.S. We should bind to the Text property, not Columns.

Binding a DataSource Table column to a form object (RadioButtons)

Hi,

I have a little question. I have an application which interfaces with a SQL Express Database. On a form, I want to bind a control which is made of several Radio buttons to a table column which is in fact a varchar(1). This is the picture:

Table column: OptVisualRpt varchar(1)

Screen control: 2 radio buttons

rb_VisRPTbImp_O for "yes"

rb_VisRPTbImp_N for "no"

I'm really scratching my head as how I can bind a single table column to my radio buttons controls. I think that I just can't this way but rather have to use an intermediate variable or control.

Solution 1?

I thought of using a local variable that I would set to "y" or "n" following "CheckedChanged" events fired from radio buttons but I don't know how to bind this variable to my table column.

Solution 2?

I thought of placing a hidden text control into my form, which one would be binded to my table column, that I would also set to "y" or "n" following "CheckedChanged" events fired from radio buttons.

Any of these solutions which would be feasible or any more neat way to do it?

Many thanks in advance,

Stphane

Hi again,

Finally sounds that last night I could solve my problem by myself. Let me explain.

1- I first got rid of the "Binding source". I removed following lines:

=> private: System::Windows::Forms::BindingSource ^ InfoBaseBindingSource;

=> this->InfoBaseBindingSource = (gcnew System::Windows::Forms::BindingSource(this->components));

=> (cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->InfoBaseBindingSource))->BeginInit();

=> //
// InfoBaseBindingSource
//

this->InfoBaseBindingSource->DataMember = L"InfoBase";
this->InfoBaseBindingSource->DataSource = this->Test_ADODataSet;

=> (cli::safe_cast<System::ComponentModel::ISupportInitialize^ >(this->InfoBaseBindingSource))->EndInit();

2- At the same time, for every field control that were fed by it, I removed from code the following line (here is an example):

this->ID_Compagnie->DataBindings->Add((gcnew System::Windows::Forms::Binding(L"Text", this->InfoBaseBindingSource, L"Identification", true)));

3- I then changed the Form1_Load routine for the following:

this->InfoBaseTableAdapter->Fill(this->Test_ADODataSet->InfoBase);

DataTable ^ Dt_Infobase = this->Test_ADODataSet->Tables["InfoBase"];
if(Dt_Infobase != nullptr)
{
array<DataRow^> ^ Rw_InfoBase = Dt_Infobase->Select();
if(Rw_InfoBase->Length != 0)
{
this->ID_Compagnie->Text = Rw_InfoBase[0]["Identification"]->ToString();
this->Adr_Compagnie->Text = Rw_InfoBase[0]["Adresse"]->ToString();
...

==> Example of loading a masked text box

// Affichage et rectification du format du Code Postal/ZipCode

if(String::Compare(this->Pays_Compagnie->Text,"Canada") == 0)
{
this->CPZip_Compagnie->Mask = ">L0>L 0>L0";
this->Pnl_VSZCode->Hide();
}
else
{
this->Pnl_VSZCode->Show();
if(this->CPZip_Compagnie->Text->Length > 5)
{
this->VScrl_ZCode->Value = 1;
this->CPZip_Compagnie->Mask = "99999";
}
else
{
this->VScrl_ZCode->Value = 0;
this->CPZip_Compagnie->Mask = "99999-9999";
}
}

this->CPZip_Compagnie->Text = Rw_InfoBase[0]["CodePostal_Zip"]->ToString();

...

==> Example of "loading" info vs a set of radio buttons

String ^ Logo_ModeAff = Rw_InfoBase[0]["LogoRpt_ModeAff"]->ToString();
if(Logo_ModeAff == "C")
{
this->rb_ModeAffLCoord->Checked = true;
this->rb_ModeAffLOnly->Checked = false;
}
else
{
this->rb_ModeAffLCoord->Checked = false;
this->rb_ModeAffLOnly->Checked = true;
}
}

4- I then changed the B_Accept_Click routine for the following:

I removed following sentences:

this->InfoBaseBindingSource->EndEdit();
this->InfoBaseTableAdapter->Update(this->Test_ADODataSet->InfoBase);
this->Test_ADODataSet->AcceptChanges();

And replaced them with following:

DataTable ^ Dt_Infobase = this->Test_ADODataSet->Tables["InfoBase"];
if(Dt_Infobase != nullptr)
{
array<DataRow^> ^ Rw_InfoBase = Dt_Infobase->Select();
if(Rw_InfoBase->Length == 0)
{
DataRow ^ NRw_InfoBase = Dt_Infobase->NewRow();

NRw_InfoBase["Identification"] = this->ID_Compagnie->Text;
...
==> Example of a "saving" info vs a set of radio buttons

if(this->rb_ModeAffLCoord->Checked == true)
NRw_InfoBase["LogoRpt_ModeAff"] = "C";
else
NRw_InfoBase["LogoRpt_ModeAff"] = "L";
...

Dt_Infobase->Rows->Add(NRw_InfoBase);
}
else
{
Rw_InfoBase[0]["Identification"] = this->ID_Compagnie->Text;
...
==> Example of a "replacing" info vs a set of radio buttons

if(this->rb_ModeAffLCoord->Checked == true)
Rw_InfoBase[0]["LogoRpt_ModeAff"] = "C";
else
Rw_InfoBase[0]["LogoRpt_ModeAff"] = "L";
...
}

this->InfoBaseTableAdapter->Update(this->Test_ADODataSet);
this->Test_ADODataSet->AcceptChanges();
}

5- I finally changed the B_Cancel_Click routine for the following:

I removed following sentences:

this->InfoBaseBindingSource->EndEdit();

This code is maybe not the best way to do it. If someone has any more proper way to do it, I would certainly appreciate to learn. My conclusion is that doing this binding process manually gives better results than relying on a BindingSource object.

Stphane

binding a database records to a dropdownlist

Hey I just wanted to know if anyone could tell me how to bind values from a database to a dropdownlist?

DropDownlist.DataSource = dataset; // dataset returned from the database

DropDownlist.DataBind();

|||

Hi here is a detailed code fromdata bind a DropDownList declaratively to a data source control:

<html><head> <script language="VB" runat="server"> Sub SubmitBtn_Click(sender As Object, e As EventArgs) Label1.Text = "You chose: " + DropDown1.SelectedValue End Sub </script></head><body> <h3><font face="Verdana">DataBinding DropDownList</font></h3> <form runat=server> <asp:DropDownList id="DropDown1" DataSourceID="SqlDataSource1" DataTextField="au_lname" DataValueField="au_id" runat="server" /> <asp:SqlDataSource id="SqlDataSource1" ConnectionString='<%$ ConnectionStrings:Pubs%>' SelectCommand="select DISTINCT au_id, au_lname from authors" runat="server"/> <asp:button Text="Submit" OnClick="SubmitBtn_Click" runat=server/> <p> <asp:Label id=Label1 Font-Names="Verdana" font-size="10pt" runat="server" /> </form></body></html>

Hope it helps.

bind variables / parameter queries

Hi,

I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.

Does anyone know how I can do this in SQL Server?

For instance, I have 20,000 employees and I'm pulling info by SS#:

Select * from EmpTable where SS_number = [SSN]

Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?

Thanks.write a stored procedure, and instead of calling the database engine 20,000 times, just call it once and pass it a list of 20,000 numbers

come to think of it, where would you get 20,000 numbers? sounds like you might want to look for a JOIN solution|||SQL Server actually goes you one better, in that its ODBC drivers will automagically parameterize a query for you (unless you get really creative in modifying the query).

As Rudy pointed out though, if you have more than 20 iterations from a given client, you really ought to be thinking about a JOIN based solution... Doing that kind of thing on that scale one row at a time is WAY too much work for me!

-PatP|||Thanks, guys. I'll get write access to the backend and write a stored proc.

I'll have to read up on how to pass values to the proc (I'm guessing it's like a function).

Thanks again.|||How about this?:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "DSN=PKRebate2001", "sa", ""
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "sp_UpdateCustomerUnique"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ImportMonth").Value = IM
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing|||If you are looking to get automatic parameterization, that VBA code is conceptually good.

-PatP

Bind Variable in CURSOR

SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.

I have a stored procedure which is not working the way I think it
should be.

I have a CURSOR which has a variable in the WHERE clause:

DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @.dbname
ORDER BY tablename

It won't return anything, even when I verify that @.dbname has a value
and if I run the query in Query Analyzer with the value, it returns
rows:

SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = 'Archive'
ORDER BY tablename

DB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-29
09:52:11.823
DtsAdtStdArchive_DataSourceType5175768822006-03-29
09:52:11.8702006-03-29 09:52:11.887
DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-29
09:52:12.103

I've taken out most of the guts for simplicity, but here's what I've
got:

--CREATE TABLE dbo.db_ind
--(
--db_ind_tkintIDENTITY,
-- id int NULL,
-- tablename sysname NOT NULL,
-- indid int NULL,
-- indexname sysname NOT NULL,
-- shcontig1dt datetime NULL,
-- defragdt datetime NULL,
-- shcontig2dt datetime NULL,
-- reindexdt datetime NULL
--)

ALTER PROCEDURE IDR
(@.hours int
)
AS

--SET NOCOUNT ON
--SET ANSI_WARNINGS OFF

DECLARE @.tabname varchar(100),
@.indname varchar(100),
@.dbname varchar(50),
@.vsql varchar(1000),
@.v_hours varchar(4),
@.shcontig1dtdatetime,
@.shcontig2dtdatetime,
@.defragdtdatetime,
@.reindexdtdatetime,
@.idint,
@.indidint,
@.rundbcursorint,
@.runtabcursorint,
@.runindcursorint

DECLARE get_dbs CURSOR local fast_forward FOR
SELECT dbname
FROM db_jobs
WHERE idrdate < getdate() - 4
or idrdate is null
ORDER BY dbname

DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @.dbname
ORDER BY tablename

DECLARE get_inds CURSOR local fast_forward FOR
SELECT indid, indexname, defragdt, reindexdt
FROM db_ind
WHERE dbname = @.dbname
AND tablename = @.tabname
ORDER BY indexname

OPEN get_dbs
FETCH NEXT FROM get_dbs
INTO @.dbname

IF @.@.FETCH_STATUS = 0
SELECT @.rundbcursor = 1
ELSE
SELECT @.rundbcursor = 0

SELECT @.v_hours = CONVERT(varchar,@.hours)

--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====

WHILE @.rundbcursor = 1
BEGIN -- db while

PRINT '============================='
PRINT @.dbname
PRINT '============================='

OPEN get_tabs

FETCH NEXT FROM get_tabs
INTO @.tabname, @.id, @.shcontig1dt, @.shcontig2dt

IF @.@.FETCH_STATUS = 0
BEGIN
PRINT 'table: ' + @.tabname
SELECT @.runtabcursor = 1
end
ELSE
BEGIN
PRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITS
SELECT @.runtabcursor = 0
end

WHILE @.runtabcursor = 1
BEGIN
PRINT @.dbname
PRINT @.tabname

--================================================== ================================================== =====

OPEN get_inds
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

WHILE @.runindcursor = 1
BEGIN
PRINT 'Index:' + @.dbname + '.' + @.tabname + '.' + @.indname

FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

END-- 1st loop through indexes
CLOSE get_inds

--================================================== ================================================== =====

--==========
PRINT 'db.tab: ' + @.dbname + '.' + @.tabname

--==========

--================================================== ================================================== =====

OPEN get_inds
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

WHILE @.runindcursor = 1
BEGIN

PRINT 'dbname: ' + @.dbname
PRINT 'tabname: ' + @.tabname
PRINT 'indname: ' + @.indname

FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

END -- 2nd loop through indexes
CLOSE get_inds

--================================================== ================================================== =====

FETCH NEXT FROM get_tabs
INTO @.tabname, @.id, @.shcontig1dt, @.shcontig2dt

IF @.@.FETCH_STATUS = 0
SELECT @.runtabcursor = 1
ELSE
SELECT @.runtabcursor = 0

END-- loop through tables
CLOSE get_tabs

PRINT 'Index Maintenence complete. Job report in
[DB_Rpt_Fragmentation]'
PRINT ''

FETCH NEXT FROM get_dbs
INTO @.dbname

IF @.@.FETCH_STATUS = 0
SELECT @.rundbcursor = 1
ELSE
SELECT @.rundbcursor = 0

END -- loop through databases
CLOSE get_dbs
deallocate get_dbs
deallocate get_tabs
deallocate get_inds

And this is what I'm getting:

=============================
Archive
=============================

(0 row(s) affected)

not getting any tables!
Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]

..
..
..
etc.

Am I missing something obvious?

Thank you for any help you can provide!!One of my fellow emps got it - apparently the CURSOR needed to be
declare w/in the loop right before I opened it.

I moved the get_tabs and get_inds cursor declarations and all is well .
.. .sql

Bind to multiple tables from stored procedure

I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?

Isthis article of any use?|||no fortunately not :(

bind the asp:SqlDataSource to a label control

how can i display the result of an asp:SqlDataSource into a lable control.
the sqldatasource returns the count for some thing ie "select count(*) as total from tbl"

please help

There are two general approaches you can use:
1. Use a data bound control, such as a FormView or DetailsView. Bind the data bound control to the SqlDataSource, and then place the Label inside that control and databind it as you would any other control in a template.
2. Manually call SqlDataSource's Select() method and extract the result value:
DataSet result = (DataSet)SqlDataSource.Select(DataSourceSelectArguments.Empty);
Label1.Text = result.Tables[0].Rows[0][0].ToString();
Thanks,
Eilon

Bind SQL reporting services (Reports) to ASP.NET (Urgent)

Hi all,

Currently I have a project that require me to use reporting services (Reports) together with ASP.net web application.

However, I have the following problem.

I have an asp.net web application. From there, I have a textbox whereby user can type a specific date. The specific date should then be incorporate with the sql reporting services. The report that is being generated will contain data that is on the specific date.

How can I do that?

I try to search through the internet but to no result. Can anyone help out?

Thank you so much.Read a book on Reporting Services ... you can pass everything to Reporting Services through the webservices it exposes ...
Microsoft has some good books about Reporting Services ... Search for William Vaugh ... He's very good

Bind object to RDLC Report

Hi,all
I hava a question about Bind object to RDLC Report,the RDLC like
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DummyDataSource">
<ConnectionProperties>
<ConnectString />
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>47739726-cc8c-4719-b061-c392c2cceb68</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>WindowsApplication1_MedcialCase</DataSetName>
<Top>0.25cm</Top>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ID">
<rd:DefaultName>ID</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Name">
<rd:DefaultName>Name</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PatientOfMedcialCase.Value.Name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Name_1">
<rd:DefaultName>Name_1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="PatientOfMedcialCase">
<rd:DefaultName>PatientOfMedcialCase</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PatientOfMedcialCase.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Patient Of Medcial Case</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>8cm</Width>
</TableColumn>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
</TableColumns>
<Height>1.90476cm</Height>
</Table>
</ReportItems>
<Height>5cm</Height>
</Body>
<rd:ReportID>a69b2d8e-258a-4212-8d4a-c67c96055732</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="WindowsApplication1_MedcialCase">
<rd:DataSetInfo>
<rd:DataSetName>WindowsApplication1</rd:DataSetName>
<rd:ObjectDataSourceType>WindowsApplication1.MedcialCase, WindowsApplication1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null</rd:ObjectDataSourceType>
<rd:TableName>MedcialCase</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText />
<DataSourceName>DummyDataSource</DataSourceName>
</Query>
<Fields>
<Field Name="ID">
<rd:TypeName>System.Guid</rd:TypeName>
<DataField>ID</DataField>
</Field>
<Field Name="PatientOfMedcialCase">
<rd:TypeName>WindowsApplication1.Patient</rd:TypeName>
<DataField>PatientOfMedcialCase</DataField>
</Field>
<Field Name="Name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Name</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>24cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

the object I bind to the Report like

public class MedcialCase
{
private Guid iD;

public Guid ID
{
get { return iD; }
set { iD = value; }
}

private Patient patient;

public Patient PatientOfMedcialCase
{
get { return patient; }
set { patient = value; }
}

}

public class Patient
{
private string name;

public string Name
{
get { return name; }
set { name = value; }
}

}

and the code bind object to report like

MedcialCase medcialCase = new MedcialCase();
medcialCase.ID = Guid.NewGuid();
Patient patient = new Patient();
patient.Name = "Michael";
medcialCase.PatientOfMedcialCase = patient;
this.MedcialCaseBindingSource.DataSource = medcialCase;
this.reportViewer1.RefreshReport();

I choose the MedcialCase.ID and MedcialCase.PatientOfMedcialCase.Name to show, but when the report was run ,,just MedcialCase.ID has been shown,
the text of MedcialCase.PatientOfMedcialCase.Name is "=Fields!Name.Value"

I modify it to "=Fields!PatientOfMedcialCase.Value.Name.Value" and run it ,the "#Error" show in the text of Name

anyone can tell me why?and how to bind object like MedcialCase to the Report?

thanks!

This is snippet from

http://www.gotreportviewer.com/objectdatasources/index.html

Nested objects

Only the first level of public properties can be dragged and dropped to a report. If the type of a property is a custom class with its own properties then you can manually enter an expression to access the values of second level properties. For example if the Employee class has a HomeAddress property of type Address then you can enter the expression =Fields!HomeAddress.Value.Zip to access the value of Zip property.

If the value of a property is a collection then you can use a subreport to display the collection. See this example.

In your case, this translates to "=Fields!PatientOfMedcialCase.Value.Patient.Name"

Hope that helps,

Tudor Trufinescu

|||

hi,Tudor

Thanks for your advice,

when I try to do follow your advice,I Modify the class like:

public class Book
{
private string m_title;
private string m_publisher;
private float m_listPrice;

public Book(string title, string publisher, float listPrice)
{
m_title = title;
m_publisher = publisher;
m_listPrice = listPrice;
}

public string Title
{
get { return m_title; }
set { m_title = value; }
}

public string Publisher
{
get { return m_publisher; }
set { m_publisher = value; }
}

public float ListPrice
{
get { return m_listPrice; }
set { m_listPrice = value; }
}
}

class Author
{
private int m_id;
private string m_name;
private Book book;

public Author(int id)
{
m_id = id;
}

public Author(string name, int id, Book books)
{
m_name = name;
m_id = id;
book = books;
}

public int Id
{
get { return m_id; }
set { m_id = value; }
}

public string Name
{
get { return m_name; }
set { m_name = value; }
}

public Book Books
{
get { return this.book; }
set { this.book = value; }
}
}

and report text like "=Fields!Books.Value.ListPrice" as you say,

when the report run ,the text still display the "#error"

How shall I do?

|||

I have changed the object data source sample to illustrate the use of nested objects

See this post for more details.

http://blogs.msdn.com/tudortr/archive/2006/04/10/NestedObjectsDataSource.aspx

Thanks,

Tudor

|||that's helpful,i have solved the problem,thanks!|||Just wondering why I get the following error when I use GenericsCollection:

"The Value expression for the textbox ‘AddressLine1’ refers to the field ‘Contacts’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. "

for example I have these attributes of a class Company:

public Address PrivateAddress
{
get { return _privateAddress; }
set
{
_privateAddress = value;
}
}

public GenericCollection<Address> Contacts
{
get { return _contacts; }
set
{
_contacts = value;
}
}

I have no problems using the PrivateAddress object, but the Contacts gives me the above error message.

Has anyone implemented Generics objects? Any help would be appreciated.
Thanks

Bind object to RDLC Report

the object I bind to the Report like

public class MedcialCase
{
private Guid iD;

public Guid ID
{
get { return iD; }
set { iD = value; }
}

private Patient patient;

public Patient PatientOfMedcialCase
{
get { return patient; }
set { patient = value; }
}

}

public class Patient
{
private string name;

public string Name
{
get { return name; }
set { name = value; }
}

}

and the code bind object to report like

I modify it to "=Fields!PatientOfMedcialCase.Value.Name.Value" and run it ,the "#Error" show in the text of Name

anyone can tell me why?and how to bind object like MedcialCase to the Report?

thanks!

This is snippet from

http://www.gotreportviewer.com/objectdatasources/index.html

Nested objects

If the value of a property is a collection then you can use a subreport to display the collection. See this example.

In your case, this translates to "=Fields!PatientOfMedcialCase.Value.Patient.Name"

Hope that helps,

Tudor Trufinescu

|||

hi,Tudor

Thanks for your advice,

when I try to do follow your advice,I Modify the class like:

public class Book
{
private string m_title;
private string m_publisher;
private float m_listPrice;

public Book(string title, string publisher, float listPrice)
{
m_title = title;
m_publisher = publisher;
m_listPrice = listPrice;
}

public string Title
{
get { return m_title; }
set { m_title = value; }
}

public string Publisher
{
get { return m_publisher; }
set { m_publisher = value; }
}

public float ListPrice
{
get { return m_listPrice; }
set { m_listPrice = value; }
}
}

class Author
{
private int m_id;
private string m_name;
private Book book;

public Author(int id)
{
m_id = id;
}

public Author(string name, int id, Book books)
{
m_name = name;
m_id = id;
book = books;
}

public int Id
{
get { return m_id; }
set { m_id = value; }
}

public string Name
{
get { return m_name; }
set { m_name = value; }
}

public Book Books
{
get { return this.book; }
set { this.book = value; }
}
}

and report text like "=Fields!Books.Value.ListPrice" as you say,

when the report run ,the text still display the "#error"

How shall I do?

|||

I have changed the object data source sample to illustrate the use of nested objects

See this post for more details.

http://blogs.msdn.com/tudortr/archive/2006/04/10/NestedObjectsDataSource.aspx

Thanks,

Tudor

|||that's helpful,i have solved the problem,thanks!|||Just wondering why I get the following error when I use GenericsCollection:

for example I have these attributes of a class Company:

public Address PrivateAddress
{
get { return _privateAddress; }
set
{
_privateAddress = value;
}
}

public GenericCollection<Address> Contacts
{
get { return _contacts; }
set
{
_contacts = value;
}
}

I have no problems using the PrivateAddress object, but the Contacts gives me the above error message.

Has anyone implemented Generics objects? Any help would be appreciated.
Thanks
sql

Bind my own DataSet that I get in my WebApp and show in the Reporting Services - is it pos

All,
Does anybody know if I can bind my own DataSet dynamically getting it in my
WebApp and show it in the Reporting Services using the services as a
formatter/viewer of this DataSet - is it possible?
Or all I can do with the RS 2000 is just a new VD for each new report and I
have to use this URL to show anything from my app? I didn't see anything
helpful allowing us to integrate the Reporting Services into WebApp yet on
the fly. What's the difference if the RS generates the DataSet or it's
generated by my application? The second way is much more flexible and
practical. Is it already implemented in the RS 2005? I saw the controls, but
didn't test them yet since my solution is not convertible to the VS2005 with
the new Reporting Services.
Just D.VS 2005 have two new controls. Webform and winform. You can use them in
local mode and give them the dataset. In local mode you do have more you
have to do (subforms etc).
In 2000 you would have to create a data processing extension (which would
work for either 2000 or 2005).
RS is designed as a service oriented application. You request a service and
RS implements it. You can call stored procedures easily or use SQL
statement. Pass in the parameters from your app and let RS create the
dataset.
Any particular reason you want to be passing a dataset. If you let RS handle
it then it will automatically do a lot for you. For instance, take the issue
of subreports. If you are handling the dataset then for each subreport you
need to respond to an event and generate the datasets for the subreport as
well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Just D." <no@.spam.please> wrote in message
news:Kk5If.13830$eR.455@.fed1read03...
> All,
> Does anybody know if I can bind my own DataSet dynamically getting it in
> my WebApp and show it in the Reporting Services using the services as a
> formatter/viewer of this DataSet - is it possible?
> Or all I can do with the RS 2000 is just a new VD for each new report and
> I have to use this URL to show anything from my app? I didn't see anything
> helpful allowing us to integrate the Reporting Services into WebApp yet on
> the fly. What's the difference if the RS generates the DataSet or it's
> generated by my application? The second way is much more flexible and
> practical. Is it already implemented in the RS 2005? I saw the controls,
> but didn't test them yet since my solution is not convertible to the
> VS2005 with the new Reporting Services.
> Just D.
>|||I've got a case where I need to bind to a dataset that I fetch. In my
case, I need to get a stored definition of a user-customized query, and
generate RDL from that definition. I then need to fill a weakly-typed
dataset to be used as the data source for the generated report.
Any reference on how to use pre-filled datasets in SRSS? I've been
searching Google and MSDN, and have so far come up short.
Thanks in advance.
Bruce L-C [MVP] wrote:
> VS 2005 have two new controls. Webform and winform. You can use them in
> local mode and give them the dataset. In local mode you do have more you
> have to do (subforms etc).
> In 2000 you would have to create a data processing extension (which would
> work for either 2000 or 2005).
> RS is designed as a service oriented application. You request a service and
> RS implements it. You can call stored procedures easily or use SQL
> statement. Pass in the parameters from your app and let RS create the
> dataset.
> Any particular reason you want to be passing a dataset. If you let RS handle
> it then it will automatically do a lot for you. For instance, take the issue
> of subreports. If you are handling the dataset then for each subreport you
> need to respond to an event and generate the datasets for the subreport as
> well.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Just D." <no@.spam.please> wrote in message
> news:Kk5If.13830$eR.455@.fed1read03...
> > All,
> >
> > Does anybody know if I can bind my own DataSet dynamically getting it in
> > my WebApp and show it in the Reporting Services using the services as a
> > formatter/viewer of this DataSet - is it possible?
> >
> > Or all I can do with the RS 2000 is just a new VD for each new report and
> > I have to use this URL to show anything from my app? I didn't see anything
> > helpful allowing us to integrate the Reporting Services into WebApp yet on
> > the fly. What's the difference if the RS generates the DataSet or it's
> > generated by my application? The second way is much more flexible and
> > practical. Is it already implemented in the RS 2005? I saw the controls,
> > but didn't test them yet since my solution is not convertible to the
> > VS2005 with the new Reporting Services.
> >
> > Just D.
> >
> >|||If you are creating your own RDL then the way to go is to definitely get the
new controls with VS 2005. In local mode you don't need any server. You can
give the report the rdlc and the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kevin L." <kevin.lowe0@.gmail.com> wrote in message
news:1139862643.634896.4400@.o13g2000cwo.googlegroups.com...
> I've got a case where I need to bind to a dataset that I fetch. In my
> case, I need to get a stored definition of a user-customized query, and
> generate RDL from that definition. I then need to fill a weakly-typed
> dataset to be used as the data source for the generated report.
> Any reference on how to use pre-filled datasets in SRSS? I've been
> searching Google and MSDN, and have so far come up short.
> Thanks in advance.
>
> Bruce L-C [MVP] wrote:
>> VS 2005 have two new controls. Webform and winform. You can use them in
>> local mode and give them the dataset. In local mode you do have more you
>> have to do (subforms etc).
>> In 2000 you would have to create a data processing extension (which would
>> work for either 2000 or 2005).
>> RS is designed as a service oriented application. You request a service
>> and
>> RS implements it. You can call stored procedures easily or use SQL
>> statement. Pass in the parameters from your app and let RS create the
>> dataset.
>> Any particular reason you want to be passing a dataset. If you let RS
>> handle
>> it then it will automatically do a lot for you. For instance, take the
>> issue
>> of subreports. If you are handling the dataset then for each subreport
>> you
>> need to respond to an event and generate the datasets for the subreport
>> as
>> well.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Just D." <no@.spam.please> wrote in message
>> news:Kk5If.13830$eR.455@.fed1read03...
>> > All,
>> >
>> > Does anybody know if I can bind my own DataSet dynamically getting it
>> > in
>> > my WebApp and show it in the Reporting Services using the services as a
>> > formatter/viewer of this DataSet - is it possible?
>> >
>> > Or all I can do with the RS 2000 is just a new VD for each new report
>> > and
>> > I have to use this URL to show anything from my app? I didn't see
>> > anything
>> > helpful allowing us to integrate the Reporting Services into WebApp yet
>> > on
>> > the fly. What's the difference if the RS generates the DataSet or it's
>> > generated by my application? The second way is much more flexible and
>> > practical. Is it already implemented in the RS 2005? I saw the
>> > controls,
>> > but didn't test them yet since my solution is not convertible to the
>> > VS2005 with the new Reporting Services.
>> >
>> > Just D.
>> >
>> >
>

Bind multi-table dataset to a datagrid

I have created a SQL procedure that returns a dataset with a varying number of tables like the following example:

RepID-- PhoneUPS
---- ----
3---- 3

RepID-- PhoneUPS
---- ----
4---- 0

RepID-- PhoneUPS
---- ----
5---- 2

No more results.
(9 row(s) returned)
@.RETURN_VALUE = 0

All of the tables have the same header row, but are a seperate table. From my experience, I am not able to bind more than one table to a datagrid. Does anyone have any suggestions on how I can go about displaying this full dataset on my aspx page? I've been going in circles on this for two days, so I'm open to any suggestions :)

Cheers,
AndrewCan you create a UNION query in the stored procedure, rather then seperate resultsets?

Failing that, you can manually add each row of each resultset to the Items collection of the Datagrid.|||Thanks for the tip. I've been working on my SQL procedure trying to incorporate the UNION in the SELECT statement. I am having problems with the logic of working it in, though. Here is the original SQL procedure I've created:

/*BEGIN Procedure */

ALTER PROCEDURE Rep_Status_Array

/* Build array or ID numbers to cycle through */
DECLARE RepIDs_cursor CURSOR
FOR SELECT UserID
FROM TBL_Users
WHERE (TBL_Users.DepartmentID > 0)

OPEN RepIDs_cursor
DECLARE @.userID int
FETCH NEXT FROM RepIDs_cursor INTO @.userID

/* Begin WHILE loop to collect data for each ID */
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT
RepID=@.userID,

PhoneUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 11) AND (SalesmanID = @.userID)),

LOTUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),

CVR=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),

FETCH NEXT FROM RepIDs_cursor INTO @.userID
END
/* END WHILE loop */

CLOSE RepIDs_cursor
DEALLOCATE RepIDs_cursor

/* END Procedure */

The problem I'm having with this is that each time through the WHILE loop creates a new table in the dataset that is returned by the procedure. Any suggestions?|||Is it even possible to use UNION or UNION ALL within a WHILE loop?|||To the best of my knowledge, it is not possible to use the UNION statement in conjunction with a WHILE loop.

I'm going to go with Douglas' second recommendation and manually create a table within my page, adding each row one at a time.|||Another alternative:

Create a temporary table, and then INSERT all selected rows into the temp table, and then at the end of the SP,

SELECT * FROM #temp

(with any required ORDER BY).|||Thanks for the help, Douglas, much appreciated!

Andrew

Bind message to a constraint check

I'm doing a migration form Sybase to Microsoft Sql Server 2005. On my sybase
datbase, I have some check constrainst. Thos contraints are linked (sybase
procedure sp_bindmsg) with specific messages in case that those constraint
are not followed.
Idon't know and don't find something equivalent on Sql Server.
Thanks for your help!You cannot bind a user defined message to system errors (like of you want to add a row to a
referencing table if a value doesn't exists in the referenced table).
Consider requesting the feature to sqlwish@.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"valerie uzan" <valerieu@.srl.co.il> wrote in message
news:ed0ffe6febd048e98407c892029b9413@.ureader.com...
> I'm doing a migration form Sybase to Microsoft Sql Server 2005. On my sybase
> datbase, I have some check constrainst. Thos contraints are linked (sybase
> procedure sp_bindmsg) with specific messages in case that those constraint
> are not followed.
> Idon't know and don't find something equivalent on Sql Server.
> Thanks for your help!

Tuesday, March 27, 2012

Binding WebService as Datasource

binding textbox with SqlDataSource

binding textbox text to a database field

Binding Render output to ReportViewer control?

Binding last row in table to a label

Binding Datatable to Report

Binding dataset to a reportviewer control

Binding data to text box

Binding a DataSource Table column to a form object (RadioButtons)

binding a database records to a dropdownlist

bind variables / parameter queries

Bind Variable in CURSOR

Bind to multiple tables from stored procedure

bind the asp:SqlDataSource to a label control

Bind SQL reporting services (Reports) to ASP.NET (Urgent)

Bind object to RDLC Report

Nested objects

Bind object to RDLC Report

Nested objects

Bind my own DataSet that I get in my WebApp and show in the Reporting Services - is it pos

Bind multi-table dataset to a datagrid

Bind message to a constraint check

Best way to copy database

Blog Archive

About Me