How to control visibility of table columns at runtime?

Hi all..

Using ReportViewer in an ASPX page, is it possible to hide certain columns of a table at runtime?

That table is databound to a business object and I'm using local reporting capabilities of Visual Studio 2005.

Thanks

Jaime

I assume you are using a "table" reportitem. In report designer you can select the entire column of a table. In the properties window (F4), you should then see a "Visibility" property. You could set the Visibility.Hidden property to e.g. =Parameters!HideColumns.Value (assuming HideColumns is a boolean report parameter).

-- Robert

|||

Hi Robert..

I meant programmatically. How can I change Visibility property of the table using code. Suppose the table is named tblDatos and report viewer control is named rptBusqueda.

Jaime

How to control visibility of table columns at runtime?

Hi all..

Using ReportViewer in an ASPX page, is it possible to hide certain columns of a table at runtime?

That table is databound to a business object and I'm using local reporting capabilities of Visual Studio 2005.

Thanks

Jaime

I assume you are using a "table" reportitem. In report designer you can select the entire column of a table. In the properties window (F4), you should then see a "Visibility" property. You could set the Visibility.Hidden property to e.g. =Parameters!HideColumns.Value (assuming HideColumns is a boolean report parameter).

-- Robert

|||

Hi Robert..

I meant programmatically. How can I change Visibility property of the table using code. Suppose the table is named tblDatos and report viewer control is named rptBusqueda.

Jaime

How to control the loading of SqlDataSource?

I have an SqlDatSource that I have fully setup at design time, but I don't want it to open and load as soon as the page loads, instead I want to open it based on a condition.

How do you do that with a DataSource?

I know that I can simply remove the Select query, and then set it at run time, but I'm looking for something better, that allows me to have the Select query set at design time, in part because I have a lot of parameters.

Usually you have a control such as GridView or DetailsView that is connected to the SqlDataSource through its DataSourceId attribute. This will cause it to execute the query. If you leave the attribute empty the query will not be executed. From code you can set the DataSource and then call DataBind(). That will cause the SqlDataSource to execute:

GridView1.DataSource = SqlDataSource1;GridView1.DataBind();// Now the query is executed

|||

Within the SqlSataSource.Selecting event, you will have access to the Cancel property which you can set based on your conditions. If Cancel is set to True, then the Select event will not occur.

|||

while michielvoo's way is probably better, another way you can get the date to not display is to just set the GridView's Visible="false" initially, if you want the datasourceID property of the sqldatasource to be defined in the contol creation. Then, in your button_click event or whatever you do to get the data, you do:

VB:

GridView1.DataBind()
GridView1.Visible = True

C#:

GridView1.DataBind();
GridView1.Visible = True;

How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to Control Sheet Name of Exported Excel Report?

Is there a way to control the sheet name when a report is exported to Excel? I have four matrices where a page break is made between each of them. Once exported to Excel, the resulting file has "Sheet1", "Sheet2", "Sheet3" and "Sheet4" as the names of its worksheets. Would it be possible to change it to a more user-friendly name?I am looking for the sames answer. Has anyone found this information out yet?|||

This is not possible with RS 2000 or 2005 by itself. It's on our wishlist for a future release.

You can do this today with a third party tool SoftArtisans OfficeWriter. With OfficeWriter you can name sheets at design time:

http://officewriter.softartisans.com/officewriter-250.aspx

|||Chris -- Can the OfficeWriter name sheets in a current report when it is exported or would I have to re-create the report?|||

Here is something that is "almost as good"...... you can make Excel create a Document Map for each of your reports - whether they are tables or matrices. This example uses a table and 2 matrices, with both matrices on one sheet.

Put your table in a rectangle; in the rectangle properties enter text for the Label property. Put the matrices in a second rectangle and also enter the Label property. When you generate the report, a document map is created - when you render this to Excel, a Document Map is created on one sheet, and the reports are on 2 separate sheets. The sheets still have names 'Document Map', 'Sheet1', Sheet2', but at least the user can see from the document map what each sheet represents.

How to construct the query in Stored Proc

