Populating the DataGridView with Data

[Papa_Don]

Group,

I've got part of my code started to populate the DataGridView with the data I need to return. When testing it, I have this long pause and no returned data. I finally have to kill it via <Control><Alt><Delete>. I've tried reading through mulitple websites to find some information to connect and fill the table, but I don't think I'm understanding it correctly. Hopefully you can help.

What I ultimately want to to:

1) Search the database for part numbers that begin with "AO" (my test database has two examples in it)
2) Return these two examples to the grid.

        Dim connstr = "Data Source=Don-PC;Provider=SQLOLEDB;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011"
        Dim search1 As String = "'%" & string1 & "%'"
        Dim sqlquery = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like " & search1
        Dim connection As SqlConnection = New SqlConnection(connstr)
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()
        command.CommandText = sqlquery
        Dim reader As SqlDataReader = command.ExecuteReader()
        reader.Read()
        Me.DataDesignSolutionsDataSet1BindingSource.DataSource = DataDesignSolutionsDataSet1
        connection.Close()

Hopefully you'll see what I'm missing (or have wrong) and help me fix it.

I remind you, I'm a newbie programmer. This is my first attempt to using the DataGridView.

Thanks,

Don

[RvSon]

HI.
what you mean my string1 over there? what values should be there in the string.
Are you using any controls to feel the string and using string values to get the query execute.
What you can do is,
Just put one textbox to your page. enter value to it and assign to textbox.
OR
Try this out. hope it will work for you if it's not please let me know. I will give you more input's on it.
**

 Dim search1 As String =  '"& string1 &"'
        Dim sqlquery = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like '% & search1 %'
        **

[Papa_Don]

Ravi,

I've made some changes to the code and it now looks like this:

        Dim string1 As String = frmOrderEntry2.txbPartNo.Text
        Dim string2 As String = string1 & "*"
        txbString1.Text = string2
        Dim search1 As String = string1 & "%"

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Data Source=Don-PC;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011"
        con.Open()
        Dim ds As DataSet = New DataSet
        Dim adapter As New OleDb.OleDbDataAdapter
        Dim sql As String

        sql = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like " & search1
        adapter.SelectCommand = New OleDb.OleDbCommand(sql, con)
        adapter.Fill(ds)
        dgvPartDescription.DataSource = ds.Tables(0)

So you understand what I'm doing (or trying to do):

a) "string1" retrieves the users entry from a form called "frmOrderEntry2" which was entered in the the "txbPartNo" textbox.

b) "string2" adds the asterisk to the end of the users entry for display purposes only and is displayed in the textbox called "txbString1".

c) "search1" adds the percent sign so that it can be read as a "begin as" for the search within the database.

d) "dgvPartDescription" is the name of the DataGridView control within the form.

Please review the above code and see what I'm missing. I'm still getting nothing in the grid.

Thanks again for your help.

Don

[RvSon]

Hi,
Please Make sure that you are getting values into a source using break point.
You need to bind your DGV.

[Papa_Don]

Ravi,

I don't know how to "bind" the DataGridView. Is that done programatically?

[RvSon]

I guess, you can achieve this with,
DGVName.bindingSource1.DataSource = table;
before binding please make sure that datasource is filled with appropriate data.

[Papa_Don]

Ravi,

I'm trying to follow your recommendation. I've written it as follows:

dgvInventory.BindingSource1.DataSource = "INVENTORY"

dgvInventory is the name of my DataGridView table
INVENTORY is the name of the database table

If this is what you meant for me to do, it doesn't work. I'm getting errors as soon as I write the code.

Since my last posting, I've gone back and started all over and added some important controls. This includes the table adapter, the table adapter manager, etc. I'm now getting results to come back with the following code:

    Private Sub frmPopup_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Me.INVENTORYTableAdapter.Fill(Me.DataDesignSolutionsDataSet2.INVENTORY)

        Dim string1 As String = frmOrderEntry2.txbPartNo.Text
        Dim string2 As String = string1 & "*"
        txbString1.Text = string2
        Dim search1 As String = string1 & "%"

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Data Source=Don-PC;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011"
        Dim sqlquery = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM [INVENTORY] where [INV-PART-NUMBER] like " & search1
        con.Open()
        Dim ds As DataSet = New DataSet
        ds.Clear()
        Dim adapter As New OleDb.OleDbDataAdapter
        adapter.SelectCommand = New OleDb.OleDbCommand(sqlquery, con)

    End Sub

