?
Solved

data tier help

Posted on 2006-05-01
36
Medium Priority
?
265 Views
Last Modified: 2010-04-23
Okay, using Nwind.mdb and .Net 2003, I'm trying to create a two-tiered application that will simply display a combo box of the [Contact Name] (based upon [Customer ID].
When the customer's name is selected, I want to display their [Company Name].

So, here's what I've done:

1) Created the Customer class
2) Created the Data connection to NWind.mdb (& daCust data adapter)
3) Generated dataset, dsCust
4) Dropped a data view, dvCust into my project

I'm unsure of how to proceed from here.  The book models a bit of a different scenario.
I thought I'd grasped that one, but this "try it on your own" version is leaving me a
bit confused.

Anyone willing to help me understand this a bit better?
0
Comment
Question by:sirbounty
  • 17
  • 16
  • +2
36 Comments
 
LVL 5

Expert Comment

by:MageDribble
ID: 16579248
you need to issue a command to the dataadaptor to return your results.  Command is just a query or a stored procedure call.  Then fill your dataset with the data from the data adaptor (fill function).
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16579401
Have you successfully created a Customer object with the information from the database? If so, I'd like to see the code to maybe understand your current methodology. Possible make a suggestion that follows your current direction.
0
 
LVL 5

Expert Comment

by:proten
ID: 16579426
If you have already generated your dataset all you need to do is set the datasource of the combobox.  I am unclear as to why you would need the dataview (or datagrid??)

The simplest way to do it would be:

ComboBox1.DisplayMember = "[Contact Name]"
ComboBox1.ValueMember = "[Company Name]"
ComboBox1.DataSource = ds.tables(0)

Then in the ComboBox1 selectedindexchanged event, set the field display to CStr(ComboBox1.SelectedValue).  First check that ComboBox1.SelectedIndex > -1.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 67

Author Comment

by:sirbounty
ID: 16579520
No, I haven't created the customer object yet.
I was toying with trying to code it myself, but thought I'd get some good guidance first.
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16579591
Well here are my recommendations. I'm assuming that you at least know the basics of OOP classes. Let's say your Customer table looks like this:

CustomerNumber int
CustomerName varchar(50)
CompanyName varchar(50)

You would setup 3 private variables and 3 public properties for each of the fields. Each property interacts with it's associated private variable. Example:

Private _CustomerNumber as Integer

Public Property CustomerNumber as Integer
   Get
     Return _CustomerNumber
   End Get
   Set(ByVal Value as Integer)
      _CustomerNumber = Value
   End Set
End Property

So that's the preferred way to handle properties in classes. Now you will need a method with which to Load in a Customer. I tend to do it this way:

Public Class Customer
'Privates
'Properties

Public Sub New() 'default constructor

End Sub

Public Sub New(ByVal p_CustomerNumber as Integer)
   Me.Load(p_CustomerNumber)
End Sub

Public Sub Load(ByVal p_CustomerNumber as Integer)
     Dim sql as String = "SELECT ..... WHERE CustomerNumber=" & p_CustomerNumber
     'the sql string is really irrellevant. However you prefer to retrieve data, the point is, here, you will retrieve a DataTable from the datasource through your adapter

     Dim tbl as DataTable 'your datatable
     Dim row as DataRow = tbl.Rows(0) 'if your customernumber is unique, then you only have 1 row to worry about.
     Me.CustomerNumber = row("CustomerNumber")
     Me.CustomerName = row("CustomerName")
     Me.CompanyName = row("CompanyName")
End Sub

I didn't elaborate too much on the data retrieval but there are many ways to go about getting the data. I prefer to use some functions that I have written long ago (.NET 1.1) that pretty much reduce the required code for a DataTable down to 2 lines. You may prefer to use a different approach.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16581514
Thank you wtconway.
I'm working on coding it at this point.
I'm specifically trying to learn how to do this with a data adapter/set, and using a data tier.
I'm still struggling a bit on oop, but trying to learn it more, thus the question.
Thank you for the explanation.

Here's where I'm stuck - filling the combobox with "Contact Name"
My form code:

    Dim mobjCustomer As Customer

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dvCust As DataView, dsCust As DataSet
        Try
            daCust.Fill(DsCust1)
            With ComboBox1
                .DataSource = dvCust
                .DisplayMember = ("Contact Name")
                .ValueMember = "Customer ID"
                .SelectedIndex = -1
            End With
  [...]
    End Sub
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 200 total points
ID: 16582475
You are setting the DataSource property to dvCust, but you haven't set it anywhere.  You are filling DsCust1, but you aren't using it anywhere:

