6 Replies - 7535 Views - Last Post: 01 November 2017 - 08:04 AM

#1 andrewsw   User is offline

  • head thrashing
  • member icon

Reputation: 6630
  • View blog
  • Posts: 27,105
  • Joined: 12-December 12

Using Parameters with MS Access/OleDb

Posted 02 April 2015 - 09:19 AM

We should not concatenate values to a string to create a SQL statement, we should use parameters. Parameterized queries are secure and avoid issues over the formatting of values (date formatting, etc.).

OleDbCommand.Parameters :MSDN

MS said:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

People will often use named parameters (@SomeName) which are more descriptive than just question marks. Our queries will still work but it is the order that the parameter values are supplied that is significant, not their names or the spelling of their names, as my snippets will demonstrate.
Imports System.Data.OleDb

Module Module1

    Sub Main()
        Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
        sConn &= "Data Source=C:\Users\Andrew\Documents\Staff Database.accdb;Persist Security Info=False;"

        Using conn As New OleDbConnection(sConn)
            conn.Open()
            Dim sUpdate As String = "UPDATE tblStaff SET Salary = Salary + 100 WHERE OfficeID = ? AND Department = ?"
            Using cmd As New OleDbCommand(sUpdate, conn)
                cmd.Parameters.AddWithValue("@AnyName", "LO01") 'it is the order that matters!
                cmd.Parameters.AddWithValue("@AnotherName", "ADM")
                Dim iResult = cmd.ExecuteNonQuery()
                Console.WriteLine("{0} records updated.", iResult)
            End Using
        End Using

        Console.ReadKey()
    End Sub

End Module


This second version uses named parameters in the SQL statement:
Imports System.Data.OleDb

Module Module1

    Sub Main()
        Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
        sConn &= "Data Source=C:\Users\Andrew\Documents\Staff Database.accdb;Persist Security Info=False;"

        Using conn As New OleDbConnection(sConn)
            conn.Open()
            Dim sUpdate As String = "UPDATE tblStaff SET Salary = Salary + 100 WHERE OfficeID = @Office AND Department = @Admin"
            Using cmd As New OleDbCommand(sUpdate, conn)
                cmd.Parameters.AddWithValue("@Office", "LO01") 'it is the order that matters!
                cmd.Parameters.AddWithValue("@Admin", "ADM")
                Dim iResult = cmd.ExecuteNonQuery()
                Console.WriteLine("{0} records updated.", iResult)
            End Using
        End Using

        Console.ReadKey()
    End Sub

End Module


To prove that the names themselves are not significant run the code again (with your own database of course) having changed to:
            cmd.Parameters.AddWithValue("@Foo", "LO01") 'it is the order that matters!
            cmd.Parameters.AddWithValue("@Bar", "ADM")


The code still works. It is the order that is important, not the names themselves.

Is This A Good Question/Topic? 2
  • +

Replies To: Using Parameters with MS Access/OleDb

#2 andrewsw   User is offline

  • head thrashing
  • member icon

Reputation: 6630
  • View blog
  • Posts: 27,105
  • Joined: 12-December 12

Re: Using Parameters with MS Access/OleDb

Posted 19 April 2016 - 08:40 AM

My snippet uses AddWithValue (I'm not alone in doing this), but see this article about how the data type can be incorrectly inferred:

Can we stop using AddWithValue() already?

The solution is to be explicit about the data type,

cmd.Parameters.Add("@Parameter", SqlDbType.DateTime).Value = MyDateTimeVariable

Was This Post Helpful? 2
  • +
  • -

#3 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1515
  • View blog
  • Posts: 3,832
  • Joined: 01-February 13

Re: Using Parameters with MS Access/OleDb

Posted 19 April 2016 - 10:38 AM

I`m glad that i`m not one of those AddWithValue people. :neat:

Actually i have recently started learning a little about using databases but, have only used the solution you have shown to specify the data type specifically. I will stay away from the AddWithValue if i run across it in my future learning. 8)
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • head thrashing
  • member icon

Reputation: 6630
  • View blog
  • Posts: 27,105
  • Joined: 12-December 12

Re: Using Parameters with MS Access/OleDb

Posted 19 April 2016 - 10:40 AM

I'll probably still use it ;) I'm obstinate.

Quote

However, this can happen even when the .Net type is known. VarChar vs NVarChar or Char from strings is one way. Date vs DateTime is another.

The good news is that most of the time, these type mismatches don’t matter.

The advantage in using AddWithValue is that I don't want to be concerned with the specific data type. But, from a professional standpoint, the advice is probably good to avoid it.
Was This Post Helpful? 0
  • +
  • -

