[DforReal]

I am slowly learning vb.net as I search for solutions to my project, but I cant seem to manage to fit all the pieces I need together to get it to do what I want.

I am working on a multi-form project right now for shipping and receiving inventory.

I am working on my new orders section and this section contains
order number txt box
employee txt box
vendor combo box
part number combo box
model number combo box
quantity combo box

datagridview

add item button
save order button


The idea is that when you enter data in the fields, you can hit the add item button. The following actions will happen:
text in vendor combobox will be added as a dropdown selection
text in part number combobox will be added as a dropdown selection
text in model number combobox will be added as a dropdown selection

[part number can be associated with multiple model numbers but not vise versa
when an existing part number is chosen, the model numbers are narrowed down to being able to add a new model number or choose from a model number previously associated with part number chosen]

DataGridView displays part number, model number and quantity

part number, model number, quantity are then cleared allowing you to re-enter those 3 fields, already associated with the order number, employee, vendor, then appear in a new line on the datagridview until the order is assembled each different part number at a time

Then:
Hitting the save order button:
saves a line of data with all 6 fields entered, one line for each part number, and records the date item was saved, all recorded into a database.

Note: Should not record to database until save order is pressed.


So far I have

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        'Create Data connection
        'Declare variables to connect to string

        Dim fldr As String
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String


        fldr = Application.StartupPath()
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source=" & fldr & "\SRC.mdb"
        con.ConnectionString = dbProvider & dbSource

        'Create database adapter
        Dim daNew As New OleDb.OleDbDataAdapter("select * from On_Order", con)

        'Create a data set
        Dim dsNew As New DataSet

        'Set select command
        daNew.SelectCommand.CommandText = "Select * From On_Order"

        'Update Commands
        Dim cbNew As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daNew)

        'Fill dataset
        daNew.Fill(dsNew, "On_Order")

        'Create new row
        Dim rowInsert As DataRow = dsNew.Tables("On_Order").NewRow
        'Add data to the row from text
        rowInsert("Order_Number") = txtOrder_Number.Text
        rowInsert("Vendor") = cmb_Vendor.Text
        rowInsert("Employee") = txtEmployee.Text
        rowInsert("Part_Number") = cmbPart_Number.Text
        rowInsert("Model_Number") = cmbModel_Number.Text
        rowInsert("Qty") = txtQty.Text

        'Add row to data set
        dsNew.Tables("On_Order").Rows.Add(rowInsert)




    End Sub

I have been working on it for a few days now and cant seem to figure out how to display specific information in the dgv. (sorry, I already deleted the code that didnt work to display to dgv)


I like to make detailed comments so I know what is suppose to do what, again, I am learning


Seems like alot, and this is only 1/3 of the first form, but if i can get alot of that done, im sure ill have learned enough to get me close to being able to finishing the rest.


Anyway, If I could get help tackling a little bit here and there, I could definately see myself learning this and being able to press on. I appreciate any assistance and advice. I know its always nice to have others look at things because there always is multiple ways to do the same task.

Thanks

[demausdauth]

What it looks like is that you have done everything to fill the DataSet that will be the source for the DataGridView, if I have understood your post. So what you need to do is assign the table in the DataSet to the DataGridView source.

        'Add row to data set
        dsNew.Tables("On_Order").Rows.Add(rowInsert)
        
        ''You may need to call the AcceptChanges method on the DataSet before trying to bind it to the DataGridView
        'dsNew.AcceptChanges()

        'By setting the DataSource property of the DataGridView it will automatically bind the columns and 
        ' and data.
        YourDataGridView.DataSource = dsNew.Tables("On_Order")

[DforReal]

Excellent. That appears to have worked so far. Here is current code

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        'Create Data connection
        'Declare variables to connect to string

        Dim fldr As String
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String


        fldr = Application.StartupPath()
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source=" & fldr & "\SRC.mdb"
        con.ConnectionString = dbProvider & dbSource

        'Increase value of n for multiple entries
        n = n + 1

        'Create database adapter
        Dim daNew As New OleDb.OleDbDataAdapter("select * from On_Order", con)
        Dim daDGV As New OleDb.OleDbDataAdapter("select * from On_Order", con)

        'Create a data set
        Dim dsNew As New DataSet
        Dim dsDGV As New DataSet

        'Set select command
        daNew.SelectCommand.CommandText = "Select * From On_Order"
        daDGV.SelectCommand.CommandText = "Select Part_Number, Model_Number, Qty From On_Order"

        'Update Commands
        Dim cbNew As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daNew)
        Dim cbDGV As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daDGV)

        'Fill dataset
        daNew.Fill(dsNew, "On_Order")
        daDGV.Fill(dsDGV, "On_Order")

        'Create new row
        Dim rowNew As DataRow = dsNew.Tables("On_Order").NewRow
        Dim rowDGV As DataRow = dsDGV.Tables("On_Order").NewRow

        'Add data to the row from text
        rowNew("Order_Number") = txtOrder_Number.Text
        rowNew("Vendor") = cmb_Vendor.Text
        rowNew("Employee") = txtEmployee.Text
        rowNew("Part_Number") = cmbPart_Number.Text
        rowNew("Model_Number") = cmbModel_Number.Text
        rowNew("Qty") = txtQty.Text

        rowDGV("Part_Number") = cmbPart_Number.Text
        rowDGV("Model_Number") = cmbModel_Number.Text
        rowDGV("Qty") = txtQty.Text

        'Add row to data set
        dsNew.Tables("On_Order").Rows.Add(rowNew)
        dsDGV.Tables("On_Order").Rows.Add(rowDGV)

        'Set DataSource to DataGridView
        dgvNew.DataSource = dsDGV.Tables("On_Order")

        'Add to n for use in Save Order button
        n = n + 1


    End Sub

Now here is the tricky part....