Hi,
In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:
Codes in ASPX.VB:
Dim SQL as String="SELECT * From Table1"
IF UserOption <> Null then
SQL = SQL & " WHERE Column1=" UserOption"
End if

Now, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.
But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).
Hope you can understanding what my mean and give me advices.
Regards,
Raymond

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

|||

I n SQL Server you can use the CASE statement, try the links below for sample code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Thanks a lot! I will study it. I also find this structure may help, which introducedd in another thread in this forum:

AND ([DD1 Industry Code] LIKE @.FilterIndustries OR @.FilterIndustries ='')

|||

scu wrote:

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

Will it affect the performance when the query is built up in a dynamic string rather than build up within stored procedure using CASE/IF..THEN..ELSE/COALESCE statements? It is important concern to the effectiveness when the store procedure contains complicated queries.|||Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.
|||

scu wrote:

Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.

Thanks for the kindly sharing from all of you!|||You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick|||

nick-w wrote:

You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick

Great article! Thanks Nick!

How to connect to sql server on another machine through asp.net

Hi All,

I have been struggling with this problem for quite some time. Basically
i have an asp page (on the ISP server) that I want to connect to a sql
server on another machine (on my server) and I have been trying to
figure out how to do this. I tried to connect using an ip address in
the data source and it did not work. The asp.net code looks something
like this:

