'____________________________________________________________
'                        S Q  L   C O M M A N D   O B J E C T
'                             PARAMETERIZED QUERIES

'____________________________________________________________

'Updated 11/4/08

'If you type in a city, SQL will return records for that city.
'Be sure to change the name of your SQL server in the Conn String below.

Option Explicit On
Option Strict On

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Private NorthwindConnection As New SqlConnection("Data Source=.\sqlexpress2005;Initial Catalog=Northwind;Integrated Security=True")

    Private Sub ExecuteSqlButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExecuteSqlButton.Click

        Try
            ' Create a new Command object
            Dim CustomersByCityCommand As New SqlCommand

            ' Set the command properties
            CustomersByCityCommand.Connection = NorthwindConnection
            CustomersByCityCommand.CommandType = CommandType.Text
            CustomersByCityCommand.CommandText = "SELECT CustomerID, CompanyName, City " & _
                                                    "FROM Customers " & _
                                                    "WHERE City = @City"

            ' Create the @City parameter
            Dim CityParameter As New SqlParameter

            ' Set its name and data type
            CityParameter.ParameterName = "@City"
            CityParameter.SqlDbType = SqlDbType.NVarChar

            ' Since the city column in the database allows
            ' null values we can set the IsNullable property
            ' to allow null values.
            CityParameter.IsNullable = True

            ' Add the parameter to the Commmand object
            CustomersByCityCommand.Parameters.Add(CityParameter)

            ' Set the parameters value to the
            ' the text in the CityTextBox
            CityParameter.Value = CityTextBox.Text

            ' Create a StringBuilder to store the results of the query
            Dim results As New System.Text.StringBuilder

            ' You must open the connection before executing the command
            CustomersByCityCommand.Connection.Open()

            ' Assign the results of the SQL statement to a data reader
            Dim reader As SqlDataReader = CustomersByCityCommand.ExecuteReader

            While reader.Read
                For i As Integer = 0 To reader.FieldCount - 1
                    results.Append(reader(i).ToString & C#Tab)
                Next
                results.Append(Environment.NewLine)
            End While

            ' Close the data reader and the connection
            reader.Close()
            CustomersByCityCommand.Connection.Close()

            ResultsTextBox.Text = results.ToString


        Catch ex As Exception

            MessageBox.Show("An error occurred..." & ex.Message)

        End Try



    End Sub
End Class