the n values were suppost to be for arrays on the datasets for everytime you click the button, it creates a new row, which I had envisioned as

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        'Create Data connection
        'Declare variables to connect to string

        Dim fldr As String
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String


        fldr = Application.StartupPath()
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source=" & fldr & "\SRC.mdb"
        con.ConnectionString = dbProvider & dbSource

        'Increase value of n for multiple entries
        n = n + 1

        'Create database adapter
        Dim daNew(n) As New OleDb.OleDbDataAdapter("select * from On_Order", con)
        Dim daDGV(n) As New OleDb.OleDbDataAdapter("select * from On_Order", con)

        'Create a data set
        Dim dsNew(1) As New DataSet
        Dim dsDGV(1) As New DataSet

        'Set select command
        daNew(n).SelectCommand.CommandText = "Select * From On_Order"
        daDGV(n).SelectCommand.CommandText = "Select Part_Number, Model_Number, Qty From On_Order"

        'Update Commands
        Dim cbNew(n) As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daNew(n))
        Dim cbDGV(n) As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(daDGV(n))

        'Fill dataset
        daNew(n).Fill(dsNew(n), "On_Order")
        daDGV(n).Fill(dsDGV(n), "On_Order")

        'Create new row
        Dim rowNew(n) As DataRow = dsNew(n).Tables("On_Order").NewRow
        Dim rowDGV(n) As DataRow = dsDGV(n).Tables("On_Order").NewRow

        'Add data to the row from text
        rowNew(n)("Order_Number") = txtOrder_Number.Text
        rowNew(n)("Vendor") = cmb_Vendor.Text
        rowNew(n)("Employee") = txtEmployee.Text
        rowNew(n)("Part_Number") = cmbPart_Number.Text
        rowNew(n)("Model_Number") = cmbModel_Number.Text
        rowNew(n)("Qty") = txtQty.Text

        rowDGV(n)("Part_Number") = cmbPart_Number.Text
        rowDGV(n)("Model_Number") = cmbModel_Number.Text
        rowDGV(n)("Qty") = txtQty.Text

        'Add row to data set
        dsNew(n).Tables("On_Order").Rows.Add(rowNew(n))
        dsDGV(n).Tables("On_Order").Rows.Add(rowDGV(n))

        'Set DataSource to DataGridView
        dgvNew(n).DataSource = dsDGV(n).Tables("On_Order")

        'Add to n for use in Save Order button
        n = n + 1


    End Sub

Everytime you click, you get a new row, with new data added to the previous data. It would then save to the database upon clicking a different "Save" button.

Apparently "arrays can not be declared with 'New'" so i need to think something around this...


Additionally, in my grid, it shows the part number column as Part_Number. Is it possible to change the heading?

I know its alot, i thought as soon as i figured out the above, i would be home free with the array...guess not

[demausdauth]

First off is this a real application or is it a homework assignment. I want to know so I know what we can and cannot do. If it is homework that may limit us in how we do things, if not... then great we can do it the way it should be done.

I am having some trouble understanding some of these requirements.

DforReal, on 20 October 2010 - 10:48 AM, said:

I am working on my new orders section and this section contains

order number txt box
employee txt box
vendor combo box
part number combo box
model number combo box
quantity combo box
datagridview
add item button
save order button

Easy enough to get, done.

DforReal, on 20 October 2010 - 10:48 AM, said:

The idea is that when you enter data in the fields, you can hit the add item button.

Which fields? all 6 fields? or only some of them.

DforReal, on 20 October 2010 - 10:48 AM, said:

The following actions will happen:

text in vendor combobox will be added as a dropdown selection
text in part number combobox will be added as a dropdown selection
text in model number combobox will be added as a dropdown selection

Ok so how do the vendor, part number, and model number comboboxes get values in them? Are they filled from the database? From the description above you want to be able to type into the combobox text field and then when the Add Item button is clicked it should add any different (new) item to the respective combobox datasource. Is that correct?

DforReal, on 20 October 2010 - 10:48 AM, said:

[part number can be associated with multiple model numbers but not vise versa
when an existing part number is chosen, the model numbers are narrowed down to being able to add a new model number or choose from a model number previously associated with part number chosen]

This suggests that when the part number combox selected index is changed that we will need to search the datagridview source to find a list of model numbers. Correct? Since the datagridview source will be the temporary datasource until a save to the database has happened.

DforReal, on 20 October 2010 - 10:48 AM, said:

DataGridView displays part number, model number and quantity

part number, model number, quantity are then cleared allowing you to re-enter those 3 fields, already associated with the order number, employee, vendor, then appear in a new line on the datagridview until the order is assembled each different part number at a time

This suggests to me that initially the datagridview is loaded from the database. So you may have filled orders (all 6 fields completed) and orders not completed (at least 3 fields filled in [part number, model number and quantity]). For those orders that are not completed we need to be able to entered the missing values (employee, ordernumber, and the vendor). Correct so far?

Then we need to be able to enter an entirely new order from scratch, entering and selecting all 6 fields worth of data. Correct so far? Is this added by clicking on the AddItem button?

How is incomplete data loaded into the fields to be edited? Is this done by selecting a row in the datagridview?

Another question. If we have just added an item is reasonable to assume that we then clear everything, as though we are waiting for a new order?

DforReal, on 20 October 2010 - 10:48 AM, said:

Then:Hitting the save order button:saves a line of data with all 6 fields entered, one line for each part number, and records the date item was saved, all recorded into a database.

Note: Should not record to database until save order is pressed.

This last one is pretty straight forward I think as well.

Splitting the requirements up has helped me understand a little of what may need to be done. I might have even answered some of the questions I asked but I still want your input on the questions.

[DforReal]

First off, I want to thank you for the help, I have been learning quite a bit and appreciate your responses greatly.

demausdauth, on 21 October 2010 - 09:13 PM, said:

First off is this a real application or is it a homework assignment. I want to know so I know what we can and cannot do. If it is homework that may limit us in how we do things, if not... then great we can do it the way it should be done.

I am creating this program for work purposes. We ship and receive different products and right now, we use a white board to track our information, and guys forget to log the info, so the white board is never accurate and it is hard to find things. My perception is that we can use the computer to easily track and log, and it wont be as time consuming as writing the information on the white board

Here is a visual of the form. Right now I am just working on coding the New Order group box

demausdauth, on 21 October 2010 - 09:13 PM, said:

DforReal, on 20 October 2010 - 10:48 AM, said:

The idea is that when you enter data in the fields, you can hit the add item button.

Which fields? all 6 fields? or only some of them.