#5 alobi   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 186
  • Joined: 21-January 13

Re: Using Parameters with MS Access/OleDb

Posted 13 August 2016 - 10:02 AM

Thank you guys, I appreciate all the expertize. I will try and see how I can implement this in my code and I will get back you.
Was This Post Helpful? 0
  • +
  • -

#6 AmrAly   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 01-November 17

Re: Using Parameters with MS Access/OleDb

Posted 01 November 2017 - 08:01 AM

View Postandrewsw, on 19 April 2016 - 08:40 AM, said:

My snippet uses AddWithValue (I'm not alone in doing this), but see this article about how the data type can be incorrectly inferred:

Can we stop using AddWithValue() already?

The solution is to be explicit about the data type,

cmd.Parameters.Add("@Parameter", SqlDbType.DateTime).Value = MyDateTimeVariable

Hi Andrewsw,
I'm anew member in your forum,Really it's a helpful forum......
I used this code to Save and update into my Access database
Sub SaveInves()
        
        If txtVisNo.Text = GetAutonumber("Inves", "Vis_no") Then
            cmd = New OleDbCommand("INSERT INTO Inves(Patient_no, Vis_no, Name, Inves_name, Inv_Date, Result, Inves1, Inves2, Inves3, Inves4, Inves5," &
                               "Date1, Date2, Date3, Date4, Date5, Result1, Result2, Result3, Result4, Result5)" &
                               "VALUES(@Patient_no, @Vis_no, @Name, @Inves_name, @Inv_Date, @Result, @Inves1, @Inves2, @Inves3, @Inves4, @Inves5," &
                               "@Date1, @Date2, @Date3, @Date4, @Date5, @Result1, @Result2, @Result3, @Result4, @Result5)", conn)

            With cmd.Parameters
                .AddWithValue("@Patient_no", txtVisPatNo.Text).DbType = DbType.Int32
                .AddWithValue("@Vis_no", txtVisNo.Text).DbType = DbType.Int32
                .AddWithValue("@Name", txtVisName.Text).DbType = DbType.String
                .AddWithValue("@Inves_name", cbxInvest.Text).DbType = DbType.String
                .AddWithValue("@Inv_Date", DTPickerInv.Text).DbType = DbType.DateTime
                .AddWithValue("@Result", cbxResult.Text).DbType = DbType.String
                .AddWithValue("@Inves1", cbxInvest1.Text).DbType = DbType.String
                .AddWithValue("@Inves2", cbxInvest2.Text).DbType = DbType.String
                .AddWithValue("@Inves3", cbxInvest3.Text).DbType = DbType.String
                .AddWithValue("@Inves4", cbxInvest4.Text).DbType = DbType.String
                .AddWithValue("@Inves5", cbxInvest5.Text).DbType = DbType.String
                .AddWithValue("@Date1", DTPickerInv1.Text).DbType = DbType.DateTime
                .AddWithValue("@Date2", DTPickerInv2.Text).DbType = DbType.DateTime
                .AddWithValue("@Date3", DTPickerInv3.Text).DbType = DbType.DateTime
                .AddWithValue("@Date4", DTPickerInv4.Text).DbType = DbType.DateTime
                .AddWithValue("@Date5", DTPickerInv5.Text).DbType = DbType.DateTime
                .AddWithValue("@Result1", cbxResult1.Text).DbType = DbType.String
                .AddWithValue("@Result2", cbxResult2.Text).DbType = DbType.String
                .AddWithValue("@Result3", cbxResult3.Text).DbType = DbType.String
                .AddWithValue("@Result4", cbxResult4.Text).DbType = DbType.String
                .AddWithValue("@Result5", cbxResult5.Text).DbType = DbType.String
            End With

            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If

            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        End If

And i found that i can use it as follow
 With cmd.Parameters
                .Add("@Patient_no", OledbType.Integer).Value = CInt(Val(txtVisPatNo.Text))
                .Add("@Vis_no", OledbType.Integer).Value = CInt(Val(txtVisNo.Text))
                .Add("@Name", OledbType.VarChar).Value = txtVisName.Text
                .Add("@Inves_name", OledbType.VarChar).Value = cbxInvest.Text
                .Add("@Inv_Date", OledbType.Date).Value = DTPickerInv.Text

Is this true ...?
Thanks in advance ......By the way your tutorials are very useful
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • head thrashing
  • member icon

Reputation: 6630
  • View blog
  • Posts: 27,105
  • Joined: 12-December 12

Re: Using Parameters with MS Access/OleDb

Posted 01 November 2017 - 08:04 AM

Thank you.

That looks okay at a glance. Give it a go.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1