1) .DataSource = DsCust1.Tables(0)

2) dvCust = New DataView(DsCust1.Tables(0))

Bob
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16586396
sirbounty, do me a big favor. Could you post your entire form code? If you are using a module or outside class, just copy/paste the relevant parts from those. That would help me a little.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16588927
okay - I changed item #1 as indicated by TLO.
I wasn't quite sure where to place #2.

But here's my code as-is right now. (don't laugh :)

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
[I did remove this section for obvious reasons]
#End Region

    Dim mobjCustomer As Customer

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dvCust As DataView, dsCust As DataSet
        Try
            daCust.Fill(DsCust1)
            With ComboBox1
                .DataSource = DsCust1.Tables(0)
                .DisplayMember = ("Contact Name")
                .ValueMember = "Customer ID"
                .SelectedIndex = -1
            End With
        Catch ex As Exception
            MessageBox.Show(Err.Description)
        End Try

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        mobjCustomer = New Customer(ComboBox1.SelectedValue)
    End Sub
End Class

================
Then my customer class has:

Public Class Customer
    Private custNo As Int32, custName As String, coName As String

    Public Property CustomerNumber() As Int32
        Get
            Return custNo
        End Get
        Set(ByVal Value As Int32)
            custNo = Value
        End Set
    End Property

    Public Property CustomerName() As String
        Get
            Return custName
        End Get
        Set(ByVal Value As String)
            custName = Value
        End Set
    End Property

    Public Property CompanyName() As String
        Get
            Return coName
        End Get
        Set(ByVal Value As String)
            coName = Value
        End Set
    End Property

    Public Function GetData(ByVal strParameter As String) As DataSet
    End Function

    Public Sub New(ByVal mCustNumb As Integer)
    End Sub
End Class
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16590221
Ok your Customer class looks fine. You seem to be on the right track. Just make sure you include a generic constructor should you want to create a new instance without setting properties, like this:

Dim cust as New Customer()

Right now your class won't allow that. As for the GetData function. Let's focus on that right now.

I would suggest not making this a function but a sub routine. You would have this routine retrieve data from a sql database or access, really, any db you want. Do you know how to do this part? Once the data has been retrieved (into a DataTable), you will want to do stuff like this:

'note: tbl is your datatable
Dim row as DataRow = tbl.Rows(0) 'assumes that you will only have one record in the DB for a customer
Me.CustomerNumber = row("CustomerNumber")
Me.CustomerName = row("Customer Name")
Me.CompanyName = row("Company Name")

something along those lines. But at this point, what I need to know is, do you know how to retrieve data from a datasource?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16592222
I 'thought' I did.  
I'm a self-taught VB6 guy trying to learn .NET.
I did an exercise where there was no class and was able to retrieve the data from Access, but classes seem to always throw me for some reason.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16595169
"Just make sure you include a generic constructor should you want to create a new instance without setting properties"

I'm not sure what the reasons would be to do so?  In the scope of what I'm doing here, I only want to display the customer names in the combo and when one is selected, show the company.  To set it generically would be for a more versatile approach, correct?

As for retrieving the data, in my form code, when my try reaches the .ValueMember row, I get this error:

Cast from type 'DataRowView' to type 'Integer'is not valid.

I do appreciate all your help. :^)
0
 
LVL 7

Accepted Solution

by:
wtconway earned 1800 total points
ID: 16597004
Well you're going to want to move the Form1_Load code inside your class to the GetData sub routine. I'm going to take your class and show you what I usually do, hopefully that will help you a little bit. Here's your class as I would write it. I really focused on the data retrieval since that is your main problem right now.