The 6 fields need to be complete to be able to hit the add item button. I have coded that as below

    Private Sub txtOrder_Number_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtOrder_Number.TextChanged
        If txtOrder_Number.Text = Nothing Then
            btnAdd.Enabled = False
        ElseIf txtEmployee.Text = Nothing Then
            btnAdd.Enabled = False
        ElseIf cmb_Vendor.Text = Nothing Then
            btnAdd.Enabled = False
        ElseIf cmbPart_Number.Text = Nothing Then
            btnAdd.Enabled = False
        ElseIf cmbModel_Number.Text = Nothing Then
            btnAdd.Enabled = False
        ElseIf txtQty.Text = Nothing Then
            btnAdd.Enabled = False
        Else
            btnAdd.Enabled = True
        End If
    End Sub

Calling each required box in the textchanged action. That works

demausdauth, on 21 October 2010 - 09:13 PM, said:

DforReal, on 20 October 2010 - 10:48 AM, said:

The following actions will happen:

text in vendor combobox will be added as a dropdown selection
text in part number combobox will be added as a dropdown selection
text in model number combobox will be added as a dropdown selection

Ok so how do the vendor, part number, and model number comboboxes get values in them? Are they filled from the database? From the description above you want to be able to type into the combobox text field and then when the Add Item button is clicked it should add any different (new) item to the respective combobox datasource. Is that correct?

I have a separate table in my database called associations. This is where the part number and model numbers will be stored for use in reference to part_number and model_number drop downs. When the save item button is clicked, the information in those 2 fields will be saved to this section of the database which can be called on other forms. Figure this one shouldnt be too hard so I could figure it out myself at a later date (assumption, lol) Those drop downs will pull the selection information from that table, or if new info is typed in those boxes, it will be saved to the table and added to the drop down list upon clicking the add button

demausdauth, on 21 October 2010 - 09:13 PM, said:

DforReal, on 20 October 2010 - 10:48 AM, said:

[part number can be associated with multiple model numbers but not vise versa
when an existing part number is chosen, the model numbers are narrowed down to being able to add a new model number or choose from a model number previously associated with part number chosen]

This suggests that when the part number combox selected index is changed that we will need to search the datagridview source to find a list of model numbers. Correct? Since the datagridview source will be the temporary datasource until a save to the database has happened.

Pretty close, but it will be pulling from my associations table described above. So if i have part numbers 1,2, and model number a,b,c,d and I have entered as new items 1,a 1,b 2,c 2,d that tells me that part number 1 could possibly be model a or b, so if i select part number 1 from the drop down, i have the option to choose a or b for the model number or enter a new one.

[Our manufacturers often change their model numbers, however our part numbers stay the same for the new model]

demausdauth, on 21 October 2010 - 09:13 PM, said:

DforReal, on 20 October 2010 - 10:48 AM, said:

DataGridView displays part number, model number and quantity

part number, model number, quantity are then cleared allowing you to re-enter those 3 fields, already associated with the order number, employee, vendor, then appear in a new line on the datagridview until the order is assembled each different part number at a time

This suggests to me that initially the datagridview is loaded from the database. So you may have filled orders (all 6 fields completed) and orders not completed (at least 3 fields filled in [part number, model number and quantity]). For those orders that are not completed we need to be able to entered the missing values (employee, ordernumber, and the vendor). Correct so far?

Then we need to be able to enter an entirely new order from scratch, entering and selecting all 6 fields worth of data. Correct so far? Is this added by clicking on the AddItem button?

The DataGridView will be used only as a temporary list that will be cleared each time the form is loaded or the Save Order button is pressed.

What the DGV will be used for is a comprehensive list of the items and quantities associated with that order number, employee, and vendor.

We will not be writing anything (except the associations) to the database until the save order is pressed.
The add item will add the entered part number, model number, and qty to the dgv for the user to look at and figure out if the order has any additional items to add to the order. When the list in the DGV looks correct for that order number, the user will press the save order button. The information in the DGV will be purged, however those corresponding items will save to the On_Order database with each line of items being a new row.

DGV will look like this before hitting save order:
PN1 MN1 Qty1
PN2 MN2 Qty2

Which when hitting the save order button will translate into the On_Order database as:
OrderNumber Employee Vendor PN1 MN1 Qty1
OrderNumber Employee Vendor PN2 MN2 Qty1

demausdauth, on 21 October 2010 - 09:13 PM, said:

Another question. If we have just added an item is reasonable to assume that we then clear everything, as though we are waiting for a new order?

The add Item will clear the selections for partnumber modelnumber and qty, but the order number, employee, and vendor will be locked in.

When the save order button is hit, all 6 fields will be cleared

demausdauth, on 21 October 2010 - 09:13 PM, said:

How is incomplete data loaded into the fields to be edited? Is this done by selecting a row in the datagridview?

If you add a row of items that is incorrect, you can delete that row in the DGV and it will not write that row of data into the database when the save order button is hit (Still figuring out the logistics on that one)




If think ive addressed all the questions. If i have not, please let me know. Again, im new to programming but im learning and I know that there are many ways to write different actions, i just seem to think that they work better in my head.

You've definitely helped me out alot so far getting to where I am now and I greatly appreciate that

[demausdauth]

First off I want to apologize for not getting back to you sooner. Here I asked all these questions and I feel I haven't responded in a timely manner. My oldest son "found" several viruses while surfing, so I have been battling those most of the last couple days. Man I hate viruses! :

So on to the important stuff!

After reviewing everything that you posted and answered, and your screen shot of your form, I have few suggestions, etc... Other than the blatantly obvious that most programmers would state: I would have done it differently , a few things that might help.

A textbox is used to handle alpha-numeric data. Your quantity is (I assume) numerical only data. So as a suggestion I would use a NumberUpDown field instead of a textbox. The advantages to this are it already excludes the user from entering alphabetical characters, you can set minimum and maximum values, and you can set an interval just to name a few.

Working with what your screen shot and answers show I built a similar form, although I did not have access to an Access database so I have not run the code to determine if it works in the manner you want it to.

