Input string error in "update statment" 1

[toddw607]

Hello,
I have a problem with updating a datagrid. I have a function for a save button but when I press it it gives me an error saying that my update statement is incorrect. Here is the code to my update statement:
Private Sub doSave(ByVal sender As Object, ByVal e As System.EventArgs)

Dim myCommand As SqlCommand
Dim dstCopy As New DataSet
Dim strQuery As String

objConnection = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
objConnection.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)



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 + ")"

myCommand = New SqlCommand(strQuery, objConnection)
myCommand.ExecuteNonQuery()

Next
objConnection.Close()
ShowDataGrid()
End Sub


and here is my datagrid:

<form id="Form1" runat="server">
<H3 id="H3_1">Editing XH from SQL Server </H3>

<aspataGrid id="myInfo"

OnItemCommand="doInsert"
OnDeleteCommand="DBDelDataGrid_Delete"
ShowFooter="True"
runat="server"
DataKeyField="id"
AutoGenerateColumns="False" CellPadding="4"
BackColor=" #C6DEFF"
EditItemStyle-BackColor="#eeeeee"
HeaderStyle-BackColor="Black"
HeaderStyle-ForeColor="Blue"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True">
<Columns>
<asp:TemplateColumn HeaderText="CAGE">
<FooterTemplate>
<asp:TextBox ID="add_CAGE" Columns="7" Runat="Server" />
</FooterTemplate>

<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtCAGE" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("CAGE") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="CAGE" Columns="7"
Text='<%# Container.DataItem("CAGE") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Name">
<FooterTemplate>
<asp:TextBox ID="add_Name" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtName" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Name") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Name" Columns="7"
Text='<%# Container.DataItem("Name") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Street">
<FooterTemplate>
<asp:TextBox ID="add_Street" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtStreet" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Street") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Street" Columns="7"
Text='<%# Container.DataItem("Street") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="City">
<FooterTemplate>
<asp:TextBox ID="add_City" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtCity" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("City") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="City" Columns="7"
Text='<%# Container.DataItem("City") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="State">
<FooterTemplate>
<asp:TextBox ID="add_State" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtState" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("State") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="State" Columns="7"
Text='<%# Container.DataItem("State") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Nation">
<FooterTemplate>
<asp:TextBox ID="add_Nation" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtNation" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Nation") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Nation" Columns="7"
Text='<%# Container.DataItem("Nation") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Postal">
<FooterTemplate>
<asp:TextBox ID="add_Postal" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtPostal" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Postal") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Postal" Columns="7"
Text='<%# Container.DataItem("Postal") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Phone">
<FooterTemplate>
<asp:TextBox ID="add_Phone" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtPhone" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Phone") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Phone" Columns="7"
Text='<%# Container.DataItem("Phone") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Insert">
<FooterTemplate>
<asp:Button CommandName="Insert" Text="Add" ID="btnAdd" Runat="server" />
</FooterTemplate>
<ItemTemplate>
<asp:Button CommandName="Delete" Text="Delete" ID="btnDel" Runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Save">
<FooterTemplate>
<asp:Button CommandName="Save" Text="Save" ID="btnSave" OnClick="doSave" runat="server" />
</FooterTemplate>
</asp:TemplateColumn>

</Columns>
</aspataGrid>



</form>
</body>
</html>

Can someone please tell me what is the problem. I've been working on this for almost a week and I'm not sure which syntax to use. Thank you.

 

[toddw607]

Sorry, this is what I get when I actually enter data into the fields:

declare @P1 nvarchar(25)
set @P1=NULL
declare @P2 nvarchar(25)
set @P2=NULL
declare @P3 nvarchar(20)
set @P3=NULL
declare @P4 nvarchar(2)
set @P4=NULL
declare @P5 nvarchar(20)
set @P5=NULL
declare @P6 nvarchar(10)
set @P6=NULL
declare @P7 nvarchar(12)
set @P7=NULL
exec XHInsert @CAGE = default, @BusName = @P1 output, @Street = @P2 output, @City = @P3 output, @State = @P4 output, @Nation = @P5 output, @Postal = @P6 output, @Phone = @P7 output
select @P1, @P2, @P3, @P4, @P5, @P6, @P7

 