Public Class Customer
    Private custNo As Int32, custName As String, coName As String

    Public Property CustomerNumber() As Int32
        Get
            Return custNo
        End Get
        Set(ByVal Value As Int32)
            custNo = Value
        End Set
    End Property

    Public Property CustomerName() As String
        Get
            Return custName
        End Get
        Set(ByVal Value As String)
            custName = Value
        End Set
    End Property

    Public Property CompanyName() As String
        Get
            Return coName
        End Get
        Set(ByVal Value As String)
            coName = Value
        End Set
    End Property

    Public Sub Load(ByVal strParameter As String)
        Dim sql As String = "SELECT Blah FROM Table"
        Dim row As DataRow = GetDataTable(sql, "Provider=Microsoft.Jet.OleDb.4.0;Data Source=blah;").Rows(0)
        Me.CustomerName = row("Contact Name")
        Me.CustomerNumber = row("CustomerNumber")
        Me.CompanyName = row("CompanyName")
    End Sub

    Public Shared Function LoadAll() As ArrayList
        Dim ret As New ArrayList
        Dim sql As String = "SELECT * FROM Table"
        Dim tbl As DataTable = GetDataTable(sql, "data source")
        Dim row As DataRow
        For Each row In tbl.Rows
            Dim cust As New Customer(12345)
            cust.CustomerName = row("Contact Name")
            cust.CustomerNumber = row("CustomerNumber")
            cust.CompanyName = row("CompanyName")
            ret.Add(cust)
        Next
        Return ret
    End Function

    Public Sub New(ByVal mCustNumb As Integer)
    End Sub

    'I wrote this function a LOOOONNNGGG time ago to make data retrieval a 1-line process for me
    'so far it has worked nearly perfectly for my uses. Maybe you will find it helpful as well.
    'Should this method work, let me know and I can get you a whole class of 1-line data functions.
    Private Shared Function GetDataTable(ByVal sql As String, ByVal Source As String) As DataTable
        Dim conConnect As New System.Data.OleDb.OleDbConnection(Source)
        Dim dapAdapter As New System.Data.OleDb.OleDbDataAdapter(sql, conConnect)
        Dim dstDataSet As New DataSet
        Dim tblDataTable As DataTable
        Try
            conConnect.Open()
            dapAdapter.Fill(dstDataSet)
            tblDataTable = dstDataSet.Tables(0)
            dstDataSet.Tables.Clear()
        Catch ex As System.Data.OleDb.OleDbException
            'handle your error here with something like this:
            MsgBox(ex.Message)
        Finally
            conConnect.Close()
        End Try
        Return tblDataTable
    End Function
End Class

Now that class will allow you to do this.

Dim cust as New Customer(12345) 'your constructor requires an argument for cust number
cust.Load("blah") 'your data retrieval method also required a parameter, should this be the customer number?
'Now you can reference the customer like so:
MsgBox(cust.CustomerName)

As for loading all the Customers and displaying them in a drop down list. I would use the LoadAll() method like this.

Dim arr as ArrayList = Customer.LoadAll()
ComboBox1.DataSource = arr
ComboBox1.DisplayMember = "CustomerName"
ComboBox1.ValueMember = "CustomerNumber"

can you try that?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16597248
trying now...
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16597420
I'm getting the "cast" error again here:

 Dim cust As New Customer(row(0))
