By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,188 Members | 1,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,188 IT Pros & Developers. It's quick & easy.

Update command with parameters problem

P: 12
Expand|Select|Wrap|Line Numbers
  1. Private Function AddCompanyOvertime() As Integer
  2.         Dim companyID As Integer = GetCompanyID()
  3.         Console.WriteLine(companyID)
  4.         Dim paramCompanyID As New SqlParameter("@CompanyID", SqlDbType.Int)
  5.         Dim paramCompanyOvertimeID As New SqlParameter("@companyOvertimeID", SqlDbType.Int)
  6.         Dim paramDate As New SqlParameter("@Date", SqlDbType.DateTime)
  7.         Dim paramOvertimePrice As New SqlParameter("@OvertimePrice", SqlDbType.Float)
  8.         Dim paramAdditionalRoutePrice As New SqlParameter("@AdditionalRoutePrice", SqlDbType.Float)
  9.         Dim paramAdditionalRoute As New SqlParameter("@AdditionalRoute", SqlDbType.Text)
  10.         Dim paramRoute As New SqlParameter("@Route", SqlDbType.Text)
  11.         Dim paramNewRecordID As New SqlParameter("@NewRecordID", SqlDbType.Int)
  12.         Dim newCompanyOvertimeID As New Integer
  13.         newCompanyOvertimeID = 0
  14.  
  15.         paramCompanyID.Direction = ParameterDirection.Input
  16.         paramCompanyID.Value = companyID
  17.         paramDate.Direction = ParameterDirection.Input
  18.         'actual date of record, ie, clientDate
  19.         paramDate.Value = ClientDateTBox.Text()
  20.         paramRoute.Direction = ParameterDirection.Input
  21.         paramRoute.Value = clientOvertimeRouteTBox.Text
  22.         paramOvertimePrice.Direction = ParameterDirection.Input
  23.         If companyFOPriceTBox.Enabled = True Then
  24.             paramOvertimePrice.Value = companyFOPriceTBox.Text
  25.         Else
  26.             paramOvertimePrice.Value = companyVOPriceTBox.Text
  27.         End If
  28.         If clientARouteTBox.Enabled = True Then
  29.             paramAdditionalRoute.Direction = ParameterDirection.Input
  30.             paramAdditionalRoute.Value = clientARouteTBox.Text
  31.             paramAdditionalRoutePrice.Direction = ParameterDirection.Input
  32.             paramAdditionalRoutePrice.Value = companyARPriceTBox.Text
  33.         Else
  34.             paramAdditionalRoute.Direction = ParameterDirection.Input
  35.             paramAdditionalRoute.Value = System.DBNull.Value
  36.             paramAdditionalRoutePrice.Direction = ParameterDirection.Input
  37.             paramAdditionalRoutePrice.Value = System.DBNull.Value
  38.         End If
  39.         paramNewRecordID.Direction = ParameterDirection.Output
  40.         Try            
  41.             If standbyCB.Checked = False Then
  42.                 SqlInsertCommandCompanyOvertime.Parameters.Add(paramCompanyID)
  43.                 SqlInsertCommandCompanyOvertime.Parameters.Add(paramDate)
  44.                 SqlInsertCommandCompanyOvertime.Parameters.Add(paramOvertimePrice)
  45.                 SqlInsertCommandCompanyOvertime.Parameters.Add(paramRoute)
  46.                 SqlInsertCommandCompanyOvertime.Parameters.Add(paramAdditionalRoute)
  47.                 SqlInsertCommandCompanyOvertime.Parameters.Add(paramAdditionalRoutePrice)
  48.                 SqlInsertCommandCompanyOvertime.Parameters.Add(paramNewRecordID)
  49.                 SqlInsertCommandCompanyOvertime.Connection.Open()
  50.                 SqlInsertCommandCompanyOvertime.ExecuteNonQuery()
  51.                 SqlInsertCommandCompanyOvertime.Connection.Close()
  52.                 newCompanyOvertimeID = SqlInsertCommandCompanyOvertime.Parameters.Item("@NewRecordID").Value()
  53.                 Console.WriteLine(newCompanyOvertimeID)
  54.                 SqlInsertCommandCompanyOvertime.Parameters.Clear()
  55.             Else
  56.                 paramCompanyOvertimeID.Direction = ParameterDirection.Input
  57.                 paramCompanyOvertimeID.Value = AddCompanyStandby()
  58.  
  59.                 SqlUpdateCommandCompanyOvertime.Parameters.Add(paramOvertimePrice)
  60.                 SqlUpdateCommandCompanyOvertime.Parameters.Add(paramRoute)
  61.                 SqlUpdateCommandCompanyOvertime.Parameters.Add(paramAdditionalRoute)
  62.                 SqlUpdateCommandCompanyOvertime.Parameters.Add(paramAdditionalRoutePrice)
  63.                 SqlUpdateCommandCompanyOvertime.Parameters.Add(paramCompanyOvertimeID)
  64.                 SqlUpdateCommandCompanyOvertime.Connection.Open()
  65.                 SqlUpdateCommandCompanyOvertime.ExecuteNonQuery()
  66.                 SqlUpdateCommandCompanyOvertime.Connection.Close()
  67.                 SqlUpdateCommandCompanyOvertime.Parameters.Clear()
  68.             End If
  69.         Catch ex As Exception
  70.             Console.WriteLine(ex.StackTrace)
  71.         End Try
  72.         Return newCompanyOvertimeID
  73.     End Function
  74.  
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
Oct 25 '08 #1
Share this Question
Share on Google+
3 Replies


debasisdas
Expert 5K+
P: 8,127
you need to pass the parameter of the correct data type to avoid the error.
Oct 25 '08 #2

P: 12
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
Oct 25 '08 #3

P: 12
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.
Oct 28 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.