ok i need help with this code i more than triple checked the spelling so i dont know what is wrong anymore and i have checked that i inserted the right data also.

This is my code i use to insert into my DB when i break it i get the error "Column name or number of supplied values does not match table definition." what does this mean coz everything in my table is exactly the same

Try
            sqlcon.Open()
            Dim sql As String = "INSERT INTO hero VALUES(@heroName, @heroStrenght, @heroAgility, @heroIntelligence, @heroAffilLiation, @heroDamage, @heroArmor, @heroMoveSpeed, @heroAttackRange, @heroMissleSpeed, @heroSightRange, @heroSkill1, @heroSkill2, @heroSkill3, @heroSkill4, @heroPicture)"
            Dim cmd As New SqlCommand(sql, sqlcon)

            cmd.Parameters.AddWithValue("@heroName", txtHeroName.Text)
            cmd.Parameters.AddWithValue("@heroStrenght", txtStrength.Text)
            cmd.Parameters.AddWithValue("@heroAgility", txtAgility.Text)
            cmd.Parameters.AddWithValue("@heroIntelligence", txtINtelli.Text)
            cmd.Parameters.AddWithValue("@heroAffilLiation", txtAff.Text)
            cmd.Parameters.AddWithValue("@heroDamage", txtDamage.Text)
            cmd.Parameters.AddWithValue("@heroArmor", txtArmor.Text)
            cmd.Parameters.AddWithValue("@heroMoveSpeed", txtMS.Text)
            cmd.Parameters.AddWithValue("@heroAttackRange", txtAR.Text)
            cmd.Parameters.AddWithValue("@heroMissleSpeed", txtMissileS.Text)
            cmd.Parameters.AddWithValue("@heroSightRange", txtSR.Text)
            cmd.Parameters.AddWithValue("@heroSkill1", txtSkill1.Text)
            cmd.Parameters.AddWithValue("@heroSkill2", txtSkill2.Text)
            cmd.Parameters.AddWithValue("@heroSkill3", txtSkill3.Text)
            cmd.Parameters.AddWithValue("@heroSkill4", txtSkill4.Text)

            Dim ms As New MemoryStream()
            pbHero.BackgroundImage.Save(ms, pbHero.BackgroundImage.RawFormat)
            Dim data As Byte() = ms.GetBuffer()
            Dim p As New SqlParameter("@heroPicture", SqlDbType.Image)
            p.Value = data
            cmd.Parameters.Add(p)
            cmd.ExecuteNonQuery()

            MessageBox.Show("Successfully added", "SAVED", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show("An error occured during saving", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
        sqlcon.Close()

Name your fields explicitly. If your table has more columns than specified values the insert will fail. The format is

INSERT INTO table (fldname1,fldname2,etc)
            VALUES('value1','value2',etc)

Keeping in mind that you don't need the single quotes around numeric fields. Also, by explicitly naming your fields you make the insert independent of the actual column ordering in the database.

what do you mean by "single quotes around numeric fields". im not catching what you are saying.

each column in the insert statement is in my table accept for heroID

got it working didnt put in the forgeinkey of the other table

Be carefull not to allow sql injection in your code. Based on what I see here you might put hazardous code in a 'skill' field. Lets say I would like to register my skill "; drop table [users];" in you will have a problem. Look in to stored procedures before further designing your application.

Edited 4 Years Ago by marcel.vandersluys.3

I gave the actual syntax of the INSERT command which requires single quotes around text fields. You don't have to worry about this when you use parameterized entry (which you should use to avoid SQL injection attacks). There are two reasons you should specify the field names when you do an insert

  1. You can omit fields that are auto-increment or which have default values
  2. Your insert is independent of the order of the fields in the table

Both points are especially important if the structure of the table is ever changed (fields reordered or one or more fields added).

This article has been dead for over six months. Start a new discussion instead.