Parameter has no default value error

• • Reply

  14 years ago

  by rich50

  Richard Watts

  , United Kingdom

  Joined 14 years ago

  I'm currently writing some code to UPDATE a database but I keep getting the following error
  
  OleDbException was unhandled
  Parameter @Venue1 has no default value.
  
  My code is below, the error occurs on the ExecuteNonQuery line, however the variable sent to the database is a string and the field is setup as Text so I'm not sure whats wrong. Does anyone have why this error is ocrruring and hoe to fix it?
  
  
  
  
  

  Many thanks
  
  

  Dim objCommand As OleDbCommand = New OleDbCommand
  
  intPosition = objCurrencyManagerDesCost.Position 'Save Currrent record position
  
  'Set OledbCommand object properties
  objCommand.Connection = objConnection
  objCommand.CommandText = "UPDATE VenueCosts " & _
  "SET qty1 = @qty1, Venue1 = @Venue1, DelCost1 = @DelCost1, date1 = @date1, " & _
  "qty2 = @qty2, Venue2 = @Venue2, DelCost2 = @DelCost2, date2 = @date2, " & _
  "qty3 = @qty3, Venue3 = @Venue3, DelCost3 = @DelCost3, date3 = @date3, " & _
  "qty4 = @qty4, Venue4 = @Venue4, DelCost4 = @DelCost4, date4 = @date4, " & _
  "qty5 = @qty5, Venue5 = @Venue5, DelCost5 = @DelCost5, date5 = @date5, " & _
  "qty6 = @qty6, Venue6 = @Venue6, DelCost6 = @DelCost6, date6 = @date6, " & _
  "qty7 = @qty7, Venue7 = @Venue7, DelCost7 = @DelCost7, date7 = @date7, " & _
  "qty8 = @qty2, Venue8 = @Venue8, DelCost8 = @DelCost8, date8 = @date8, " & _
  "qty9 = @qty9, Venue9 = @Venue9, DelCost9 = @DelCost9, date9 = @date9, " & _
  "qty10 = @qty10, Venue10 = @Venue10, DelCost10 = @DelCost10, date10 = @date10 " & _
  "WHERE VerveWip2RowNo = @VerveWip2RowNo"
  objCommand.CommandType = CommandType.Text
  
  'Add parameters for the placeholders in the OldbCommandText property
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty1", Quantity1))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty2", Quantity2))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty3", Quantity3))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty4", Quantity4))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty5", Quantity5))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty6", Quantity6))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty7", Quantity7))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty8", Quantity8))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty9", Quantity9))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty10", Quantity10))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue1", Ven1))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue2", Ven2))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue3", Ven3))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue4", Ven4))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue5", Ven5))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue6", Ven6))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue7", Ven7))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue8", Ven8))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue9", Ven9))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Venue10", Ven10))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost1", Cost1))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost2", Cost2))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost3", Cost3))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost4", Cost4))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost5", Cost5))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost6", Cost6))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost7", Cost7))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost8", Cost8))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost9", Cost9))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@DelCost10", Cost10))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date1", Date1))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date2", Date2))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date3", Date3))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date4", Date4))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date5", Date5))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date6", Date6))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date7", Date7))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date8", Date8))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date9", Date9))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@date10", Date10))
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@VerveWip2RowNo", BindingContext(objDataViewDesCost).Current("VerveWip2RowNo")))
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

   

  objConnection.Open() 'Open the connection
  objCommand.ExecuteNonQuery() 'Execute SQL Command
  objConnection.Close() 'Close the Connection
  
  

   

  Report abuse
• • Reply

  13 years ago

  by MagicCity33

  Phalander Owens

  , United States

  Joined 15 years ago

  Hello Rich50,

  For your parameter try changing it to this.

  'Add parameters for the placeholders in the OldbCommandText property
  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty1", OleDbType.VarWChar, 25).Value = Quantity1
  

  
  Or could have said:
  

  objCommand.Parameters.Add(New OleDb.OleDbParameter("@Qty1",

  OleDbType.VarChar)).Value = Quantity1

  You have to specify what type of variable it is you are placing into the database and it must be the same type as what you have set the database fields up for.  After typing oledbtype. the intellisence menu should pop up give you a list of options to choose from.  The last field in the parenthese specifis the maximum size/length allowed so it will chop everything off after this max character has been reached.
  
  

  I was having the same problem a few moments ago and stumbled across this.  The value that you are inputing can not be inside the parenthese.

  .Value is the key

  Hope this helps you.

  Report abuse
• • Reply

  13 years ago

  by andimatthewsmr2

  Andrew Matthews

  AVON, United Kingdom

  Joined 13 years ago

  Have you checked that your variable Ven1 is being assigned data?

  e.g. Ven1 = Me.Somedata before executing the Proc? if yes test it by adding in a default value e.g. Ven1 = "Hello World"...

  Report abuse