Conn.Open ("Provider=SQLOLEDB;Password=xxxx;Persist Security
Info=True;User ID=xxxx;Initial Catalog=mssqlxxx;Data
Source=xx.xxx.xxx.xxx,1433;Network Library=dbmssocn")

That did not work. Gave me an access denied or sql server does not
exist error. I tried playing around with or without the 1433 port,
still no luck.

Is there something I need to set up on sql server in order for me to
connect to this?? Can someone give me some suggestion how i can connect
to another computer?

My ISP offers sql server support and they give me an ip address to
connect directly to the sql server, code looks something like this:

conMyData = New SqlConnection( "Server=xxxxxxxxx.registeredsite.com;
UID=userxxxxxx; PWD=xxxxxx;Database=samplecom" )

now, my question is, how do I set up my sql server to have an ip
address so i can connect??

so i see 2 possible w ays to do this but i have no clue to make this
work. Any help is appreciated.This article might be useful:

http://www.aspfaq.com/show.asp?id=2126

Simon

how to connect to MDF file with classic ASP?

Hi,
i have already posted this problem in an ASP newsgroup without succes, so i
try here.
I try to access within an cliassic ASP page an MDF file (newres.mdf) of sql
server express 2005 database which is located in
c:\inetpub\wwwroot\reskrosql\App_Data.
I get following error :
Provider error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done"
The code in ASP file:
--
<%
set objdc = Server.CreateObject("ADODB.Connection")
objdc.Open(application("newres")) 'error line
%>
global.asa:
--
Sub Application_OnStart
Application("newres") =
" Provider=SQLOLEDB;DATABASE=c:\inetpub\ww
wroot\reskrosql\App_Data\newres.mdf
;Data
Source=. \SQLEXPRESS;AttachDbFilename=c:\inetpub\
wwwroot\reskrosql\App_Data\n
ewres.mdf;Integrated
Security=True;User Instance=True"
'i also tried this:
Sub Application_OnStart
Application("newres") = "Provider=SQLOLEDB;DATABASE=newres;Data
Source=. \SQLEXPRESS;AttachDbFilename=c:\inetpub\
wwwroot\reskrosql\App_Data\n
ewres.mdf;Integrated
Security=True;User Instance=True"
End Sub
Thanks for help
BartYou might want to have a look at some of these:
http://msdn2.microsoft.com/en-us/library/ms345154.aspx Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn.microsoft.com/sql/defau... />
erinst.asp
User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/a.../05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/expre...qlexcustapp.asp
Embedding Express in Apps
http://www.microsoft.com/downloads/...&DisplayLang=en
SSMS Express
http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
Backing up Express
Andrew J. Kelly SQL MVP
"Bart" <b@.sdq.dc> wrote in message
news:%23%23w0O9RbHHA.3584@.TK2MSFTNGP02.phx.gbl...
> Hi,
> i have already posted this problem in an ASP newsgroup without succes, so
> i try here.
> I try to access within an cliassic ASP page an MDF file (newres.mdf) of
> sql server express 2005 database which is located in
> c:\inetpub\wwwroot\reskrosql\App_Data.
> I get following error :
> Provider error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done"
> The code in ASP file:
> --
> <%
> set objdc = Server.CreateObject("ADODB.Connection")
> objdc.Open(application("newres")) 'error line
> %>
> global.asa:
> --
> Sub Application_OnStart
> Application("newres") =
> " Provider=SQLOLEDB;DATABASE=c:\inetpub\ww
wroot\reskrosql\App_Data\newres.m
df;Data
> Source=. \SQLEXPRESS;AttachDbFilename=c:\inetpub\
wwwroot\reskrosql\App_Data
\newres.mdf;Integrated
> Security=True;User Instance=True"
>
> 'i also tried this:
> Sub Application_OnStart
> Application("newres") = "Provider=SQLOLEDB;DATABASE=newres;Data
> Source=. \SQLEXPRESS;AttachDbFilename=c:\inetpub\
wwwroot\reskrosql\App_Data
\newres.mdf;Integrated
> Security=True;User Instance=True"
> End Sub
> Thanks for help
> Bart
>
>|||Thanks Andrew,
to be honest, i read all of them but could not find an example of a
connectionstring for ASP using AttachDbFilename
and i'm getting fed up to read and search for 2 days something that is not
even so particular. I get the impression that it's the first time someone
try this ...
What i noticed is that when i change the connectionstring with a false path,
like:
"Provider=SQLOLEDB;Data
Source=. \SQLEXPRESS;AttachDbFilename=c:\WRONGPAT
H;Integrated
Security=True;User Instance=True"
i still get the same error. Maybe the problem is elsewhere|||Well to be honest I am not sure if classic ASP can access SQL Express or
not. I don't mess with express much but the first link did say there was
some issues with earlier versions of MDAC and connecting under certain
conditions. Look here under Data Access section:
http://msdn2.microsoft.com/en-us/library/ms345154.aspx
In the below link it goes into great detail about connecting using
AttachDBFileName but not necessarily with classic ASP.
http://msdn2.microsoft.com/en-us/library/bb264564.aspx
Have you tried this with .net and or the SNAC driver to see if you can get
it to work at all yet?
Andrew J. Kelly SQL MVP
"Bart" <b@.sdq.dc> wrote in message
news:epUZNiXbHHA.260@.TK2MSFTNGP02.phx.gbl...
> Thanks Andrew,
> to be honest, i read all of them but could not find an example of a
> connectionstring for ASP using AttachDbFilename
> and i'm getting fed up to read and search for 2 days something that is not
> even so particular. I get the impression that it's the first time someone
> try this ...
> What i noticed is that when i change the connectionstring with a false
> path, like:
> "Provider=SQLOLEDB;Data
> Source=. \SQLEXPRESS;AttachDbFilename=c:\WRONGPAT
H;Integrated
> Security=True;User Instance=True"
> i still get the same error. Maybe the problem is elsewhere
>
>|||AttachDBFilename should work from OLEDB or ODBC but the User instance clause
is only available from SQLClient - ADO.Net so it's not clear how much good
AttachDBFilename will do you.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bart" <b@.sdq.dc> wrote in message
news:epUZNiXbHHA.260@.TK2MSFTNGP02.phx.gbl...
> Thanks Andrew,
> to be honest, i read all of them but could not find an example of a
> connectionstring for ASP using AttachDbFilename
> and i'm getting fed up to read and search for 2 days something that is not
> even so particular. I get the impression that it's the first time someone
> try this ...
> What i noticed is that when i change the connectionstring with a false
> path, like:
> "Provider=SQLOLEDB;Data
> Source=. \SQLEXPRESS;AttachDbFilename=c:\WRONGPAT
H;Integrated
> Security=True;User Instance=True"
> i still get the same error. Maybe the problem is elsewhere
>
>|||Yes it works with asp.net.
Classic ASP works even when connecting a database in sqlexpress (not a mdf).
I use this stringconnection:
Sub Application_OnStart
Application("newresfull") = "Provider=SQLOLEDB;Data
Source=laptop\sqlexpress;Integrated Security=SSPI;Initial Catalog=test"
End Sub
I think it's simply impossible to access a mdf file with asp ...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:%23WmHJhYbHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Well to be honest I am not sure if classic ASP can access SQL Express or
> not. I don't mess with express much but the first link did say there was
> some issues with earlier versions of MDAC and connecting under certain
> conditions. Look here under Data Access section:
> http://msdn2.microsoft.com/en-us/library/ms345154.aspx
>
> In the below link it goes into great detail about connecting using
> AttachDBFileName but not necessarily with classic ASP.
> http://msdn2.microsoft.com/en-us/library/bb264564.aspx
> Have you tried this with .net and or the SNAC driver to see if you can get
> it to work at all yet?
> --
> Andrew J. Kelly SQL MVP
> "Bart" <b@.sdq.dc> wrote in message
> news:epUZNiXbHHA.260@.TK2MSFTNGP02.phx.gbl...
>|||Thanks, so the conclusion is:
it's impossible to access a MDF file with classic asp?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
news:EF1406A3-C657-44F9-8FF6-3F1D032175E1@.microsoft.com...
> AttachDBFilename should work from OLEDB or ODBC but the User instance
> clause is only available from SQLClient - ADO.Net so it's not clear how
> much good AttachDBFilename will do you.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bart" <b@.sdq.dc> wrote in message
> news:epUZNiXbHHA.260@.TK2MSFTNGP02.phx.gbl...
>|||Depends on how you define impossible. You can't access it using the
connection string you are trying to use because only ADO.Net understands the
Userinstance parameter. You can definitely attach it to SQL Express and
access it the way you would any other database. You can also access it
through the named pipe name but that's not something I would recommend for a
production system. More information on user instances is available here:
http://msdn2.microsoft.com/en-us/library/bb264564.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bart" <b@.sdq.dc> wrote in message
news:%23kiVRyZbHHA.4832@.TK2MSFTNGP02.phx.gbl...
> Thanks, so the conclusion is:
> it's impossible to access a MDF file with classic asp?
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:EF1406A3-C657-44F9-8FF6-3F1D032175E1@.microsoft.com...
>|||"You can definitely attach it to SQL Express and
access it the way you would any other database."
Do you mean: starting sql server management studio and there attaching the
MDF file?
But then i loose the benefit of having a MDF file which can be
administratted independantly of sql server ...
About named pipe name, i installed sqlexpress as "default" (not named
instance).
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
news:O16huWabHHA.596@.TK2MSFTNGP06.phx.gbl...
> Depends on how you define impossible. You can't access it using the
> connection string you are trying to use because only ADO.Net understands
> the Userinstance parameter. You can definitely attach it to SQL Express
> and access it the way you would any other database. You can also access
> it through the named pipe name but that's not something I would recommend
> for a production system. More information on user instances is available
> here:
> http://msdn2.microsoft.com/en-us/library/bb264564.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Bart" <b@.sdq.dc> wrote in message
> news:%23kiVRyZbHHA.4832@.TK2MSFTNGP02.phx.gbl...
>

how to connect to database in SQL Sever Managment Studio

hi

i have created a .aspx page and a sql database in visual studio ,, my question is how can i connect that database in SQL Server Managment Studio?

Just use SqlConnection to refer to any database on SQL Server as you likeYou can start from here:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(d=ide).aspx

|||

thanks for the answer ,, but i still need some help.. I i have never used the sql managment studio before...the sql database iam trying to connect to is on my harddrive in the App_data folder in my asp project ,,,, when i start SQL Managamenet studio it wants me to enter the

Server Name:

Server Type:

Authentication

so i typ in

Server Type: Database Engien

Authentication: Windows Authenication

Server Name: mycomputer\SQLEXPRESS this is set by defult

so when i click on connect , it connects to the mycomputer\SQLEXPRESS. am i suppose to find my database among the databases that is in the Object Explorer?

this is my connection string that i found in the web.config file

connectionString

="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

thank you for your help

by the way ,, the reason why i want to connect my to my database in sql management studio is that i want to enable full text search and indexing

|||

To manage database file in Management Studio, you have to 'register' the database file under the App_Data to the SQL instance. We can use detaching/attaching to do this: firstly in VS2005 right click the database file ->choose Detach; then in Management Studio right click Databases-> choose Attach. There are a lot of materials in SQL2005 Books Online, here is a start link:

http://msdn2.microsoft.com/en-us/library/ms190794.aspx

How to connect to a DB multiple times through page

Hey all,

I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page. I want to pull information once in one spot, but then pull different information in a different spot on the page. Anyway, in the first spot, I have the following code:

Dim conStringAsString = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString

Dim conAsNew SqlConnection(conString)

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con

con.Open()

Dim RSAs SqlDataReader = cmd.ExecuteReader()

While RS.Read()

blah blah blah

End While

End Using

Then in my other spot on the page I have the following:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

Using con

con.Open()

Dim RDAs SqlDataReader = cmdresults.ExecuteReader()

While RD.Read()%>

<tr>

<td>

<%=RD%>

</td>

</tr>

<%EndWhile

EndUsing

When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, "

RDAs SqlDataReader = cmdresults.ExecuteReader()" Any ideas? If possible a little explanation on how multiple connections to the same database work would be nice just for future reference.

Thanks in advance!!,

Chris

I think you are not using separate file for the code behind.

fischecp:

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)

fischecp:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

Did you observe that you haven't provided the connection for the second command ?

Even if you would have done so, unless you close the previous datareader you'll get another error when trying to bind the second datareader stating you already have a reader attached to this connection. One more thing, you're trying to open the same connection again in your second code. This will also result in an error stating the connection is already open.

Now, my advices to you are:

If possible, use code behind files. This way you will be able to differentiate your UI and Code.

In the code behind write small functions for the tasks you've mentioned above. Create a connection object in each of the function and dispose it before the code leaves the function.

You can use data binding techniques to bind the data returned in your datareader to any of the UI controls.

Hope this will help.

|||

fischecp:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

i think you forgot to give "con" as SqlConnection in New SqlCommand arguments... as..

Dim cmdresultsAsNew SqlCommand("SelectUsers.Firstname, sum(PointsID) as TotalPoints from Football_Input,Football_Schedule, Users where Football_Input.TeamID =Football_Schedule.winID and users.userid = Football_Input.UserID Groupby Users.firstname",con)

also make sure you close the connection each time after operation completes.. and re-open for a new operation..

Good Luck./.

|||

Ok, I appreciate the help. At least my page runs without errors, however! Now I get the page to load but in place of the data from the database is, "System.Data.SqlClient.SqlDataReader" in every line where the information from the database should be displaying.

Here is my code that I fixed (At least I think I fixed haha)

con =New SqlConnection(conString)

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname", con)

Using con

con.Open()

Dim RDAs SqlDataReader = cmdresults.ExecuteReader()

While RD.Read()%>

<tr>

<td>

<%=RD%>

</td>

</tr>

<%EndWhile

RD.close()

con.close()

EndUsing

I also closed out my previous connection and datareader like you guys mentioned. I'll post the code for that just incase. And I will use functions to clean it all up. I just would like to see how it works first =)

Dim conStringAsString = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString

Dim conAsNew SqlConnection(conString)

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con

con.Open()

Dim RSAs SqlDataReader = cmd.ExecuteReader()

While RS.Read()

EndWhile

con.Close()

RS.Close()

EndUsing

Any Ideas? Thanks again guys,

Chris

|||

<%=RD%>

You probably want to specify which field you are trying to display on the page. Like:

<%= RD.Item(0) %>

Or "columnname" instead of index.

|||

haha wow, I have had my head so deep into this I didn't even stop to think that. Sometimes you just need to take a step back =)

Thanks again guys! you solved everything!

sql

How to connect Report in ReportViewer to a datasource?

Hi:

I am new to ReportViewer. I have an .aspx page that takes data that makes a request for a Permit. When the User clicks submit, I add the record to the database then get the unique identity of that record and display it in a popup (as a confirmation number). I would then like a report to show the data the User just submitted.

I have added an .aspx page called RequestReport.aspx and put a ReportViewer on it. I added a report called PermitRequest.rdlc with a textbox that acts as a Title. I linked RequestReport to pull PermitRequest.rdlc and it successfully displays the report. My question is that I don't know how to add a datasource to the report to show the new record. I did save the unique record id to a Session variable called "RequestID".

Please help with directions.

Thanks in advance!

a few questions:

1) Are you using textbox to display the report or some other contrl (like a table)

