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;Initial Catalog=XXXXXX;User ID=XXXXXX;password=XXXXXX;")
Dim objCmd As New Data.SqlClient.SqlDataAdapter(strSQL, objConn)
objConn.Open()
objCmd.Fill(objDataSet)

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

For Each objDT In objDataSet.Tables(0).Rows
For Each objDC In objDataSet.Tables(0).Columns
txtRecID = objDataSet.Tables(0).Rows(0)("RID").ToString()
txtDate = objDataSet.Tables(0).Rows(0)("Date").ToString()
txtShift = objDataSet.Tables(0).Rows(0)("Shift_ID").ToString( )
txtType = objDataSet.Tables(0).Rows(0)("Xref_ID").ToString()
TxtOccNotes = objDataSet.Tables(0).Rows(0)("Notes").ToString()
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.Tables(0)

For Each objDT In objDataSet.Tables(0).Rows
'do something with the rows
Next
 

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)(columnname)

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.Tables(0).Rows
txtRecID.Text = objDataSet.Tables(0).Rows(i)("RID").ToString()
txtDate.Text = objDataSet.Tables(0).Rows(i)("Date").ToString()
txtShift.Text = objDataSet.Tables(0).Rows(i)("Shift_ID").ToString( )
txtType.Text = objDataSet.Tables(0).Rows(i)("Xref_ID").ToString()
TxtOccNotes.Text = objDataSet.Tables(0).Rows(i)("Notes").ToString()
Next

[BezerkRogue]

This is the page code. Could this be causing my problem?
<table style="width: 100%; text-align: center;" id="ManpowerReport" 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="ManpowerReport" 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

For Each objDT In objDataSet.Tables(0).Rows
txtRecID.Text = objDataSet.Tables(0).Rows(i)("RID").ToString()
txtDate.Text = objDataSet.Tables(0).Rows(i)("Date").ToString()
txtShift.Text = objDataSet.Tables(0).Rows(i)("Shift_ID").ToString()
txtType.Text = objDataSet.Tables(0).Rows(i)("Xref_ID").ToString()
TxtOccNotes.Text = objDataSet.Tables(0).Rows(i)("Notes").ToString()
Next
 

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

For Each objDT In objDataSet.Tables(0).Rows
txtRecID.Text = objDataSet.Tables(0).Rows(i)("RID").ToString()
txtDate.Text = objDataSet.Tables(0).Rows(i)("Date").ToString()
txtShift.Text = objDataSet.Tables(0).Rows(i)("Shift_ID").ToString()
txtType.Text = objDataSet.Tables(0).Rows(i)("Xref_ID").ToString()
TxtOccNotes.Text = objDataSet.Tables(0).Rows(i)("Notes").ToString()
Next
 

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" AutoGenerateColumns="False" DataKeyNames="RID" DataSourceID="SqlDataSource1" Width="1215px">
<Columns>
<asp:BoundField DataField="RID" HeaderText="Record ID:" InsertVisible="False" ReadOnly="True"
SortExpression="RID" />
<asp:BoundField DataField="Date" HeaderText="Date:" SortExpression="Date" />
<asp:BoundField DataField="Shift_ID" HeaderText="Shift:" SortExpression="Shift_ID" />
<asp:BoundField DataField="Xref_ID" HeaderText="Type Of Occurence:" SortExpression="Xref_ID" />
<asp:BoundField DataField="Notes" HeaderText="Notes:" SortExpression="Notes" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SomeConnectionString %>"
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))">
<SelectParameters>
<asp:SessionParameter Name="Shift_ID" SessionField="Shift_ID" Type="Double" />
<asp:SessionParameter Name="Xref_ID" SessionField="Type" Type="Double" />
<asp:SessionParameter Name="Date" SessionField="Date" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>

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.InvalidCastException: 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:


[InvalidCastException: Object must implement IConvertible.]
System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +2514354
System.Web.UI.WebControls.Parameter.GetValue(Objec t value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges) +264
System.Web.UI.WebControls.Parameter.get_ParameterV alue() +66
System.Web.UI.WebControls.ParameterCollection.GetV alues(HttpContext context, Control control) +254
System.Web.UI.WebControls.SqlDataSourceView.Initia lizeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) +276
System.Web.UI.WebControls.SqlDataSourceView.Execut eSelect(DataSourceSelectArguments arguments) +754
System.Web.UI.DataSourceView.Select(DataSourceSele ctArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.Perform Select() +149
System.Web.UI.WebControls.BaseDataBoundControl.Dat aBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.Ens ureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundContro l.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.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" AutoGenerateColumns="False" DataKeyNames="RID" DataSourceID="SqlDataSource1" Width="1134px" GridLines="Both" EnableViewState="true" Visible="true">
<Columns>
<asp:BoundField DataField="RID" HeaderText="RID" Visible="true" ReadOnly="True"
SortExpression="RID" />
<asp:BoundField DataField="Notes" HeaderText="Notes" Visible="true" SortExpression="Notes" />
</Columns>
</asp:gridview>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PerfDataConnectionString %>"
SelectCommand="SELECT [RID], [Notes] FROM [Safety_data] WHERE (([Date] = @Date) AND ([Shift_ID] = @Shift_ID) AND ([Xref_ID] = @Xref_ID))" SelectCommandType="Text">
<SelectParameters>
<asp:QueryStringParameter Name="Date" QueryStringField="strSelDate" Type="DateTime" />
<asp:QueryStringParameter Name="Shift_ID" QueryStringField="strShiftPull" Type="Decimal" />
<asp:QueryStringParameter Name="Xref_ID" QueryStringField="strTypePull" Type="Decimal" />
</SelectParameters>
</asp:SqlDataSource>

[Plater]

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