Listing DataSet Records

[BezerkRogue]

I am trying to get a listing of all records in a dataset to display in a table. I have tried several options I have found through research and get no where. Anyone have any easier ideas? Being new to programming in general, this have given me fits. Here's the last thing I tried:

strSQL = "Select * from Safety_Data where xref_id=" & strTypePull & "and Shift_ID=" & strShiftPull & "and Date='" & strSelDate & "'"
Dim objDataSet As New Data.DataSet
Dim objConn As New Data.SqlClient. SqlConnection(" Data Source=XXXXXX;I nitial Catalog=XXXXXX; User ID=XXXXXX;passw ord=XXXXXX;")
Dim objCmd As New Data.SqlClient. SqlDataAdapter( strSQL, objConn)
objConn.Open()
objCmd.Fill(obj DataSet)

Dim objDC As Data.DataColumn
Dim objDT As Data.DataRow

For Each objDT In objDataSet.Tabl es(0).Rows
For Each objDC In objDataSet.Tabl es(0).Columns
txtRecID = objDataSet.Tabl es(0).Rows(0)(" RID").ToString( )
txtDate = objDataSet.Tabl es(0).Rows(0)(" Date").ToString ()
txtShift = objDataSet.Tabl es(0).Rows(0)(" Shift_ID").ToSt ring()
txtType = objDataSet.Tabl es(0).Rows(0)(" Xref_ID").ToStr ing()
TxtOccNotes = objDataSet.Tabl es(0).Rows(0)(" Notes").ToStrin g()
Next
Next

[Plater]

What are you looking for? All the rows?

Based on your query it would appear that objDataSet will only have one table so
objDataSet.Tabl es(0)

[code=vbnet]
For Each objDT In objDataSet.Tabl es(0).Rows
'do something with the rows
Next
[/code]


What is the problem, it looks like you got it?

[nateraaaa]

You don't need the foreach loop that loops through the columns. The columns can be referenced by looping through the rows as you are doing.

row(i)(columnna me)

Remove the foreach loop that loops through the columns and the code should work properly for you.

Nathan

[BezerkRogue]

This is the changed code. I have 3 records in dataset but it only displays one.

Dim objDC As Data.DataColumn
Dim objDT As Data.DataRow
Dim i As Integer

For Each objDT In objDataSet.Tabl es(0).Rows
txtRecID.Text = objDataSet.Tabl es(0).Rows(i)(" RID").ToString( )
txtDate.Text = objDataSet.Tabl es(0).Rows(i)(" Date").ToString ()
txtShift.Text = objDataSet.Tabl es(0).Rows(i)(" Shift_ID").ToSt ring()
txtType.Text = objDataSet.Tabl es(0).Rows(i)(" Xref_ID").ToStr ing()
TxtOccNotes.Tex t = objDataSet.Tabl es(0).Rows(i)(" Notes").ToStrin g()
Next

[BezerkRogue]

This is the page code. Could this be causing my problem?
<table style="width: 100%; text-align: center;" id="ManpowerRep ort" border="1">
<tr>
<td style="text-align: center; width: 75px;">
Record ID:
</td>
<td style="text-align: center; width: 159px;">
Date of Occurence:
</td>
<td style="text-align: center; width: 30px;">
Shift:
</td>
<td style="text-align: center; width: 148px;">
Type of Occurence:
</td>
<td style="text-align: center;">
Description of Occurence:
</td>
</tr>
<tr>
<td style="text-align: left; width: 75px;">
<asp:Label ID="txtRecID" runat="server"> </asp:Label>
</td>
<td style="text-align: left; width: 159px;">
<asp:Label ID="txtDate" runat="server"> </asp:Label>
</td>
<td style="text-align: left; width: 30px;">
<asp:Label ID="txtShift" runat="server"> </asp:Label>
</td>
<td style="width: 148px">
<asp:Label ID="txtType" runat="server"> </asp:Label>
</td>
<td>
<asp:Label ID="TxtOccNotes " runat="server"> </asp:Label>
</td>
</tr>
<tr>
<tr>
<td colspan="3" style="font-weight: bold; text-align: right">
Enter Record Number:</td>
<td style="width: 148px">
<asp:TextBox ID="txtSelect" runat="server"> </asp:TextBox>
</td>
<td align="left">
<asp:Button ID="cmdSubmit" runat="server" Text="Submit" /></td>
</tr>

</table>

[balabaster]

