error with insert command

[lucaazori]

Hi, please i need your help, i had error while i tried to insert into my data base, here is my code:

Try
                
                'preparer les requetes
                Dim sqlQRY1 As String = "INSERT INTO AUDIO  Values ('" + nomAudio + "')"
                Dim sqlQRY2 As String = "INSERT INTO IMAGE(Nom_Img)  Values ('" + nomImage + "')"
               
                'ouvrir la connexion
                myConnexion.Open()
                'creation des commandes
                Dim cmd1 As OleDbCommand = New OleDbCommand(sqlQRY1, myConnexion)
                Dim cmd2 As OleDbCommand = New OleDbCommand(sqlQRY2, myConnexion)
               
                'exécution des requetes
                cmd1.ExecuteNonQuery()
                cmd2.ExecuteNonQuery()
               
                MsgBox("Données insérées avec succes")
                myConnexion.Close()
                
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                myConnexion.Close()
            End Try

to make sure i have tried with only cmd1 and it works but with cmd2 i got error:

System.Data.OleDb.OleDbException:Syntax error in INSERT INTO Statement

thx in advance .

[lolafuertes]

On the sqlQRY1 you DO NOT specify wich fields to fill wich meand that the AUDIO table has only one field.

On the sqlQRY2 you specify to fill ONLY the field called Nom_Img in the table IMAGE. If this table has more fields, you must:
* Or define default values on the IMAGE table for all the rest of field and declare them as NOT required
* Or supply data for all the required fields inthe table IMAGE.

If this is not the case, please supply a table definition and the full error message.

PD: Can be a good idea to add a begintransaction and then commit the transaction (or rollback if fails), to ensure that the insert commnad(s) is/are isolated, preventing data loss.

[lucaazori]

Hi, actually i tried the same with the audio table, each table (audio and image) has one field so it`s not here the problem, how can i put a photo in the post to show you full errors please ??

[lolafuertes]

Just on the advanced editor search for the attach files (mange attachements) button

[lucaazori]

here is the full message, thank you in advance

[lolafuertes]

What is the content of nomImage variable?

If in the contents exists some apostrophe this can be interpreted as the end of variable value and the rest of the contents as part of the INSERT sentence wicht is not.

This is called SQL injection and is a very common problem in countries using the apostrophe in their language.

If this is the case, you can assure that the sintax is correct using

Dim sqlQRY2 As String = "INSERT INTO IMAGE(Nom_Img)  Values ('" + nomImage.Replace("'","''") + "')"

Hope this helps

[lucaazori]

hi, i have tried what you said but the problem still unsolved besides there is no apostrophe in the name of the image, the name of the image and the wave both generated by a fonction which return a name made with random , here is the code for the function if you need:

Module Module1
    'les 2 tableaux déclarés ci-dessous sert pour la fonction calculerNom

    'un tableau contiendra les chiffres de 0 à 9
    Public tabInt(10) As Integer

    'un tableau contiendra les lettres de l`alphabet
    Public tabChar(26) As Char

    'fonction pour calculer un nouveau nom a partir d`un nom donné

    Public Function calculerNom()
        Dim i As Integer
        'initialisation des élèments du tabInt 
        For i = 0 To 9
            tabInt(i) = i
        Next i
        'initialisation des élèments du tabChar
        tabChar(0) = "A"
        tabChar(1) = "B"
        tabChar(2) = "C"
        tabChar(3) = "D"
        tabChar(4) = "E"
        tabChar(5) = "F"
        tabChar(6) = "G"
        tabChar(7) = "H"
        tabChar(8) = "I"
        tabChar(9) = "J"
        tabChar(10) = "K"
        tabChar(11) = "L"
        tabChar(12) = "M"
        tabChar(13) = "N"
        tabChar(14) = "O"
        tabChar(15) = "P"
        tabChar(16) = "Q"
        tabChar(17) = "R"
        tabChar(18) = "S"
        tabChar(19) = "T"
        tabChar(20) = "U"
        tabChar(21) = "V"
        tabChar(22) = "W"
        tabChar(23) = "X"
        tabChar(24) = "Y"
        tabChar(25) = "Z"
        'une variable de la classe Random
        Dim A1 As New Random()
        Dim A2 As New Random()
        Dim chiffreChoisi As Integer 'indique l`indice aléatoire du chiffre à extraire
        Dim lettreChoisi As Integer 'indique l`indice aléatoire du caractère à extraire
        'le nouveau nom 
        Dim nouveauNom As String = ""
        For i = 0 To 5
            chiffreChoisi = A1.Next(0, 10)
            nouveauNom = nouveauNom + tabInt(chiffreChoisi).ToString
            lettreChoisi = A2.Next(0, 27)
            nouveauNom = nouveauNom + tabChar(lettreChoisi).ToString
        Next
        Return nouveauNom
    End Function
End Module

[lucaazori]

Hi, i have noticed that the problem may come from IMAGE table, because i replaced the word IMAGE in sqlQRY2 by AUDIO so that the insert command will insert the IMAGE name in the AUDIO table which is also composed of one text field and it worked so the issue is from data base exactly IMAGE table, i tried to reconstruct this table with another name but the problem is the sams with same error message :( i don`t know what to do.

[lolafuertes]

Can you put here the IMAGE table definition?

Out of topic, if you define tabInt and tabChar as string you can initialize them on the dim statement like

Dim tabInt as String = "0123456789"
Dim tabChar as String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

To assign the selected char you can

nouveauNom = nouveauNom + tabInt.Chars(chiffreChoisi).ToString
nouveauNom = nouveauNom + tabChar.Chars(lettreChoisi).ToString

[lucaazori]

hi, here is the IMAGE table definition

and here the dataBase structure

hope it will help you to find with me solution ;)

[lucaazori]

sorry it was small resolution, hope this better

[lolafuertes]

Nothing wrong.

Just debug the INSERT INTO IMAGE command before being ececuted, take the command string and create a new Query using the SQL language on the Access DB. See what is the message from the Access side.

Another possibility is that IMAGE is reserved word. Try INSERT INTO [IMAGE] Values ...

[lucaazori]

Hi, thank you so much i tried this:

Dim sqlQRY2 As String = "INSERT INTO [IMAGE] Values ('" + nomImage + "')"

it worked fine so IMAGE has to appear as a reserved word ?? stronge!! thank you again