954,529 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article

Data Adapter Fill Method

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

michaelzip
Newbie Poster
19 posts since Jan 2011
Reputation Points: 10
Solved Threads: 0
 

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

Pgmer
Master Poster
714 posts since Apr 2008
Reputation Points: 54
Solved Threads: 121
 

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

tomason
Newbie Poster
4 posts since Feb 2011
Reputation Points: 10
Solved Threads: 0
 

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
Light Poster
27 posts since Feb 2011
Reputation Points: 12
Solved Threads: 2
 
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")
geoamins2
Light Poster
27 posts since Feb 2011
Reputation Points: 12
Solved Threads: 2
 

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
Newbie Poster
19 posts since Jan 2011
Reputation Points: 10
Solved Threads: 0
 

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

michaelzip
Newbie Poster
19 posts since Jan 2011
Reputation Points: 10
Solved Threads: 0
 

very good I like it

epathtrade
Newbie Poster
2 posts since Apr 2011
Reputation Points: 10
Solved Threads: 0
 

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

michaelzip
Newbie Poster
19 posts since Jan 2011
Reputation Points: 10
Solved Threads: 0
 

This article has been dead for over three months

Post: Markdown Syntax: Formatting Help
You
View similar articles that have also been tagged: