Hi every body. I got an asp.net script that suppost to update teams record. When i change
the division value to first and click on on update i find that it does not update the division value
and i get this as an output. I be happy if some expert help me fix this borken code. Thanks


1
----------->no vlaue for playerno!!!!
first

UPDATE TEAMS SET TEAMNO = '1', PLAYERNO = '', DIVISION = 'first' WHERE PLAYERNO = ;
An Error Occurred: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ';'.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.teamsupdate_aspx.DBEditDataGrid_Update(Object Sender,
 DataGridCommandEventArgs E) in C:\teamsupdate.aspx:line 95





line 95 points to here :
            objCommand.ExecuteNonQuery()

<%@ Page Language="VB" Debug="true" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>



<script language="VB" runat="server">
    Dim objConnection As SqlConnection
    Dim myDataReader As SqlDataReader

    Sub Page_Load(Sender As Object, E As EventArgs)
        ' Set up our connection.
        objConnection = New SqlConnection("Data Source=(local);" _
            & "Initial Catalog=teniss2;User Id=web;Password=web;" _
            & "Connect Timeout=15;Network Library=dbmssocn;")

        LoadDataFromDB

        If Not IsPostBack Then
            DataBindGrid
        End If
    End Sub

    Sub LoadDataFromDB()
        Dim objCommand As SqlCommand

        ' Create new command object passing it our SQL query
        ' and telling it which connection to use.
        objCommand = New SqlCommand("SELECT * FROM TEAMS where playerno = " +Request.QueryString("person_id")+";", objConnection)

        ' Open the connection, execute the command, and close the connection.
        objConnection.Open()
        myDataReader = objCommand.ExecuteReader(System.Data.CommandBehavi or.CloseConnection)
    End Sub

    Sub DataBindGrid()
        DBEditDataGrid.DataSource = myDataReader
        DBEditDataGrid.DataBind
    End Sub

    Sub DBEditDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
        DBEditDataGrid.EditItemIndex = E.Item.ItemIndex
        DataBindGrid
    End Sub

    Sub DBEditDataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
        DBEditDataGrid.EditItemIndex = -1
        DataBindGrid
    End Sub

    Sub DBEditDataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
        ' Since the textboxes are autogenerated we don't know their names,
        ' but we do know their positions.
        ' Dim intplayer As String = E.Item.Cells(0).Text
        Dim tbText0 As String = E.Item.Cells(0).Text
        Dim tbText1 As String = E.Item.Cells(1).Text
        Dim tbText2 As TextBox = E.Item.Cells(2).Controls(0)


        ' If you're not sure you've got the right values... check!
        ' Response.Write(intplayer)
            Response.Write("<BR>")
                Response.Write(tbText0)
        Response.Write("<BR>")
        Response.Write(tbText1)
                Response.Write("<BR>")
        Response.Write(tbText2.Text)
                Response.Write("<BR>")



        ' Update the appropriate record in our database.
        Dim objCommand As SqlCommand
        Dim strSQLQuery As String

        ' Build our update command.
              strSQLQuery = "UPDATE TEAMS " _
                     & "SET TEAMNO = '" & Replace(tbText0, "'", "''") & "', " _
                     & "PLAYERNO = '" & tbText1 & "', " _
                     & "DIVISION = '" & Replace(tbText2.Text, "'", "''") & "' " _
                     & "WHERE PLAYERNO = " & tbText1 & ";"


        ' Again... if you're not sure you've got the right command built...
        ' you can always check!
        Response.Write(strSQLQuery)

        ' Create new command object passing it our SQL query
        ' and telling it which connection to use.
        objCommand = New SqlCommand(strSQLQuery, objConnection)

        ' Close our open DataReader
        myDataReader.Close

        Try
            ' Execute the command
            objConnection.Open()
            objCommand.ExecuteNonQuery()
            objConnection.Close()
        Catch Ex as Exception
            Response.Write("<p><strong>An Error Occurred:</strong> " & Ex.ToString() & "</p>" & vbCrLf)
            Response.Write("<p>Most likely you tried to enter data that was inappropriate.</p>" & vbCrLf)
            Response.Write("[list]" & vbCrLf)
            Response.Write("<li>The text field is limited to 10 characters at the database level.</li>" & vbCrLf)
            Response.Write("<li>The integer field is a smallint... no text and only values from -32768 to 32767!</li>" & vbCrLf)
            Response.Write("<li>The datetime field must contain a string that can be converted to a valid Date / Time</li>" & vbCrLf)
            Response.Write("</ul>" & vbCrLf)
        Finally
            objConnection.Close()
        End Try

        ' Refresh our copy of the data
        LoadDataFromDB

        ' Reset our current edit item and rebind the grid
        DBEditDataGrid.EditItemIndex = -1
        DataBindGrid
    End Sub
</script>

<html>
<head>
<title>ASP.NET Database Edit Sample</title>
</head>
<body>

<form runat="server">

<asp:DataGrid id="DBEditDataGrid" runat="server"
    BorderWidth = "1" CellSpacing = "2" CellPadding = "2"
    HeaderStyle-Font-Bold = "True"



     OnEditCommand = "DBEditDataGrid_Edit"
    OnCancelCommand = "DBEditDataGrid_Cancel"
    OnUpdateCommand = "DBEditDataGrid_Update"

    AutoGenerateColumns = "False"
>
    <Columns>

            <asp:BoundColumn HeaderText="TEAMNO" DataField="TEAMNO" ReadOnly="True" />
        <asp:BoundColumn HeaderText="PLAYERNO" DataField="PLAYERNO" />
        <asp:BoundColumn HeaderText="DIVISION" DataField="DIVISION" />



        <asp:EditCommandColumn
            HeaderText = "Edit"
            EditText = "Edit"
            CancelText = "Cancel"
            UpdateText = "Update"
        />
    </Columns>
</asp:DataGrid>

</form>



<p>
Click <a href="./back.aspx">here</a>
to return back
</p>

</body>
</html>

1) Why you are updating PLAYERNO with tbText1 when you have the same in the where clause?

2) If its required then why you are not using the old Value of PLAYERNO in the where clause.

3) You are using the single quote in the update clause for PLAYERNO, ie "PLAYERNO = '" & tbText1 & "', "
but not in the where clause ie . "WHERE PLAYERNO = " & tbText1 & ";"

so Is it a numeric field or string field ?

Hope finding the answer to these Q's will be a solution.

Prashant