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 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
************ => 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:

<%@ 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 ************ 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 " & _
                               "[************] = @************, [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("@************", OleDbType.VarWChar, 75)
        parameterSEX.Value = ************
        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="************" 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" />
            </Columns>
        </asp:DataGrid>

    </form>
</body>
</html>

Unfortunately, that error message doesn't provide quite as much information as we'd need. However, from it I can deduce two things:

1. The error is on the .NET side vs. the SQL side.
2. The error looks like a conversion/formatting error. Based on your table structure the only field that seems like it would cause that error would be the date field. Perhaps the value that .NET is trying to convert to a date for the SQL parameter is malformed.

-Peter

Thank u for u reply. Well i only tried to change name feild and i get the this error . I also tried to change the brith date filed and i got same error! My db is in access and i posted its field type above. Do u think there is some thing wrong with the update sql statement?

You're missing several commas in the UPDATE after some fields.

-Peter

thank u for u reply. I changed it to this :



but still i get the same error. could u help me fix this broken code since i am not using and development environment it is hard to find the mistake.Thanks

I don't see anything wrong with that statement construction. I'm not sure then what the problem is. It has to be caused by the data you are providing the command. Like I mentioned in my second post, the error message (System.FormatException) hints that there's something wrong with the data on the .NET side before the SQL command is even getting executed. If it was a SQL error you'd be getting a SqlException.

-Peter

hi,

Check all your numeric field's values, they might be null. Also if datatype of each field matches with parameter type.

If your numeric fields are null convert them to DBNull.Value.


Prashant