"The DataAdapter.SelectCommand property needs to be initialized."

[Dilah J]

'Inline Code Example HereHello i'm having this error message when adding to multiple table:
'"The DataAdapter.SelectCommand property needs to be initialized."
'I need to have my data saved into both table(tblStudentInfo, tblPayment) when btnFinish clicked. Please 'help, i'm working on this for days and haven't had any luck.

'For reference i attached codings below (frmMainMenu_load, btnFinish):

Public Class frmMainMenu
    Dim connetionString As String
    Dim connection As OleDbConnection
    Dim oledbAdapter As OleDbDataAdapter
    Dim ds As New DataSet
    Dim firstSql As String
    Dim secondSql As String

    Dim MaxRows As Integer = 8
    Dim subject As String
    Dim TotalSubject As Integer

    Private Sub frmMainMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DTS_Management.mdb;"
        firstSql = "SELECT * From tblStudentInfo"
        secondSql = "SELECT * From tblPayment"
        connection = New OleDbConnection(connetionString)
        Try
            connection.Open()
            oledbAdapter = New OleDbDataAdapter(firstSql, connection)
            oledbAdapter.Fill(ds, "First Table")
            oledbAdapter.SelectCommand.CommandText = secondSql
            oledbAdapter.Fill(ds, "Second Table")
            oledbAdapter.Dispose()
            connection.Close()
            'JUST TO CHECK IF CONNECTION ARE MADE
            ''retrieve first table data
            'For i = 0 To ds.Tables(0).Rows.Count - 1
            '    MsgBox(ds.Tables(0).Rows(i).Item(0) & "  --  " & ds.Tables(0).Rows(i).Item(1))
            'Next
            ''retrieve second table data
            'For i = 0 To ds.Tables(1).Rows.Count - 1
            '    MsgBox(ds.Tables(1).Rows(i).Item(0) & "  --  " & ds.Tables(1).Rows(i).Item(1))
            'Next
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try
    End Sub


    Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click
        Dim autono As New Random 'Variables for unique id_no
        Dim cb As New OleDb.OleDbCommandBuilder(oledbAdapter)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables(0).NewRow()
        dsNewRow.Item(0) = autono.Next(1000, 9999) 'Input unique ID in D
        '-Student Record Tab
        dsNewRow.Item(1) = Studentnametxt.Text
        dsNewRow.Item(2) = IcNotxt.Text
        dsNewRow.Item(3) = Sexcb.Text
        dsNewRow.Item(4) = Format(BirthdatePicker.Value, "dd/MMM/yyyy")
        dsNewRow.Item(5) = Nationalitytxt.Text
        dsNewRow.Item(6) = TextBox2.Text 'email
        dsNewRow.Item(7) = Studentcontactno.Text
        dsNewRow.Item(8) = Addresstxt.Text
        dsNewRow.Item(9) = Guardiannametxt.Text
        dsNewRow.Item(10) = Guardiancontacttxt.Text
        dsNewRow.Item(11) = Guardian_Name2.Text
        dsNewRow.Item(12) = Guardian_Contact2.Text
        dsNewRow.Item(13) = StudentPicturetxt.Text
        dsNewRow.Item(14) = Elementarytxt.Text
        dsNewRow.Item(15) = Eleyeartxt.Text
        dsNewRow.Item(16) = CurrentSchooltxt.Text
        dsNewRow.Item(17) = cbCurrentYear.Text
        'start/end course
        dsNewRow.Item(18) = StartDate1.Text
        dsNewRow.Item(19) = DateStart2.Text
        dsNewRow.Item(20) = DateStart3.Text
        dsNewRow.Item(21) = End1.Text
        dsNewRow.Item(22) = End2.Text
        dsNewRow.Item(23) = End3.Text

        'This is to prevent from error
        If subject = "" Then
            subject = "nil"
        End If
        dsNewRow.Item(24) = subject

        ds.Tables(0).Rows.Add(dsNewRow)
        oledbAdapter.Update(ds, "First Table")


        MsgBox("Record added.")
    End Sub

[AndreRet]

Have a look here.

You need to use your "firstsql" or "secondsql" strings to build the selectcommand, which will then initialise it, it seems.

[Dilah J]

Hi AndreRet,

Thank you for the fast reply. I tried the code that you'd given but now i got an error saying:
"Object reference not set to an instance of an object."

The coding below supposed to insert only studentnametxt.text data in item(1) into my first table as i am only trying out the correct code before proceding any further.

I'm new to VB so i don't really familiar with it. Can you elaborate more. Coding sample might be really useful.

Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFinish.Click
        Dim autono As New Random 'Variables for unique id_no
        Dim cb As New OleDb.OleDbCommandBuilder(oledbAdapter)
        Dim dsNewRow As DataRow

        'Dim drEmployee As DataRow
        dsNewRow = ds.Tables("tblStudentInfo").NewRow()

        With dsNewRow
            .Item(1) = Studentnametxt.Text
        End With

        ds.Tables("tblStudentInfo").Rows.Add(dsNewRow)
        oledbAdapter.Update(ds, "First Table")

        MsgBox("Record added.")
    End Sub