41
Hi all!
I am attempting to bring an SQL Server table into ASP.NET using the datagrid . I have set all cells to be a text box by which the user can just click on and edit the cell. I have a save button and when I click the save button the page reloads and the new data is in the cell, however when I close out and come back in the data is no longer there. I have a databind() subroutine which includes a postback included within my code for the save button which is as follows:
Private Sub doSave(ByVal sender As Object, ByVal e As System.EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim mySqlDataAdapter As SqlDataAdapter
Dim dstCopy As New DataSet
Dim strQuery As String
Dim intError As Integer = 0
myConnection = New SqlConnection( _
"server=SQLSERVER;database=upsizedCandidate;Integr ated Security=True;")
myConnection.Open()
Dim dgItem As DataGridItem
For Each dgItem In myInfo.Items
'Dim txtID As TextBox = _
' CType(dgItem.Cells(0).FindControl("txtID"), TextBox)
Dim txtCAGE As TextBox = _
CType(dgItem.Cells(1).FindControl("txtCAGE"), TextBox)
Dim txtName As TextBox = _
CType(dgItem.Cells(2).FindControl("txtName"), TextBox)
Dim txtStreet As TextBox = _
CType(dgItem.Cells(3).FindControl("txtStreet"), TextBox)
Dim txtCity As TextBox = _
CType(dgItem.Cells(4).FindControl("txtCity"), TextBox)
Dim txtState As TextBox = _
CType(dgItem.Cells(5).FindControl("txtState"), TextBox)
Dim txtNation As TextBox = _
CType(dgItem.Cells(6).FindControl("txtNation"), TextBox)
Dim txtPostal As TextBox = _
CType(dgItem.Cells(7).FindControl("txtPostal"), TextBox)
Dim txtPhone As TextBox = _
CType(dgItem.Cells(8).FindControl("txtPhone"), TextBox)
Dim item As Integer
If txtCAGE.Text.Trim <> String.Empty Then
If IsNumeric(dgItem.Cells(9).Text) Then
item = CInt(dgItem.Cells(9).Text)
strQuery = "Update Table1 SET CAGE='" + txtCAGE.Text _
+ "',Name='" + txtName.Text + _
+"',Street='" + txtStreet.Text + _
+"',City='" + txtCity.Text + _
+"',State='" + txtState.Text + _
+"',Nation='" + txtNation.Text + _
+"',Postal='" + txtPostal.Text + _
+"',Phone='" + txtPhone.Text + _
"' WHERE ID=" + CStr(item)
Else
item = 0
strQuery = "INSERT INTO XH(CAGE,Name,Street,City,State,Nation,Postal,Phone ) VALUES('" _
+ txtCAGE.Text + "','" + _
+txtName.Text + "','" + _
+txtStreet.Text + "','" + _
+txtCity.Text + "','" + _
+txtState.Text + "','" + _
+txtNation.Text + "','" + _
+txtPostal.Text + "','" + _
+txtPhone.Text + "','" + "')"
End If
myCommand = New SqlCommand(strQuery, myConnection)
myCommand.ExecuteNonQuery()
strQuery = String.Empty
myCommand.Dispose()
End If
Next
myConnection.Close()
BindData()
End Sub
Here is my page_load and databind:
Sub Page_Load(ByVal sender As Object, ByVal E As EventArgs)
If Not Page.IsPostBack Then
BindData()
End If
End Sub
Public Sub BindData()
Dim myDataSet As New DataSet
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter( _
"SELECT * FROM XH", _
"server=SQLSERVER;database=upsizedCandidate;Integr ated Security=True")
mySqlDataAdapter.Fill(myDataSet, "XH")
myInfo.DataSource = myDataSet.Tables("XH")
myInfo.DataBind()
End Sub
My datagrid is:
<asp:DataGrid id="myInfo"
OnItemCommand="doInsert"
OnEditCommand="doEdit"
OnCancelCommand="doCancel"
OnUpdateCommand="doUpdate"
OnSaveCommand="doSave"
<asp:TemplateColumn HeaderText="Save">
<FooterTemplate>
<asp:Button CommandName="Save" Text="Save" ID="btnSave" runat="server" />
</FooterTemplate>
</asp:TemplateColumn>
Anyone have any thoughts or suggestions, it would be GREATLY appreciated. Thanks in advance!!
