1,105,352 Community Members

Data Adapter Fill Method

Member Avatar
michaelzip
Light Poster
26 posts since Jan 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Example database:

BookInfoBookID: HSD993Z
Title: Introduction to SQL

BookID: LDJA293
Title: Advance Computer Programming

BookID: KSKL194
Title: Object Oriented Programming

StudentInfo
StudentID: 2938485
FullName: Michael Zip

StudentID: 2949284
FullName: Ray Williams


BorrowerInfo
StudentID: 2938485
BookID: KSKL194

StudentID: 2938485
BookID: HSD993Z

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

Member Avatar
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 50 [?]
Q&As Helped to Solve: 158 [?]
Skill Endorsements: 3 [?]
Featured
 
0
 

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

Member Avatar
tomason
Newbie Poster
4 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
geoamins2
Light Poster
30 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
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.

Member Avatar
geoamins2
Light Poster
30 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
1
 
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")
Member Avatar
michaelzip
Light Poster
26 posts since Jan 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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';

Member Avatar
michaelzip
Light Poster
26 posts since Jan 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
epathtrade
Newbie Poster
2 posts since Apr 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

very good I like it

Member Avatar
michaelzip
Light Poster
26 posts since Jan 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: