Select Delete commands

StewartBW 1,145 Reputation points
2024-05-02T14:23:13.35+00:00

Hello,

I've been given this samples for insert into and update:

Using command As New OleDbCommand("INSERT INTO Table (Column1) VALUES (?)", connection)
   command.Parameters.AddWithValue("@param1", field1)
   command.ExecuteNonQuery()
   command.Parameters.Clear()
End Using
Using command As New OleDbCommand("UPDATE [Table] SET Column1 = ? WHERE ConditionColumn = ?", connection)
     command.Parameters.AddWithValue("@param1", newValue)
     command.Parameters.AddWithValue("@param2", conditionValue)
     command.ExecuteNonQuery()
   command.Parameters.Clear()
End Using

Can I use parameterized queries for SELECT and DELETE commands as well?

If so, anyone can give a sample like above?

Thanks.

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,008 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,735 questions
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 46,456 Reputation points Microsoft Vendor
    2024-05-02T14:50:38.0666667+00:00

    Hi @StewartBW , Welcome to Microsoft Q&A,

    Yes, you can use parameterized queries with SELECT and DELETE commands. Here is the sample code:

    Example of SELECT command using parameterized query

    
    Using command As New OleDbCommand("SELECT * FROM [Table] WHERE Column1 = ?", connection)
    
         command.Parameters.AddWithValue("@param1", searchValue)
    
         Using reader As OleDbDataReader = command.ExecuteReader()
    
             While reader.Read()
    
                 ' Process query results
    
             End While
    
         End Using
    
    End Using
    
    

    Example of DELETE command using parameterized query

    
    Using command As New OleDbCommand("DELETE FROM [Table] WHERE ConditionColumn = ?", connection)
    
         command.Parameters.AddWithValue("@param1", conditionValue)
    
         command.ExecuteNonQuery()
    
    End Using
    
    

    The searchValue and conditionValue in these examples are the values you need to query or delete.

    Best Regards,

    Jiale


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Karen Payne MVP 35,436 Reputation points
    2024-05-02T15:39:26.16+00:00

    As per my reply in your other question this uses Dapper.

    using Dapper;
    using System.Data;
    using System.Data.OleDb;
    
    internal class Table1Operations
    {
        private IDbConnection _cn = new OleDbConnection(
            @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb");
        public void Add(Table1 sender)
        {
            const string statement =
                """
                INSERT INTO Table1
                (
                    Column1
                )
                VALUES
                (
                    @Column1
                )
                """;
            _cn.Execute(statement, new { sender.Column1 });
        }
    
        public void Delete(Table1 sender)
        {
            const string statement =
                """
                DELETE FROM Table1 
                WHERE Id = @Id
                """;
            _cn.Execute(statement, new { Id = sender.Id });
    
        }
    
        public List<Table1> ReadAllRecords()
        {
            return _cn.Query<Table1>(
                    """
                    SELECT Id, Column1 
                    FROM Table1
                    """)
                .AsList();
        }
    }
    
    public class Table1
    {
        public int Id { get; set; }
        public string Column1 { get; set; }
    }
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.