CODEWITHSUNDEEP
asp.netvb.netms-access
FrankSharp
FrankSharp

Reputation: 2632

I can't insert value in my acces database

This my code in VB.NET.

My try catch says there is a syntax error in the instruction INSERT INTO. I don't know what happened to my INSERT. I searched for the error for over an hour... I'm not an expert in VB.NET, I'm better in C#, but I need to do this anyway in VB...

Thanks for helping me!!

Sub InsertRecord()
Dim conClasf As OleDbConnection
Dim cmdClasf As New OleDbCommand
Dim strClasf As String
Dim strSQL As String
Dim intRowsAff As Integer

 lblErrMsg.Text = ""
 lblRecsAff.Text = ""
  strClasf = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & _
         server.mappath("BecsEtMuseaux.mdb") & ";"
 conClasf = New OleDbConnection(strClasf)
 conClasf.Open

Randomize
    strSQL = "INSERT INTO client (" & _
        "UserName, " & _
        "Prenom, " & _
        "Nom, " & _
        "password, " & _
        "mail, " & _
        "Addresse, " & _
        "Ville, " & _
        "PostalCode, " & _
        "Province, " & _
        "Pays, " & _
        "AnimalGenre, " & _
        "NomAnimal, " & _
        "Race, " & _
") VALUES ('" & _
        Replace(txtUserName.Text, "'", "''") & _
        "', '" & _
        Replace(txtPrénom.Text, "'", "''") & _
        "', '" & _
        Replace(txtNom.Text, "'", "''") & _
        "', '" & _
        Replace(txtPass.Text, "'", "''") & _
        "', '" & _
        Replace(txtMail.Text, "'", "''") & _
        "', " & _
        Replace(txtAdresse.Text, "'", "''") & _
        "', " & _
         Replace(txtVille.Text, "'", "''") & _
        "', " & _
        Replace(txtPostal.Text, "'", "''") & _
        "', " & _
         Replace(txtProvince.Text, "'", "''") & _
        "', " & _
         Replace(txtPays.Text, "'", "''") & _
        "', " & _
         Replace(rblAnimal.Text, "'", "''") & _
        "', " & _
        Replace(txtAnimal.Text, "'", "''") & _
        "', " & _
         Replace(txtRace.Text, "'", "''")

      cmdClasf = New OleDbCommand(strSQL, conClasf)
      Try
      intRowsAff = cmdClasf.ExecuteNonQuery()
      Catch ex As Exception
      lblErrMsg.Text = ex.Message    
      End Try
      lblRecsAff.Text = intRowsAff & " record(s) inserted"
      conClasf.Close
 End Sub

Upvotes: 0

Views: 255

Answers (2)

KV Prajapati
KV Prajapati

Reputation: 94645

Escape the Password field. It is reserved word of Access Engine..

strSQL = "INSERT INTO client (UserName,Prenom,Nom,[password],mail,
          Addresse,Ville,PostalCode,Province,Pays,AnimalGenre,NomAnimal,Race)
          VALUES 
          (@UserName,@Prenom,@Nom,@password,@mail,
            @Addresse,@Ville,@PostalCode,@Province,@Pays,
             @AnimalGenre,@NomAnimal,@Race)"

Upvotes: 2

BizApps
BizApps

Reputation: 6130

Maybe this cause you an error.

       "Race, " & _ //Race**,** try to remove ,
")

Much better if you try this:

You can use Parameters to avoid sqlinjection.

  strSQL = "INSERT INTO client (UserName,Prenom,Nom,password,mail,Addresse,Ville,PostalCode,Province,Pays,AnimalGenre,NomAnimal,Race) VALUES (@UserName,@Prenom,@Nom,@password,@mail,@Addresse,@Ville,@PostalCode,@Province,@Pays,@AnimalGenre,@NomAnimal,@Race)"

  cmdClasf.Parameters.Add("@UserName", OleDbType.VarChar, 50).Value = txtUserName.Text
 //DO OTHER STUFF UNITL @Race

See also:

Parameter Queries in ASP.NET with MS Access

Configuring Parameters and Parameter Data Types (ADO.NET)

Use Parameters in your sql command

Hope this help.

Regards

Upvotes: 1

Related Questions