2) Do you want an SQL type solution that you can handle programatically or a solution where the report is tied to a fixed dataset.

3) What IDE are you using (Visual Web Express or Studio)

bullpit

|||

Hi:

(1) I am using a textbox to display the report name and a table to display the data.

(2) Either one is OK. I just want to be able to pass the newly given unique id to the report and then have the report display only the record data for that id. Each record will change with each user and each new entry.

(3) Visual Studio 2005 SP1 and framework 2.0 talking to a SQL Server 2000 database.

I now have the report displaying all records, but I only want to display the last saved record. Do I need to set a parameter to pass? If so, I see how to pass it programmatically with VB code behind. I don't see how I tell the report to accept a parameter as part of the SQL Statement.

|||

The way I do, I handle everything from codebehind...that gives me more control...that way i can send an SQL query to fetch whatever i want...in your case, you have the id...build a select query string in codebehind to select only the records with that id...then bind to the report...

these two funtions will help you get started if you want to go that way:

public void FindAll()
{
// Set the processing mode for the ReportViewer to Local
ReportViewer1.ProcessingMode = ProcessingMode.Local;
LocalReport rep = ReportViewer1.LocalReport;
rep.ReportPath = "Report.rdlc";
DataSet ds = GetSalesDataFull();
// Create a report data source for the sales order data
ReportDataSource dsMaintenanceDS = new ReportDataSource();
dsMaintenanceDS.Name = "DataSet1_Main";
dsMaintenanceDS.Value = ds.Tables["Main"];
rep.DataSources.Clear();

//example to set report parameter values from codebehind
ReportParameter param = new ReportParameter("nRows", dDownListNRows.SelectedValue);
this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { param });