First thing I realized was that you do not have a common place to store you data access code. If you need to change your connection string, how many places in your code are you going to have to find and change? If you kept instantiating it in each of the events AddItem, SaveItem, EditItem, and probably in Load that is 4 places on this form alone. We can move this into the class level but once again if you do this for each of the forms in the application, how many does that become? Best method is to do it once and then make it accessible to the whole application, hence we have GlobalStuff.

Option Strict On

Imports System.Data
Imports System.Data.OleDb

Public Class GlobalStuff

First we turn on Option Strict, this forces us to explicitly declare things, and more importantly to explicitly convert datatypes. This isn't so important in this class, but on the forms it is. Next we import the Data namespaces that we will need to get at the Access database. Then comes the class declaration:GlobalStuff.

    Const ConString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source={0}\SRC.mdb"
    Private Shared _DatabaseFolder As String = String.Empty

    Public Shared ReadOnly Property ConnectionString() As String
        Get
            'sets a default for the database folder
            If String.IsNullOrEmpty(_DatabaseFolder) Then
                'sets the default to be the root of the C-Drive
                _DatabaseFolder = "C:"
            End If
            Return String.Format(ConString, _DatabaseFolder)
        End Get
    End Property

    Public Shared WriteOnly Property DataBaseFolder() As String
        Set(ByVal value As String)
            _DatabaseFolder = value
        End Set
    End Property

The first 2 items in this class are private member variables, meaning they are not accessible outside of the class, as well they do not need to be. The ConString is really the connectionstring for the database with folder missing, well not missing but replaced with the {0}. This will be useful later on when we use String.Format(). The next is the folder path that should be really set when the application itself loads, but for all intents and purposes we will fill it in the Form_Load event.

Next we have to properties of this class one being ReadOnly and one being WriteOnly, that bit being fairly self explanatory I hope. The DataBaseFolder property is how we set the folder path for the connections string. The ConnectionString property does 2 things, first it determines if the folder path has been set, if not it sets a default value to use, and second it returns a String that is a proper connectionstring. Now the String.Format() method is going to substitute each curlybraced item with a corresponding item following the first String Item. Huh?
Some links are in order: MSDN String.Format and another String.Format resource.

    Public Shared Function GetTable(ByVal selectStatement As String, ByVal params As List(Of OleDbParameter)) As DataTable

        Dim ReturnTable As New DataTable

        If Not String.IsNullOrEmpty(selectStatement.Trim()) Then
            Try
                Using oleConnection As New OleDbConnection(ConnectionString)

                    Using oleCommand As New OleDbCommand(selectStatement, oleConnection)

                        'check for parameters
                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        'fill the returning datatable
                        ReturnTable.Load(oleCommand.ExecuteReader())

                        ReturnTable.AcceptChanges()

                    End Using 'end of the command object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:GetTable")
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:GetTable")
            End Try
        Else
            MessageBox.Show("Select statement cannot be blank.", "SelectStatement:GetTable")
        End If 'end check for a select statement

        Return ReturnTable
    End Function

    Public Shared Function GetDataSet(ByVal selectStatement As String, ByVal params As List(Of OleDbParameter)) As DataSet
        Dim ReturnData As New DataSet

        If Not String.IsNullOrEmpty(selectStatement.Trim()) Then
            Try
                Using oleConnection As New OleDbConnection(ConnectionString)

                    Using oleAdapter As New OleDbDataAdapter(selectStatement, oleConnection)

                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleAdapter.SelectCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        'fill the dataset
                        oleAdapter.Fill(ReturnData)

                        ReturnData.AcceptChanges()
                    End Using 'end of the dataadapter object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:GetDataSet")
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:GetDataSet")
            End Try
        Else
            MessageBox.Show("Select statement cannot be blank.", "SelectStatement:GetDataSet")
        End If 'end check for a select statement

        Return ReturnData
    End Function

    Public Shared Function ExecuteSql(ByVal sqlStatement As String, ByVal params As List(Of OleDbParameter)) As Boolean

        If Not String.IsNullOrEmpty(sqlStatement) Then

            Try

                Using oleConnection As New OleDbConnection(ConnectionString)
                    Using oleCommand As New OleDbCommand(sqlStatement, oleConnection)

                        'check for parameters
                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        Dim recordsAffected As Integer = -1

                        'The ExecuteNonQuery method will perform Update,Insert, and Delete statements. If 
                        ' the statements execute, then the number of records that were affected by the
                        ' statement is returned. 
                        recordsAffected = oleCommand.ExecuteNonQuery()

                        'statement has been executed and we can return that it executed successfully
                        Return True

                    End Using 'end of the command object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:ExecuteSql")

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:ExecuteSql")

            End Try
        Else
            MessageBox.Show("SQL Statement cannot be blank.", "SQLStatement:ExecuteSql")
        End If
        Return False
    End Function
End Class

Now this is the meat and potatoes of this data access class. This is a bare bones list of functions. The first function does as the name implies gets a table. You pass in a select statement, and then a list of parameters (something you may or may not use) and it will return a DataTable object for the select statement you passed. Same goes for the GetDataset function, only instead of a DataTable returned it will be a DataSet object. The ExecuteSql function is a little different, its main purpose is to perform Updates, Inserts and Deletes on your database. Yes it could do Selects but it won't return any selected results, so what is the point, after all that is what the GetTable and GetDataSet functions are for. Anyways, when the ExecuteSql function is used it will return True/False depending on whether it was able to perform the specified SQL action against the database.

And that pretty much wraps up the GlobalStuff class.

[demausdauth]

On to the Orders form.
I named my form: NewOrders
3 textboxes (txtOrderNumberAdd, txtEmployeeAdd, txtEmployeeEdit)
5 comboboxes (cboVendorAdd, cboPartNumberAdd, cboModelNumberAdd, cboVendorEdit, cboOrderNumberEdit)
1 numericupdown (nudQuantity)
3 datagridviews (dgvOrderHistory, dgvNew, dgvEdit)
3 buttons (btnAdd, btnEdit, btnSave)

Based on some of the answers and looking at the code, I determined that I need to split up some of the operations for this form. Mainly I decided that I could make my code more readable by putting all the validation into its own function and perform the validation when the user tries to perform the add, rather than do validating when the user is typing text into the textbox. This causes excess processing that doesn't need to be done. Next I decided that the filling of the datagridviews needed to happen in 2 separate methods, the first one does the initial filling of the datatable and the second applies that filled data to the datagridview.

