Recipe 13.4. Using SQL Parameters


Problem

You need to insert a lot of records into a table, and you would like to simply replace the new data values in the SQL statement without having to build the SQL statement from scratch each time.

Solution

Attach one or more Parameter objects to the Command object.

Discussion

The following SQL Server example inserts new records into Table1, setting the Column2 column to a distinct value for each inserted record:

 ' ----- Connect to the database. Dim connectionString As String = _    "Data Source=MySystem\SQLEXPRESS;" & _    "Initial Catalog=MyDatabase;Integrated Security=true" Dim theDatabase As New SqlClient.SqlConnection(connectionString) theDatabase.Open() ' ----- Build the generic command text. Dim theCommand As New SqlClient.SqlCommand() theCommand.CommandText = _    "INSERT INTO Table1 (Column1) VALUES (@NewValue)" theCommand.Connection = theDatabase ' ----- Add the first record. theCommand.Parameters.AddWithValue("NewValue", "Blue") theCommand.ExecuteNonQuery() ' ----- Add the second record. theCommand.Parameters("NewValue").Value = "Red" theCommand.ExecuteNonQuery() ' ----- Finished. theCommand = Nothing theDatabase.Close() theDatabase.Dispose() 

Parameters are often used to interact with stored procedures, but you can use them in other, more basic SQL statements to substitute for placeholders included in the SQL statement text. In SQL Server, placeholders begin with an at sign (@) followed by a parameter identifier. These identifiers match the names used when creating SqlParameter objects. These parameters are attached to the SqlCommand object containing the generic SQL text.

Once you've created the command object, adding parameters is simple. The easiest method employs the Parameters collection's AddWithValue() method to add the named parameters. This method accepts a parameter name and a value as any System.Object instance:

 theCommand.Parameters.AddWithValue("NewValue", "Blue") 

Once the parameter is in place, you can modify it in later statement reprocessing, accessing it by name:

 theCommand.Parameters("NewValue").Value = "Red" 

The exact syntax used to identify parameter placeholders in the SQL statement text may vary between providers.

See Also

Recipe 13.5 uses parameters to interact with stored procedures.




Visual Basic 2005 Cookbook(c) Solutions for VB 2005 Programmers
Visual Basic 2005 Cookbook: Solutions for VB 2005 Programmers (Cookbooks (OReilly))
ISBN: 0596101775
EAN: 2147483647
Year: 2006
Pages: 400

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net