where row(0) is the CustomerID (in the first instance, equivalent to ALFKI
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16597448
Well your parameter in the constructor expects an Integer. Also, at this point would you need to pull data from the db? The Load() method handles all that.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16597481
Presumably so, if I'm populating the combo?
Should I just change the type to string?
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16597521
The LoadAll function inside the class will handle loading all the data into an ArrayList that contains a bunch of Customer objects. You would want to program your sql statement in that function and let it handle the data retrieval. The point is to encapsulate all the interaction between the client and the db server inside the Customer class. Then it becomes as easy as: cust.Load() or cust.Save()

If your customer number is stored as a text or string field in the database, store it as a string in the class.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16597575
Converted to string and now it's working down to the last line in form_load:
ComboBox1.ValueMember = "CustomerID"

"Could not bind to the new display member"
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16597591
Ok. Show me the updated Form_load handler.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16597631
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Stop
        Dim arr As ArrayList = Customer.LoadAll()
        ComboBox1.DataSource = arr
        ComboBox1.DisplayMember = "CustomerName"
        ComboBox1.ValueMember = "CustomerID"
    End Sub
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16597690
ah...

You have switched the data source of the combobox to the arraylist brought in by Customer.LoadAll() - that's good.

Now that you have switched the data source to your Customer objects, you need to use the properties they contain, not the names of the fields in the database. I often keep them the same so there's no confusion. For right now, changed "CustomerID" to "CustomerNumber"
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16597752
I had it at that and was getting the 'cast' error - even after converting it to string.
I tried again now - same error.
Properties and declarations are all string now...
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16597764
Show me the completed Customer class. And if you can, show me the fieldnames/datatypes of the table.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16597835
Do you not have the Nwind.mdb?  I thought it was installed by default with VS, perhaps not.
Within table Customers
  CustomerID Text
  CompanyName Text
  ContactName Text

I just copied the class you posted above.  I've made very few modifications, but here it is:
Public Class Customer
    Private custNo As String, custName As String, coName As String

    Public Property CustomerNumber() As String
        Get
            Return custNo
        End Get
        Set(ByVal Value As String)
            custNo = Value
        End Set
    End Property

    Public Property CustomerName() As String
        Get
            Return custName
        End Get
        Set(ByVal Value As String)
            custName = Value
        End Set
    End Property

    Public Property CompanyName() As String
        Get
            Return coName
        End Get
        Set(ByVal Value As String)
            coName = Value
        End Set
    End Property

    Public Sub Load(ByVal strParameter As String)
        Dim sql As String = "SELECT Blah FROM Table"
        Dim row As DataRow = GetDataTable(sql, "Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\nwind.mdb;").Rows(0)
        Me.CustomerName = row("Contact Name")
        Me.CustomerNumber = row("CustomerNumber")
        Me.CompanyName = row("CompanyName")
    End Sub

    Public Shared Function LoadAll() As ArrayList
        Dim ret As New ArrayList
        Dim sql As String = "SELECT * FROM Customers"
        Dim tbl As DataTable = GetDataTable(sql, "Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\nwind.mdb;")
        Dim row As DataRow
        For Each row In tbl.Rows
            Dim cust As New Customer(row(0))
            cust.CustomerName = row("ContactName")
            cust.CustomerNumber = row("CustomerID")
            cust.CompanyName = row("CompanyName")
            ret.Add(cust)
        Next
        Return ret
    End Function

    Public Sub New(ByVal mCustNumb As String)
    End Sub

    'I wrote this function a LOOOONNNGGG time ago to make data retrieval a 1-line process for me
    'so far it has worked nearly perfectly for my uses. Maybe you will find it helpful as well.
    'Should this method work, let me know and I can get you a whole class of 1-line data functions.
    Private Shared Function GetDataTable(ByVal sql As String, ByVal Source As String) As DataTable
        Dim conConnect As New System.Data.OleDb.OleDbConnection(Source)
        Dim dapAdapter As New System.Data.OleDb.OleDbDataAdapter(sql, conConnect)
        Dim dstDataSet As New DataSet
        Dim tblDataTable As DataTable
        Try
            conConnect.Open()
            dapAdapter.Fill(dstDataSet)
            tblDataTable = dstDataSet.Tables(0)
            dstDataSet.Tables.Clear()
        Catch ex As System.Data.OleDb.OleDbException
            'handle your error here with something like this:
            MessageBox.Show(ex.Message)
        Finally
            conConnect.Close()
        End Try
        Return tblDataTable
    End Function
End Class

0
 
LVL 7

Expert Comment

by:wtconway
ID: 16597845
I'm gonna run to lunch. I'll take a look at your stuff when I get back.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16597853
ok - thanx again. :)
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16599634
I'm not ignoring you. My boss has called me into his office for a while to go over some program changes. I WILL look at this THIS afternoon so just gimme a few on this. I'll be posting something back soon.
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16600062
The code looks like it should work. You really don't need the customer number on the constructor, you're not using it. As for the LoadAll() method, it looks fine.

You say you're still getting an error?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16602115
Aha - I think I've discovered the problem...
When I said that it's occuring at
        ComboBox1.ValueMember = "CustomerNumber"
it's actually occuring because that triggers the 'change' event for the combo box...

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        mobjCustomer = New Customer(ComboBox1.SelectedValue)
    End Sub
End Class
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16602124
Have you removed the handler and test it?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16602132
So, skipping over that line, it goes through, but does nothing yet..
One example in the book suggests a boolean variable to determine if the combo is simply being populated or a value is actually being selected.
So, I suppose the New sub needs to be altered to take the customerID and locate the CompanyName.
Presumably I would use a select statement to pull out the ID where it's equal to the customer name inside this:?

Public Sub New(ByVal mCustNumb As String)
[.]
End Sub
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16602139
I'm confused. What are you wanting to do now? Are you saying that you CANNOT populate the dropdown?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16602168
Dropdown is populated with customer names.
I want, that when a customer is selected, I can use the customer id to pull the related company name from the same table...
0
 
LVL 7

Expert Comment

by:wtconway
ID: 16602175
You don't have to, you have loaded the company name into the Customer object. If you still have the event handler for the cbo_selectedIndexChanged, try this:

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim cust as Customer = CType(ComboBox1.SelectedItem,Customer)
        MsgBox(cust.CompanyName)
    End Sub
0
 
LVL 67

Author Comment

by:sirbounty
ID: 16602195
Nice - it works.  Though I don't actually understand it all.
Let me digest it in the morning and post back if I have any clarification questions?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Integration Management Part 2
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question