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.
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.