Error trying to update a record using asp.net datagrid

[liunx]

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

<aspataGrid 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>
</aspataGrid>

</form>

<hr />

<p>
Click <a href=http://www.webdeveloper.com/forum/archive/index.php/"./back.aspx">here</a>
to return back
</p>

</body>
</html>after the line

Response.Write(strSQLQuery)

put

Response.Flush()

Run the app.
Look at your query string that is on the screen from the Response.Write.

That will most likely show you what is wrong

EricOR set break points and step through the code.OR set break points and step through the code.

well do u mean visual studio 2003 ? i am not using any of developer environment!!