Option Strict On

Imports System.Data
Imports System.Data.OleDb


Public Class NewOrders

    Dim dtNewOrderHistory As DataTable
    Dim dtNewOrder As DataTable
    Dim dtEditOrder As DataTable

This is the start of the form, the main thing here is that we create 3 class level variables, the DataTables that will be the sources for the datagridviews.

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

        'fills the order history datagridview
        FillOrderHistory()

        'fills the new order datagridview
        FillNewOrder()



    End Sub

This is the initial load for these 2 datagridviews. The code for these 2 methods is shown below.

#Region "Control Events"

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        'checks to make sure the add item entries are valid
        If IsValidAddItem() Then

            '##### Order History DataGridView #####
            Dim drNewOrderHistory As DataRow

            'Gets a DataRow with the same schema(columns with datatypes, etc...) as the rest
            ' of the rows in the datatable for the orderhistory
            drNewOrderHistory = dtNewOrderHistory.NewRow

            'build the new row of information for orders history
            drNewOrderHistory("Order_Number") = txtOrderNumberAdd.Text.Trim()
            drNewOrderHistory("Vendor") = cboVendorAdd.SelectedText.Trim() 'cmb_Vendor.Text
            drNewOrderHistory("Employee") = txtEmployeeAdd.Text.Trim() 'txtEmployee.Text
            drNewOrderHistory("Part_Number") = cboPartNumberAdd.SelectedText.Trim()  'cmbPart_Number.Text
            drNewOrderHistory("Model_Number") = cboModelNumberAdd.SelectedText.Trim() 'cmbModel_Number.Text
            drNewOrderHistory("Qty") = Convert.ToInt32(nudQuantityAdd.Value) ' txtQty.Text

            'add the new row to the dataTable that backs the DataGridview
            dtNewOrderHistory.Rows.Add(drNewOrderHistory)

            'do NOT call AcceptChanges -- if we don't call accept changes then when we want to 
            ' post the updates to the database we can look for those rows that have an Added
            ' status.

            dgvOrderHistory.Refresh()

            ''Alternate code if the .Refresh() method does not work as intended
            'SetOrderHistory()
            '##### Order History DataGridView #####


            '##### New Order DataGridView #####
            Dim drNewOrder As DataRow
            'Gets a DataRow with the same schema(columns with datatypes, etc...) as the rest
            ' of the rows in the datatable for the orderhistory
            drNewOrder = dtNewOrder.NewRow

            'build the new row of information for orders history
            drNewOrder("Part_Number") = cboPartNumberAdd.SelectedText.Trim()  'cmbPart_Number.Text
            drNewOrder("Model_Number") = cboModelNumberAdd.SelectedText.Trim() 'cmbModel_Number.Text
            drNewOrder("Qty") = Convert.ToInt32(nudQuantityAdd.Value) ' txtQty.Text

            'add the new row to the dataTable that backs the DataGridview
            dtNewOrder.Rows.Add(drNewOrder)

            'do NOT call AcceptChanges -- if we don't call accept changes then when we want to 
            ' post the updates to the database we can look for those rows that have an Added
            ' status.

            dgvNew.Refresh()
            ''Alternate code if the .Refresh() method does not work as intended
            'SetNewOrders()

            '##### New Order DataGridView #####

        End If

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click




    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

    End Sub

#End Region

Here is the code for the Control events. In the btnAdd.click event we are checking for valid entries first then basically filling a datarow object that was created from its respective datatable (this is a built in method, awesome!) and then adding that datarow object to the table. Now in some cases you would call AcceptChanges on the datatable, but we don't want to do that, because later on in the Save event we can loop through the datatables and for each datarow we can check its rowstate and perform updates or inserts.

#Region "Private Methods"
    ''' <summary>
    ''' This function checks validity in the sense that the entered information
    ''' is not blank or has been selected.
    ''' </summary>
    ''' <returns>True if values have been entered or selected; False if a value is missing.</returns>
    ''' <remarks></remarks>
    Private Function IsValidAddItem() As Boolean

        Dim errorMessageToShow As String = String.Empty

        'check the order number -- does one exist
        If Not String.IsNullOrEmpty(txtOrderNumberAdd.Text.Trim()) Then

            'If order number is valid check the employee
            If Not String.IsNullOrEmpty(txtEmployeeAdd.Text.Trim()) Then

                'If Employee is valid -- check the vendor
                If cboVendorAdd.SelectedItem IsNot Nothing OrElse Not String.IsNullOrEmpty(cboVendorAdd.SelectedText) Then

                    'If vendor is valid -- check part number
                    If cboPartNumberAdd.SelectedItem IsNot Nothing OrElse Not String.IsNullOrEmpty(cboPartNumberAdd.SelectedText) Then

                        'If Part Number is Valid -- check the Model number
                        If cboModelNumberAdd.SelectedItem IsNot Nothing OrElse Not String.IsNullOrEmpty(cboModelNumberAdd.SelectedText) Then

                            'If Model Number is Valid -- check Quantity
                            If Not nudQuantityAdd.Value > 0 Then
                                errorMessageToShow = "Select or enter a Quantity"
                            End If

                        Else
                            errorMessageToShow = "Select or enter a Model Number"
                        End If

                    Else
                        errorMessageToShow = "Select or enter a Part Number"
                    End If
                Else
                    errorMessageToShow = "Select or enter a Vendor"
                End If
            Else
                errorMessageToShow = "Employee is not valid"
            End If

        Else
            errorMessageToShow = "Order Number is not valid."
        End If

        If Not String.IsNullOrEmpty(errorMessageToShow) Then
            MessageBox.Show(errorMessageToShow, "Validating Add Item")
            Return False
        End If

        Return True
    End Function

