Error trying to update a record using asp.net datagrid

Dev Articles Community Forums Sponsor:

May 10th, 2005, 01:32 PM

method

Contributing User

Join Date: May 2005

Posts: 54

Time spent in forums: 3 h 45 m 12 sec
Reputation Power: 4

Error trying to update a record using asp.net datagrid

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()

Code:

 <%@ 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("<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>

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: