Listbox to only appear if there are records returned from the SQL select query

I would like to make a listbox only appear if there are results returned by the SQL select statement.

I want this to be assessed on a click event of a button before the listbox is rendered.

I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?

hi,

there must be datasource(dataset or reader) for that listbox i believe.

if its dataset then use dataset.tables[0].Rows.Count, if its reader then use reader.hasrows.

hope it helps.

regards,

satish.

|||

Thanks Satish,

I used an if statement to assess if rows > 0

Cheers,

Ben.

|||

cheers Ben.

satish.

sql

list with grouping loses space between records

hi,
I'm new to reporting services and have had a play with it but wonder if
I can get a quick answer here.
I have a dataset and want to show some details of the parent record
with some details of the child.
I have used a table and that is all ok except for the fact that on the
child record I have table headings (which I have put into the parent
grouping row). Now this row expands because of the data in the parent
record which means that the child record doesn't start until after the
parent record. I want the child record to show as a table with its
headers in line with the parent record i.e. the table headers will be
independent of the parent record - not growing.
So I decided to try and use a list. This works as I want except now, I
am not sure how to get a gap between the records. When there are no
child records I get a nice gap but if there are, say 3 child records,
then the gap between the last child record and the next parent record
is virtually non-existent. I have tried putting empty rectangles in and
even an empty textbox with a "CanShrink=False" to try and force a
certain gap between parent records but there's probably something
really basic I'm missing.
Thanks for any help
Philactually the list doesn't work as i want because each of the cells
grows independently of each other so the borders don't align - is there
a way round this?

list parameterized dates

Hello,

For my report I want to have the days listed as a dropdown with simple dates during the month (3/27/2007), but there may be many records for that date as it is using the time as well. Unfortunately I don't even know where to begin

Ultimately the user will be given a dropdown listbox showing the simple dates only for the current month and on selection user will view only that days report. How do I basically treat each day as a group in the parameter?

Thanks!

Create a dataset with the following sql statement:

SELECT convert(varchar,<datefield>,101),<datefield>

FROM table

WHERE <datefield> between '01/01/2007' and '04/01/2007' specify the time frame here

group by convert(varchar,<datefield>,101),<datefield>

ORDER BY <datefield> -- add this field so you can sort by date

This will display only the date without the time for the time period specified in the where clause.

Then create a parameter in your report called date and select this dataset for the available values.

|||

Hello,

Will the date picker not work for you? If you set the data type of your parameter as DateTime, the user will get a little calendar button next to the parameter where they can select a date. If this won't work for you, you'll have to create a table in your database that holds all the possible values to be selected, then you will have to create another dataset (select Dates from CalendarTable) and use it as your parameters available values.

As for the issue with the records having the time in the datetime field... You can get around that by converting the field to a date without the time on it. For example, in your SQL query, you may have ... where DateField = @.DateParam. In order to fix this, do this instead ... where convert(varchar, DateField, 101) = @.DateParam. The 101 style will remove the time from your date.

Hope this helps.

Jarret

|||

This definitely is on the right track. Though I can't get it to only group by single dates.

My query window still has all corresponding records of the dates and not singular dates.

Question, on the CONVERT statement what does the number 101 represent?

Thank you Susan22

|||

I have yet to see a datepicker appear if I simply change the datatype to datetime.

Even if I just use a known datefield I get a querylist view of all the dated records.

I see where all this will be worth learning though. Just the basic reports (drag&drop) are amazingly useful. Thanks for your help.

|||

That's true and if you remove the ORDER BY and the date field and only select distinct records you won't be able to sort by date because the date field has been converted to string.

The 101 returns the date format MM/DD/YYYY.

I would follow Jarret's suggestion and create a non-queried parameter and type in all possible dates in the available values table.

|||

Are you on RS2000 or RS2005?

I could be wrong, but I don't think the date picker was available in RS2000, so if that's what you're using, you'll need to go the other route I mentioned in my previous post.

In regards to Susan's query, I don't think you were getting the grouped dates because the actual date was being grouped on also. Try this instead:

SELECT distinct convert(varchar,<datefield>,101)

FROM table

WHERE <datefield> between '01/01/2007' and '04/01/2007' specify the time frame here

group by convert(varchar,<datefield>,101)

ORDER BY <datefield> -- add this field so you can sort by date

Jarret

List of SQL table that have data in it

I want to create a query to return all table name in my SQL database that ha
s
more than 0 records how can I do that ?
Thanks
FREDTry,
use northwind
go
create table #t (
tname sysname,
rcnt int
)
declare @.tn sysname
declare @.sql nvarchar(4000)
declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name)
from
information_schema.tables
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + N''', count(*) from ' + @.tn
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select
*
from
#t
where
rcnt > 0
drop table #t
go
AMB
"FRED" wrote:

> I want to create a query to return all table name in my SQL database that
has
> more than 0 records how can I do that ?
> Thanks
> FRED
>|||Is this useful for you?
select object_name(si.id), rows
from sysindexes si
where si.id = (select object_id(so.name) from sysobjects so where so.type =
'u' and si.id = so.id)
and si.indid < 2
and si.rows > 0
order by 1
"FRED" <FRED@.discussions.microsoft.com> wrote in message
news:7BAC0DB0-2BA9-436F-8105-FCC395631E1A@.microsoft.com...
> I want to create a query to return all table name in my SQL database that
has
> more than 0 records how can I do that ?
> Thanks
> FRED
>|||My problem is solved
Thanks
FRED
"Armando Prato" wrote:

> Is this useful for you?
> select object_name(si.id), rows
> from sysindexes si
> where si.id = (select object_id(so.name) from sysobjects so where so.type
=
> 'u' and si.id = so.id)
> and si.indid < 2
> and si.rows > 0
> order by 1
> "FRED" <FRED@.discussions.microsoft.com> wrote in message
> news:7BAC0DB0-2BA9-436F-8105-FCC395631E1A@.microsoft.com...
> has
>
>

list box is not displaying

I have a list box getting a filtered recordset from a stored procedure using ADO calling in VB6.
The recordset returns the two records with correct values. The problem is it is not displaying in the list box. Why? I tried using .additem property but it is not available in Access 2000. Can anyone help me with this?

Function ..
Dim rs_get_defect_desc As New ADODB.Recordset, lot_n as integer
Set lot_n = 4051

Rs_get_defect_desc.Open "EXEC spGet_desc_defect @.lot_n=" & lot_n, CurrentProject.Connection
Do While Not rs_get_defect_desc.EOF

lstbox.RowSource = rs_get_defect_desc(0) & " " & rs_get_defect_desc(1)
rs_get_defect_desc.MoveNext

LoopI am confused, - is it VB6 or Access?