This is the page code. Could this be causing my problem?
<table style="width: 100%; text-align: center;" id="ManpowerRep ort" border="1">
<tr>
<td style="text-align: center; width: 75px;">
Record ID:
</td>
<td style="text-align: center; width: 159px;">
Date of Occurence:
</td>
<td style="text-align: center; width: 30px;">
Shift:
</td>
<td style="text-align: center; width: 148px;">
Type of Occurence:
</td>
<td style="text-align: center;">
Description of Occurence:
</td>
</tr>
<tr>
<td style="text-align: left; width: 75px;">
<asp:Label ID="txtRecID" runat="server"> </asp:Label>
</td>
<td style="text-align: left; width: 159px;">
<asp:Label ID="txtDate" runat="server"> </asp:Label>
</td>
<td style="text-align: left; width: 30px;">
<asp:Label ID="txtShift" runat="server"> </asp:Label>
</td>
<td style="width: 148px">
<asp:Label ID="txtType" runat="server"> </asp:Label>
</td>
<td>
<asp:Label ID="TxtOccNotes " runat="server"> </asp:Label>
</td>
</tr>
<tr>
<tr>
<td colspan="3" style="font-weight: bold; text-align: right">
Enter Record Number:</td>
<td style="width: 148px">
<asp:TextBox ID="txtSelect" runat="server"> </asp:TextBox>
</td>
<td align="left">
<asp:Button ID="cmdSubmit" runat="server" Text="Submit" /></td>
</tr>

</table>

