Update command with parameters problem

[kpeeroo]

Private Function AddCompanyOvertime() As Integer
        Dim companyID As Integer = GetCompanyID()
        Console.WriteLine(companyID)
        Dim paramCompanyID As New SqlParameter("@CompanyID", SqlDbType.Int)
        Dim paramCompanyOvertimeID As New SqlParameter("@companyOvertimeID", SqlDbType.Int)
        Dim paramDate As New SqlParameter("@Date", SqlDbType.DateTime)
        Dim paramOvertimePrice As New SqlParameter("@OvertimePrice", SqlDbType.Float)
        Dim paramAdditionalRoutePrice As New SqlParameter("@AdditionalRoutePrice", SqlDbType.Float)
        Dim paramAdditionalRoute As New SqlParameter("@AdditionalRoute", SqlDbType.Text)
        Dim paramRoute As New SqlParameter("@Route", SqlDbType.Text)
        Dim paramNewRecordID As New SqlParameter("@NewRecordID", SqlDbType.Int)
        Dim newCompanyOvertimeID As New Integer
        newCompanyOvertimeID = 0
 
        paramCompanyID.Direction = ParameterDirection.Input
        paramCompanyID.Value = companyID
        paramDate.Direction = ParameterDirection.Input
        'actual date of record, ie, clientDate
        paramDate.Value = ClientDateTBox.Text()
        paramRoute.Direction = ParameterDirection.Input
        paramRoute.Value = clientOvertimeRouteTBox.Text
        paramOvertimePrice.Direction = ParameterDirection.Input
        If companyFOPriceTBox.Enabled = True Then
            paramOvertimePrice.Value = companyFOPriceTBox.Text
        Else
            paramOvertimePrice.Value = companyVOPriceTBox.Text
        End If
        If clientARouteTBox.Enabled = True Then
            paramAdditionalRoute.Direction = ParameterDirection.Input
            paramAdditionalRoute.Value = clientARouteTBox.Text
            paramAdditionalRoutePrice.Direction = ParameterDirection.Input
            paramAdditionalRoutePrice.Value = companyARPriceTBox.Text
        Else
            paramAdditionalRoute.Direction = ParameterDirection.Input
            paramAdditionalRoute.Value = System.DBNull.Value
            paramAdditionalRoutePrice.Direction = ParameterDirection.Input
            paramAdditionalRoutePrice.Value = System.DBNull.Value
        End If
        paramNewRecordID.Direction = ParameterDirection.Output
        Try            
            If standbyCB.Checked = False Then
                SqlInsertCommandCompanyOvertime.Parameters.Add(paramCompanyID)
                SqlInsertCommandCompanyOvertime.Parameters.Add(paramDate)
                SqlInsertCommandCompanyOvertime.Parameters.Add(paramOvertimePrice)
                SqlInsertCommandCompanyOvertime.Parameters.Add(paramRoute)
                SqlInsertCommandCompanyOvertime.Parameters.Add(paramAdditionalRoute)
                SqlInsertCommandCompanyOvertime.Parameters.Add(paramAdditionalRoutePrice)
                SqlInsertCommandCompanyOvertime.Parameters.Add(paramNewRecordID)
                SqlInsertCommandCompanyOvertime.Connection.Open()
                SqlInsertCommandCompanyOvertime.ExecuteNonQuery()
                SqlInsertCommandCompanyOvertime.Connection.Close()
                newCompanyOvertimeID = SqlInsertCommandCompanyOvertime.Parameters.Item("@NewRecordID").Value()
                Console.WriteLine(newCompanyOvertimeID)
                SqlInsertCommandCompanyOvertime.Parameters.Clear()
            Else
                paramCompanyOvertimeID.Direction = ParameterDirection.Input
                paramCompanyOvertimeID.Value = AddCompanyStandby()
 
                SqlUpdateCommandCompanyOvertime.Parameters.Add(paramOvertimePrice)
                SqlUpdateCommandCompanyOvertime.Parameters.Add(paramRoute)
                SqlUpdateCommandCompanyOvertime.Parameters.Add(paramAdditionalRoute)
                SqlUpdateCommandCompanyOvertime.Parameters.Add(paramAdditionalRoutePrice)
                SqlUpdateCommandCompanyOvertime.Parameters.Add(paramCompanyOvertimeID)
                SqlUpdateCommandCompanyOvertime.Connection.Open()
                SqlUpdateCommandCompanyOvertime.ExecuteNonQuery()
                SqlUpdateCommandCompanyOvertime.Connection.Close()
                SqlUpdateCommandCompanyOvertime.Parameters.Clear()
            End If
        Catch ex As Exception
            Console.WriteLine(ex.StackTrace)
        End Try
        Return newCompanyOvertimeID
    End Function
 

Hi all! got a problem with my code when am running an UPDATE command. Basically what am doing in this code is calling a function AddCompanyStandby() that will return me a newly entered record id as an Integer. I want to use that id for the update command to insert new data in that same column but am getting errors. Any ideas?


A first chance exception of type 'System.FormatException' occurred in System.Data.dll
4
4
4
4
55
at System.Data.SqlClient.SqlParameter.CoerceValue(Obj ect value, MetaType destinationType)
at System.Data.SqlClient.SqlParameter.GetCoercedValue ()
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.SetUpRPCParameter s(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at WindowsApplication1.Form1.AddCompanyOvertime() in C:\Documents and Settings\Root\My Documents\Visual Studio 2008\Projects\WindowsApplication1\WindowsApplicati on1\Form2.vb:line 91

[debasisdas]

you need to pass the parameter of the correct data type to avoid the error.

[kpeeroo]

i dont understand. I have commented all of the parameters i am passing to my update command except that of price, ie, only one parameter of type float. By the way am using stored procedure and the type is float there as well. But now getting this error:


A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at WindowsApplication1.Form1.AddCompanyOvertime() in C:\Documents and Settings\Root\My Documents\Visual Studio 2008\Projects\WindowsApplication1\WindowsApplicati on1\Form2.vb:line 91

[kpeeroo]

Hi guys

just sorted out the problem. It was with my stored procedure parameters. I cannot leave a variable in the file without declaring it because sql database server will expect it. So better comment every variable that is not to be passed to the sql command! Also, I had to check the parameters in the properties in design mode of my SqlDataAdapter. In my case, I had to clear everything that was being generated.