Parameterized SQL Command does not update my column

Question

I have a simple button which end the Work day in my Management System. Sadly when pressed all fine but the Column in my Table is still empty.

Here is the code:

Dim sql As String = "UPDATE [TA-Arbeitszeit] SET Ende = @ende WHERE Personal_nr = @Personal_nr"
    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\recycle2000.mdb;"),
             cmd As New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.Add("@Personal_nr", OleDbType.VarChar).Value = tbxUserInput.Text.Trim()
        cmd.Parameters.Add("@ende", OleDbType.VarChar).Value = DateTime.Now.ToString("G")
        Dim icount As Integer = cmd.ExecuteNonQuery

    End Using

Why are you using a text column to store dates? Access databases have a dedicated data type for dates and times. Use it. — jmcilhinney
– jmcilhinney, Commented Apr 28, 2020 at 9:38

jmcilhinney · Accepted Answer · 2020-04-28 09:41:01Z

1

Access doesn't fully support named parameters. Although you can and should use parameter names for your own clarity, Access ignores them. It simply inserts your parameter values into the SQL code in the order they are added. That means that they need to be added in the same order as they appear in the SQL code. Your SQL code has @ende before @Personal_nr but when you add parameters you do it the other way around. Switch the order in which you add the parameters and you should hopefully be fine.

answered Apr 28, 2020 at 9:41

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BK201 Over a year ago

Okay, i use parameters because i don't want to have any SQL-Injections. And i didn't knew about the order that my parameters should be with Access. Thank you. I can see now the time inserted into the End column.

Collectives™ on Stack Overflow

Parameterized SQL Command does not update my column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related