INSERT unbound data into SQL Database table

[cseewald]

Hi All

My application uses a datagridview to allow a user to enter data to create new Companies, Employees etc.

The data entry happens on a datagridview. Some of the data entered is mandatory and some is optional. How do i loop through the datagrid view ensuring that at least all Mandatory information is supplied and then INSERT the data.
This is the datagridview structure:
Col(0) ID column
Col(1) Description Column ie Company Name
Col(3) Value Column ie "ABC Company"
Col(4) Optional / Mandatory indicator

The conditions that must be met to insert data are:

• Col(0) must contain a value
• If Col(4) is "Mandatory" Then Column 3 must have a value

The insert statement is very simple, its the firing of the insert statement based on the conditions for each row in the datagridview that is the issue.

[Lord_Rat]

http://msdn.microsoft.com/en-us/library/ykdxa0bc.aspx

[cseewald]

Hi Lord Rat

Not sure how i missed that post, been searching for a while now. That helps with the validation. My main concern now is firing the insert statement for each row, once the validation has succeeded. My INSERT statement is a stored procedure. How can i call the sproc for each successful row so that the data can be inserted

[Lord_Rat]

Loop?

Or if it's on a row-by-row basis as they're edited, you could do it in the dataGridView1_CellEndEdit

[cseewald]

Hi Lord_Rat

Thanks for the reply, once i have this one nailed, 80% of my app can be completed.

The loop is defintely the way to go, however calling a sproc with all its parameter definitions, opening and closing connections etc within the loop would be a huge waste of rescource.

How can i code it so that the parameter declarations and connection details etc are only declared once (outside the loop?), and only the insert statement fires repatedly for each row that passes the validation.

Thanks

Chris

[jmcilhinney]

I would question why you're grid isn't bound. If you are going to use the grid unbound then, to insert the data into the database, I'd suggest looping through the grid and, for each row, adding a row to a DataTable. You can then use a DataAdapter to save the whole lot to the database. If you're going to do that though, you may as well create the DataTable in the first place and bind it to the grid. By configuring the DataTable you also automatically validate your data, i.e. if a DataColumn doesn;t all nulls then the user will have to enter a value into that column in the grid.

[cseewald]

Hi jmcilhinney

The app uses an entity attribute model allowing for different datatypes to be stored ie Alphanumeric,Date and numeric.

For example
Entity: Company
Attributes
Company Name : Alphanumeric data
Number of Emps : Numeric data
Registration date: Date/time data

This data is then written to the different tables below:
tbEntAttAlphaNumericVal
tbEntAttNumericVal
tbEntAttDateTimeVal

The INSERT statements fire based on the fact that the value has been supplied if it is Mandatory and the row is not blank.

From my original post

The conditions that must be met to insert data are:
Col(0) must contain a value
If Col(4) is "Mandatory" Then Column 3 must have a value.

Where can i find code to implement your suggestion of datatables and loops?

[jmcilhinney]

Follow the Database FAQ link in my signature and check out the .NET resources there. One of the links is to my own Retrieving & Saving Data thread in the CodeBank. One of the examples it provides is for inserting multiple records in a batch. You would use a 'foreach' loop to enumerate the Rows collection of the grid and to get the data to add to the DataTable.