Insert Data in SQL 2005 table

bex · June 30th, 2009, 02:58 PM

Ok a have a question?

Is it possible to Insert DataGridView Data to a new table not the table that the data come from

Code:

Dim cmd As SqlCommand = cnn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * From Table1"
Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd
Dim ds As DataSet = New DataSet
da.Fill(ds)
DataGridViewSearch.DataSource = ds

Now if the Save/Update/Insert Button is clicked can that data be iserted in Table2???

pons_saravanan · July 9th, 2009, 10:18 AM

can you please share how are you going to update the database with changes?

If you are using stored procedures or update/insert sql statements change those statements to point to table2 to insert/update them.

Show us the code you have currently written for the update/insert so that it will be helpful for analysing further.

if you would like to have some samples please refer this article
http://www.vbknowledgebase.com/WebAp...ough&PartId=50

bex · July 9th, 2009, 11:55 AM

Dim cnn AsNew SqlConnection(My.Settings.TrackingConnectionString)
Dim cmd As SqlCommand = cnn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "IF (EXISTS (SELECT * FROM Orders AS t1 WHERE t1.AccountNumber = @AccountNumber and t1.SimNet = @SimNet and t1.OrderNumber='" & txtOrderNumber.Text & "')) Begin RAISERROR('Order for this account already exists.', 16, 1); End Else Begin Insert Into Orders (OrderNumber, AccountNumber, CustomerName,) Values (@OrderNumber, @AccountNumber, @CustomerName) End"
Dim account AsString = DataGridViewSearch.CurrentRow.Cells("Customer Account").Value.ToString()
Dim name AsString = DataGridViewSearch.CurrentRow.Cells("Customer Name").Value.ToString()
Dim sim AsString = DataGridViewSearch.CurrentRow.Cells("SimNet").Value.ToString()
Dim price AsString = DataGridViewSearch.CurrentRow.Cells("Cost").Value.ToString()
Dim commission AsString = DataGridViewSearch.CurrentRow.Cells("Commission").Value.ToString()
Dim quantity AsString = DataGridViewSearch.CurrentRow.Cells("Quantity").Value.ToString()

cmd.Parameters.AddWithValue("@AccountNumber", account)
cmd.Parameters.AddWithValue("@CustomerName", name)

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()

This is working but it updates only the selected row not the changes in the grid,

I need to update all changes under 1 click, I Was thinking to have a column with check boxes and say For each checkbox.checked insert data......
Is that posible ??

pons_saravanan · July 9th, 2009, 09:04 PM

Quote:

Originally Posted by bex

Dim cnn AsNew SqlConnection(My.Settings.TrackingConnectionString)
Dim cmd As SqlCommand = cnn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "IF (EXISTS (SELECT * FROM Orders AS t1 WHERE t1.AccountNumber = @AccountNumber and t1.SimNet = @SimNet and t1.OrderNumber='" & txtOrderNumber.Text & "')) Begin RAISERROR('Order for this account already exists.', 16, 1); End Else Begin Insert Into Orders (OrderNumber, AccountNumber, CustomerName,) Values (@OrderNumber, @AccountNumber, @CustomerName) End"
Dim account AsString = DataGridViewSearch.CurrentRow.Cells("Customer Account").Value.ToString()
Dim name AsString = DataGridViewSearch.CurrentRow.Cells("Customer Name").Value.ToString()
Dim sim AsString = DataGridViewSearch.CurrentRow.Cells("SimNet").Value.ToString()
Dim price AsString = DataGridViewSearch.CurrentRow.Cells("Cost").Value.ToString()
Dim commission AsString = DataGridViewSearch.CurrentRow.Cells("Commission").Value.ToString()
Dim quantity AsString = DataGridViewSearch.CurrentRow.Cells("Quantity").Value.ToString()

cmd.Parameters.AddWithValue("@AccountNumber", account)
cmd.Parameters.AddWithValue("@CustomerName", name)

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()

This is working but it updates only the selected row not the changes in the grid,

I need to update all changes under 1 click, I Was thinking to have a column with check boxes and say For each checkbox.checked insert data......
Is that posible ??

Hi

it is currently taking values from currentrow which is selected. it works as per the code.
If you want to update all the changes to the grid. you need to persist the data in the dataset or you need to update database when ever you are done editng the row.

It is bit harder to explain the entire process(http://www.vbknowledgebase.com/WebAp...ough&PartId=50) but outline is

1) have a edit/update column
2)on edit click bring the row to edit mode
3) once edited click update on the edited row
4) on update command update the changes to either dataset or database directly
5)bring the row mode to readonly/normal mode
6) rebind the grid

bex · July 10th, 2009, 03:56 AM

thanks for the reply but that still requres you to edit button per row. is there a way i can loop throw the rows?

thanks

pons_saravanan · July 10th, 2009, 04:25 AM

Quote:

Originally Posted by bex

thanks for the reply but that still requres you to edit button per row. is there a way i can loop throw the rows?

thanks

hi

Ok for that, you need to keep all the rows in edit mode. do something like follows

1) Bring the entire grid to editmode by specifying all the rows in edit mode while binding.
2) loop through all the rows by a loop and get all the controls by row.FindControl and get the respective value and use your earlier code to update the database.

I dont have any working samples for that but still it is workable. if you have any doubts please feel free to ask

bex · July 10th, 2009, 05:21 AM

can you post me a little example to looping throw the check boxes?

thanks

pons_saravanan · July 10th, 2009, 05:33 AM

Quote:

Originally Posted by bex

can you post me a little example to looping throw the check boxes?

thanks

Hi
i hope you meant it is gridview looping through not checkboxes

based on that assumption all the rows must be in edit mode

the following code will be useful for understanding how to loop through the gridview rows.

ForEach UpdateRow As GridViewRow In rgList.Rows
Dim txtDescription As TextBox = DirectCast(UpdateRow.FindControl("txtDescription"), TextBox)
'use txtDescription.Text to update the database value pertaining to this row
Next

bex · July 10th, 2009, 05:59 AM

I got this error :
UpdateRow.FindControl is not a member of System.Windows.Forms.datagridviewRow?

pons_saravanan · July 10th, 2009, 06:18 AM

If you are using windows datagridview, then you are in a wrong section . this is asp.net 3.5.