datasource question

im using this datasource to extract values from a database.

i simply want to extract the values for ratingsum and ratingcount to populate variables so as to be checked within a code loop.

how do i do this? in classis asp would be something like <%=rsRecords(RatingSum)%
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>" SelectCommand="SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE Itemid=100">
</asp:SqlDataSource>

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim dv As System.Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

If dv.Count > 0 Then
Dim myvalue = dv(0).Item("RatingSum")
Dim myvalue2 = dv(0).Item(1) 'orItem("RatingCount")
Label2.Text = CType(myvalue, Int16) & "*************Count=" & CType(myvalue2, Int16)

Else
Label2.Text = "not data!"

End If

End Sub

This way you can send the value back from your code. In this case from the label.text.

You can also use this section within your select parameter to bind your where clause parameter to a textbox control instead of the hard-coded value.

<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="itemId" PropertyName="Text" Type="int16" />

</SelectParameters>

DataSet Xml DateTime incompatible with Sql 2005

All I'm trying to do is simply write out a DataSet in .net 2.0 with
..WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
Mike
You have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike
|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
selectconvert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike
|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegrou ps.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>

DataSet Xml DateTime incompatible with Sql 2005

All I'm trying to do is simply write out a DataSet in .net 2.0 with
.WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
MikeYou have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegroups.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>sql