Um - side thought that may be way easier for you (depending on the version of .NET you're running) - what about using the GridView? It seems to me that it's way easier to do all the hookup to your dataset using one of those than the way you're doing it...just a thought. Maybe something to consider down the road...

[mzmishra]

why do not you use datagrid and get table like dispaly .
It will be easier for you

[Plater]

I see this all the time
[code=vbnet]
For Each objDT In objDataSet.Tabl es(0).Rows
txtRecID.Text = objDataSet.Tabl es(0).Rows(i)(" RID").ToString( )
txtDate.Text = objDataSet.Tabl es(0).Rows(i)(" Date").ToString ()
txtShift.Text = objDataSet.Tabl es(0).Rows(i)(" Shift_ID").ToSt ring()
txtType.Text = objDataSet.Tabl es(0).Rows(i)(" Xref_ID").ToStr ing()
TxtOccNotes.Tex t = objDataSet.Tabl es(0).Rows(i)(" Notes").ToStrin g()
Next
[/code]

And I always wonder how anyone thinks this will work. Its like you've never been taught program flow.

you are looping through a collection and setting your values to THE SAME OBJECT! The only values you will see will be the last set it had looped through when it exits the loop.

Consider:
int x=0;
for(int i=0;i<10;i++)
{
x=i;
}

You would not expect x to = 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 all at the same time, so why would you expect to see all the string values all at the same time?
x will = 9, which was the last value assigned to it.

[BezerkRogue]

I see this all the time
[code=vbnet]
For Each objDT In objDataSet.Tabl es(0).Rows
txtRecID.Text = objDataSet.Tabl es(0).Rows(i)(" RID").ToString( )
txtDate.Text = objDataSet.Tabl es(0).Rows(i)(" Date").ToString ()
txtShift.Text = objDataSet.Tabl es(0).Rows(i)(" Shift_ID").ToSt ring()
txtType.Text = objDataSet.Tabl es(0).Rows(i)(" Xref_ID").ToStr ing()
TxtOccNotes.Tex t = objDataSet.Tabl es(0).Rows(i)(" Notes").ToStrin g()
Next
[/code]

And I always wonder how anyone thinks this will work. Its like you've never been taught program flow.

you are looping through a collection and setting your values to THE SAME OBJECT! The only values you will see will be the last set it had looped through when it exits the loop.

Consider:
int x=0;
for(int i=0;i<10;i++)
{
x=i;
}

You would not expect x to = 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 all at the same time, so why would you expect to see all the string values all at the same time?
x will = 9, which was the last value assigned to it.

The truth is I haven't been. I started my career as an infrastructure engineer and have recently been pushed into ASP.Net development by my company....all with the courtesy of no training.

I entertained something like what you have listed above, but am not sure how to implement it with the code already in place.

[BezerkRogue]

I have changed this to a gridview with the following code:
<asp:GridView ID="GridView1" runat="server" AutoGenerateCol umns="False" DataKeyNames="R ID" DataSourceID="S qlDataSource1" Width="1215px">
<Columns>
<asp:BoundFie ld DataField="RID" HeaderText="Rec ord ID:" InsertVisible=" False" ReadOnly="True"
SortExpression= "RID" />
<asp:BoundFie ld DataField="Date " HeaderText="Dat e:" SortExpression= "Date" />
<asp:BoundFie ld DataField="Shif t_ID" HeaderText="Shi ft:" SortExpression= "Shift_ID" />
<asp:BoundFie ld DataField="Xref _ID" HeaderText="Typ e Of Occurence:" SortExpression= "Xref_ID" />
<asp:BoundFie ld DataField="Note s" HeaderText="Not es:" SortExpression= "Notes" />
</Columns>
</asp:GridView>
<asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server" ConnectionStrin g="<%$ ConnectionStrin gs:SomeConnecti onString %>"
SelectCommand=" SELECT [RID], [Notes], [Date], [Shift_ID], [Xref_ID] FROM [Safety_data] WHERE (([Shift_ID] = @Shift_ID) AND ([Xref_ID] = @Xref_ID) AND ([Date] = @Date))">
<SelectParamete rs>
<asp:SessionPar ameter Name="Shift_ID" SessionField="S hift_ID" Type="Double" />
<asp:SessionPar ameter Name="Xref_ID" SessionField="T ype" Type="Double" />
<asp:SessionPar ameter Name="Date" SessionField="D ate" Type="DateTime" />
</SelectParameter s>
</asp:SqlDataSour ce>

It works fine in the design view but gives me an Object must implement IConvertible when I run it in the site. I'm am now totally lost.

[Plater]

Somewhere, you are assinging a valuetype to an object that it is not the correct valuetype.
Like if something is an int and you are saying it's DateTime, I believe you can see that error.

Do you know what object or line of code the exception is pointing to?

[BezerkRogue]

Somewhere, you are assinging a valuetype to an object that it is not the correct valuetype.
Like if something is an int and you are saying it's DateTime, I believe you can see that error.

Do you know what object or line of code the exception is pointing to?

This is the stack trace:

Object must implement IConvertible.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidC astException: Object must implement IConvertible.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[InvalidCastExce ption: Object must implement IConvertible.]
System.Convert. ChangeType(Obje ct value, TypeCode typeCode, IFormatProvider provider) +2514354
System.Web.UI.W ebControls.Para meter.GetValue( Object value, String defaultValue, TypeCode type, Boolean convertEmptyStr ingToNull, Boolean ignoreNullableT ypeChanges) +264
System.Web.UI.W ebControls.Para meter.get_Param eterValue() +66
System.Web.UI.W ebControls.Para meterCollection .GetValues(Http Context context, Control control) +254
System.Web.UI.W ebControls.SqlD ataSourceView.I nitializeParame ters(DbCommand command, ParameterCollec tion parameters, IDictionary exclusionList) +276
System.Web.UI.W ebControls.SqlD ataSourceView.E xecuteSelect(Da taSourceSelectA rguments arguments) +754
System.Web.UI.D ataSourceView.S elect(DataSourc eSelectArgument s arguments, DataSourceViewS electCallback callback) +17
System.Web.UI.W ebControls.Data BoundControl.Pe rformSelect() +149
System.Web.UI.W ebControls.Base DataBoundContro l.DataBind() +70
System.Web.UI.W ebControls.Grid View.DataBind() +4
System.Web.UI.W ebControls.Base DataBoundContro l.EnsureDataBou nd() +82
System.Web.UI.W ebControls.Comp ositeDataBoundC ontrol.CreateCh ildControls() +69
System.Web.UI.C ontrol.EnsureCh ildControls() +87
System.Web.UI.C ontrol.PreRende rRecursiveInter nal() +41
System.Web.UI.C ontrol.PreRende rRecursiveInter nal() +161
System.Web.UI.C ontrol.PreRende rRecursiveInter nal() +161
System.Web.UI.P age.ProcessRequ estMain(Boolean includeStagesBe foreAsyncPoint, Boolean includeStagesAf terAsyncPoint) +1360




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.507 27.832; ASP.NET Version:2.0.507 27.832

[BezerkRogue]

I found the problem. Now I see no data even though the variables are passing and there are records available.

<asp:gridview runat="server" ID="RecordView " AutoGenerateCol umns="False" DataKeyNames="R ID" DataSourceID="S qlDataSource1" Width="1134px" GridLines="Both " EnableViewState ="true" Visible="true">
<Columns>
<asp:BoundFie ld DataField="RID" HeaderText="RID " Visible="true" ReadOnly="True"
SortExpression= "RID" />
<asp:BoundFie ld DataField="Note s" HeaderText="Not es" Visible="true" SortExpression= "Notes" />
</Columns>
</asp:gridview>
<asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server" ConnectionStrin g="<%$ ConnectionStrin gs:PerfDataConn ectionString %>"
SelectCommand=" SELECT [RID], [Notes] FROM [Safety_data] WHERE (([Date] = @Date) AND ([Shift_ID] = @Shift_ID) AND ([Xref_ID] = @Xref_ID))" SelectCommandTy pe="Text">
<SelectParamete rs>
<asp:QueryStrin gParameter Name="Date" QueryStringFiel d="strSelDate " Type="DateTime" />
<asp:QueryStrin gParameter Name="Shift_ID" QueryStringFiel d="strShiftPull " Type="Decimal" />
<asp:QueryStrin gParameter Name="Xref_ID" QueryStringFiel d="strTypePul l" Type="Decimal" />
</SelectParameter s>
</asp:SqlDataSour ce>

[Plater]

Hmm, all of mine use auto-generate columns = true?
But I don't use any of those bound fields and stuff.