[RESOLVED] [2005] ADO.NET DataSet DataAdapter

I am attempting to populate two combo box in a form as the form loads using two subs

PopulateActivity()
Public Sub PopulateSites()

it seems to work but when I look in the database after I enter the values
I get Site
System.Data.DataRowView

Question: am I suppose to create a new DataSet and DataApapter for each combo box that I am trying to populate, since they
get there info from different tables?


file attached

Code:

---

Module Module1
    'created the variable conn that represents a new instance of
    'OleDb.Connection Object
    Dim conn As New OleDb.OleDbConnection
    'created the variable DataSet that represents a new instance of the object DataSet
    Dim DataSet As New DataSet
    Dim SiteDS As New DataSet

    'create the variable DataAdapter as an instance of OleDB.OleDbDataApapter
    Dim DataAdapter As OleDb.OleDbDataAdapter

    Public CHCBTimeSheet As New TimeSheet

    Dim sql As String
    Dim MaxNumRows As Integer


 


    Public Sub PopulateActivity()

        conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" _
        & Application.StartupPath & "\TSDB.mdb"

        conn.Open()

        Dim sql2 As String

        sql2 = "SELECT Activity FROM tblActivity"

        DataAdapter = New OleDb.OleDbDataAdapter(sql2, conn)


        DataAdapter.Fill(DataSet, "Activity")

        EnterHours.cboActivity.DisplayMember = "Activity"
        'EnterHours.cboActivity.Valuemember = "Column Name of ID (if any)"
        EnterHours.cboActivity.DataSource = DataSet.Tables("Activity")

        conn.Close()

    End Sub


    Public Sub PopulateSites()

        conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" _
        & Application.StartupPath & "\TSDB.mdb"

        conn.Open()

        Dim sql3 As String

        sql3 = "SELECT site FROM tblSites"

        DataAdapter = New OleDb.OleDbDataAdapter(sql3, conn)


        DataAdapter.Fill(DataSet, "Sites")

        EnterHours.cboSite.DisplayMember = "Site"
        'EnterHours.cboSite.Valuemember = "Column Name of ID (if any)"
        EnterHours.cboSite.DataSource = DataSet.Tables("Sites")

        conn.Close()

    End Sub

End Module

---

You are using .SelectedItem which will give you the DataRow. You want to use either the .SelectedValue or .SelectedText depending on whether you have an ID or not.

I changed up the format of the code because must of it was repetitive

With the Function GetDs it returns a dataset. How can I apply this function when I am trying to update the dataset?
Example below I am trying to update one of the tables but because of the way DataAdapter is declared in the function as a local variable to that function I am unable to use it in

Code:

---

Dim CommandBuilder As New OleDb.OleDbCommandBuilder(DataAdapter)

---

and

Code:

---

DataAdapter.Update(HoursDS, "timesheet")

---

Any ideas how I can resolve this issue??


[code]
Public CHCBTimeSheet As New TimeSheet

Dim ConnStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\TSDB.mdb"

Public Function GetDS(ByVal strSQL As String, ByVal TableName As String) As DataSet
Dim conn As New OleDb.OleDbConnection(ConnStr)
Dim cmd As New OleDb.OleDbCommand(strSQL, conn)
Dim da As New OleDb.OleDbDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds, TableName)
Return ds
End Function






Public Sub EnterTime()

Dim sqlstr As String = "SELECT * FROM tblHours"
Dim HoursDS As DataSet = GetDS(sqlstr, "TimeSheet")



Dim CommandBuilder As New OleDb.OleDbCommandBuilder(DataAdapter)
Dim DataSetNewRow As DataRow

DataSetNewRow = HoursDS.Tables("timesheet").NewRow

DataSetNewRow.Item("StartTime") = CHCBTimeSheet.StartTime
DataSetNewRow.Item("EndTime") = CHCBTimeSheet.EndTime
DataSetNewRow.Item("WorkDate") = CHCBTimeSheet.WorkDate
DataSetNewRow.Item("Site") = CHCBTimeSheet.Site
DataSetNewRow.Item("Activity") = CHCBTimeSheet.Activity

HoursDS.Tables("timesheet").Rows.Add(DataSetNewRow)

DataAdapter.Update(HoursDS, "timesheet")
End Sub
[code]

I don't use the CommandBuilder. I just create a new command object and set it up to do the update and then set the DataAdapter.UpdateCommand equal to this command object. Then just use the DataAdapter.Update(HoursDS, "timesheet").