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 Concatinate results to display results in a list from Left - Right instead of Top -

Hi,
I am creating a report using SQL Server Reporting Services 2000. My
report displays values in datatable. Say, the table looks like this
EmpName Emp# Address Phone#
..... ... ...... ...
I want all phone # for an employee to be displayed in a single row.
This can be acheived by displaying phone numbers in a group footer
(group by emp#). But I want all phone# to be displayed horizontally
(Left - Right) not vertically (Top - Bottom).
Instead of displaying results like this
EmpName Emp# Address
..... ... ......
Phones
###
###
###
###
I want to display results like below -
EmpName Emp# Address Phone#
..... ... ...... ...
Phones ### , ### , ####, ###, ###, ###
..... ... ...... ...
Phones ### , ###
..... ... ...... ...
Phones ### , ###, ### , ###
How can I concatinate the phone# ? I tried using a sub report in the
footer to retrive all phone# for an employee but how to concatinate
them ?
I tried to query like this
SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
But I'm not sure about the length of the return value (may be more than
8000 characters).
So is there any way in concatinating values in Report ? Or is there any
way in acheiving this in a Listbox or Table ?
Regards,
ChiroI think Chris Hays has something that will work for you.
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
Steve MunLeeuw
"Chiro" <chirangv@.hotmail.com> wrote in message
news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> Hi,
> I am creating a report using SQL Server Reporting Services 2000. My
> report displays values in datatable. Say, the table looks like this
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> I want all phone # for an employee to be displayed in a single row.
> This can be acheived by displaying phone numbers in a group footer
> (group by emp#). But I want all phone# to be displayed horizontally
> (Left - Right) not vertically (Top - Bottom).
> Instead of displaying results like this
> EmpName Emp# Address
> ..... ... ......
> Phones
> ###
> ###
> ###
> ###
> I want to display results like below -
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> Phones ### , ### , ####, ###, ###, ###
> ..... ... ...... ...
> Phones ### , ###
> ..... ... ...... ...
> Phones ### , ###, ### , ###
>
> How can I concatinate the phone# ? I tried using a sub report in the
> footer to retrive all phone# for an employee but how to concatinate
> them ?
> I tried to query like this
> SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> But I'm not sure about the length of the return value (may be more than
> 8000 characters).
> So is there any way in concatinating values in Report ? Or is there any
> way in acheiving this in a Listbox or Table ?
> Regards,
> Chiro
>|||Hi,
Thank You.
I was able to acheive this as follows -
1. Add a Matrix control. Add Phone# in column grouping of matrix.
2. Add a ListBox control. Include Matrix inside the Listbox.
3. Edit details of Listbox to add a group expression
=RowNumber(Nothing) / 15. (15 is number of columns to be displayed)
4. Add a Matrix column group expression as your Listbox group
expression.
=RowNumber("list1_Details_Group"). Now your matrix should contain 2
group expressions. (1 for Phone # and other for controling no. of
columns).
Regards,
Chiro
Steve MunLeeuw wrote:
> I think Chris Hays has something that will work for you.
> http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
> Steve MunLeeuw
> "Chiro" <chirangv@.hotmail.com> wrote in message
> news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> > Hi,
> > I am creating a report using SQL Server Reporting Services 2000. My
> > report displays values in datatable. Say, the table looks like this
> >
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > I want all phone # for an employee to be displayed in a single row.
> > This can be acheived by displaying phone numbers in a group footer
> > (group by emp#). But I want all phone# to be displayed horizontally
> > (Left - Right) not vertically (Top - Bottom).
> > Instead of displaying results like this
> > EmpName Emp# Address
> > ..... ... ......
> > Phones
> > ###
> > ###
> > ###
> > ###
> > I want to display results like below -
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > Phones ### , ### , ####, ###, ###, ###
> > ..... ... ...... ...
> > Phones ### , ###
> > ..... ... ...... ...
> > Phones ### , ###, ### , ###
> >
> >
> > How can I concatinate the phone# ? I tried using a sub report in the
> > footer to retrive all phone# for an employee but how to concatinate
> > them ?
> > I tried to query like this
> > SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> > But I'm not sure about the length of the return value (may be more than
> >
> > 8000 characters).
> > So is there any way in concatinating values in Report ? Or is there any
> >
> > way in acheiving this in a Listbox or Table ?
> > Regards,
> > Chiro
> >

How to clear table?

Is there a way to clear all the records in a table in Microsoft SQLServer Management Studio instead of selecting all the rows and delete?

Try the link below and Truncate the table. Hope this helps.

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

|||Thanks for the response, but I'm not looking for a code clear records.I am in Microsof SQL Management Studio. How do I clear the table withinthis tool?|||

Assuming no constraint defined on the table try the link below for a walkthrough. Hope this helps.

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

|||That instruction only show how to delete a column. Are you suggestingthat deleting the column and then recreate that same column?|||

Sorry wrong link.

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

|||That link shows how to delete the whole tabe. I don't want to deletethe whole table. All I want is to clear all the records in the table. Idon't want to recreate the whole table again.|||Truncate is the clean way to do it or you have to do it by column because a database table is columns with your data in rows so if you don't want to truncate you have to do it by column.|||The reason I asked about clearing the table records is because inphpMyAdmin with MySQL, it is very simple to clear the whole records ina table. I would be very surprise if Microsoft does not have thisfeature in their MS SQL Management Studio tool.|||

MySQL is generally free so you can do crude things but SQL Server cost a lot of money companies will not like data delete operations that cannot be traced. So the links below explain Truncate and Delete very clearly and some solutions using temp table. Hope this helps.

http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/09/62960.aspx

http://www.sql-server-performance.com/q&a118.asp

|||Thanks! I will give those two links some reading.