rep.DataSources.Add(dsMaintenanceDS);
rep.Refresh();
}

private DataSet GetSalesDataFull()
{
DataSet ds = new DataSet();
string str = "";

str = @." WHERE ""ID""='" + <YOUR ID> ')";
string sqlSalesData = @."SELECT * FROM <TABLE NAME> " + str;

OdbcConnection connection = new OdbcConnection(<CONNECTION STRING>);
OdbcCommand command = new OdbcCommand(sqlSalesData, connection);
OdbcDataAdapter salesOrderAdapter = new OdbcDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}

let me know if you have more questions.

good luck...bullpit

|||

The way I do, I handle everything from codebehind...that gives me more control...that way i can send an SQL query to fetch whatever i want...in your case, you have the id...build a select query string in codebehind to select only the records with that id...then bind to the report...

these two funtions will help you get started if you want to go that way:

public void FindAll()
{
// Set the processing mode for the ReportViewer to Local
ReportViewer1.ProcessingMode = ProcessingMode.Local;
LocalReport rep = ReportViewer1.LocalReport;
rep.ReportPath = "Report.rdlc";
DataSet ds = GetSalesDataFull();
// Create a report data source for the sales order data
ReportDataSource dsMaintenanceDS = new ReportDataSource();
dsMaintenanceDS.Name = "DataSet1_Main";
dsMaintenanceDS.Value = ds.Tables["Main"];
rep.DataSources.Clear();

//example to set report parameter values from codebehind
ReportParameter param = new ReportParameter("nRows", dDownListNRows.SelectedValue);
this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { param });

