How to show all the fields in a dataset.

I have a sp that returns a different dataset depending on the parameter
values, it is dyanamic sp which has different field names and also the
number of fields.
Is there a way to incorporate this in reporting services, like in .Net
we can display the dataset by just binding it to the datagrid and
letting datagrid handle the rest.
Thanks,
TonyNo. RS does not work that way. It expects a consistent number/name of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bubaa118@.yahoo.com> wrote in message
news:1107965810.679249.274270@.c13g2000cwb.googlegroups.com...
> I have a sp that returns a different dataset depending on the parameter
> values, it is dyanamic sp which has different field names and also the
> number of fields.
> Is there a way to incorporate this in reporting services, like in .Net
> we can display the dataset by just binding it to the datagrid and
> letting datagrid handle the rest.
> Thanks,
> Tony
>|||Thanks for the prompt reply Bruce, Is there any other workaround for
the issue I have any ideas or suggestions would be great.
Thanks again,
Tony|||You can have multiple datasets in a report, each of them calling the
procedure with the appropriate parameter. This does mean the SP will get
called multiple times. Then you should be able to hide the table on the
report if there is no data. I haven't tried this but I think you can. You
still might end up with some blank lines though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bubaa118@.yahoo.com> wrote in message
news:1107967055.899883.298640@.f14g2000cwb.googlegroups.com...
> Thanks for the prompt reply Bruce, Is there any other workaround for
> the issue I have any ideas or suggestions would be great.
> Thanks again,
> Tony
>|||You could also at the end of your proc pivot (or is this really
unpivoting) the data. So for a table with structure
# keycol # col1 # col2 # col3 # col4 # ... # col<n> #
you would end up with
# keycolvalue # colname # colvalue #
this will have n rows for each row in the original table. You may want
to modify your proc to build that to begin with rather than pivot data
later.
then you could drop that into a matrix.
This would work fairly well if all variable columns are of the same
type. Otherwise you'd have to do some formatting in the stored
procedure.

How to setup SelectParameters programmatically?

Hi,

I am using Visual Web Developer 2005 Express Edition.

I am trying to SELECT three information fields from a table when the Page_Load take place (so I select the info on the fly). The refering page, sends the spesific record id as "Articleid", that looks typically like this: "http://localhost:1424/BelaBela/accom_Contents.aspx?Articleid=2". I need to extract the "Article=2" so that I can access record 2 (in this example).

How do I define the SelectParameters or QueryStingField on the fly so that I can define the WHERE part of my query (see code below). If I remove the WHERE portion, then it works, but it seem to return the very last record in the database, and if I include it, then I get an error "Must declare the scalar variable @.resortid". How do I programatically set it up so that @.resortid contains the value that is associated with "Articleid"?

My code is below.

Thank you for your advise!

Regards
Jan

/******************************************************************************** RETRIEVE INFORMATION FROM DATABASE*******************************************************************************/// specify the data sourcestring connContStr = ConfigurationManager.ConnectionStrings["tourism_connect1"].ConnectionString;SqlConnection myConn =new SqlConnection(connContStr);// define the command queryString query ="SELECT resortid, TourismGrading, resortHits FROM Resorts WHERE ([resortid] = @.resortid)";SqlCommand myCommand =new SqlCommand(query, myConn);// open the connection and instantiate a datareadermyConn.Open();SqlDataReader myReader = myCommand.ExecuteReader();// loop thru the readerwhile (myReader.Read()){ Label5.Text = myReader.GetInt32(0).ToString(); Label6.Text = myReader.GetInt32(1).ToString(); Label7.Text = myReader.GetInt32(2).ToString();}// close the reader and the connectionmyReader.Close();myConn.Close();

You can try the following, but you may need to change the resotid if it is an integer type.

SqlCommand myCommand =new SqlCommand(query, myConn);

myCommand.Parameters.Add("@.resortid"

,SqlDbType.NVarChar, 10).Value =Request.QueryString("resortid");|||

Something like this: 

String ArticleID;if (Request.QueryString["ArticleID"] !=null) ArticleID = Request.QueryString["ArticleID"];else// handle bad parameterSqlParameter param =new SqlParameter();param.ParameterName ="@.resortId";param.Value = ArticleID.ToInt32();myConn.Parameters.Add(param);

|||

Hi Limno and SGWellens,

Thanks for your help - I managed to get it working like a charm!

Regards

Jan

How to setup a fulltext index?

What is the sql syntax for creating a full text index on a table?
I have a table called bookdata with 20 some fields I would like to
index.
Then how do you construct the queries?
Is it something like 'where contains (colname, 'itemtofind')?
http://www.indexserverfaq.com/SQLFTITSQL.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<sdowney717@.msn.com> wrote in message
news:1110309891.262624.164200@.g14g2000cwa.googlegr oups.com...
> What is the sql syntax for creating a full text index on a table?
> I have a table called bookdata with 20 some fields I would like to
> index.
> Then how do you construct the queries?
> Is it something like 'where contains (colname, 'itemtofind')?
>

