Inserting Data To Access From DataGridView

[JoeMicro]

Hi,

i m trying to build an application where the user could enter data in a datagridview and it should be inserted into an Access Database (or to SQL Server which ever you will advise me to use)

i know how to create a DataSet, i tried creating a new DataTable in the dataset and bind that to the DGV but i couldn’t get it to work.

could anybody give me a head start as in which Imports to use, are how to build the Insert Query, or maybe Linq is preferable (i m using Vb.net 2008)

what i m trying to do is similer to an Invoice form.

any help is greatly appriciated

Thank you,

Joe

 

[RiverGuy]

Do you have anything started? If so, can you post your code?

 

[JoeMicro]

i didnt really do anything. is it possible to give me a heads up?

 

[RiverGuy]

OK, question #1, is this DataGridView purely for new data entry, or do you want to start the user out with a grid of the data which already exists in the table?

 

[JoeMicro]

Empty data grid view (with sum Combo Box, but i dont think thats what you asked)

Purely ready for new data entry!

thanks for taking your time to help me here.

Joe

 

[RiverGuy]

Normally, when you have a databound control such as a DataGridView, you will bind it to a DataSet or DataTable. The DataTables will be filled via DataAdapters from the database. The DataAdapters will have Select, Insert, Update, and Delete Commands assigned. When you make changes to your DataTable, you can then call the .Update method of your DataAdapter to reconcile the changes with the database. The Update method will call the appropriate Insert, Update and Delete commands based on the changes made. As you can see, it's a lot of code and a lot of work. Yes, there are wizards to help you do some of this stuff, and while they are OK, it's a little harder to give you instructions on using the wizards instead of plain old code. The above method is the best way to create your application.

However, since your app is a little easier--you just need to insert new records, you can write your own custom code.

I would start first by creating a DataTable, and binding your DataGridView to it. For example:

        Dim DT As New DataTable
        DT.TableName = "YourTableName"
        DT.Columns.Add("Column1", System.Type.GetType("System.Int32"))
        DT.Columns.Add("Column2", System.Type.GetType("System.String"))
        YourDataGridView.DataSource = DT

Note you will define the columns to match your database's table. Your DataGridView will now be bound to an empty DataTable.

Now, when you're ready to save the records to the database, you can loop through the rows of your DataTable and execute a query to insert them. For example:

        Dim CMD As New SqlClient.SqlCommand
        CMD.Connection = YourConnection
        CMD.CommandType = CommandType.Text
        CMD.CommandText = "INSERT INTO YourTable (Column1, Column2) VALUES (@Column1, Column2)"
        CMD.Parameters.Add("@Column1", SqlDbType.Int)
        CMD.Parameters.Add("@Column2", SqlDbType.VarChar, 50)
        For Each dr As DataRow In DT.Rows
            CMD.Parameters("@Column1").Value = dr.Item("Column1")
            CMD.Parameters("@Column2").Value = dr.Item("Column2")
            CMD.ExecuteNonQuery()
        Next

Make sense? Also, the above examples are for SQL Server. You should decide whether to use SQL Server or Access based on what fits the needs of your business.

 

[JoeMicro]

Thank you RiverGuy,

i know how to create a datatset with the wizard, and what i tried to do was create an empty table in the dataset and bind that to the DGV, but i didnt understand what Imports you have to use at the beginning of the code and how to get the Insert's and Update's to work.

i m coming from Access and i thought that the DGV works like a table in Access, but from your code i guess it works more like an excel sheet, you need to loop through the rows to get the data, Isnt that a long process when we have like 200 > rows to insert?

i would create a fixed table rather than a dynamic because i will be needing some combo boxes with a lot of fields, and i will also need to edit each receipt.

in other words i need to understand how an invoice form would work in order to get me going. i m sure an invoice form is not connected to a filled table...

Thank you again

Joe

 

[jebenson]

If you are using Access, use this: Imports System.Data.OleDb

For SQL Server, use this: Imports System.Data.SqlClient




I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[RiverGuy]

i m coming from Access and i thought that the DGV works like a table in Access, but from your code i guess it works more like an excel sheet, you need to loop through the rows to get the data, Isnt that a long process when we have like 200 > rows to insert?

In ADO.Net, the data is not connected to the database. A copy of data is downloaded to memory, the data is altered in memory, and then the changes are reconciled back to the database. 200 rows is very small. Looping through 200 rows will not be that slow. In fact, if you create everything from the wizard...including your DataAdapters and Insert/Update/Delete Commands, the code will loop through your changed rows in your DataTable.

i would create a fixed table rather than a dynamic because i will be needing some combo boxes with a lot of fields, and i will also need to edit each receipt.

There's nothing wrong with that. The example I provided above is an example of what I would consider to be a fixed table. A dynamic table in my opinion would not have the columns hard coded. You might be thinking of creating a strongly-typed table, which is fine as well.