Hi every body. I have this asp.net script that supposed to update players table records.But when i run it i get this error. i only tried to change name feild and i get the this error . I also tried to change the brith date feiled and i got same error! My db is in access and i posted its field type below. Do u think there is some thing wrong with the update sql statement?
I be happy if an expert help me fix this broken code.Thanks
PLAYERS TABLE data type:
PLAERNO => NUMBER
NAME => TEXT
INITIALS => TEXT
BIRTH_DATE=> DATE/TIME
SEX => TEXT
JOINED => NUMBER
STREET => TEXT
HOUSENO => TEXT
POSTCODE => TEXT
TOWN => TEXT
PHONENO => TEXT
LEAGUENO => TEXT
Server Error in '/asp' Application.
--------------------------------------------------------------------------------
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request.
Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 139: myCommand.Parameters.Add(parameterPLAYERNO)
Line 140:
Line 141: myCommand.ExecuteNonQuery() 'Execute the UPDATE query
Line 142:
Line 143: objConn.Close()
Source File: C:\edit.aspx Line: 141
code:
Code:
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack
BindData()
End If
End Sub
Sub BindData()
'1. Create a connection
Const strConnString as String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db.mdb"
Dim objConn as New OleDbConnection(strConnString)
objConn.Open() 'You must open the db connection before populating the DataReader
'2. Create a command object for the query
Const strSQL as String = "SELECT * FROM players"
Dim objCmd as New OleDbCommand(strSQL, objConn)
'3. Create/Populate the DataReader
Dim objDR as OleDbDataReader
objDR = objCmd.ExecuteReader()
dgProducts.DataSource = objDR
dgProducts.DataBind()
End Sub
Sub dgProducts_Edit(sender As Object, e As DataGridCommandEventArgs)
dgProducts.EditItemIndex = e.Item.ItemIndex
BindData()
End Sub
Sub dgProducts_Cancel(sender As Object, e As DataGridCommandEventArgs)
dgProducts.EditItemIndex = -1
BindData()
End Sub
Sub dgProducts_Update(sender As Object, e As DataGridCommandEventArgs)
'Read in the values of the updated row
Dim PLAYERNO as Integer = e.Item.Cells(1).Text
Dim NAME as String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
Dim INITIALS as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Dim BIRTH_DATE as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Dim SEX as String = CType(e.Item.Cells(5).Controls(0), TextBox).Text
Dim JOINED as String = CType(e.Item.Cells(6).Controls(0), TextBox).Text
Dim STREET as String = CType(e.Item.Cells(7).Controls(0), TextBox).Text
Dim HOUSENO as String = CType(e.Item.Cells(8).Controls(0), TextBox).Text
Dim POSTCODE as String = CType(e.Item.Cells(9).Controls(0), TextBox).Text
Dim TOWN as String = CType(e.Item.Cells(10).Controls(0), TextBox).Text
Dim PHONENO as string = CType(e.Item.Cells(11).Controls(0), TextBox).Text
Dim LEAGUENO as string = CType(e.Item.Cells(12).Controls(0), TextBox).Text
'Construct the SQL statement using Parameters
Dim strSQL as String = "UPDATE [PLAYERS] SET [Name] = @Name, " & _
"[INITIALS] = @INITIALS, [BIRTH_DATE] = @BIRTH_DATE " & _
"[SEX] = @SEX, [JOINED] = @JOINED " & _
"[STREET] = @STREET, [HOUSENO] = @HOUSENO " & _
"[POSTCODE] = @POSTCODE, [TOWN] = @TOWN " & _
"[PHONENO] = @PHONENO, [LEAGUENO] = @LEAGUENO " & _
"WHERE [PLAYERNO] = @PLAYERNO"
' Create Instance of Connection and Command Object
'1. Create a connection
Const strConnString as String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db.mdb"
Dim objConn as New OleDbConnection(strConnString)
objConn.Open()
Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
myCommand.CommandType = CommandType.Text
' Add Parameters to the SQL query
Dim parameterName as OleDbParameter = new OleDbParameter("@Name", OleDbType.VarWChar, 75)
parameterName.Value = Name
myCommand.Parameters.Add(parameterName)
Dim parameterINITIALS as OleDbParameter = new OleDbParameter("@INITIALS", OleDbType.VarWChar, 75)
parameterINITIALS.Value = INITIALS
myCommand.Parameters.Add(parameterINITIALS)
Dim parameterBIRTH_DATE as OleDbParameter = new OleDbParameter("@BIRTH_DATE", OleDbType.VarWChar, 75)
parameterBIRTH_DATE.Value = BIRTH_DATE
myCommand.Parameters.Add(parameterBIRTH_DATE)
Dim parameterSEX as OleDbParameter = new OleDbParameter("@SEX", OleDbType.VarWChar, 75)
parameterSEX.Value = SEX
myCommand.Parameters.Add(parameterSEX)
Dim parameterJOINED as OleDbParameter = new OleDbParameter("@JOINED", OleDbType.VarWChar, 75)
parameterJOINED.Value = JOINED
myCommand.Parameters.Add(parameterJOINED)
Dim parameterSTREET as OleDbParameter = new OleDbParameter("@STREET", OleDbType.VarWChar, 75)
parameterSTREET.Value = STREET
myCommand.Parameters.Add(parameterSTREET)
Dim parameterHOUSENO as OleDbParameter = new OleDbParameter("@HOUSENO", OleDbType.VarWChar, 75)
parameterHOUSENO.Value = HOUSENO
myCommand.Parameters.Add(parameterHOUSENO)
Dim parameterPOSTCODE as OleDbParameter = new OleDbParameter("@POSTCODE", OleDbType.VarWChar, 75)
parameterPOSTCODE.Value = POSTCODE
myCommand.Parameters.Add(parameterPOSTCODE)
Dim parameterTOWN as OleDbParameter = new OleDbParameter("@TOWN", OleDbType.VarWChar, 75)
parameterTOWN.Value = TOWN
myCommand.Parameters.Add(parameterTOWN)
Dim parameterPHONENO as OleDbParameter = new OleDbParameter("@PHONENO", OleDbType.Currency)
parameterPHONENO.Value = PHONENO
myCommand.Parameters.Add(parameterPHONENO)
Dim parameterLEAGUENO as OleDbParameter = new OleDbParameter("@LEAGUENO", OleDbType.VarWChar)
parameterLEAGUENO.Value = LEAGUENO
myCommand.Parameters.Add(parameterLEAGUENO)
Dim parameterPLAYERNO as OleDbParameter = new OleDbParameter("@PLAYERNO", OleDbType.Integer)
parameterPLAYERNO.Value = PLAYERNO
myCommand.Parameters.Add(parameterPLAYERNO)
myCommand.ExecuteNonQuery() 'Execute the UPDATE query
objConn.Close()
'Finally, set the EditItemIndex to -1 and rebind the DataGrid
dgProducts.EditItemIndex = -1
BindData()
End Sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<asp:DataGrid id="dgProducts" runat="server"
AutoGenerateColumns="False" CellPadding="4"
HeaderStyle-BackColor="Black"
HeaderStyle-ForeColor="White"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True"
EditItemStyle-BackColor="#eeeeee"
OnEditCommand="dgProducts_Edit"
OnUpdateCommand="dgProducts_Update"
OnCancelCommand="dgProducts_Cancel">
<Columns>
<asp:EditCommandColumn EditText="Edit Info" ButtonType="PushButton"
UpdateText="Update" CancelText="Cancel" />
<asp:BoundColumn HeaderText="PLAYERNO" DataField="PLAYERNO"
ReadOnly="True" />
<asp:BoundColumn HeaderText="NAME" DataField="NAME"
ItemStyle-HorizontalAlign="Right"
DataFormatString="{0:$#,###.##}" />
<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" />
</Columns>
</asp:DataGrid>
</form>
</body>
</html>