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' ************ = 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 '************'. 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, "'", "''") & "' " _
& "************ = " & 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("[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="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="************" DataField="************" />
<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>
</asp:DataGrid>
</form>
<p>
Click <a href="./back.aspx">here</a>
to return back
</p>
</body>
</html>
What is wrong with this update statment ?
