What's wrong with this code? [Archive]

brianh

Jun 5th, 2003, 05:55 PM

I am trying to bind multiple tables to one dataset and use these ables to fill select boxes. But the following code is not working.

<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<script runat="server">

'Page load event of the web page.
'This is similar to the Form_Load event in VB.'
Protected Sub Page_Load(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles MyBase.Load
Dim id as string
id = request.querystring("locID")
Dim myConnection as New SQLConnection("Server=DSHSDBLCY4000A;Database=NIS;UID=inet_nis_user;PWD=**********;")
myConnection.Open

Dim strSQL as String
strSQL = "select SYS_Main.LocationID, SYS_Main.Org, SYS_Main.Region from SYS_Main where SYS_Main.LocationID = " & chr(39) & id & chr(39) & "; Select * from SYS_Monitors where SYS_Monitors.LocationID = " & chr(39) & id & chr(39) & ";"

Dim DS as New DataSet()
Dim cnAdapter as new SqlDataAdapter(strSQL, myConnection)
cnAdapter.Fill(DS)

'Dim myCommand as New SQLCommand(strSQL,myConnection)
'Dim adapter As New SqlDataAdapter()

'Dim myDataReader as SQLDataReader
'myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

'dgMain.DataSource = myDataReader
'dgMain.DataBind()

myConnection.Close()

With ID1
.DataSource = DS.Tables("SYS_Main")
.DataValueField = "LocationID"
.DataTextField = "PriorityTitle"
.DataBind()
End With

With ID2
.DataSource = DS.Tables("SYS_Monitors")
.DataValueField = "Quantity"
.DataTextField = "Status"
.DataBind()
End With

End Sub

</script>
<html>
<head>
<title>Summary for # <%=request.querystring("locID")%></title>
</head>
<body>
<form runat="server">
<p>
<asp:Dropdownlist id="ID1"
Runat="server"
EnableViewState="False" >
</asp:Dropdownlist>
<asp:Dropdownlist id="ID2"
Runat="server"
EnableViewState="False" >
</asp:Dropdownlist>

</p>
</form>
</body>
</html>

Lethal

Jun 6th, 2003, 07:29 AM

You can not reference those tables by their name without explicitly telling the dataset what the table names are. Try using the indexer.

DataSource = DS.Tables(0)

brianh

Jun 6th, 2003, 09:29 AM

I did that and got the following error:

DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name PriorityTitle.

brianh

Jun 6th, 2003, 09:33 AM

What I am trying to do is the following:

I have an asp app that inputs to about 30 tables. I would like to produce a summary at the end of the app, which shows what they have input. Traditionally, I would open and close a recordset 30 times and write out the html and results of the record set for each return. I heard that with ado.net you could do some cool things with the tables collection. But, I have been unable to really make it work. Am I stuck with traditional ASP, or is there a better way to do it in .net?

Thanks!