MySQL .NET Data Provider

[mralston]

Anybody got any experience using the MySqlDirect .NET Data Provider from http://crlab.com/mysqlnet/??

I'm trying to write an admin program for a website in VB.NET, the database for the site being in MySQL. I've downloaded this data provider and installed it OK, but I can't figure out how to use it.

It's the first project I've tried to write in .NET (I'm used to normal ADO in ASP or VB6). I've managed to fill a listbox from a query on the database using this data provider and its DataReader object, but what I really want to do is populate a datagrid.

Anybody got an example they can show me? Failing that an example of how to do this using the built in database stuff in .NET would give me a good starting point!

Cheers

[jwebster03]

This would work. (Don't Forget to open up our database connection and close it though) If you need more explanation let me know.


Dim da As SqlClient.SqlDataAdapter

da = New SqlClient.SqlDataAdapter("YOUR SELECT STATEMENT GOES HERE", ConnectNameGoesHere)

da.Fill(ds, "SUBJECTLOOKUP")

DataGrid1.DataSource = ds.Tables("SUBJECTLOOKUP")

[mralston]

Thanks for that.

What should "SUBJECTLOOKUP" be?
Also what is ds?

[jwebster03]

SubjectLookup can be anything. Generally, I would name it based on the function the dataset performs. In this case, It brings back data on a subject. You can name it whatever you want. If you change the name "SUBJECTLOOKUP", make sure you change it when you populate the datagrid also.

[mralston]

Cool, and I just edited my post, what is ds?

[mralston]

Ah, never mind. Got that one. ds is a dataset.
Guess what? The bloomin thing actually works!

Thank you very much!

[jwebster03]

Send me the code where you filled your list box and I will edit it to fill the datagrid and put comments in and that should help you.

[mralston]

I haven't got that code anymore, but this is one of the pieces of code I've got to fill a grid for me...

    Private Sub Fill_Products_Grid()
        Dim oDataAdapter As MySqlDataAdapter

        oDataAdapter = New MySqlDataAdapter("SELECT * FROM products;", oConn)
        If oDataSet.Tables.Contains("products") Then oDataSet.Tables("products").Clear()
        oDataAdapter.Fill(oDataSet, "products")

        grdProducts.DataSource = oDataSet.Tables("products")
    End Sub

The connection is done elsewhere in the program.

Thanks for your help anyways.

[jwebster03]

You had a couple of things switched around. Here you go:

Dim oDataAdapter As SQLClient.SqlDataAdapter
Dim oDataSet As New DataSet()

oDataAdapter = NewSqlClient.sqlDataAdapter("Select * From Products", oConn)

oDataAdapter.Fill(oDataSet, "PRODUCTS")

If oDataset.Tables("PRODUCTS").Rows.Count > 0 Then
grdProducts.DataSource = oDataSet.Tables("PRODUCTS")




'DONT FORGET TO OPEN YOUR DATABASE CONNECTION AND CLOSE IT UP TOP.

[mralston]

That's pretty much what I've got. I forgot to mention that I'm seting up the oDataSet eslewhere too because I'm using it in other places in the project. Anyway, the one I've got at the moment works fine.

[mralston]

Ok, next question. How do I make the first three columns in my DataGrid invisible? I know this has come up in another recent thread (indeed I stole some code from it!), but it didn't make much sense to me, nor did it work!

    Private Sub Fill_Orders_Grid()
        Dim oDataAdapter As MySqlDataAdapter

        oDataAdapter = New MySqlDataAdapter("SELECT orders.order_id, orders.product_id, orders.user_id, products.title AS Product, users.name AS User, orders.order_date AS 'Date', CONCAT(orders.eta, ' days') AS 'Requested Delivery Schedule', orders.payment_method AS 'Payment Method', orders.status AS 'Order Status' FROM orders LEFT OUTER JOIN products ON orders.product_id=products.product_id LEFT OUTER JOIN users ON orders.user_id=users.user_id ORDER BY orders.order_date DESC;", oConn)
        If oDataSet.Tables.Contains("orders") Then oDataSet.Tables("orders").Clear()
        oDataAdapter.Fill(oDataSet, "orders")

        ' START: Hide ID columns
        Dim dgsTableStyle = New DataGridTableStyle()
        Dim dgtTextBoxColumn1 = New DataGridTextBoxColumn()
        Dim dgtTextBoxColumn2 = New DataGridTextBoxColumn()
        Dim dgtTextBoxColumn3 = New DataGridTextBoxColumn()

        dgsTableStyle.DataGrid = grdOrders

        dgsTableStyle.GridColumnStyles.AddRange(New DataGridColumnStyle() {dgtTextBoxColumn1, dgtTextBoxColumn1, dgtTextBoxColumn1})
        dgsTableStyle.ReadOnly = True

        dgtTextBoxColumn1.format = ""
        dgtTextBoxColumn1.FormatInfo = Nothing
        dgtTextBoxColumn1.MappingName = "order_id"
        dgtTextBoxColumn1.Width = 0

        dgtTextBoxColumn2.format = ""
        dgtTextBoxColumn2.FormatInfo = Nothing
        dgtTextBoxColumn2.MappingName = "order_id"
        dgtTextBoxColumn2.Width = 0

        dgtTextBoxColumn3.format = ""
        dgtTextBoxColumn3.FormatInfo = Nothing
        dgtTextBoxColumn3.MappingName = "order_id"
        dgtTextBoxColumn3.Width = 0

        grdOrders.TableStyles.Add(dgsTableStyle)
        ' END: Hide ID columns

        grdOrders.DataSource = oDataSet.Tables("orders")

    End Sub

It just runs as if the Hide ID columns code didn't exist.

[CoMMiE]

Hi
Try this instead

dgtTextBoxColumn1.format = ""
dgtTextBoxColumn1.FormatInfo = Nothing
dgtTextBoxColumn1.MappingName = "order_id"
dgtTextBoxColumn1.Width = 0

dgtTextBoxColumn2.format = ""
dgtTextBoxColumn2.FormatInfo = Nothing
dgtTextBoxColumn2.MappingName = "order_id"
dgtTextBoxColumn2.Width = 0

dgtTextBoxColumn3.format = ""
dgtTextBoxColumn3.FormatInfo = Nothing
dgtTextBoxColumn3.MappingName = "order_id"
dgtTextBoxColumn3.Width = 0


dgsTableStyle.GridColumnStyles.AddRange(New DataGridColumnStyle() {dgtTextBoxColumn1, dgtTextBoxColumn2, dgtTextBoxColumn3})
dgsTableStyle.ReadOnly = True

[mralston]

Ok, I've now corrected a few of my mistakes, renamed a few variables so they are more meaningful, removed a bit of unneeded code and applied CoMMiE's suggestion, but still no cigar. Here's what I've got now

    Private Sub Fill_Orders_Grid()
        Dim oDataAdapter As MySqlDataAdapter

        oDataAdapter = New MySqlDataAdapter("SELECT orders.order_id, orders.product_id, orders.user_id, products.title AS Product, CONCAT('£', products.price) as Price, users.name AS User, orders.order_date AS 'Date', CONCAT(orders.eta, ' days') AS 'Timescale', orders.payment_method AS 'Payment Method', orders.status AS 'Order Status' FROM orders LEFT OUTER JOIN products ON orders.product_id=products.product_id LEFT OUTER JOIN users ON orders.user_id=users.user_id ORDER BY orders.order_date DESC;", oConn)
        If oDataSet.Tables.Contains("orders") Then oDataSet.Tables("orders").Clear()
        oDataAdapter.Fill(oDataSet, "orders")

        ' START: Hide ID columns
        Dim styTableStyle = New DataGridTableStyle()
        Dim colOrderID = New DataGridTextBoxColumn()
        Dim colProductID = New DataGridTextBoxColumn()
        Dim colUserID = New DataGridTextBoxColumn()

        styTableStyle.DataGrid = grdOrders

        colOrderID.MappingName = "order_id"
        colOrderID.Width = 0

        colProductID.MappingName = "product_id"
        colProductID.Width = 0

        colUserID.MappingName = "user_id"
        colUserID.Width = 0

        styTableStyle.GridColumnStyles.AddRange(New DataGridColumnStyle() {colOrderID, colProductID, colUserID})
        styTableStyle.ReadOnly = True
        ' END: Hide ID columns

        grdOrders.DataSource = oDataSet.Tables("orders")

    End Sub

I'm wondering if I'm going about this the wrong way... I need code access to the ID fields that I'm trying to hide, but perhaps I shouldn't be filling them into the grid in the first place??