SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating Table using datagrid

    Ok, I switched my code as follows:

    <%@ Page Language="VB" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <html>
    <head>
    <script runat="server">
    Public Sub Page_Load(Source As Object, E As EventArgs)
    If Not Page.IsPostBack Then
    BindData()
    End If
    End Sub

    Public Sub DataGrid_Edit(ByVal Source As Object, _
    ByVal E As DataGridCommandEventArgs)
    myInfo.EditItemIndex = E.Item.ItemIndex
    BindData()
    End Sub

    Public Sub DataGrid_Cancel(ByVal Source As Object, _
    ByVal E As DataGridCommandEventArgs)
    myInfo.EditItemIndex = -1
    BindData()
    End Sub

    Public Sub DataGrid_Update(ByVal Source As Object, _
    ByVal E As DataGridCommandEventArgs)
    Dim myConnection As SqlConnection
    Dim myCommand As SqlCommand
    Dim txtmyNewCol1 As TextBox = E.Item.Cells(1).Controls(0)
    Dim txtmyNewCol2 As TextBox = E.Item.Cells(2).Controls(0)
    Dim strUpdateStmt As String

    strUpdateStmt = "UPDATE ToddsTable1 SET " & _
    "myNewCol1 = '" & txtmyNewCol1.Text & "', " & _
    "myNewcol2 = '" & txtmyNewCol2.Text & "' "

    myConnection = New SqlConnection( _
    "server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
    myCommand = New SqlCommand(strUpdateStmt, myConnection)
    myConnection.Open()
    myCommand.ExecuteNonQuery()

    myInfo.EditItemIndex = -1
    BindData()
    End Sub

    Public Sub BindData()
    Dim myDataSet As New DataSet
    Dim mySqlDataAdapter As SqlDataAdapter
    mySqlDataAdapter = New SqlDataAdapter( _
    "SELECT * FROM ToddsTable1", _
    "server=SQLSERVER;database=upsizedCandidate;Integrated Security=True")
    mySqlDataAdapter.Fill(myDataSet, "ToddsTable1")
    myInfo.DataSource = myDataSet.Tables("ToddsTable1")
    myInfo.DataBind()
    End Sub
    </script>
    </head>
    <body>
    <form id="Form1" runat="server" method="post">
    <H3>Editing ToddsTable1 from SQL Server</H3>

    <aspataGrid id="myInfo" runat="server"
    AutoGenerateColumns="False"
    OnEditCommand="DataGrid_Edit" Width="702px"
    OnCancelCommand="DataGrid_Cancel"
    OnUpdateCommand="DataGrid_Update">

    <Columns>
    <asp:EditCommandColumn
    CancelText="Cancel"
    EditText="Edit"
    UpdateText="Update" />
    <asp:BoundColumn
    DataField="myNewCol1"
    HeaderText="myNewCol1" />
    <asp:BoundColumn
    DataField="myNewCol2"
    HeaderText="myNewCol2" />
    </Columns>
    </aspataGrid>


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



    Everything works fine except when I click"Edit" then edit the text and click "update" it changes every cell in the column to the value I entered in the edited column. Any thoughts on how I could fix this?

  2. #2
    SitePoint Wizard rbutler's Avatar
    Join Date
    Jul 2003
    Location
    Springfield, MO
    Posts
    1,867
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to specify a WHERE clause on your UPDATE statement, otherwise, all fields will get updated, unless I'm not following your logic. The WHERE clause should be based on a unique ID in the table.

    Also, it looks like you're setting yourself up for a nasty SQL injection attack with how you're updating your text fields. You need to pass the values obtained through text fields in a SQL command parameter.
    Ryan Butler

    Midwest Web Design

  3. #3
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks alot Ryan. I bet that will work, my Server is down right now and I'm waiting for it to be back up. I'm not all that familiar with the SQL Injection attack, I know the basic idea. I should use a filter of some sort, right? I'll do some research and wait for my server to get back up and let you know how I do. Thanks again!

  4. #4
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    trying to read int

    I'm just trying to get the code working right now before I start to look into the SQL injection attack. I now have the following code for my update statement:
    strUpdateStmt = "UPDATE XH SET " & _
    "Name = '" & txtName.Text & "', " & _
    "Street = '" & txtStreet.Text & "', " & _
    "City = '" & txtCity.Text & "', " & _
    "State = '" & txtState.Text & "', " & _
    "Nation = '" & txtNation.Text & "', " & _
    "Postal = '" & txtPostal.Text & "', " & _
    "Phone = '" & txtPhone.Text & "' " & _
    "WHERE CAGE = " & E.Item.Cells(1).Text

    Once again, everything works except for the update. I'm getting the following error:

    Server Error in '/WebNewFileItems' Application.
    Syntax error converting the nvarchar value '018S0' to a column of data type int.
    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.Data.SqlClient.SqlException: Syntax error converting the nvarchar value '018S0' to a column of data type int.

    In the DB, the type of the CAGE is a nvarchar but the compiler is trying to read it as an int. Although the CAGE (my ID) is a unique identifer, some of the entries have letters in them which messes up this application. Why is it trying to read an int and can I assign it to look for a string or anything else?

  5. #5
    SitePoint Wizard rbutler's Avatar
    Join Date
    Jul 2003
    Location
    Springfield, MO
    Posts
    1,867
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, a WHERE clause should only be a int data type. What does your column identifier look like?
    Ryan Butler

    Midwest Web Design

  6. #6
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The column name is 'CAGE' and the type is an nvarchar. Is there any way I can just type cast it?

  7. #7
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just went through and added a new ID column in my original table and now it works fine. I'll now try to fix the SQL injection problem. Thanks for all your help.

  8. #8
    SitePoint Wizard rbutler's Avatar
    Join Date
    Jul 2003
    Location
    Springfield, MO
    Posts
    1,867
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would look something like this:

    Code:
     
    Dim objCmd as New SqlCommand
     
    objCmd="UPDATE column1, column2 SET VALUES("@column1","@column2") WHERE ID=1"
     
    objCmd.Parameters.Add("@column1", txtColumn1.text)
    objCmd.Parameters.Add("@column2", txtColumn2.text)
     
    objConn.Open()
    objCmd.ExecuteNonQuery
    objConn.Close()
    Ryan Butler

    Midwest Web Design

  9. #9
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much for you help Ryan, you've helped me out very much :-)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •