Question Looking for extended code for using parameters with dataAdapter

David_Ford

Member
Joined
May 11, 2016
Messages
10
Programming Experience
10+
Wanting to set up parameters for my DataAdapter, but I'm running into the problem of terse examples.

All I can find is code snippets for how to set up the parameters, but I'd like a fuller example of setting up the parameters, populating the parameters, making the call, and retrieving the results.

Can anyone point me in the right direction?

Thanks.
 
The following example will retrieve all records from a table where the Date column contains a value later than today's date. It binds the results to a DataGridView via a BindingSource. After editing, it will save the changes, which may include new, modified and deleted records, back to the database.
Imports System.Data.SqlClient

Public Class Form1

    Private adapter As SqlDataAdapter
    Private table As DataTable

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.ConfigureDataAdapter()
        Me.LoadData()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Me.SaveData()
    End Sub

    Private Sub ConfigureDataAdapter()
        'Create the connection object explicitly so that it can be associated with all four commands.
        Dim connection As New SqlConnection("connection string here")

        'Creating the data adapter this way will create the SelectCommand and associate it with the connection.
        '>= tomorrow's date is used to allow for a time portion as well, thus any time today will be excluded.
        Me.adapter = New SqlDataAdapter("SELECT ID, Name, Number, [Date] FROM MyTable WHERE [Date] >= @Date",
                                        connection)

        'Add the parameter for the Date column to the SelectCommand.
        'Use AddWithValue because we have a value, the command will be executed once
        ' without changing the value And the data type can be inferred from the value.
        'The parameter name must match what's in the SQL code.
        'In this case, tomorrow's date is used as the value.
        Me.adapter.SelectCommand.Parameters.AddWithValue("@Date", Date.Today.AddDays(1))

        'Create the action commands.
        Dim insertCommand As New SqlCommand("INSERT INTO MyTable (Name, Number, [Date]) VALUES (@Name, @Number, @Date)",
                                            connection)
        Dim updateCommand As New SqlCommand("UPDATE MyTable SET Name = @Name, Number = @Number, [Date] = @Date WHERE ID = @ID",
                                            connection)
        Dim deleteCommand As New SqlCommand("DELETE MyTable WHERE ID = @ID", connection)

        'Add parameters to action commands.
        'Use Add because parameters will be reused and values drawn from the DataTable.

        With insertCommand.Parameters
            'Parameter sizes are only relevant for data types that have a variable size so just use zero otherwise.
            'The last argument is the name of the DataColumn the data is coming from.
            .Add("@Name", SqlDbType.VarChar, 50, "Name")
            .Add("@Number", SqlDbType.Int, 0, "Number")
            .Add("@Date", SqlDbType.DateTime2, 0, "Date")
        End With

        With updateCommand.Parameters
            .Add("@Name", SqlDbType.VarChar, 50, "Name")
            .Add("@Number", SqlDbType.Int, 0, "Number")
            .Add("@Date", SqlDbType.DateTime2, 0, "Date")
            .Add("@ID", SqlDbType.Int, 0, "ID")
        End With

        With deleteCommand.Parameters
            .Add("@ID", SqlDbType.Int, 0, "ID")
        End With

        With Me.adapter
            .InsertCommand = insertCommand
            .UpdateCommand = updateCommand
            .DeleteCommand = deleteCommand

            .MissingSchemaAction = MissingSchemaAction.AddWithKey
        End With
    End Sub

    Private Sub LoadData()
        Me.table = New DataTable
        Me.adapter.Fill(Me.table)

        Me.BindingSource1.DataSource = Me.table
        Me.DataGridView1.DataSource = Me.BindingSource1
    End Sub

    Private Sub SaveData()
        'Commit any pending edit.
        Me.BindingSource1.EndEdit()

        'Save all changes.
        Me.adapter.Update(Me.table)
    End Sub

End Class
 
I guess I was interested primarily in the SelectCommand use. How do I set the parameter value, other than with the AddWithValue command? Since I will be setting the parameter repeatedly.

I will be using the InsertCommand for another portion of my code, but right now I"m just reading lines, parsing, and trying to do lookups on the parsed values.
 
I guess I was interested primarily in the SelectCommand use. How do I set the parameter value, other than with the AddWithValue command? Since I will be setting the parameter repeatedly.

You probably ought to have said that in the first place.
Dim parameter = myCommand.Parameters.Add("@SomeColumn", SqlDbType.VarChar)

parameter.Value = someValue
 
Back
Top