1

I'm trying to insert a file to a Access Database and it is impossible at all.

The 'ExecuteNonQuery' returns that 1 file has been inserted but in table there is no chages. Where will be the problem? Thanks in advance.

The database conection is ok and i can read data from it

Dim sql As String = "INSERT INTO Cierre (TIPO,CANTIDAD,FECHA,TIENDA,CAJA) VALUES ('A','3,45','16/05/2019 21:05:34','0','0')"
Conn.Open()
Dim cmd As OleDbCommand = New OleDbCommand(sql, Conn)
Dim i = cmd.ExecuteNonQuery()
Conn.Close()
Javier
  • 111
  • 1
  • If i execute that query in ms-access or in visual studio it works ok and insert the file – Javier May 16 '19 at 19:14
  • What is your connectionstring? Did you use the DataDirectory substitution string? – Steve May 16 '19 at 19:19
  • Sorry @Steve , here it is : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Product.mdb – Javier May 16 '19 at 20:46
  • As expected. Look at this question and to my answer there. [Why saving changes to database fails](https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails) – Steve May 16 '19 at 20:47

1 Answers1

0

Check your connection string. Build your command with parameters and dispose with Using

   Using OleDbConn As New OleDbConnection("ConnectionString")
        Using Cmd As New OleDbCommand("INSERT INTO Cierre (TIPO,CANTIDAD,FECHA,TIENDA,CAJA) VALUES (@TIPO,@CANTIDAD,@FECHA,@TIENDA,@CAJA)", OleDbConn)
            Cmd.Parameters.Add("@TIPO", OleDbType.VarChar).Value = "A"
            Cmd.Parameters.Add("@CANTIDAD", OleDbType.VarChar).Value = "3,45"
            Cmd.Parameters.Add("@FECHA", OleDbType.Date).Value = New DateTime("16/05/2019 21:05:34")
            'If these are actually type INT then change OleDbType and insert as INT not string
            Cmd.Parameters.Add("@TIENDA", OleDbType.VarChar).Value = "0"
            Cmd.Parameters.Add("@CAJA", OleDbType.VarChar).Value = "0"
            Cmd.ExecuteNonQuery()
        End Using
    End Using
Mr. Tripodi
  • 809
  • 1
  • 6
  • 7
  • I have changed some lines, but it doesn't work: – Javier May 16 '19 at 20:22
  • "doesnt work" thats not helpful. please tell us the exception that you are receiving. – Mr. Tripodi May 16 '19 at 20:24
  • Pardon, problems with this comments editor ;) . I have changed some lines, but it doesn't work, It is necessary to open connection with OleDbConn.Open() after first **Using** , and the fields have been formatted as: Cmd.Parameters.Add("@TIPO", OleDbType.VarChar).Value="A" Cmd.Parameters.Add("@CANTIDAD", OleDbType.Decimal).Value=3.45 Cmd.Parameters.Add("@FECHA", OleDbType.Date).Value = New DateTime(2019, 5, 16, 21, 5, 34) Cmd.Parameters.Add("@TIENDA", OleDbType.Integer).Value = "0" Cmd.Parameters.Add("@CAJA", OleDbType.Integer).Value = "0" – Javier May 16 '19 at 20:35
  • And there are no exceptions and Cmd.ExecuteNonQuery() return that has changed 1 file – Javier May 16 '19 at 20:37
  • Yes i use DataDirectory but reads correctly , and there is no more databases there. – Javier May 16 '19 at 20:48
  • Ok, that's, i have changed DataDirectory with the whole route to file and works ok, thanks. Please @Steve answer the question to mark it as ok – Javier May 16 '19 at 20:53
  • 1
    There is no need to add another answer to the same problem. When you have enough reputation you can upvote my answer there if you like it. – Steve May 16 '19 at 20:55
  • 1
    Thanks, that answer explains perfectly what happens with "DataDirectory" and how i was turning me crazy. – Javier May 16 '19 at 21:01