VB.NET to ACCESS DataBase, Data not being saved-CodePudding

I am having trouble with my Data Save routine, saving data from several textbox's into an MSACCESS database. The code throws no errors, completes successful, but the database is not accessed. I assume it is the connection that is causing the problem but cannot see why. Can anyone suggest where I am going wrong? Thank you for looking.

 cn = New OleDbConnection
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & databaseName & "; Persist Security Info=False;"
    Dim cmd As OleDbCommand = New OleDbCommand()
    cmd.CommandType = CommandType.Text

    cmd.CommandText = "UPDATE [BRLocoDatabase2] SET [aDate] = @aDate, [aDepot] = @aDepot, [bDate] = @bDate, [bDepot] = @bDepot," & _
        " [cDate] = @cDate, [cDepot] = @cDepot, [dDate] = @dDate, [dDepot] = @dDepot WHERE [LOCO] = @Loco"
    cmd.Parameters.AddWithValue("@Loco", TextBox1.Text)
    cmd.Parameters.AddWithValue("@aDate", TextBox19.Text)
    cmd.Parameters.AddWithValue("@aDepot", TextBox20.Text)
    cmd.Parameters.AddWithValue("@bDate", TextBox21.Text)
    cmd.Parameters.AddWithValue("@bDepot", TextBox22.Text)
    cmd.Parameters.AddWithValue("@cDate", TextBox23.Text)
    cmd.Parameters.AddWithValue("@cDepot", TextBox24.Text)
    cmd.Parameters.AddWithValue("@dDate", TextBox25.Text)
    cmd.Parameters.AddWithValue("@dDepot", TextBox26.Text)

    cmd.Connection = cn
    cn.Open()
    cmd.ExecuteNonQuery()
    MessageBox.Show("Update was a Success!")
    cn.Close()

...

CodePudding user response：

You are using OLEDB and OLEDB doesn't support named arguments. Your ability to use named parameters is just a syntactic sugar. Still those parameters are used by position. In your code, you have xDate which is likely dates, then xDepot and finally a where criteria Loco. But in your parameters you are adding Loco first and dDepot last. That would be interpreted like:

update .... set aDate = TextBox1.Text, ... where [Loco] = TextBox26.Text

Try changing your code to:

    dim sql as String = <sql>UPDATE [BRLocoDatabase2] 
         SET [aDate] = @aDate, [aDepot] = @aDepot, 
             [bDate] = @bDate, [bDepot] = @bDepot,
             [cDate] = @cDate, [cDepot] = @cDepot, 
             [dDate] = @dDate, [dDepot] = @dDepot 
         WHERE [LOCO] = @Loco</sql>
    using cn as OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & databaseName & "; Persist Security Info=False;")
    using cmd As OleDbCommand = New OleDbCommand(sql, cn)

    cmd.Parameters.AddWithValue("@aDate", TextBox19.Text)
    cmd.Parameters.AddWithValue("@aDepot", TextBox20.Text)
    cmd.Parameters.AddWithValue("@bDate", TextBox21.Text)
    cmd.Parameters.AddWithValue("@bDepot", TextBox22.Text)
    cmd.Parameters.AddWithValue("@cDate", TextBox23.Text)
    cmd.Parameters.AddWithValue("@cDepot", TextBox24.Text)
    cmd.Parameters.AddWithValue("@dDate", TextBox25.Text)
    cmd.Parameters.AddWithValue("@dDepot", TextBox26.Text)
    cmd.Parameters.AddWithValue("@Loco", TextBox1.Text)

    cn.Open()
    cmd.ExecuteNonQuery()
    MessageBox.Show("Update was a Success!")
    cn.Close()
    end using
end using

EDIT: BTW, instead of AddWithValue, try using Add, specify the corret types and convert those textBoxN.Text to corresponding types (ie: DateTime.TryParse for xDate values).

CodePudding user response：

Ok, first, you probably really, but really want to put your connection string into some global settings to the project. Not only will this save you having to re-type the connection ALL OVER the place in code, but you can also test the connection, and ensure it is valid.

So, project->"choose my project Properties.

Then settings:

So, now we get to use the connection builder. (click on the [...])

Now:

then

Now, you can browse to the accDB (or mdb).

This:

And do NOT skip hitting advanced:

Then choose ACE (for accDB) or JET (for mdb)

this:

Now, if you are using vs2022, since it is now the FIRST version of VS to be x64 bits, then the test connection will NOT work (unless you using x64 Access/office).

But, at least now we have a connection string. If you using a pre-vs2022 version then test connection WILL work. As a additional step, you should set/force the project to x86.

Eg this:

DO NOT use "any cpu". You need/want/better force the project to x86 (x32 bits) if you using Access/ACE/Office x32.

On the other hand, if you using Access/ACE/Office x64, then of course you set/force the project to x64 bits.

Next up:

While you CAN USE named parameters with ACE and the oleDB provider?

The ORDER must be correct!! (only the position matters)

While I STILL suggest you use named parameters (since in the future, you can flip to SQL server or other oleDB providers, and THEY DO SUPPORT named providers.

So, keep in mind that you can use named parameters, but ONLY the order is what matters.

Another FYI:

Don't use addwith - use STRONG data typing.

So, this code should work:

    Using conn As New OleDbConnection(My.Settings.TEST444)
        Dim strSQL As String =
            "UPDATE [BRLocoDatabase2] SET [aDate] = @aDate, [aDepot] = @aDepot, [bDate] = @bDate,
                    [bDepot] = @bDepot, [cDate] = @cDate, [cDepot] = @cDepot,
                    [dDate] = @dDate, [dDepot] = @dDepot WHERE [LOCO] = @Loco"

        Using cmd As New OleDbCommand(strSQL, conn)

            With cmd.Parameters
                .Add("@aDate", OleDbType.Date).Value = TextBox19.Text
                .Add("@aDepot", OleDbType.VarWChar).Value = TextBox20.Text
                .Add("@bDate", OleDbType.Date).Value = TextBox21.Text
                .Add("@bDepot", OleDbType.VarWChar).Value =  TextBox22.Text
                .Add("@cDate", OleDbType.Date).Value = TextBox23.Text
                .Add("@dDepot", OleDbType.VarWChar).Value = TextBox26.Text
                .Add("@dDate", OleDbType.Date).Value = TextBox25.Text
                .Add("@cDepot", OleDbType.VarWChar).Value = TextBox24.Text
                .Add("@Loco", OleDbType.Integer).Value = TextBox1.Text
            End With
            conn.Open()
            cmd.ExecuteNonQuery()
            MessageBox.Show("Update was a Success!")
        End Using
    End Using