rep.DataSources.Add(dsMaintenanceDS);
rep.Refresh();
}

private DataSet GetSalesDataFull()
{
DataSet ds = new DataSet();
string str = "";

str = @." WHERE ""ID""='" + <YOUR ID> ')";
string sqlSalesData = @."SELECT * FROM <TABLE NAME> " + str;

OdbcConnection connection = new OdbcConnection(<CONNECTION STRING>);
OdbcCommand command = new OdbcCommand(sqlSalesData, connection);
OdbcDataAdapter salesOrderAdapter = new OdbcDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}

let me know if you have more questions.

good luck...bullpit

|||

Hi Bullpit:

Thank you for your response. Do you have this in Visual Basic code behind? Also, if i do this all in the code behind...will I still have the fields in the dataset on the report to drag and drop to the table on the report? Will I still be able to layout the design of the report?

|||

Sorry, I do not have this code in VB. And yes, you will be able to design the table the way you want. I wanted my report to be dynamic, so I gave the user the choice to choose the fields she/he wants on the report..so whatever field name user chooses, corresponding values are bound to that column in details section...but if you don't want that, you can have the column names as static (by drag and drop)...i believe it should work...also, there are several tools online to convert c# to vb...you can use them...

P.S. If you choose to go codebehind, then remember to add the name of the datasource (in our case "DataSet1_Main") in the Report Data Source option in IDE.

