Accessing data in a column

I have a SQLDataSource returning data for a formview. This should be a simple question that I cannot find an answer to. The SQLDataSource is returning only one row of data. How do I access the value of one of the columns?

Thanks for the information.

The following code example iterates through aDataReader object, and returns two columns from each row.

SqlDataReader reader = command.ExecuteReader();if (reader.HasRows)while (reader.Read()) Console.WriteLine("\t{0}\t{1}", reader.GetInt32(0), reader.GetString(1));else Console.WriteLine("No rows returned.");reader.Close();

|||So should I just use a datareader (built in a separate class file) instead of using the SQLDataSource?|||SQLDataSource is always used to bind to a control . If you want to fetch data from DB , DataReader is a better choice .

accessing blank values in a table

Hi,
I am trying to format a column of varchar which has decimal values..Since it
is varchar I was not able to format it directlyu, so had to mulitply and
divide each value by 100 and then apply formatting..Not all the rows of the
table have data, some are blank..and these show up as "#Error" on the
report.I try not to do any operations on them by using "Isnothing" or
comparing each value with "" in the IIf statment..But neither works...Can
someone tell me how I can access the blanks in the table...
ThanksTo see if the column is empty in the IIF statement, use the following syntax
IIF ("" & MyColumn <> "", the rest ...)
It takes an empty string and concatenates the column value to the empty
string, if the column is empty, it results in an empty string.
"Dolly" <Dolly@.discussions.microsoft.com> wrote in message
news:FD0B22DD-BA90-4B2C-9495-A5E783B0467B@.microsoft.com...
> Hi,
> I am trying to format a column of varchar which has decimal values..Since
it
> is varchar I was not able to format it directlyu, so had to mulitply and
> divide each value by 100 and then apply formatting..Not all the rows of
the
> table have data, some are blank..and these show up as "#Error" on the
> report.I try not to do any operations on them by using "Isnothing" or
> comparing each value with "" in the IIf statment..But neither works...Can
> someone tell me how I can access the blanks in the table...
> Thanks

Access to TEXT column

Hi,

I have a table with a TEXT column and this column contains an XML document. I'm developing a TSQL stored procedure which reads the content of this column and accesses the values in the XML elements but I have a lot of problems...

1) How can I read the text column and store the value in a variable? I tried

declare @.a varchar(2048)
set @.a = (SELECT TEXT_COLUMN FROM MY_TABLE)

but it returns

Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

I tried also with the READTEXT function but I just can't find how to store data read in a variable...

2) which length should I use for the VARCHAR variable which will store the data? Is it possible not to specify a length with SQL Server 7 or 2000?

Thanks!

Andrea

In SQL Server 7/2000 local variables cannot have a text data type. My advice would be if you know the length of the xml data is not going to be greater than 4000 chars (aprox.), use an NVARCHAR, that is going to allow you to be more flexible with your code.

Hope this helps,

Roberto Hernández-Pou
http://community.rhpconsulting.net

|||

you can try this

declare @.a varchar(8000)

set @.a = (SELECT convert(varchar(8000),text1) FROM texttest where text1like'some%' )

select @.a

You 'll be able to use text column in procedures but it has to converted to an accepted datatype in procedures

|||Just keep in mind that a decent sized XML document can easily go past 8000 characters causing any extra data to be truncated.|||

Hi,

thanks Gopi, Roberto and Whitney! Useful answers and comments!

Andrea

Access to group field value from the child element

I have a matrix and have a column groups on month and products ( I have only
4 products) and am showing the number of products sold in that month. What I
need is to show instead of number products, I need to show the % of sales for
that product as compared to all sales.
I need to access the group total in the child field. If 10 items were sold
in each product, I would like to show 25% (10/40 * 100) in the detail. Is
there any way to access the group total in the child fields.
Any help is appreciated.
--
Thanks
RK BalajiI found the solution.. I just have to use the scope parameter in the count
function to get the value. Thanks.
"RK Balaji" wrote:
> I have a matrix and have a column groups on month and products ( I have only
> 4 products) and am showing the number of products sold in that month. What I
> need is to show instead of number products, I need to show the % of sales for
> that product as compared to all sales.
> I need to access the group total in the child field. If 10 items were sold
> in each product, I would like to show 25% (10/40 * 100) in the detail. Is
> there any way to access the group total in the child fields.
> Any help is appreciated.
> --
> Thanks
> RK Balaji

ACCESS SQL COLUMN DATA TYPE

HELLO

i have an sql server database, with a table an some columns.

how can i get the data type of each column and the lengh define in the database table?

Are you working with SQL Server 2000 or 2005?

|||

sql 2000

|||

Fire up profiler, filter to your spid, and run sp_help on a table. See what SQL Server does. You can copy the code from what SQL Server does and use it for yourself. To start with, check out syscolumns in books online.

|||

Or, you could use the INFORMATION_SCHEMA views to do it:

SELECT *FROM INFORMATION_SCHEMA.columnsWHERE table_Name =table  

HTH,

Stu

Access second grid from SQL stored procedure

Hi:
I have a SQL stored procedure that returns 2 tables. How can I access the
fields from the second table. even though the column names are distinct in
the 2 tables, my reports do not get to the second table's fields. I cannot
join the 2 tables returned. And i cannot have them in 2 separate stored
procedures, because they use the data from the first table.
Please suggest something.
ThanksAre you using temporary tables? Not sure why you can't combine the 2 tables
into a 3rd table to return one result set. Can you explain further?
"NI" wrote:
> Hi:
> I have a SQL stored procedure that returns 2 tables. How can I access the
> fields from the second table. even though the column names are distinct in
> the 2 tables, my reports do not get to the second table's fields. I cannot
> join the 2 tables returned. And i cannot have them in 2 separate stored
> procedures, because they use the data from the first table.
> Please suggest something.
> Thanks|||well my first table is like a summary table, and the second table is like a
detailed table. One issue am having whihc is stopping me from combining both
tables is that the second table has duplicates (whihc I want to show in the
second table but not in the first table). I know SQL RS has the hide
duplicates option, ad if I do a grouping on that particular column it hides
duplicates, but the issue is with the count. If I have just one table (the
one with duplicates in it), I cannot get the right count (without duplicates)
in RS, as it always returns me the count with duplicates. so i had to
separate these two tables.
Also though there a few common columns in these tables, knowing the data I
know it wouldnt make much sense combining them, so I want to leave them as
separate tables.
Right now the workaround I have is have 2 different datasets doing the same
thing, but returning only one of the select statements. But its kinda wierd
to have 2 separate datasets doing the same thing.
Thanks
"phil" wrote:
> Are you using temporary tables? Not sure why you can't combine the 2 tables
> into a 3rd table to return one result set. Can you explain further?
> "NI" wrote:
> > Hi:
> >
> > I have a SQL stored procedure that returns 2 tables. How can I access the
> > fields from the second table. even though the column names are distinct in
> > the 2 tables, my reports do not get to the second table's fields. I cannot
> > join the 2 tables returned. And i cannot have them in 2 separate stored
> > procedures, because they use the data from the first table.
> >
> > Please suggest something.
> >
> > Thanks|||And you never will, except for using aggregation functions.
I think this is a bummer.
Look up SCOPE in the RS help and you will see what I mean.
Here's a sample assuming dataset1 and dataset2:
TextBox1.value = Fields!FirstName.Value
TextBox2.value = Sum(Fields!Income.Value, "dataset2")
BR//Jerry