This is the validation method. Essentially it checks each of the various controls to make sure that data has been entered or selected, for adding an item only.

    ''' <summary>
    ''' This method will clear the Order History datatable of data and
    ''' get the schema and data from the Access database
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub FillOrderHistory()
        'this will clear the table of data
        dtNewOrderHistory = New DataTable()

        'this will fill the table with datafrom the database
        dtNewOrderHistory = GlobalStuff.GetTable("SELECT * FROM On_Order", Nothing)

        'calls the sub procedure that will set the datasource
        ' and change column headers
        SetOrderHistory()

    End Sub

    ''' <summary>
    ''' This method will clear the NewOrder datatable of data and 
    ''' get the schema from the Access database
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub FillNewOrder()
        'this will clear the table of data
        dtNewOrder = New DataTable()

        'this will fill the table with datafrom the database
        ' by passing an order number of -1, assuming there is no order numbers of -1,
        ' we will get back a datatable that has no rows but it will have the schema
        dtNewOrder = GlobalStuff.GetTable("SELECT Part_Number, Model_Number, Qty FROM On_Order WHERE Order_Number = -1", Nothing)

        'calls the sub procedure that will set the datasource
        ' and change column headers
        SetNewOrders()

    End Sub

    ''' <summary>
    ''' This method will assign the datatable to the order history datagridview 
    ''' and then rename the header text of some of the columns
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetOrderHistory()
        'this will fill the order history datagrid view
        dgvOrderHistory.DataSource = dtNewOrderHistory

        'change the datagrid view column captions
        dgvOrderHistory.Columns("Part_Number").HeaderText = "Part Number"
        dgvOrderHistory.Columns("Order_Number").HeaderText = "Order Number"

    End Sub

    ''' <summary>
    ''' This method will assign the datatable to the new order datagridview
    ''' and then rename the header text of some of the columns
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetNewOrders()
        'this will set the datasource and fill the datagrid view
        dgvNew.DataSource = dtNewOrder

        'change the datagrid view column captions
        dgvNew.Columns("Part_Number").HeaderText = "Part Number"
        dgvNew.Columns("Model_Number").HeaderText = "Model Number"
        dgvNew.Columns("Qty").HeaderText = "Quantity"


    End Sub
#End Region
End Class

The first 2 methods of these 4 are used to get the respective data from the database. The last 2 methods are used to put that data into the datagridviews and rename column headers. The reason I split these up was because if we need to perform the code that assigns the datatable to the datasource of a datagridview, we don't want to get this information from the database again, because we will lose any changes that were added to the datatable or edited.


Couple of additional things. With the GetTable function you can use this to fill anything control that is bindable: datagridviews or comboxes are 2 that come to mind, and you don't have to worry about connections or anything like that. Those are taken care of inside the function itself, no need to worry if the connection is left open or whatnot.

Consider moving the validation out of the TextChanged event, remember that code is going to run every time there is a change to the text, whereas say in the add click event, it's one time no extra processing and the same thing is accomplished.

Hopefully this helps you out. By no means is this complete. Any questions just ask.

This post has been edited by demausdauth: 24 October 2010 - 11:05 PM

[DforReal]

Wow, thanks again for the reply! Thats an awful lot of things to go through so it might take me a couple days. I will work it out and get back with the progress. Again, your talents are greatly appreciated. I feel like I should be able to start grasping alot of concepts from this.

[DforReal]

So far, I have made it to:

demausdauth, on 24 October 2010 - 09:40 PM, said:

    Public Shared Function GetTable(ByVal selectStatement As String, ByVal params As List(Of OleDbParameter)) As DataTable

        Dim ReturnTable As New DataTable

        If Not String.IsNullOrEmpty(selectStatement.Trim()) Then
            Try
                Using oleConnection As New OleDbConnection(ConnectionString)

                    Using oleCommand As New OleDbCommand(selectStatement, oleConnection)

                        'check for parameters
                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        'fill the returning datatable
                        ReturnTable.Load(oleCommand.ExecuteReader())

                        ReturnTable.AcceptChanges()

                    End Using 'end of the command object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:GetTable")
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:GetTable")
            End Try
        Else
            MessageBox.Show("Select statement cannot be blank.", "SelectStatement:GetTable")
        End If 'end check for a select statement

        Return ReturnTable
    End Function

    Public Shared Function GetDataSet(ByVal selectStatement As String, ByVal params As List(Of OleDbParameter)) As DataSet
        Dim ReturnData As New DataSet

        If Not String.IsNullOrEmpty(selectStatement.Trim()) Then
            Try
                Using oleConnection As New OleDbConnection(ConnectionString)

                    Using oleAdapter As New OleDbDataAdapter(selectStatement, oleConnection)

                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleAdapter.SelectCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        'fill the dataset
                        oleAdapter.Fill(ReturnData)

                        ReturnData.AcceptChanges()
                    End Using 'end of the dataadapter object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:GetDataSet")
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:GetDataSet")
            End Try
        Else
            MessageBox.Show("Select statement cannot be blank.", "SelectStatement:GetDataSet")
        End If 'end check for a select statement

        Return ReturnData
    End Function

    Public Shared Function ExecuteSql(ByVal sqlStatement As String, ByVal params As List(Of OleDbParameter)) As Boolean

        If Not String.IsNullOrEmpty(sqlStatement) Then

            Try

                Using oleConnection As New OleDbConnection(ConnectionString)
                    Using oleCommand As New OleDbCommand(sqlStatement, oleConnection)

                        'check for parameters
                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        Dim recordsAffected As Integer = -1

                        'The ExecuteNonQuery method will perform Update,Insert, and Delete statements. If 
                        ' the statements execute, then the number of records that were affected by the
                        ' statement is returned. 
                        recordsAffected = oleCommand.ExecuteNonQuery()

                        'statement has been executed and we can return that it executed successfully
                        Return True

                    End Using 'end of the command object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:ExecuteSql")

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:ExecuteSql")

            End Try
        Else
            MessageBox.Show("SQL Statement cannot be blank.", "SQLStatement:ExecuteSql")
        End If
        Return False
    End Function
End Class

So I figured i would paste the code into my globalstuff class that I created as per directions and break it down while I have the code in my project so my head can understand it. Granted, there is still a bit that is over my head in there, In my error set I am getting OleDbParameter, OleDbConnection, OleDbException is not defined. Looking at the code:

Public Shared Function GetTable(ByVal selectStatement As String, ByVal params As List(Of OleDbParameter)) As DataTable

