multiple tables in a single dataset

  • Thread starter Thread starter Joon Park via .NET 247
  • Start date Start date
J

Joon Park via .NET 247

(Type your message here)

--------------------------------
From: Joon Park

Hi I am very new to ASP.NET and database access. Ive triednumerous times however ive beed getting a data type mismatcherror and i am so lost. my page is suppose to display recordsfrom a database using dataset with multiple tables here is thecode below


<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<%@ Page Language="VB" Debug="true" %>


<html>
<SCRIPT language="VB" runat="server" >
Dim objConnection As OleDbConnection
Dim strConnection As String
Dim objAdapter As OleDbDataAdapter
Dim SelectCmd As String

sub Page_load()
Dim BID As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; DataSource=C:\Inetpub\wwwroot\test\test.mdb"

BID = Request.QueryString("BID")

If Not BID = "" Then
B_ID.Value = Request.QueryString("BID")
GetName_Click()
End IF
End sub

Sub GetName_Click()

SelectCmd = "SELECT B.NAME, ((A.@Pref1 * .6) + (A.@Pref2* .3) + (A.@Pref3 * .1)) AS SCORE FROM SD_CUST_SURVEY AS A,SD_CONTACT_INFO AS B WHERE (A.BUSINESS_ID = B.BUSINESS_ID) ANDA.BUSINESS_ID IN (@BID)ORDER BY ((A.@Pref1 * .6) + (A.@Pref2 *.3) + (A.@Pref3 * .1)) DESC"

objConnection = New OleDbConnection(strConnection)
objAdapter = New OleDbDataAdapter(SelectCmd,objConnection)

objAdapter.SelectCommand.Parameters.Add(NewOleDbParameter("@BID", OleDbType.Char))
objAdapter.SelectCommand.Parameters("@BID").Value =B_ID.Value
objAdapter.SelectCommand.Parameters.Add(NewOleDbParameter("@Pref1", OleDbType.Char))
objAdapter.SelectCommand.Parameters("@Pref1").Value =P1.Value
objAdapter.SelectCommand.Parameters.Add(NewOleDbParameter("@Pref2", OleDbType.Char))
objAdapter.SelectCommand.Parameters("@Pref2").Value =P2.Value
objAdapter.SelectCommand.Parameters.Add(NewOleDbParameter("@Pref3", OleDbType.Char))
objAdapter.SelectCommand.Parameters("@Pref3").Value =P3.Value


Dim DS As New DataSet()
' fill data set
objAdapter.Fill(DS, "SD_CUST_SURVEY")


' bind to data grid
MyDataGrid.DataSource =DS.Tables("SD_CUST_SURVEY").DefaultView
MyDataGrid.DataBind()
End Sub

</SCRIPT>

HELP!
thanks
 
You are only calling Fill for one table and your select command doesn't
specify more than one query so you are only getting one table back. Where
are you getting the exception? put a breakpoint on page load and step
through it and see where you are getting the exeption.

Also, are those parameters in your query or field names? If they are field
names, you really may want to change them to a non reserved character
fields. That's almost surely the problem b/ cfrom the usage, I'm guessing
they are field names. Parameters in SQL must begin with the @ symbol so I
think it's probably getting confused there. I tried creating a field with
that name and the adapter wraps it with a [] symbo [@fieldname] , if I take
off the [] then it won't fill and blows up when I try to fill it. Try
wrapping each of the fieldnames in [] and see if that doesn't fix it, but if
it does, I'd really recommend changing those names ASAP.

HTH,

Bill
(Type your message here)

--------------------------------
From: Joon Park

Hi I am very new to ASP.NET and database access. Ive tried numerous times
however ive beed getting a data type mismatch error and i am so lost. my
page is suppose to display records from a database using dataset with
multiple tables here is the code below


<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<%@ Page Language="VB" Debug="true" %>


<html>
<SCRIPT language="VB" runat="server" >
Dim objConnection As OleDbConnection
Dim strConnection As String
Dim objAdapter As OleDbDataAdapter
Dim SelectCmd As String

sub Page_load()
Dim BID As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\Inetpub\wwwroot\test\test.mdb"

BID = Request.QueryString("BID")

If Not BID = "" Then
B_ID.Value = Request.QueryString("BID")
GetName_Click()
End IF
End sub

Sub GetName_Click()

SelectCmd = "SELECT B.NAME, ((A.@Pref1 * .6) + (A.@Pref2 * .3) +
(A.@Pref3 * .1)) AS SCORE FROM SD_CUST_SURVEY AS A, SD_CONTACT_INFO AS B
WHERE (A.BUSINESS_ID = B.BUSINESS_ID) AND A.BUSINESS_ID IN (@BID)ORDER BY
((A.@Pref1 * .6) + (A.@Pref2 * .3) + (A.@Pref3 * .1)) DESC"

objConnection = New OleDbConnection(strConnection)
objAdapter = New OleDbDataAdapter(SelectCmd, objConnection)

objAdapter.SelectCommand.Parameters.Add(New OleDbParameter("@BID",
OleDbType.Char))
objAdapter.SelectCommand.Parameters("@BID").Value = B_ID.Value
objAdapter.SelectCommand.Parameters.Add(New OleDbParameter("@Pref1",
OleDbType.Char))
objAdapter.SelectCommand.Parameters("@Pref1").Value = P1.Value
objAdapter.SelectCommand.Parameters.Add(New OleDbParameter("@Pref2",
OleDbType.Char))
objAdapter.SelectCommand.Parameters("@Pref2").Value = P2.Value
objAdapter.SelectCommand.Parameters.Add(New OleDbParameter("@Pref3",
OleDbType.Char))
objAdapter.SelectCommand.Parameters("@Pref3").Value = P3.Value


Dim DS As New DataSet()
' fill data set
objAdapter.Fill(DS, "SD_CUST_SURVEY")


' bind to data grid
MyDataGrid.DataSource = DS.Tables("SD_CUST_SURVEY").DefaultView
MyDataGrid.DataBind()
End Sub

</SCRIPT>

HELP!
thanks
 
Back
Top