good luck

How to Configure PSP

HI,
Anyone know how to configure the pl/sql server page application. iam new to this area, can you help me regarding PSP.
Thank youLast time I created a PSP page was about 2 years ago. I created a HTML file with embedded PL/SQL and the used the PSP converter/uploaded to load it into the database. The upload then creates a PL/SQL procedure/package (not quite sure) in the database which can be executed via the PL/SQL mod on the IAS server. PSP was in a way their version of JSP (Java Server Pages HTML pages with Java embedded into them which when executed were converted)

I personally used designer to design generate Web PL/SQL forms as I found it more benificial at the time.

Maybe PSP has moved on since then have a look at this thread and see what other people think.

http://groups.google.ie/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=1e2f54c1.0106290422.5029eb80%40posting.goo gle.com&rnum=1&prev=/groups%3Fq%3Doracle%2Bpsp%2Bvs%2Bweb%2Bpl/sql%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D1e2f54c1.0106290422.5029eb80%2540postin g.google.com%26rnum%3D1

I hope I have been of some help.

Regards

Edwin|||this page may also help you

http://www.orafaq.com/faqmodpl.htm#CONFIG|||Hi,

To Develop and Deploy PL/SQL Server Pages, you need the Oracle Server 8.1.6 or later and either Oracle Application Server PL/SQL Cartridge or Oracle WebDB PL/SQL Gateway.|||Hi,

1. Install the PL/SQL Toolkit
2. Create the Listener
3. Create a Database Access Descriptor
4. Create an Application called pspApp
5. Add a Cartridge to your Application
6. Create the PL/SQL Server Pages Script
7. Load the PL/SQL Server Page
8. Verify Procedure creation
9. Reload OAS
10. Execute the PSP script from OAS|||Originally posted by satish_ct
Hi,

To Develop and Deploy PL/SQL Server Pages, you need the Oracle Server 8.1.6 or later and either Oracle Application Server PL/SQL Cartridge or Oracle WebDB PL/SQL Gateway.

As far as I am aware none of these products are supported.

Can I recommend that you use version 9i or higher of the database and IAS as OAS is not as reliable and also may not be supported. IAS come with PL/SQL module in replace of the PL/SQL cartridge.

Regards

Edwin|||U Better check & make sure that these products are supported.

Originally posted by edwinjames
As far as I am aware non of these products are supported.

Can I recommend that you use version 9i or higher of the database and IAS as OAS is not as reliable and also may not be supported. IAS come with PL/SQL module in replace of the PL/SQL cartridge.

Regards

Edwin

how to colum the page?

Good day!

can any one help me how to colum the page? example i have three colums in the page if the data exceed in the first colum it wil go to the second colum then 3rd colum.

Can any one hlp me. Your help is greatly appreciated

See this article in MSDN:

http://msdn2.microsoft.com/en-us/library/ms155816.aspx

How to code ASP.NET page with return value Store Procedure?

Does anyone know how to call a SQL store procedure that return a value to the page?

I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.

Are there a similar article somewhere?

Thank you all!

check out the docs about using ExecuteScalar() Method.|||

Using ExecuteScalar() requires a separate procedure call. I would like to use the same procedure call that to insert a record into a table and also return an ID back. My store procedure look like this:

sqlString = "EXEC spInsertRow 'parm1','parm2','parm3', @.ReturnID OUTPUT"

Thanks

|||

In the end of your sproc:

SELECT @.ReturnID = @.@.IDENTITY

|||

I suggested ExecuteScalar assuming you are doing some insert and want to return the ID.

If that is not the case you can use the OUTPUT parameters..

Dim res as integer

myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.returnId"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.bigint
mycommand.Parameters.Add(myParam)

res = mycommand.Parameters("@.result").Value

|||

Thank you. It works

|||

Can you share your general procedure. I'm having the same issue and have tested many ways. Still not working.

See posthttp://forums.asp.net/1178243/ShowPost.aspx

Thanks

How to code an aspx page to run a stored procedure with a parameter

My stored proceddure "My Programs" includes a parameter @.meid.
How do I code an aspx file to run the procedure with a user ID, e.g. EmpID?
I tried the following codes, but the error message indicated it can not find
the Stored Procedure. How do I pass the EmpID as a Stored Procedure parameter?

<%
meid = EmpId
cmd.CommandText ="MyPrograms meid"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection2
sqlConnection2.Open()
reader3 = cmd.ExecuteReader(meid)
While reader3.Read()
sb.Append(reader3(i).ToString() +".....<BR> <BR /> ")
EndWhile
%>

TIA,
Jeffrey

Check if this helps:http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx

|||

Thanks.

My co-worker told me I am supposed not need to know the parameter name, "@.meid".
Then what does "Failed to convert parameter value from a String to a Int32" mean?
Why the ExecuteReader() needs to convert EmpID to Int32? It's laready an integer.

TIA,
Jeffrey

<%
cmd.CommandText ="MyPrograms"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@.meid", SqlDbType.Int))
cmd.Parameters("@.meid").Value =EmpID
cmd.Connection = sqlConnection2
sqlConnection2.Open()
reader3 = cmd.ExecuteReader() Failed to convert parameter value from a String to a Int32.]

While reader3.Read()

sb.Append(reader3(i).ToString() +".....<BR> <BR /> ")

EndWhile

|||

I think I have solved the problem. The reason for the rror: "Failed to convert parameter value from a String to a Int32"
is Value = "EmpID". It should be EmpID.

Thanks gain.

How to close the blank page which is opening when clicked on export !

Hi
I tried to export my report to Excel and PDF, it is exporting
correctly.
But it leaves a page opened, which was opened when I clicked on Export
Link.
How to close that page?
Is there any setting available for it?
Or else how to close it using C#.Net code?
Any help is appriciated.
TIA
PuneetOn Sep 25, 2:16 pm, appu <ajmera.pun...@.gmail.com> wrote:
> Hi
> I tried to export my report to Excel and PDF, it is exporting
> correctly.
> But it leaves a page opened, which was opened when I clicked on Export
> Link.
> How to close that page?
> Is there any setting available for it?
> Or else how to close it using C#.Net code?
> Any help is appriciated.
> TIA
> Puneet
Since this is a browser window, it would be difficult to close the
container window via C#.NET (ASP.NET). I'm not familiar w/your
circumstances; however, you could try calling the report via URL where
you would only pop open a save dialog. This URL might be helpful from
a custom application.
http://SomeServerName/reportserver?/SomeReportDirectory/ReportName&rs:Command=Render&Param1=SomeValue&rs:Format=PDF
-or-
http://SomeServerName/reportserver?/SomeReportDirectory/ReportName&rs:Command=Render&Param1=SomeValue&rs:Format=Excel
The format is:
http://<ServerName>/reportserver?/<Directory>/<ReportName>&rs:Command=Render&<ParameterName>=<ParameterValue>&rs:Format=<PDF/CSV/XML/Excel/MHTML>
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant