Error trying to update a record using asp.net datagrid

[tony007]

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

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.CommandBehavior.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("<ul>" & 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>

<hr />

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

</body>
</html>