Error Input string was not in a correct format

When I run the "add new" on this form, I get a system error: "Input string was not in a correct format"... "System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't store <> in siteID Column. Expected type is Decimal.". It was expecting a decimal yet I don't see any where a decimal value was declared. I used the webmatrix "editable data grid" template and replaced fields and variables with my own. I'm using MSDE/SQL7, the siteID field in SQL is set for numeric, incrementing by 1, primary key. Thanks for any help. The form is:

' TODO: update the ConnectionString and Command values for your application


Dim connectionString As String = "server='(local)'; trusted_connection=true; database='networkSQL2'"
Dim SelectCommand As String = "SELECT siteID, muxCode, siteCode, city FROM siteCodes"


Dim isEditing As Boolean = False


Sub Page_Load(Sender As Object, E As EventArgs)


    If Not Page.IsPostBack Then


        ' Databind the data grid on the first request only
        ' (on postback, bind only in editing, paging and sorting commands)


        BindGrid()


    End If


End Sub


' ---------------------------------------------------------------
'
' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
'


Sub DataGrid_ItemCommand(Sender As Object, E As DataGridCommandEventArgs)


    ' this event fires prior to all of the other commands
    ' use it to provide a more graceful transition out of edit mode


    CheckIsEditing(e.CommandName)


End Sub


Sub CheckIsEditing(commandName As String)


    If DataGrid1.EditItemIndex <> -1 Then


        ' we are currently editing a row
        If commandName <> "Cancel" And commandName <> "Update" Then


            ' user's edit changes (If any) will not be committed
            Message.Text = "Your changes have not been saved yet.  Please press update to save your changes, or cancel to discard your changes, before selecting another item."
            isEditing = True


        End If


    End If


End Sub


Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)


    ' turn on editing for the selected row


    If Not isEditing Then


        DataGrid1.EditItemIndex = e.Item.ItemIndex
        BindGrid()


    End If


End Sub


Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)


    ' update the database with the new values


    ' get the edit text boxes
    Dim siteID As String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
    Dim muxCode As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
    Dim siteCode As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
    Dim city As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text


    ' TODO: update the Command value for your application
    Dim myConnection As New SqlConnection(ConnectionString)
    Dim UpdateCommand As SqlCommand = new SqlCommand()
    UpdateCommand.Connection = myConnection


    If AddingNew = True Then
        UpdateCommand.CommandText = "INSERT INTO siteCodes(muxCode, siteCode, city) VALUES (@muxCode, @siteCode, @city)"
    Else
        UpdateCommand.CommandText = "UPDATE siteCodes SET muxCode = @muxCode, siteCode = @siteCode, city = @city"
    End If


    ' UpdateCommand.Parameters.Add("@siteID").Value = siteID
    ' UpdateCommand.Parameters.Add("@muxCode").Value = muxCode
    ' UpdateCommand.Parameters.Add("@siteCode").Value = siteCode
    ' UpdateCommand.Parameters.Add("@city").Value = city


    ' execute the command
    Try
        myConnection.Open()
        UpdateCommand.ExecuteNonQuery()


    Catch ex as Exception
        Message.Text = ex.ToString()


    Finally
        myConnection.Close()


    End Try


    ' Resort the grid for new records
    If AddingNew = True Then
        DataGrid1.CurrentPageIndex = 0
        AddingNew = false
    End If


    ' rebind the grid
    DataGrid1.EditItemIndex = -1
    BindGrid()


End Sub


Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)


    ' cancel editing


    DataGrid1.EditItemIndex = -1
    BindGrid()


    AddingNew = False


End Sub


Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)


    ' delete the selected row


    If Not isEditing Then


        ' the key value for this row is in the DataKeys collection
        Dim keyValue As String = CStr(DataGrid1.DataKeys(e.Item.ItemIndex))


        ' TODO: update the Command value for your application
        Dim myConnection As New SqlConnection(ConnectionString)
        Dim DeleteCommand As New SqlCommand("DELETE FROM [siteCodes] WHERE ([siteCodes].[siteID] = @siteID)", myConnection)


        ' execute the command
        myConnection.Open()
        DeleteCommand.ExecuteNonQuery()
        myConnection.Close()


        ' rebind the grid
        DataGrid1.CurrentPageIndex = 0
        DataGrid1.EditItemIndex = -1
        BindGrid()


    End If


End Sub


Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)


    ' display a new page of data


    If Not isEditing Then


        DataGrid1.EditItemIndex = -1
        DataGrid1.CurrentPageIndex = e.NewPageIndex
        BindGrid()


    End If


End Sub


Sub AddNew_Click(Sender As Object, E As EventArgs)


    ' add a new row to the end of the data, and set editing mode 'on'


    CheckIsEditing("")


    If Not isEditing = True Then


        ' set the flag so we know to do an insert at Update time
        AddingNew = True


        ' add new row to the end of the dataset after binding


        ' first get the data
        Dim myConnection As New SqlConnection(ConnectionString)
        Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)


        Dim ds As New DataSet()
        myCommand.Fill(ds)


        ' add a new blank row to the end of the data
        Dim rowValues As Object() = {"", "", ""}
        ds.Tables(0).Rows.Add(rowValues)


        ' figure out the EditItemIndex, last record on last page
        Dim recordCount As Integer = ds.Tables(0).Rows.Count


        If recordCount > 1 Then


            recordCount -= 1
            DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
            DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize


        End If


        ' databind
        DataGrid1.DataSource = ds
        DataGrid1.DataBind()


    End If