Unfortunately, it is bringing every item that I have in my test database. My intent is to have the table list the items that begin with "AO". My database table only contains two of those item types.

Thoughts?

Thanks again for your help.

Don

[G_Waddell]

Hi
your databinding is wrong, the dataset will contain a table with the data retrieved from the query run in your data adapter. (The idea is that you can store several different query results in a single dataset and even link them with relationships - think of it as a local database)

In fact you did have the datagrid binding to the correct datatable at the end of your second post:

dgvPartDescription.DataSource = ds.Tables(0)

You may have just needed to refresh it....

dgvPartDescription.Refresh

[Papa_Don]

Mr. Waddell,

I don't follow you.

your databinding is wrong, the dataset will contain a table with the data retrieved from the query run in your data adapter

I've added the binding as you suggested (albeit since my last post, I've started all over building the DataGridView from scratch. So some names have changed). So my latest iteration looks like this:

Private Sub frmPopup_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Me.INVENTORYTableAdapter.Fill(Me.DataDesignSolutionsDataSet2.INVENTORY)

        Dim string1 As String = frmOrderEntry2.txbPartNo.Text
        Dim string2 As String = string1 & "*"
        txbString1.Text = string2
        Dim search1 As String = string1 & "%"
        Dim sqlquery As String
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Data Source=Don-PC;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011"
        sqlquery = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM [INVENTORY] where [INV-PART-NUMBER] like " & search1
        con.Open()
        Dim ds As DataSet = New DataSet
        ds.Clear()
        Dim adapter As New OleDb.OleDbDataAdapter
        adapter.SelectCommand = New OleDb.OleDbCommand(sqlquery, con)
        dgvInventory.DataSource = ds.Tables(0)
        dgvInventory.Refresh()

    End Sub

Now the DataGridView is BLANK. Nothing comes back.... With and without the last statement. Is it possible the statement "dgvInventory.DataSource = ds.Tables(0)" is in the wrong place?

And I thought Visual Basic was going to be easy!! LOL!!

Thanks for helping!

Don

[TnTinMN]

Don,

No offense intended, but you need to get more of the basics (no pun intended) under control first.

Learn to use the debugger. It is there for reason it and is your best friend.

Debugging User Interface Reference

Set a break-point in your code and inspect the variable's contents by hovering your mouse pointer over the variable name. This will allow you to see if the variables actually hold what you think they do.

You are using SQL Server, yet your code is using the OLEBD namespace data objects (connection, dataadapter, etc.). This in itself is not incorrect, but there is the SQLClient namespace that has versions of these objects specifically tailored to SQL Server.

Minor issue: you are hard coding your connection strings into your code. This will make it a pain later on when you want to distribute your application. You could define a string variable in Module or my preference is to use the My.Settings collection that VB provides. You can access this by going to Project Properties and selectioning the settings tab. When you define the setting, select "Connection string" for the type.

In the code you have shown, you are "opening" a connection. You should be close every connection you open. I believe the DataAdapter fill method does not require you top open the connection first. It will open and close the connection that you have provided it.

I also suggest that you consider using the DataSet designer to assist you in creating strongly typed classes that you can the use in your code. You will be able to craft your queries in a design environment and test them out prior to exposing them in your own code. Let the dang machine write all the monotonous stuff!

Working with Datasets in Visual Studio

[scudzilla]

^That. And with ds being empty, the dgv would of course be blank. Correct me if I'm wrong, but shouldn't strings in the query be between single quotes?

[RvSon]

HI,

Try With this code.
  try
        {
            adp = new SqlDataAdapter(sqlQuery, conn);
            tbl = new DataTable();
            adp.Fill(tbl);
            grdView.DataSource = tbl;
            grdView.DataBind();
        }
        catch (Exception ex)
        {
            MsgBox("Populate GridView Error: " + ex.Message, form);
        }

-----------------------------------------------------
OR
Try this in your existing Code
First
Dim search1 As String = "'%" & string1 & "%'",
And Remove ds.clear() as alrady you have defiend new dataset so no need to clear the dataset.

Second
adapter.SelectCommand = New OleDb.OleDbCommand(sqlquery, con)
adapter.Fill(ds)

DGVName.DataSource = ds.tables(0)

Follow this,
http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.datasource.aspx

[Papa_Don]

I got it fixed. It had everything to do with

Dim search1 As String = "'%" & string1 & "%'"

I wasn't putting the single quote (') properly.

Thanks for everyones help!!

Don