I would thing that declaration for the shared function would declare selectStatement as a string and the OleDbParameter as the datatable


So I would assume that:

Using oleConnection As New OleDbConnection(ConnectionString)

would declare oleConnection as a new datatable(string) which i cant seem to wrap my head around.

I think I am missing something in figuring out that part of the code

[DforReal]

I have attached my SRC.mdb in case you can use it to see what I am working with.

Not sure if I am following this correctly but from the top, I have the following in a new class file I created called GlobalStuff.vb which includes all the following code:

Public Class GlobalStuff
    Const ConString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source={0}\SRC.mdb"
    Private Shared _DatabaseFolder As String = String.Empty

    Public Shared ReadOnly Property ConnectionString() As String
        Get
            'sets a default for the database folder
            If String.IsNullOrEmpty(_DatabaseFolder) Then
                'sets the default to be the root of the C-Drive
                _DatabaseFolder = "C:"
            End If
            Return String.Format(ConString, _DatabaseFolder)
        End Get
    End Property

    Public Shared WriteOnly Property DataBaseFolder() As String
        Set(ByVal value As String)
            _DatabaseFolder = value
        End Set
    End Property


    Public Shared Function GetTable(ByVal selectStatement As String, ByVal params As List(Of OleDbParameter)) As DataTable

        Dim ReturnTable As New DataTable

        If Not String.IsNullOrEmpty(selectStatement.Trim()) Then
            Try
                Using oleConnection As New OleDbConnection(ConnectionString)

                    Using oleCommand As New OleDbCommand(selectStatement, oleConnection)

                        'check for parameters
                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        'fill the returning datatable
                        ReturnTable.Load(oleCommand.ExecuteReader())

                        ReturnTable.AcceptChanges()

                    End Using 'end of the command object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:GetTable")
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:GetTable")
            End Try
        Else
            MessageBox.Show("Select statement cannot be blank.", "SelectStatement:GetTable")
        End If 'end check for a select statement

        Return ReturnTable
    End Function

    Public Shared Function GetDataSet(ByVal selectStatement As String, ByVal params As List(Of OleDbParameter)) As DataSet
        Dim ReturnData As New DataSet

        If Not String.IsNullOrEmpty(selectStatement.Trim()) Then
            Try
                Using oleConnection As New OleDbConnection(ConnectionString)

                    Using oleAdapter As New OleDbDataAdapter(selectStatement, oleConnection)

                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleAdapter.SelectCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        'fill the dataset
                        oleAdapter.Fill(ReturnData)

                        ReturnData.AcceptChanges()
                    End Using 'end of the dataadapter object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:GetDataSet")
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:GetDataSet")
            End Try
        Else
            MessageBox.Show("Select statement cannot be blank.", "SelectStatement:GetDataSet")
        End If 'end check for a select statement

        Return ReturnData
    End Function

    Public Shared Function ExecuteSql(ByVal sqlStatement As String, ByVal params As List(Of OleDbParameter)) As Boolean

        If Not String.IsNullOrEmpty(sqlStatement) Then

            Try

                Using oleConnection As New OleDbConnection(ConnectionString)
                    Using oleCommand As New OleDbCommand(sqlStatement, oleConnection)

                        'check for parameters
                        If params IsNot Nothing AndAlso params.Count > 0 Then
                            ' add the parameters to the command object
                            oleCommand.Parameters.AddRange(params.ToArray())
                        End If

                        'make sure the connection is open
                        If Not oleConnection.State = ConnectionState.Open Then oleConnection.Open()

                        Dim recordsAffected As Integer = -1

                        'The ExecuteNonQuery method will perform Update,Insert, and Delete statements. If 
                        ' the statements execute, then the number of records that were affected by the
                        ' statement is returned. 
                        recordsAffected = oleCommand.ExecuteNonQuery()

                        'statement has been executed and we can return that it executed successfully
                        Return True

                    End Using 'end of the command object
                End Using 'end of the connection object

            Catch ex As OleDbException
                MessageBox.Show(ex.Message, "OleDbException:ExecuteSql")

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Exception:ExecuteSql")

            End Try
        Else
            MessageBox.Show("SQL Statement cannot be blank.", "SQLStatement:ExecuteSql")
        End If
        Return False
    End Function
End Class

I am assuming that this class, GlobalStuff.vb will handle just the database connection information and will declare variables for reference in my entire application, so this GlobalStuff class file is where I am receiving the OleDbParameter, OleDbConnection, OleDbException is not defined errors that I cant seem to figure out why it thinks we havent delcared those yet.

I feel like my brain is all over the place because that is an awful lot of code to sift through and learn, so sorry if I seem like I am a child at trying to figure this out, but you have been an amazing help so far and I cant thank you enough

Attached File(s)

•  SRC.zip (36.87K)
  Number of downloads: 64

[demausdauth]

DforReal, on 26 October 2010 - 07:40 AM, said:

So I figured i would paste the code into my globalstuff class that I created as per directions and break it down while I have the code in my project so my head can understand it. Granted, there is still a bit that is over my head in there, In my error set I am getting OleDbParameter, OleDbConnection, OleDbException is not defined.

Make sure that you have the Imports at the top of the class.

DforReal, on 26 October 2010 - 07:40 AM, said:

Looking at the code:

Public Shared Function GetTable(ByVal selectStatement As String, ByVal params As List(Of OleDbParameter)) As DataTable

I would thing that declaration for the shared function would declare selectStatement as a string and the OleDbParameter as the datatable

What this signature is telling us:

• That selectStatement is defined as a String. And when we use this in code it will be set and then passed.
  
• That the params is a generic List of the type OleDbParameter.
  
• When the function finishes processing it will return a DataTable object, hopefully filled with data.

When we want to use this function we need to pass it 2 things a String value that is a select statement and a List of parameters. Now to begin with you can ignore the List of parameters and pass Nothing to satisfy this requirement of the function. Later, as you understand it more you may want to use them, if it will make it easier for you, you can make the List optional:

Public Shared Function GetTable(ByVal selectStatement As String, Optional ByVal params As List(Of OleDbParameter) = Nothing) As DataTable

