Fill: SelectCommand.Connection property has not been initialized.

[chanthung]

I am using MsAccess DB and Vb.NET 2008. This is a code to check the Database for duplicate entries but m geting an Error.

" Fill: SelectCommand.Connection property has not been initialized."
And this points to LINE NO 14. What could be the cause..? thanks in adv

Dim con As OleDbConnection
        Dim cmditem As New OleDbCommand
        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable

        con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data   
                          Source=D:\PROJECTS\database.mdb")
        con.Open()
        cmditem.CommandText = "Select * from ITEM where ItemCode='" &    
  Trim(Me.TextBox1.Text) & "'"

        da.SelectCommand = cmditem
        da.Fill(ds, "ITEM")  .........................LINE 14
        dt = ds.Tables("ITEM")
        If dt.Rows.Count > 0 Then
            MessageBox.Show("Item Code already exist in database. 
Please enter different ID.", "Error", MessageBoxButtons.OK)
        Exit Sub
        End If
  frmItems.ITEMTableAdapter.Insert(Me.TextBox1.Text, Me.TextBox2.Text, Me.TextBox3.Text)
        frmItems.ITEMTableAdapter.Fill(frmItems.SDBDataSet.ITEM)
        frmItems.ITEMTableAdapter.Update(frmItems.SDBDataSet.ITEM)

[ndeniche]

That's because you're not specifying the Connection Property for da.SelectCommand (da.SelectCommand.Connection)

Try

da.SelectCommand.Connection = con
da.SelectCommand = cmditem

[chanthung]

hi, tried you code. i get this error msg
"Object reference not set to an instance of an object."
Cant figureout where m going wrong...sorry for being so naive.
Can you please give more help..thanks

[chanthung]

Thanks for the help. now the code works and accepts the values passed through the
textboxes. But when i try to enter the a Duplicate ID, say 11 twice, i still
get an Error message.

"Data type mismatch in criteria expression."

con.Open()
        cmditem.CommandText = "Select * from ITEM where ItemCode='" &  

                           Trim(Me.TextBox1.Text) & "'"
       
     [B]   da.SelectCommand = cmditem
        da.SelectCommand.Connection = con[/B]
        'da.SelectCommand = cmditem
        da.Fill(ds, "ITEM")
        dt = ds.Tables("ITEM")
        If dt.Rows.Count > 0 Then
            MessageBox.Show("Item Code already exist in database. 	

Please enter different ID.", "Error", MessageBoxButtons.OK)
            Exit Sub
        End If

        frmItems.ITEMTableAdapter.Insert(Me.TextBox1.Text,Me.TextBox2.Text,            
                                                    Me.TextBox3.Text)
        frmItems.ITEMTableAdapter.Fill(frmItems.KDBDataSet.ITEM)
        frmItems.ITEMTableAdapter.Update(frmItems.KDBDataSet.ITEM)

[ndeniche]

Somewhere in your code you're assigning a value that doesn't match the destination variable's type. Be sure to double check it

[Mike Askew]

"Data type mismatch in criteria expression." is an error relating to your SQL.

In this case you are putting '11' around the number 11. When using numbers in the SQL you should leave out the ' ' :)

[chanthung]

"Data type mismatch in criteria expression." is an error relating to your SQL.

In this case you are putting '11' around the number 11. When using numbers in the SQL you should leave out the ' ' :)

Many many thanks..its now working. Thanks to all for helping me out.. Cheers!!

Tried diff code here..

    con.Open()
    cmdItem = New OleDbCommand("Select * from ITEM where ItemCode= " &        
        Me.TextBox1.Text & "", con)
    drr = cmdItem.ExecuteReader
    If Not drr.HasRows Then
       .........insert code here
    else 
    MsgBox("Error...ID Exist")
    End If