Click here to Skip to main content
15,891,184 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Some columns on my grid is currently populating from a database table.
It also contains some textbox and DDL for user to input values.
Now, when user click on save button, i want to save all data from grid, which is prepopluating and which is entered by user into a separate database table.
I am thinking of using DataTable, but don't know how to do this.
My code is in VB.NET.

Any help is really appreciated.

[Added from Comment]

Here's my code In Insert command, i need to pass my pk value also, which will come from a label control on page.

VB
Public Sub btnsave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsave.Click If True Then Dim rowIndex As Integer = 0 Dim sc As New StringCollection() If ViewState("CurrentTable") IsNot Nothing Then Dim dtCurrentTable As DataTable = DirectCast(ViewState("CurrentTable"), DataTable) If dtCurrentTable.Rows.Count > 0 Then For i As Integer = 1 To dtCurrentTable.Rows.Count 'extract the TextBox values 

Dim box1 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(4).FindControl("TxtSTUPort"), TextBox) 
Dim box2 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(5).FindControl("TxtAAPort"), TextBox) 
Dim box3 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(7).FindControl("TxtFPort"), TextBox) 
Dim box4 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(8).FindControl("TxtQoFUsed"), TextBox) 
Dim box5 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(9).FindControl("TxtPPort"), TextBox) 
Dim box6 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(10).FindControl("TxtBFPort"), TextBox) 
Dim box7 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(11).FindControl("TxtLOPort"), TextBox) 
Dim ddl As DropDownList = DirectCast(GD_Prod.Items(rowIndex).Cells(12).FindControl("ddlLOCode"), DropDownList) 
Dim box8 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(13).FindControl("TxtTSPort"), TextBox) 'get the values from the TextBoxes 'then add it to the collections with a comma "," as the delimited values 

sc.Add(box1.Text + "," + box2.Text & "," + box3.Text & "," + box4.Text & "," + box5.Text & "," + box6.Text & "," + box7.Text & "," + ddl.SelectedValue & "," + box8.Text) 
rowIndex += 1 
Next 'Call the method for executing inserts InsertRecords(sc) 
End If 
End If 
End If 
End Sub 

Private Sub InsertRecords(ByVal sc As StringCollection) 
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("FOOD_AND_NUTRITIONConnectionString").ConnectionString) 
Dim sb As New StringBuilder(String.Empty) 
Dim splitItems As String() = Nothing 

For Each item As String In sc Const sqlStatement As String = "INSERT INTO FNProdRecDetails (PL_Student_Portions_Planned, PL_Adults_ALC__Portions_Planned, PL_Factor_Portions, DOC_Quantity_Of_Food_Used, DOC_Portions_Prepared, DOC_Portions_Brought_Forward, DOC_Portions_Leftover,DOC_Leftover_Code, DOC_Total_Portions_Served) VALUES" 

If item.Contains(",") Then 
splitItems = item.Split(",".ToCharArray()) 
sb.AppendFormat("{0}('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}'); ", sqlStatement, splitItems(0), splitItems(1), splitItems(2), splitItems(3), splitItems(4), splitItems(5), splitItems(6), splitItems(7), splitItems(8)) 
End If 
Next 
Try 
conn.Open() 
Dim cmd As New SqlCommand(sb.ToString(), conn) 
cmd.CommandType = CommandType.Text 
cmd.ExecuteNonQuery() 'Display a popup which indicates that the record was successfully inserted 
Page.ClientScript.RegisterClientScriptBlock(GetType(Page), "Script", "alert('Records Successfuly Saved!');", True) 
Catch ex As System.Data.SqlClient.SqlException 
Dim msg As String = "Insert Error:" 
msg += ex.Message 
Throw New Exception(msg) 
Finally 
conn.Close() 
End Try 
End Sub
Posted
Updated 13-Jan-11 14:52pm
v4
Comments
thatraja 13-Jan-11 20:59pm    
check my updated answer

It's really a frequent question here. You can search CP also google.

Save Dynamic TextBox Values from GridView to Database[^]

OP wrote:
But i have one issue. In my insert query, i want to insert an additional field(PKey Value) which is not related to my grid columns. Since when i run insert command it shows me error, that primary key can't be null. on same webform i have one label which have that primary key value, now how can i insert this value using code above.


You need to check if the PrimaryKey field value is null before saving the data. So check the value of the primary key column value while debugging.

Let us know

BTW Here after include your code in your question for quick response.
 
Share this answer
 
v2
Comments
Rajesh Anuhya 11-Jan-11 23:51pm    
Good answer +5
Rohit.Net100 12-Jan-11 16:13pm    
I tried the code mention in link which you send me.
But i have one issue.
In my insert query, i want to insert an additional field(PKey Value) which is not related to my grid columns.
Since when i run insert command it shows me error, that primary key can't be null.
on same webform i have one label which have that primary key value, now how can i insert this value using code above.

Please help.
thatraja 12-Jan-11 23:55pm    
Probably you should show us your code man.
Rohit.Net100 13-Jan-11 9:13am    
Here's my code
In Insert command, i need to pass my pk value also, which will come from a label control on page.
Public Sub btnsave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsave.Click
If True Then
Dim rowIndex As Integer = 0
Dim sc As New StringCollection()
If ViewState("CurrentTable") IsNot Nothing Then
Dim dtCurrentTable As DataTable = DirectCast(ViewState("CurrentTable"), DataTable)
If dtCurrentTable.Rows.Count > 0 Then
For i As Integer = 1 To dtCurrentTable.Rows.Count
'extract the TextBox values
Dim box1 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(4).FindControl("TxtSTUPort"), TextBox)
Dim box2 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(5).FindControl("TxtAAPort"), TextBox)
Dim box3 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(7).FindControl("TxtFPort"), TextBox)
Dim box4 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(8).FindControl("TxtQoFUsed"), TextBox)
Dim box5 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(9).FindControl("TxtPPort"), TextBox)
Dim box6 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(10).FindControl("TxtBFPort"), TextBox)
Dim box7 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(11).FindControl("TxtLOPort"), TextBox)
Dim ddl As DropDownList = DirectCast(GD_Prod.Items(rowIndex).Cells(12).FindControl("ddlLOCode"), DropDownList)
Dim box8 As TextBox = DirectCast(GD_Prod.Items(rowIndex).Cells(13).FindControl("TxtTSPort"), TextBox)

'get the values from the TextBoxes
'then add it to the collections with a comma "," as the delimited values
sc.Add(box1.Text + "," + box2.Text & "," + box3.Text & "," + box4.Text & "," + box5.Text & "," + box6.Text & "," + box7.Text & "," + ddl.SelectedValue & "," + box8.Text)
rowIndex += 1
Next
'Call the method for executing inserts
InsertRecords(sc)
End If
End If
End If
End Sub

Private Sub InsertRecords(ByVal sc As StringCollection)
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("FOOD_AND_NUTRITIONConnectionString").ConnectionString)
Dim sb As New StringBuilder(String.Empty)
Dim splitItems As String() = Nothing
For Each item As String In sc
Const sqlStatement As String = "INSERT INTO FNProdRecDetails (PL_Student_Portions_Planned, PL_Adults_ALC__Portions_Planned, PL_Factor_Portions, DOC_Quantity_Of_Food_Used, DOC_Portions_Prepared, DOC_Portions_Brought_Forward, DOC_Portions_Leftover,DOC_Leftover_Code, DOC_Total_Portions_Served) VALUES"

If item.Contains(",") Then
splitItems = item.Split(",".ToCharArray())
sb.AppendFormat("{0}('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}'); ", sqlStatement, splitItems(0), splitItems(1), splitItems(2), splitItems(3), splitItems(4), splitItems(5), splitItems(6), splitItems(7), splitItems(8))
End If
Next
Try
conn.Open()
Dim cmd As New SqlCommand(sb.ToString(), conn)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
'Display a popup which indicates that the record was successfully inserted
Page.ClientScript.RegisterClientScriptBlock(GetType(Page), "Script", "alert('Records Successfuly Saved!');", True)
Catch ex As System.Data.SqlClient.SqlException
Dim msg As String = "Insert Error:"
msg += ex.Message
Throw New Exception(msg)
Finally
conn.Close()
End Try
End Sub
VB
'this code will loop to get the value then it sends to 2 procedure to insert into the database
Private Sub save__details()
        Dim S As Integer
        For S = 0 To datagridgridview1.RowCount - 1
            If CStr(datagridgridview1.Item(0, S).Value()) <> ""  Then
                'payment details
                updateRecord("insert into table1 (code)" & _
                  " values('" & CStr(datagridgridview1.Item(0, S).Value()) & "' )")
            End If
        Next
'loop all the record until end using next.
    End Sub

'this procedure will be used to insert to the databases as u loop
 Function updateRecord(ByVal query As String) As Integer
        Try
            Dim rowsEffected As Integer = 0
            Dim connection As New SqlConnection("YOUR CONNECTION STRING  ")
            Dim cmd As New sqlcommand(query, connection)
            ''InputBox("", query, query)
            connection.Open()
            rowsEffected = cmd.ExecuteNonQuery()
            connection.Close()
            Return rowsEffected
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Function
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900