VB 2005 - Inserting Values to DB


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: VB 2005 - Inserting Values to DB

  1. #1
    Join Date
    Jun 2006
    Posts
    4

    VB 2005 - Inserting Values to DB

    I have a oledbcommand object with the following command text:

    Code:
    INSERT INTO FactoryAQueue
                          (Species, Quantity, Cloth, Stuffing, Accessory, Packing, QtyLeft)
    VALUES     ('[@Species]', '[@Quantity]', '[@Cloth]', '[@Stuffing]', '[@Accessories]', '[@Packing]', '[@QtyLeft]')
    my code to insert:

    Code:
     
    
            Dim species As String = cbxFactoryMakeSpecies.SelectedItem.ToString
            Dim quantity As Integer = CInt(cbxQtyAvail.SelectedItem)
    
            cmdAddJobsToFactory.Parameters.AddWithValue("@Cloth", "Red")
            cmdAddJobsToFactory.Parameters.AddWithValue("@Stuffing", "foam")
            cmdAddJobsToFactory.Parameters.AddWithValue("@Accossories", "plastic")
            cmdAddJobsToFactory.Parameters.AddWithValue("@Packing", "paper bags")
    
            cmdAddJobsToFactory.Parameters.AddWithValue("@Species", species)
            cmdAddJobsToFactory.Parameters.AddWithValue("@Quantity", quantity)
            cmdAddJobsToFactory.Parameters.AddWithValue("@QtyLeft", quantity)
    
            OleDbConnection1.Open()
    
            Dim rowsAffected2 As Integer
            rowsAffected2 = cmdAddJobsToFactory.ExecuteNonQuery 'error thrown here
    
            OleDbConnection1.Close()
            cmdAddJobsToFactory.Parameters.Clear()
    error:

    Data type mismatch in criteria expression.
    the quantity and qtyleft in the database are both set to number type integer

    the rest are set to "text"

    any ideas as to why this is being thrown?

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    To troubleshoot this I would execute the query starting with one column and then continue to add columns until the query fails. This will enable you to identify the column that is generating the error (unless of course it's identified in the trace and you didn't mention it).
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jun 2006
    Posts
    4
    Ok, I did remove the quotes, but I couldn't remove the brackets. I ran this command one paramater at a time. Turns out the paramaters with numbers work fine AND the adding the species paramater is fine, however the paramaters cloth, stuffing, accessory, and packing, all return the error. I've checked the DB and everything seems all right. Those columns properties are the exact same as the species column. I'm stumped.

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    You may need to specify the data types of the parameters. I'm not sure what type of database you are working with, but below is an example that uses OLEDB:

    Code:
    AccessCommand.Parameters.Add("@ParamName", System.Data.OleDb.OleDbType.VarWChar).Value = "SomeValue"
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Jun 2006
    Posts
    4
    thank you that fixed the issue

Similar Threads

  1. Inserting column in Excel using VB 6 ?
    By alexlex in forum VB Classic
    Replies: 2
    Last Post: 01-03-2006, 09:51 AM
  2. storing access table values in arrays in Vb
    By vbcoder in forum VB Classic
    Replies: 1
    Last Post: 04-14-2005, 06:39 AM
  3. Comparing NULL values (VB, ADO, SQL Server)
    By Heather in forum VB Classic
    Replies: 1
    Last Post: 02-08-2001, 03:55 PM
  4. Replies: 0
    Last Post: 07-01-2000, 03:44 AM
  5. Multi-row calculations
    By Bob Hines in forum Database
    Replies: 7
    Last Post: 04-27-2000, 11:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center