[bboffin]

To go back to your original solution try using String.Format when constructing a SQL statement (or indeed any string) that includes many values as your code was not taking account of all the necessary apostrophes required in the SQL to surround the text values.

For example:

 strQuery = String.Format("INSERT INTO XH (CAGE, Name, Street, City, State, Nation, Postal, Phone) VALUES('{0}', '{1}', {2}', '{3}', '{4}', '{5}', '{6}', '{7}')", txtCAGE.Text, txtName.Text, txtStreet.Text, txtCity.Text, txtState.Text, txtNation.Text, txtPostal.Text, txtPhone.Text)

Note how much easier it is to see that you have constructed the SQL properly.

However that this technique is open to abuse if users type in some mailicious SQL in the text fields and you don't validate them. This is known as SQL Insertion. Look it up!

Stored procedures are a better way to go unless the data values are known to be safe.



Bob Boffin

 

[toddw607]

It worked with my original solution! Only problem is that it inserts the entire table at the end of the real table along with the one undated cell Any thoughts on how I can only update the "dirty" cells of just re-create the table that is currently on the screen? I'm not really sure if I'm making sense. Let me know if you understand.

 

[toddw607]

I have been trying to look for a tutorial for this subject but it seems there isn't one. When I use the "insert" statement (which is commented out in the following code) then it updates the changed text boxes but it adds the entire table back onto itself. When I use the "update" statement then it returns the values back to their original values after editing. I've switched my table to a smaller one with only 2 columns. Here is my subroutine for the "save" button:

Private Sub doSave(ByVal sender As Object, ByVal e As System.EventArgs)

Dim myCommand As SqlCommand
Dim dstCopy As New DataSet
Dim strQuery As String

objConnection = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
objConnection.Open()
Dim dgItem As DataGridItem
For Each dgItem In myInfo.Items
Dim txtmyNewCol1 As TextBox = _
CType(dgItem.Cells(0).FindControl("txtmyNewCol1"), TextBox)

Dim txtmyNewCol2 As TextBox = _
CType(dgItem.Cells(1).FindControl("txtmynewCol2"), TextBox)


strQuery = String.Format("update ToddsTable1 set myNewCol1= '" + txtmyNewCol1.Text + "',myNewCol2='" + txtmyNewCol2.Text + "'")
' strQuery = String.Format("INSERT INTO ToddsTable1 (myNewCol1, myNewcol2) VALUES('{0}', '{1}')", txtmyNewCol1.Text, txtmyNewCol2.Text)

myCommand = New SqlCommand(strQuery, objConnection)
myCommand.ExecuteNonQuery()
Next

objConnection.Close()
ShowDataGrid()
End Sub

I'm having a really hard time with this code. Can anyone give me an idea or a suggestion?

 

[toddw607]

Ok, now I have the button working somewhat but after I add a new field to the columns and click "save" then it updates the table so that every textbox has the newly entered data in it. How can I help this?

 

[toddw607]

The problem is that the update source for all cells resides in the last row of the table. Please Help!!

 

[bboffin]

If you are using Visual Studio try using a DataAdapter.

When you use VS to configure a DataAdapter it prompts for the SELECT statement and then generates INSERT, DELETE and UPDATE statements that it can use when the DataAdapter's Update method is called. The SELECT statement is used by the Fill method.

Take a look at the SQL that it generates and you will understand a part of how to manage updating a record but you also have to appreciate how the Update method works.

The Update method examines the state of each row (RowState property) in the DataSet's table and calls the appropriate SQL statement depending on what it finds.

If the row is marked as a new row it uses the Insert command
If the row is marked as Deleted it uses the Delete command
If the row is marked as Modified it uses the Update command

In addition each field in each row contains both the original value and any new value that has been assigned to it. This is used by the Update method to identify rows and to ensure that values are not changed inappropriately.

You may wish to use the DataAdapter's Update method as it eliminates the need to write a lot of code but it is regarded by some people as inefficient.

The DataAdapter also works best where a single table is being queried and updated although clever use of Stored Procedures can sometimes overcome this.


Bob Boffin