[2005] need help with OLE

[zathras]

Hi, im doing a piece of work that needs to be completed soon.

Basically its a vb.net project where I have to use OLE commands and access queries to access a database and manipulate it.

This is one of the things I need to do:

Construct a file menu item that contains a sub menu item Load data. On selecting this item the entire catalogue of products should be displayed within a datagrid on the form. The image2 for the initial product should be displayed on the form together with the product full description. When a different product is selected from the datagrid the description and image data should be updated to reflect the new products detail. The user should be able to navigate through the rows of information within the datagrid by using the up and down arrows on the keyboard

I've created the load data sub and I can get all the products loaded up onto the datagrid. the picture also loads onto the datagrid.

I'm having problems getting the image and description onto the textbox and picturebox.

This is the code ive used for the load data sub so you have an idea of the kind of level of coding I need to do (I am hopeless at VB.net, alot of the code used here is from the tutorials I did before that are supposed to help)

Private Sub LoadDataToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadDataToolStripMenuItem.Click
conn = New OleDbConnection(strConn) ' making the connection
dv = New DataView
ds = New DataSet
adaptor = New OleDbDataAdapter(strSQL, conn)
cmdbld = New OleDbCommandBuilder(adaptor) ' openin the db and giving the command

adaptor.Fill(ds, "products") ' fills the dataset
dv.Table = ds.Tables("products") ' gives the dataset to the table

DataGridView1.DataSource = dv ' displays the dataview to the datagrid


End Sub

And variables used in the above sub are:

Private strSQL As String = "SELECT tblProducts.ProductID, tblProducts.ProductName, tblProducts.ProductCategoryID,tblProducts.ProductPicture, tblProducts.FullDescription, tblProducts.Price, tblProducts.StockLevel FROM tblProducts;"

Private strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\UNI\Dataprogramming\Assignment\Students assignment resources\Gadgets.mdb"

Private ds As DataSet ' declares a new dataset
Private dv As DataView ' declares dv as dataview
Private adaptor As OleDbDataAdapter ' declares adaptor to be used in this form instead of the full command
Private conn As OleDbConnection ' declares conn to be used in this form instead of the full command
Private cmdbld As OleDbCommandBuilder ' declares cmdbld to be used in this form instead of the full command

So the first thing I need to do is get the picture and description to be displayed onto a picturebox and textbox.

[zathras]

correction: need to add the full description to a listbox not textbox.


im trying to get it working using the following 2 sections of code:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbocategory.SelectedIndexChanged
        Dim cnn As OleDbConnection = New OleDbConnection
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\UNI\Dataprogramming\Assignment\Students assignment resources\Gadgets.mdb"
        cnn.Open()

        Dim cmd As OleDbCommand = New OleDbCommand
        cmd.CommandText = "SELECT tblProducts.FullDescription FROM tblProducts WHERE tblProducts.ProductCategoryID= '" & cbocategory.Text & " ' "
        cmd.CommandType = CommandType.Text
        cmd.Connection = cnn

        Dim da As OleDbDataAdapter = New OleDbDataAdapter
        Dim dsproducts As DataSet = New DataSet
        da.SelectCommand = cmd
        da.Fill(dsproducts, "productstable")

        lbdesc.DataSource = dsproducts.Tables("productstable")

        lbdesc.DisplayMember = "FullDescription"

        lbdesc.ValueMember = "FullDescription"

        cnn.Close()
        cnn.Dispose()

    End Sub

Private Sub products_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        Dim cnn As OleDbConnection = New OleDbConnection
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\UNI\Dataprogramming\Assignment\Students assignment resources\Gadgets.mdb"
        cnn.Open()

        Dim cmd As OleDbCommand = New OleDbCommand
        cmd.CommandText = "SELECT DISTINCT ProductCategoryID from tblProducts"
        cmd.CommandType = CommandType.Text
        cmd.Connection = cnn

        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader

        If dr.HasRows Then
            Do While dr.Read
                cbocategory.Items.Add(dr("ProductCategoryID"))
            Loop
        End If
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()

End Sub

But so far am having no luck, it points to the da.fill (dsproducts, "productstable") line and highlites it in yellow.

It says oledbexception was unhandled, data type mismatch in criteria expression.


I forgot to mention theres also a combobox.

this is wot its for:

On opening the form a dropdownlist (product categories) should be uniquely populated with each product category from the database.

On selecting a category from the dropdownlist only the products relating to this category should be displayed within the datagrid. The datagrid should obtain its information directly from the database. The action should not result from filtering the data in the datagrid control.

I think I can use this to show the picture onto the picturebox but I need to be able to get it working first, it shows the categories from the drop down menu but when I select it says the error about data mismatch.

[zathras]

Looks like I was going along the wrong approach by using the combobox.

The main problem is getting the description field and picture (binary data in the db) to a textbox and picturebox.


Apparently this is spose to work when the datagrid is loaded and you can go up and down from the datagrid and the pic/desc should change.

It shouldnt change from the combobox..

Does anybody have an idea how I could do it?