What is wrong with this update statment

well i got an aspx page that supposed to update some data in sql server db and it generates this sql statment but it give error in writing to sql server 2000:

8NewcastleCkB7/8/1962 12:00:00 AMF1980Station Road46584WOInglewood070-4584582983

UPDATE PLAYERS SET NAME = 'NewcastleCk', INITIALS = B, BIRTH_DATE = '7/8/1962 12:00:00 AM' SEX = F, JOINED = '1980' STREET = Station Road, HOUSENO = '4', POSTCODE = 6584WO, TOWN = 'Inglewood' PHONENO = 070-458458, LEAGUENO ='2983' WHERE PLAYERNO = 8;

An Error Occurred: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'SEX'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.db_edit2_aspx.DBEditDataGrid_Update(Object Sender, DataGridCommandEventArgs E)


I be happy if some one help me what is worong here. Thanks
Here is the code :

<%@ Page Language="VB" %>
<%@ 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 Players;", 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 tbText1 As TextBox = E.Item.Cells(1).Controls(0)
Dim tbText2 As TextBox = E.Item.Cells(2).Controls(0)
Dim tbText3 As TextBox = E.Item.Cells(3).Controls(0)
Dim tbText4 As TextBox = E.Item.Cells(4).Controls(0)
Dim tbText5 As TextBox = E.Item.Cells(5).Controls(0)
Dim tbText6 As TextBox = E.Item.Cells(6).Controls(0)
Dim tbText7 As TextBox = E.Item.Cells(7).Controls(0)
Dim tbText8 As TextBox = E.Item.Cells(8).Controls(0)
Dim tbText9 As TextBox = E.Item.Cells(9).Controls(0)
Dim tbText10 As TextBox = E.Item.Cells(10).Controls(0)
Dim tbText11 As TextBox = E.Item.Cells(11).Controls(0)
' If you're not sure you've got the right values... check!
Response.Write(intplayer)

Response.Write(tbText1.Text)
Response.Write(tbText2.Text)
Response.Write(tbText3.Text)
Response.Write(tbText4.Text)
Response.Write(tbText5.Text)
Response.Write(tbText6.Text)
Response.Write(tbText7.Text)
Response.Write(tbText8.Text)
Response.Write(tbText9.Text)
Response.Write(tbText10.Text)
Response.Write(tbText11.Text)
' Update the appropriate record in our database.
Dim objCommand As SqlCommand
Dim strSQLQuery As String
' Build our update command.
strSQLQuery = "UPDATE PLAYERS " _
& "SET NAME = '" & Replace(tbText1.Text, "'", "''") & "', " _
& "INITIALS = " & tbText2.Text & ", " _
& "BIRTH_DATE = '" & Replace(tbText3.Text, "'", "''") & "' " _
& "SEX = " & tbText4.Text & ", " _
& "JOINED = '" & Replace(tbText5.Text, "'", "''") & "' " _
& "STREET = " & tbText6.Text & ", " _
& "HOUSENO = '" & Replace(tbText7.Text, "'", "''") & "', " _
& "POSTCODE = " & tbText8.Text & ", " _
& "TOWN = '" & Replace(tbText9.Text, "'", "''") & "' " _
& "PHONENO = " & tbText10.Text & ", " _
& "LEAGUENO ='" & Replace(tbText11.Text, "'", "''") & "' " _
& "WHERE PLAYERNO = " & intplayer & ";"
' 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="PLAYERNO" DataField="PLAYERNO" ReadOnly="True" />
<asp:BoundColumn HeaderText="NAME" DataField="NAME" />
<asp:BoundColumn HeaderText="INITIALS" DataField="INITIALS" />
<asp:BoundColumn HeaderText="BIRTH_DATE" DataField="BIRTH_DATE" />
<asp:BoundColumn HeaderText="SEX" DataField="SEX" />
<asp:BoundColumn HeaderText="JOINED" DataField="JOINED" />
<asp:BoundColumn HeaderText="STREET" DataField="STREET" />
<asp:BoundColumn HeaderText="HOUSENO" DataField="HOUSENO" />
<asp:BoundColumn HeaderText="POSTCODE" DataField="POSTCODE" />
<asp:BoundColumn HeaderText="TOWN" DataField="TOWN" />
<asp:BoundColumn HeaderText="PHONENO" DataField="PHONENO" />
<asp:BoundColumn HeaderText="LEAGUENO" DataField="LEAGUENO" />
<asp:EditCommandColumn
HeaderText = "Edit"
EditText = "Edit"
CancelText = "Cancel"
UpdateText = "Update"
/>
</Columns>
</aspataGrid>
</form>
<hr />
<p>
Click <a href="./back.aspx">here</a>
to return back
</p>
</body>
</html>

Hi,

SEX = F is a text field, wrap it in single quotes

SEX = 'F'

hope this helps

Hi,

there must also be a comma between


you also have to wrap these fields in single quotes, they look like they are text fields from the sql you posted
initials
street
postcode
phoneno

And another comma missing between TOWN and PHONENO

hope this helps

Many thanks for u reply . This part of code generates the sql statment :

Build our update command.
strSQLQuery = "UPDATE PLAYERS " _
& "SET NAME = '" & Replace(tbText1.Text, "'", "''") & "', " _
& "INITIALS = " & tbText2.Text & ", " _
& "BIRTH_DATE = '" & Replace(tbText3.Text, "'", "''") & "' " _
& "SEX = " & tbText4.Text & ", " _
& "JOINED = '" & Replace(tbText5.Text, "'", "''") & "' " _
& "STREET = " & tbText6.Text & ", " _
& "HOUSENO = '" & Replace(tbText7.Text, "'", "''") & "', " _
& "POSTCODE = " & tbText8.Text & ", " _
& "TOWN = '" & Replace(tbText9.Text, "'", "''") & "' " _
& "PHONENO = " & tbText10.Text & ", " _
& "LEAGUENO ='" & Replace(tbText11.Text, "'", "''") & "' " _
& "WHERE PLAYERNO = " & intplayer & ";"

where you think i made a mistake ? it is dam difficult to find out where i missed those commas!!! I be happy if u point me to there. thanks

Hi,

sorry for the later reply, I just came was between work and home for a while.

here is the new update statement, it should work

hope this helps

Since it's SQL Server, it would end like this