how to set these varibles? help

I have a serious of fields like: f1, f2, f3,f4,f5,f6,f7,f8,f9,f10
The table(s) goes like this:
Id f1 f2 f3 f4 ... f10
1 apple pear pineapple orange
2 chinese english greek britan...
...
Now I want to set only one variable to get the value of these fields:
I wrote like this:
declare @.m int,f@.m nvarchar(50),i int
set @.m=1
set @.i=1
set f@.m=(select f@.m from s where id=@.i)
It seems that sql doesn't support such way of declare. What can I do?
Thanks in advance!
Just a quick hint - why don't yo simply use a table data type variable?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"treesy" <treesy@.hostran.com.cn> wrote in message
news:equEgdcsEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I have a serious of fields like: f1, f2, f3,f4,f5,f6,f7,f8,f9,f10
> The table(s) goes like this:
> Id f1 f2 f3 f4 ... f10
> 1 apple pear pineapple orange
> 2 chinese english greek britan...
> ...
> Now I want to set only one variable to get the value of these fields:
> I wrote like this:
> declare @.m int,f@.m nvarchar(50),i int
> set @.m=1
> set @.i=1
> set f@.m=(select f@.m from s where id=@.i)
>
> It seems that sql doesn't support such way of declare. What can I do?
>
> Thanks in advance!
>

how to set these varibles? help

I have a serious of fields like: f1, f2, f3,f4,f5,f6,f7,f8,f9,f10
The table(s) goes like this:
Id f1 f2 f3 f4 ... f10
1 apple pear pineapple orange
2 chinese english greek britan...
...
Now I want to set only one variable to get the value of these fields:
I wrote like this:
declare @.m int,f@.m nvarchar(50),i int
set @.m=1
set @.i=1
set f@.m=(select f@.m from s where id=@.i)
It seems that sql doesn't support such way of declare. What can I do?
Thanks in advance!Just a quick hint - why don't yo simply use a table data type variable?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"treesy" <treesy@.hostran.com.cn> wrote in message
news:equEgdcsEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I have a serious of fields like: f1, f2, f3,f4,f5,f6,f7,f8,f9,f10
> The table(s) goes like this:
> Id f1 f2 f3 f4 ... f10
> 1 apple pear pineapple orange
> 2 chinese english greek britan...
> ...
> Now I want to set only one variable to get the value of these fields:
> I wrote like this:
> declare @.m int,f@.m nvarchar(50),i int
> set @.m=1
> set @.i=1
> set f@.m=(select f@.m from s where id=@.i)
>
> It seems that sql doesn't support such way of declare. What can I do?
>
> Thanks in advance!
>

how to set these varibles? help

I have a serious of fields like: f1, f2, f3,f4,f5,f6,f7,f8,f9,f10
The table(s) goes like this:
Id f1 f2 f3 f4 ... f10
1 apple pear pineapple orange
2 chinese english greek britan...
...
Now I want to set only one variable to get the value of these fields:
I wrote like this:
declare @.m int,f@.m nvarchar(50),i int
set @.m=1
set @.i=1
set f@.m=(select f@.m from s where id=@.i)
It seems that sql doesn't support such way of declare. What can I do?
Thanks in advance!Just a quick hint - why don't yo simply use a table data type variable?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"treesy" <treesy@.hostran.com.cn> wrote in message
news:equEgdcsEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I have a serious of fields like: f1, f2, f3,f4,f5,f6,f7,f8,f9,f10
> The table(s) goes like this:
> Id f1 f2 f3 f4 ... f10
> 1 apple pear pineapple orange
> 2 chinese english greek britan...
> ...
> Now I want to set only one variable to get the value of these fields:
> I wrote like this:
> declare @.m int,f@.m nvarchar(50),i int
> set @.m=1
> set @.i=1
> set f@.m=(select f@.m from s where id=@.i)
>
> It seems that sql doesn't support such way of declare. What can I do?
>
> Thanks in advance!
>

how to set null values to the database ?

I have some textboxes that could be blank when the form is submitted. If so, I want those fields in SQL database table to be set NULL when the insert is executed. I also need to convert the values in these textbexes to either INT or String if there are values there. I tried the following.

--Code

int RowNumber;

if (((TextBox)(e.Item.FindControl("RowNumber"))).Text != "" )
{
RowNumber = int.Parse(((TextBox)(e.Item.FindControl("RowNumber"))).Text);
}
else
{
RowNumber = DBNULL.value;
}

--Code

I got the error:

Cannot implicitly convert type "System.DBNull" to "int".

I want to pass RowNumber later to set NULL on the table. I know the data type is not correct, but how I get around with it ? and Is this the only problem ?

Thanks you very dmuchYou'll have to wait until you pass the value into the parameter itself to perform the test; once there, you can pass the parameter the value DBNull.Value.|||Thank you very much.. Can you show me how to do it.. please ?|||Assuming you are using sprocs, and passing in parameters to these sprocs:

'With your command object
myCmd.Parameters.Add("@.myParam",SQLDBType.INT).Value = IIf(myTextBox.Text <> "", myTextBox.Text, DBNull.Value)

Obviously, this is over-simplified (and in VB, no less), but I hope it gives you a general idea...|||I understand that I can do this when I put up the Sqlparameter object. But the problem is that I need to pass all the variables from my code-behind to a component which handles all the Sqlparameter things. It would not all me to pass the variables if they are not assigned certain values. so I need to assign NULL to some of them if they have no values. But I cannot do that .. any help ?|||The way I handle this is to pass a non-value to the component; for example, Integer.MinValue. The test for MinValue inside of your sub, and replace it with DBNull.Value.|||Thank you very much, I will try that...

How to set a key for a field in a data table

In my SQL Server 2005 database I have created a table with several data fields. One of the fields is designated as a primary key. And another one is just a key.

I can easily set the first field as the primary key, but am not able to set the second field as being just a key field.

I perform these steps to set the primary key.

(1) Right click on the table name and then select 'Open Table Definition'

(2) I right click on the field in my data table and select 'Select Primary Key'

As I mentioned previously, to set another field up as just a key field, I am not able to do this. My choices are the following when I right click on the data field to be designated as the key field:

Set Primary Key, Insert Column, Delete Column,Relationships,Indexes/Keys,Full Text Index,XML Indexes, Check constraints,Properties.

None of these choices will allow me to set the field up as a key field.

Someone please help me out with this problem.

I'm not sure what you're asking for but I can make some guesses. A Primary Key uniquely identifies each record. Therefore there can only be a single primary key for a table. However, a PK can be composed of more than 1 field.

If, however, you have one field which is the key and a second field which contains unique values, then you will want the first field as the key and add an index or constraint for the second field.

HTH.

How to set "0" instead of blank spaces to a column or field.

Hi i hv a doubt in Sql server reporting..I do generate some reports based on some criteria.In the results screen i hv empty fields based on the search i hv generated.I need to set "0" instead of blank spaces in the fields..Can any one help me?

What about using either using SQL Server logic to tranform the data or use reporting services to encapsulate the logic

CASE LEN(LTRIM(RTRIM(SomeColumn))) WHEN 0 THEN 0 ELSE SomeColumn END

would be the SQL Server alternative

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||I do use a select query from a view in my .rdl file.While selecting i need to set "0" to some columns which ever is null.Will it works?|||

CASE LEN(LTRIM(RTRIM(ISNULL(SomeColumn,'')))) WHEN 0 THEN 0 ELSE SomeColumn END

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to set "0" instead of blank spaces to a column or field.

Hi i hv a doubt in Sql server reporting..I do generate some reports based on some criteria.In the results screen i hv empty fields based on the search i hv generated.I need to set "0" instead of blank spaces in the fields..Can any one help me?

What about using either using SQL Server logic to tranform the data or use reporting services to encapsulate the logic

CASE LEN(LTRIM(RTRIM(SomeColumn))) WHEN 0 THEN 0 ELSE SomeColumn END

would be the SQL Server alternative

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||I do use a select query from a view in my .rdl file.While selecting i need to set "0" to some columns which ever is null.Will it works?|||

CASE LEN(LTRIM(RTRIM(ISNULL(SomeColumn,'')))) WHEN 0 THEN 0 ELSE SomeColumn END

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to separate the DateTime field into Two Fields in View

Hi ,

I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?

Best Regards,

make use of this

DATEPART

Returns an integer representing the specified datepart of the specified date.

Syntax

DATEPART ( datepart , date )

Arguments

datepart

Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft? SQL Server?.

Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.

Return Types

int

Remarks

The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.

Examples

The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.

SELECT GETDATE() AS 'Current Date' GO

Here is the result set:

Current Date Feb 18 1998 11:46PM SELECT DATEPART(month, GETDATE()) AS 'Month Number' GO

Here is the result set:

Month Number 2

This example assumes the date May 29.

SELECT DATEPART(month, GETDATE()) GO

Here is the result set:

-- 5 (1 row(s) affected)

In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

Here is the result set:

-- 1 1 1900 |||

With datepart you will have to write several statements to getthe correct parts and afterwards join them back concatenating fields.

depending exactly on what you need the correct path is:

a) use datediff as in (if you need dates)

select getdate() /*fulldatetime*/, cast(datediff(day,0,getdate()) as datetime) /*justdate*/, getdate()-cast(datediff(day,0,getdate()) as datetime) /*justtime*/

b) use convert as in (if your ok with strings)

select getdate() /*fulldatetime*/, convert( varchar(20),getdate(),102) /*justdate*/, convert( varchar(20),getdate(),108) /*justtime*/

a) is much faster then (b) and much much faster then datepart solution.

|||

Thanks very much,