Newbie: DataAdapter and DataSet Use?

[Shanon Swafford]

I am building an application to be used to maintain 5 different tables in a
"Customer Database" using VB.NET and Access 2002.

Database tables are:
Customers
Customer Locations
Orders
Suppliers
Shippers
Employees

I plan to have a tab on the form for each of the tables where the user can
view/add/edit
records in the database.

My questions are:
1. How many DataSets and DataAdapters do I really need to build?

For instance, after building the functionality to update one table, I
currently have:
OleDbDataAdapter1 and DataSet11 - to get a list of the employee names
from the Employee Table.
OleDbDataAdapter2 and DataSet21 - to return the employee info from
Employee Table one employee at a time.
I use #1 to fill a combobox with all the available employees and #2 to get
and update info about a particular employee.

At the rate I am going, I will have 10 DataAdapters and 10 Datasets which I
am thinking is a lot.

Is it possible to use one DataAdapter and Dataset combination but pass
either a wildcard or a real value for a EmployeeID to get the information
per table?

2. How do you guys implement this type of functionality?

The following code gives me an error stating "Input string was not in a
correct format" when I try to pass a wildcard as a parameter. I think if I
can get this to work, I'll only have to use one DataAdapter and DataSet
combination for each table.


'Works when I pass a number for the EmployeeID, but I can't figure out how
to pass a wildcard.
'Generated by VS:
Me.OleDbSelectCommand3.CommandText = "SELECT EmployeeID, First_Name, _
Middle_Name, Last_Name FROM Employees WHERE (EmployeeID Like ?)

'In a fill procedure generated on a click:
Dim EmpEmployeeID As String
EmpEmployeeID = "%"
Try
DataSet31.Clear()
OleDbDataAdapter3.SelectCommand.Parameters("EmployeeID").Value = _
EmpEmployeeID

OleDbDataAdapter3.Fill(DataSet31.Employees)
Catch ex As Exception
MessageBox.Show("Can't Get Data from Database" & vbCrLf & ex.Message)
End Try

Thanks in advance,
Shanon

 

[Gordon Goddard]

-----Original Message-----

I am building an application to be used to maintain 5 different tables in a
"Customer Database" using VB.NET and Access 2002.

Database tables are:
Customers
Customer Locations
Orders
Suppliers
Shippers
Employees

I plan to have a tab on the form for each of the tables where the user can
view/add/edit
records in the database.

My questions are:
1. How many DataSets and DataAdapters do I really need to build?

For instance, after building the functionality to update one table, I
currently have:
OleDbDataAdapter1 and DataSet11 - to get a list of the employee names
from the Employee Table.
OleDbDataAdapter2 and DataSet21 - to return the employee info from
Employee Table one employee at a time.
I use #1 to fill a combobox with all the available employees and #2 to get
and update info about a particular employee.

At the rate I am going, I will have 10 DataAdapters and 10 Datasets which I
am thinking is a lot.

Is it possible to use one DataAdapter and Dataset combination but pass
either a wildcard or a real value for a EmployeeID to get the information
per table?

2. How do you guys implement this type of functionality?

The following code gives me an error stating "Input string was not in a
correct format" when I try to pass a wildcard as a parameter. I think if I
can get this to work, I'll only have to use one DataAdapter and DataSet
combination for each table.


'Works when I pass a number for the EmployeeID, but I can't figure out how
to pass a wildcard.
'Generated by VS:
Me.OleDbSelectCommand3.CommandText = "SELECT EmployeeID, First_Name, _
Middle_Name, Last_Name FROM Employees WHERE (EmployeeID Like ?)

'In a fill procedure generated on a click:
Dim EmpEmployeeID As String
EmpEmployeeID = "%"
Try
DataSet31.Clear()
OleDbDataAdapter3.SelectCommand.Parameters ("EmployeeID").Value = _
EmpEmployeeID

OleDbDataAdapter3.Fill(DataSet31.Employees)
Catch ex As Exception
MessageBox.Show("Can't Get Data from Database" & vbCrLf & ex.Message)
End Try

Thanks in advance,
Shanon





.
Hi Shanon;

For your select you try something like this:

Dim newsql As String = SELECT * from Employees Where
FirstName LIKE '%mik%'.

I would suggest that you invest in a good book such as
Applied ADO.net - Building Data Driven Solutions by
Mahesh Chand and David Talbot an Apress book. They have a
lot of great examples some of them show how to use one
data adapter and combing several tables into one dataset.

Good Luck with your problem.

Gordon

 

[Suresh]

Question 1.
I really dont have a convincing answer for no. 1

Question 2.
Have u tried including single codes around the "%",
like "'%'".

Just a guess. Might work who knows.

Suresh

-----Original Message-----