Newbie: Saving all cells in Datagrid from SQL Server

[tina1122]

Hi guys,
I'm new to ASP.NET and I am getting an error when I try to save a Datagrid. I am bringing in a table from SQL Server and placing it inside the datagrid which I've turned all the cells into text boxes to display the data and to edit anything at any time. The data is displayed fine and I can edit the contents but when I click on my save button to save the entire datagrid I get the following error:

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.


I also have included my method:
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

myConnection = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated 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 XH 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

I'm sure the problem is inside the method after numerous test tries. Can anyone help me with this? I would be very happy. Thank you.
-Tina

 

[ca8msm]

Try getting the actual SQL and running it in Query Analyzer. I suspect it will still fail but at least you will be able do drop one column at a time and see which field isn't in the right format. Also, you really need to read up on SQL Injection.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[tina1122]

I do not have the Query Analyzer accessible on the server I am using the compile the code. I have changed my code though. Here it is:

Private Sub doSave(ByVal sender As Object, ByVal e As System.EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim dstCopy As New DataSet
Dim strQuery As String

myConnection = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
myConnection.Open()
Dim dgItem As DataGridItem
For Each dgItem In myInfo.Items
Dim txtCAGE As TextBox = _
CType(dgItem.Cells(0).FindControl("txtCAGE"), TextBox)
Dim txtName As TextBox = _
CType(dgItem.Cells(1).FindControl("txtName"), TextBox)
Dim txtStreet As TextBox = _
CType(dgItem.Cells(2).FindControl("txtStreet"), TextBox)
Dim txtCity As TextBox = _
CType(dgItem.Cells(3).FindControl("txtCity"), TextBox)
Dim txtState As TextBox = _
CType(dgItem.Cells(4).FindControl("txtState"), TextBox)
Dim txtNation As TextBox = _
CType(dgItem.Cells(5).FindControl("txtNation"), TextBox)
Dim txtPostal As TextBox = _
CType(dgItem.Cells(6).FindControl("txtPostal"), TextBox)
Dim txtPhone As TextBox = _
CType(dgItem.Cells(7).FindControl("txtPhone"), TextBox)


Dim item As Integer
If txtCAGE.Text.Trim <> String.Empty Then
If IsNumeric(dgItem.Cells(8).Text) Then
item = CInt(dgItem.Cells(8).Text)
strQuery = "Update XH 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

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()
End If
Next

myConnection.Close()
BindData()
End Sub
Any other thoughts? Thanks,
Tina

 

[ca8msm]

I don't know what changes you've made to the code but both recommendations I made still apply.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[tina1122]

Commenting out and testing, I'm pretty sure my error is with the statements:
Dim txtCAGE As TextBox = _
CType(dgItem.Cells(5).FindControl("txtCAGE"), TextBox)...etc

What else would be possible to place in this statement? What could be the error?

 

[ca8msm]

You need to get the actual SQL, drop one column at a time and see which field isn't in the right format.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.