End Sub


' ---------------------------------------------------------------
'
' Helpers Methods:
'


' property to keep track of whether we are adding a new record,
' and save it in viewstate between postbacks


Property AddingNew() As Boolean


    Get
        Dim o As Object = ViewState("AddingNew")
        If o Is Nothing Then
            Return False
        End If
        Return CBool(o)
    End Get


    Set(ByVal Value As Boolean)
        ViewState("AddingNew") = Value
    End Set


End Property


Sub BindGrid()


    Dim myConnection As New SqlConnection(ConnectionString)
    Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)


    Dim ds As New DataSet()
    myCommand.Fill(ds)


    DataGrid1.DataSource = ds
    DataGrid1.DataBind()


End Sub

Re: Error Input string was not in a correct format

Have you tried wrapping your input string which is text with a Decimal.Parse(string) to put it in the correct data type for your database.

Re: Error Input string was not in a correct format

Let me back up. I am quite the Newbie, I am trying to use Webmatrix in order to learn ASP.Net in an IDE. Not sure how to "wrap" anything yet and I wouldn't know where to put this wrap since I have no idea where this decimal expectation is coming from. In using the template, all I changed were the variable names and textbox names. The fundamental question I have is why is Framework/webpage/SQL/Whatever expecting a decimal? Nothing I can see in the Webmatrix Editable Datagrid template suggests a decimal. Thanks JOE

Re: Error Input string was not in a correct format

I've never used Web Matrix, The only think I might suggest is that is that if your using the graphical template editor with a template column, you could try clicking on the control inside of the template control and viewing the DataBindings property. Look at what is inside the Container, this allows you to specify certain data types to expect to bind etc.

Re: Error Input string was not in a correct format

The Webmatrix does provide places for that data, but not suggestions on what to use and for what purpose. Those questions can't be answered, I believe, until the first question is answered- "where in the above script is it suggested that my "siteID" variable is a decimal? Thanks.

Re: Error Input string was not in a correct format

Only other thing I can think of is that your code somewhere is making an implicit conversion without you knowing. In order to see if it is doing this (again I'm only familiar with VS) but you should be able to declare: option strict = on option explicit = on This should trigger the compiler to show you where any implicity conversions are taking place.

Re: Error Input string was not in a correct format

Ok, after reading some tutorials, I re-wrote the script and now I get and SQL syntax error that I just don't see. Can someone provide a couple fresh eyeballs and possibly spot my problem? Thanks! JOE Error: Update failed:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at ASP.editv1_aspx.MyDataGrid_Update(Object sender, DataGridCommandEventArgs e) in C:\Documents and Settings\joe\My Documents\editv1.aspx:line 62 script: <script runat="server"> Dim strConn As String = "server='(local)'; trusted_connection=true; database='networkSQL2'" ' Dim strConn As String = "SELECT siteID, muxCode, siteCode, city FROM siteCodes" Sub Page_Load(Src As Object, E As EventArgs) If Not (IsPostBack) BindGrid() End If End Sub Sub BindGrid() Dim myConnection as New SqlConnection (strConn) Dim DS As DataSet Dim MyCommand As SqlDataAdapter MyCommand = new SqlDataAdapter("select * from siteCodes", MyConnection) DS = new DataSet() MyCommand.Fill(DS, "siteCodes") MyDataGrid.DataSource=DS.Tables("siteCodes").DefaultView MyDataGrid.DataBind() End Sub Public Sub MyDataGrid_Cancel(sender As Object, e As DataGridCommandEventArgs) MyDataGrid.EditItemIndex = -1 BindGrid() End Sub Sub MyDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs) MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex) BindGrid() End Sub Public Sub MyDataGrid_Update(sender As Object, e As DataGridCommandEventArgs) ' Dim siteID As String = CType(e.Item.FindControl("siteID"), TextBox).Text Dim muxCode As String = CType(e.Item.FindControl("muxCode"), TextBox).Text Dim siteCode As String = CType(e.Item.FindControl("siteCode"), TextBox).Text Dim city As String = CType(e.Item.FindControl("city"), TextBox).Text Dim mySqlConnection as New SqlConnection (strConn) Dim mySqlCommand as SqlCommand Dim strSql as String strSql = "Exec s_update_forward '" & muxCode & "', " strSql = strSql & "'" & siteCode & "', " strSql = strSql & "'" & city & "', " ' strSql = strSql & "'" & strSource & "'" mySqlCommand = new SqlCommand(strSql, mySqlConnection) Try Message.text = "Update failed:" mySqlConnection.Open() mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection) Message.text = "UPDATED Successfully!" Catch exc as exception if Instr(1, exc.tostring, "duplicate key") > 0 then Message.text = Message.text + "Cannot insert duplicate values." else Message.text = Message.text + exc.tostring() end if Catch SQLexc as sqlexception Message.text = Message.text + sqlexc.tostring() Finally mySqlConnection.Close() End Try MyDataGrid.EditItemIndex = -1 BindGrid() End Sub Public Sub MyDataGrid_Delete(sender As Object, e As DataGridCommandEventArgs) Message.Text = "TBD" MyDataGrid.EditItemIndex = -1 BindGrid() End Sub </script> <form id="editDataGrid" runat="server">

* * *

</form>