Filling a dataset with more than one table

Please take what I say here with a grain of salt, but I am not very familiar with .NET. However, it sounds like you have two tables and you want to have some relationship between them. If so, then forming a join and using data shaping is the approach that you should use here. Otherwise, how will you make the data sources relate to each other?

FtB

I am also thinking that what you're doing here, if you get past the syntax issues, is that the news will overwrite the events.

FtB

The two tables are not related.  I just thought it would be easier to deal with the information if they were in separate tables.  The events bit goes like this:

          <asp:label id="Results" runat="server"/>
          <asp:panel id="EventsPanel" runat="server">
          <asp:datagrid id="EventsGrid" runat="server" autogeneratecolumns="false" border="0" align="center" cellspacing="5" font-bold="true" font-name="Verdana, Arial, Helvetica, sans-serif" font-size="10" forecolor="#999999">
          <headerstyle font-bold="true" font-name="Verdana, Arial, Helvetica, sans-serif" font-size="10" forecolor="#CC0000"/>
          <columns>
          <asp:hyperlinkcolumn headertext="EVENT" datanavigateurlfield="EventID" datanavigateurlformatstring="javascript:var w=window.open('eventdetails.aspx?EventID={0}','popup','scrollbars=1,width=400,height=350');" datatextfield="Title"/>
          <asp:boundcolumn headertext="DATE" DataFormatString="{0:d}" datafield="Date"/>
          </columns>
          </asp:datagrid>
          </asp:panel>

(sorry for the way the line breaks occur!)

I then wanted to take the most up-to-date entry in the News table and place that in my page somewhere else.

Initially I wanted to have two datasets and deal with it like that, but I don't think thats possible. (I'm quite new to this!)

Does that make sense?

James

I see a few options:

1) fill the data grid with the Events with one adaptor, and then fill a field with the top event from news in another

2) create separate adaptors for Events and News creating a data grid for each.


FtB

The latter option sounds better.  How do I achieve this.  Is my initial code close?

Cheers

James

This should do it as long as you have two separate datagrids, one called events and the other called news:

dim dbconn,sql,dbcomm,dbread

dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\inetpub\wwwroot\youth\db\youthdb.mdb;")
dbconn.Open()

sql="SELECT * FROM Events ORDER BY Date"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()

Events.DataSource=dbread
Events.DataBind()


sql="SELECT * FROM News ORDER BY Date"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()

News.DataSource=dbread
News.DataBind()

dbread.Close()
dbconn.Close()

Thanks for that FtB.  I've tried to implement your suggested code (see below), but I have an error using the DataReader object in the instant when I declare it;

Compiler Error Message: BC30390: 'System.Data.OleDb.OleDbDataReader.Private Sub New(connection As System.Data.OleDb.OleDbConnection, command As System.Data.OleDb.OleDbCommand, depth As Integer, chapter As System.IntPtr)' is not accessible in this context because it is 'Private'.

Any ideas where I may be going wrong?

James

====================

Sub Page_Load(Sender As Object, E As EventArgs)

If Not IsPostBack Then

  Dim objConnection As OleDbConnection = New OleDbConnection
  Dim objCommand As OleDbCommand = New OleDbCommand
  Dim objDataReader As OleDbDataReader = New OleDbDataReader
  Dim connString, SQL1, SQL2 As String

  connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\inetpub\wwwroot\youth\db\youthdb.mdb;"
  objConnection = New OleDbConnection(connString)
  objConnection.Open()

  SQL1 = "SELECT * FROM Events ORDER BY Date"
  SQL2 = "SELECT * FROM News ORDER BY Date"

  objCommand = New OleDbCommand(SQL1, objConnection)
  objDataReader = objCommand.ExecuteReader()
  EventsGrid.DataSource = objDataReader
  EventsGrid.DataBind()

  objCommand = New OleDbCommand(SQL2, objConnection)
  objDataReader = objCommand.ExecuteReader()
  NewsGrid.DataSource = objDataReader
  NewsGrid.DataBind()

  objConnection.Close()

  If EventsGrid.Rows.Count < 1 Then
    Results.Text = "Unfortunately there are currently no events listed. If you you" & _
      " are an organisation and would like to submit one, please <a href='Contact-Us.htm'>" & _
      " contact us</a>."
    EventsPanel.Visible = False
  Else
    Results.Text = "Not sure what events are taking place? Then look no further!" & _
      " <p>The events listed below have all been entered by registered youth organisations" & _
      " and are open to everyone. If you are involved with an organisation and want to" & _
      " advertise a forth coming event, please <a href='Contact-Us.htm'>contact us</a>.<p>"
  End If

End If

End Sub

Take a look here for an example/tutorial--perhaps you can model your code after that?

http://www.w3schools.com/aspnet/showasp.asp?filename=demo_dbconn_datalist

FtB

Certainly a good guide.  I think yours/my code is more or less the same, with the exception being that I have Explicit set to true:

<%@ Page Explicit="True" Language="VB" Debug="True" %>

and in turn;

  Dim objConnection As OleDbConnection = New OleDbConnection
  Dim objCommand As OleDbCommand = New OleDbCommand
  Dim objDataReader As OleDbDataReader = New OleDbDataReader
  Dim connString, SQL1, SQL2 As String

Compared to;

  dim dbconn,sql,dbcomm,dbread

In the above, what is dbread?  A string?

Why is OleDbDataReader private?

I am afraid that I can't help much further--I am more of an ASP rather than ASP.net person, so I haven't played with this much. I knew just enough to spot a few problems in your original code.

FtB

This Question is now classified abandoned as no comment has been added to this question in more than 21 days.

I will leave the following recommendation on this question to the moderators in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed at the discretion of the moderator.

vnvk
EE Cleanup Volunteer