DforReal, on 26 October 2010 - 07:40 AM, said:

So I would assume that:

Using oleConnection As New OleDbConnection(ConnectionString)

would declare oleConnection as a new datatable(string) which i cant seem to wrap my head around.

I think I am missing something in figuring out that part of the code

What this line is doing is it creates a new Connection object using the preset ConnectionString. It is equivalent to:

Dim oleConnection As OleDbConnection
oleConnection = New OleDbConnection(ConnectionString)

The Using statement will automatically close the connection object and dispose of it, so we don't need to worry about specifically calling that in code.

[DforReal]

I am really like the direction this is headed. Its alot easier to read and I would have never thought of using a validation code.

One snag I hit was that the code:

                    If cboVendorAdd.SelectedItem IsNot Nothing OrElse Not String.IsNullOrEmpty(cboVendorAdd.SelectedText) Then

Does not register typed information in the field. I thought if i changed it from .SelectedText to .Text it would straighten out, but the inputted information doesnt carry over to the datagridviews (shows blank) but it does bypass the validation.

I am thinking that upon action of losing focus of the cboVendorAdd, the text in the cboVendorAdd gets added to the combobox list for the SelectedText to work.

Does the sound right?

    Private Sub cboVendorAdd_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboVendorAdd.LostFocus
        cboVendorAdd.Items.Add(cboVendorAdd.Text)
    End Sub

which would add the current text as a combobox item and allow the validation to work correctly

[DforReal]

[quote name='DforReal' date='26 October 2010 - 09:15 AM' timestamp='1288113336' post='1150775']
One snag I hit was that the code:

                    If cboVendorAdd.SelectedItem IsNot Nothing OrElse Not String.IsNullOrEmpty(cboVendorAdd.SelectedText) Then

Does not register typed information in the field. I thought if i changed it from .SelectedText to .Text it would straighten out, but the inputted information doesnt carry over to the datagridviews (shows blank) but it does bypass the validation.


I found the hand up.

I changed

drNewOrderHistory("Order_Number") = txtOrderNumberAdd.Text.Trim()
            drNewOrderHistory("Vendor") = cboVendorAdd.SelectedText.Trim() 'cmb_Vendor.Text
            drNewOrderHistory("Employee") = txtEmployeeAdd.Text.Trim() 'txtEmployee.Text
            drNewOrderHistory("Part_Number") = cboPartNumberAdd.SelectedText.Trim()  'cmbPart_Number.Text
            drNewOrderHistory("Model_Number") = cboModelNumberAdd.SelectedText.Trim() 'cmbModel_Number.Text
            drNewOrderHistory("Qty") = Convert.ToInt32(nudQuantityAdd.Value) ' txtQty.Text
 
            'add the new row to the dataTable that backs the DataGridview
            dtNewOrderHistory.Rows.Add(drNewOrderHistory)
 
            'do NOT call AcceptChanges -- if we don't call accept changes then when we want to
            ' post the updates to the database we can look for those rows that have an Added
            ' status.
 
            dgvOrderHistory.Refresh()
 
            ''Alternate code if the .Refresh() method does not work as intended
            'SetOrderHistory()
            '##### Order History DataGridView #####
 
 
            '##### New Order DataGridView #####
            Dim drNewOrder As DataRow
            'Gets a DataRow with the same schema(columns with datatypes, etc...) as the rest
            ' of the rows in the datatable for the orderhistory
            drNewOrder = dtNewOrder.NewRow
 
            'build the new row of information for orders history
            drNewOrder("Part_Number") = cboPartNumberAdd.SelectedText.Trim()  'cmbPart_Number.Text
            drNewOrder("Model_Number") = cboModelNumberAdd.SelectedText.Trim() 'cmbModel_Number.Text
            drNewOrder("Qty") = Convert.ToInt32(nudQuantityAdd.Value) ' txtQty.Text

To

            'build the new row of information for orders history
            drNewOrderHistory("Order_Number") = txtOrderNumberAdd.Text.Trim()
            drNewOrderHistory("Vendor") = cboVendorAdd.Text.Trim() 'cmb_Vendor.Text
            drNewOrderHistory("Employee") = txtEmployeeAdd.Text.Trim() 'txtEmployee.Text
            drNewOrderHistory("Part_Number") = cboPartNumberAdd.Text.Trim()  'cmbPart_Number.Text
            drNewOrderHistory("Model_Number") = cboModelNumberAdd.Text.Trim() 'cmbModel_Number.Text
            drNewOrderHistory("Qty") = Convert.ToInt32(nudQuantityAdd.Value) ' txtQty.Text

            'add the new row to the dataTable that backs the DataGridview
            dtNewOrderHistory.Rows.Add(drNewOrderHistory)

            'do NOT call AcceptChanges -- if we don't call accept changes then when we want to 
            ' post the updates to the database we can look for those rows that have an Added
            ' status.

            dgvOrderHistory.Refresh()

            ''Alternate code if the .Refresh() method does not work as intended
            'SetOrderHistory()
            '##### Order History DataGridView #####


            '##### New Order DataGridView #####
            Dim drNewOrder As DataRow
            'Gets a DataRow with the same schema(columns with datatypes, etc...) as the rest
            ' of the rows in the datatable for the orderhistory
            drNewOrder = dtNewOrder.NewRow

            'build the new row of information for orders history
            drNewOrder("Part_Number") = cboPartNumberAdd.Text.Trim()  'cmbPart_Number.Text
            drNewOrder("Model_Number") = cboModelNumberAdd.Text.Trim() 'cmbModel_Number.Text
            drNewOrder("Qty") = Convert.ToInt32(nudQuantityAdd.Value) ' txtQty.Text

So now I am going to try to code the save order buttom to save the information collected to the database and bind the dgvhistory to the database table so the dgvhistory only displays the saved orders. I should be able to do this but I will be back if I run into too many snags.

This is really coming along and im starting to understand a bit, im excited!

[DforReal]

Updated: I suck. Changing .SelectedText to .Text allows user to pass validation with no entry in the cbo fields. Damned if I do, damned if I dont.

Also, I have no idea how to enact the AcceptChanges action with the database string set up as it is now. I think I keep confusing myself.