Data Adapter Fill Method

[michaelzip]

Hi, I'm quite new with Data Adapter on VB.NET. I'm having a problem with retrieving data on different tables.

Example database:
BookInfo[INDENT]BookID: HSD993Z
Title: Introduction to SQL[/INDENT][INDENT]BookID: LDJA293
Title: Advance Computer Programming[/INDENT][INDENT]BookID: KSKL194
Title: Object Oriented Programming[/INDENT]

StudentInfo[INDENT]StudentID: 2938485
FullName: Michael Zip[/INDENT][INDENT]StudentID: 2949284
FullName: Ray Williams[/INDENT]

BorrowerInfo[INDENT]StudentID: 2938485
BookID: KSKL194[/INDENT][INDENT]StudentID: 2938485
BookID: HSD993Z[/INDENT]

To get all the list of the Students who borrowed books, the query would be

SELECT StudentInfo.FullName, BookInfo.Title
FROM BookInfo, StudentInfo, BorrowerInfo
WHERE BookInfo.BookID=BorrowerInfo.BookID AND StudentInfo.StudentID=BorrowerInfo.StudentID;

Now the Fill method on DataAdapter has a parameter of DataSet and SourceTable.
I'm confused on SourceTable, should I write all of the tables I used?
Ex: SourceTable = "BookInfo, StudentInfo, BorrowerInfo"

I can't show my reports.

I don't have problems with listing all records from one table.
Ex: "SELECT * FROM StudentInfo" and the TableSource is "StudentInfo"

Can you please help me?
Thanks in advance

[Pgmer]

Using the joins Get one resultset and fill your adapter with that resultset.

[tomason]

You can use the method:
Dim da As new DataAdapter = DataAdapter();
Dim dt As new DataTable = DataTable();
da.Fill(dt);

More information:
http://www.e-iceblue.com/Knowledgebase/Spire.DataExport/Demos/Demo.html

[geoamins2]

Also, see this query, it may help you.
just copy and paste the given query into

SELECT BorrowerInfo.StudentID, BorrowerInfo.BookID, BookInfo.Title, StudentInfo.FullName FROM BookInfo 
INNER JOIN BorrowerInfo ON BookInfo.BookID = BorrowerInfo.BookID 
INNER JOIN StudentInfo ON BorrowerInfo.StudentID = StudentInfo.StudentID;

Complate Code:
this will reterive all the required record from three tables then it will fill the data adapter and finally it will display in grid view.

Dim con As New SqlConnection
Dim adap As New SqlDataAdapter
Dim ds As New DataSet
con = New SqlConnection("data source=.\sqlserver;initial catalog=libraury;uid=sa;pwd=adnanamin")
con.Open()
adap = New SqlDataAdapter("SELECT BorrowerInfo.StudentID, BorrowerInfo.BookID, BookInfo.Title, StudentInfo.FullName FROM BookInfo 
INNER JOIN BorrowerInfo ON BookInfo.BookID = BorrowerInfo.BookID 
INNER JOIN StudentInfo ON BorrowerInfo.StudentID = StudentInfo.StudentID;[/", con)
adap.Fill(ds, "1")
DataGridView1.AutoGenerateColumns = True
DataGridView1.DataSource = ds.Tables("1")

Enjoy...if u face any other problem you may ask me.
if it could solve your problem then mark this threads as solved.

[geoamins2]

Dim con As New SqlConnectionDim adap As New SqlDataAdapterDim ds As New DataSetcon = New SqlConnection("data source=.\sqlserver;initial catalog=libraury;uid=sa;pwd=adnanamin")con.Open()adap = New SqlDataAdapter("SELECT BorrowerInfo.StudentID, BorrowerInfo.BookID, BookInfo.Title, StudentInfo.FullName FROM BookInfo INNER JOIN BorrowerInfo ON BookInfo.BookID = BorrowerInfo.BookID INNER JOIN StudentInfo ON BorrowerInfo.StudentID = StudentInfo.StudentID;", con)adap.Fill(ds, "1")DataGridView1.AutoGenerateColumns = TrueDataGridView1.DataSource = ds.Tables("1")

[michaelzip]

Thanks for the reply everyone and I'm sorry I wasn't able to response immediately. If I am not mistaken, to show the books borrowed by a specific student, my query would be.

SELECT BorrowerInfo.StudentID, BorrowerInfo.BookID, BookInfo.Title, StudentInfo.FullName FROM BookInfo INNER JOIN BorrowerInfo ON BookInfo.BookID = BorrowerInfo.BookID INNER JOIN StudentInfo ON BorrowerInfo.StudentID = StudentInfo.StudentID WHERE StudentID = '2938485';

[michaelzip]

Hi! I was able to get what I needed, thanks everyone. But recently, I have a problem showing it to a CrystalReport. It was working on DataGridView and tested it on ListView object.

Here's my code on throwing it to a Crystal Report

Button1 Procedure

Dim ds As New DataSet
Dim cr As New CrystalReport1

ds = getInfo("SELECT BorrowerInfo.StudentID, BorrowerInfo.BookID, BookInfo.Title, StudentInfo.FullName FROM BookInfo INNER JOIN BorrowerInfo ON BookInfo.BookID = BorrowerInfo.BookID INNER JOIN StudentInfo ON BorrowerInfo.StudentID = StudentInfo.StudentID", "1")

cr.SetDataSource(ds.Tables("1"))
frmReport.CrystalReportViewer1.ReportSource = cr
frmReport.Refresh()
frmReport.Show()

Connection Module

Private sqlConn As SqlConnection
Private sqlDataAdapt As SqlDataAdapter

Private Const strSqlConnection As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"


Public Function getInfo(ByVal strSql As String, ByVal strTable As String) As DataSet
  Dim dsReturn As New DataSet
  sqlConn = New SqlConnection(strSqlConnection)
  sqlConn.Open()

  sqlDataAdapt = New SqlDataAdapter
  sqlCmd = New SqlCommand(strSql, sqlConn)
  sqlDataAdapt.SelectCommand = sqlCmd
  sqlDataAdapt.Fill(dsReturn, strTable)
  sqlConn.Close()

  Return dsReturn
End Function

It always show this dialog.
[IMG]http://i52.tinypic.com/o7rcx4.jpg[/IMG]

Please help me

[epathtrade]

very good I like it

[michaelzip]

Update
I tried creating a Database1DataSet.xsd contaning the tables & fields needed (BorrowerInfo.*, BookInfo.*, StudentInfo.*) but it shows no record. Just the Column Headers