Back to the top

Requirements

• Microsoft Windows XP, Microsoft Windows Server 2003, Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
• Microsoft Visual Studio .NET

• Database terminology
• Structured Query Language (SQL)

Back to the top

How to run a command

1. Follow these steps to create a new console application in Visual Basic .NET:
   1. Start Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click Console Application under Templates.
2. Make sure that your project contains a reference to the System.Data namespace, and add a reference if it does not.
3. Use the Imports statement on the System and System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. You can also include System.Data.SqlClient or System.Data.OleDb, depending on which one you are using.

   Imports System
   Imports System.Data
   Imports System.Data.SqlClient
   					

4. Before you can create a connection to a database, you must have a connection string. Connection strings contain all of the information that you need to establish a database connection, including the server name, the database name, the user ID, and the password. For example, the following connection string points to a local computer that is running SQL Server with a strong password for the <username> account:
   
   For OleDb connections:

   Provider=SQLOLEDB.1;User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=(local)
   						

   For SqlClient connections:

   User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=(local)
   						

   NOTE: If you need more assistance determining the connection string for your database, search for "ConnectionString" in the Microsoft Developer Network (MSDN) Library at the following Microsoft Web site:
   http://search.microsoft.com/us/dev/default.asp (http://search.microsoft.com/us/dev/default.asp)
5. Visual Studio creates a module and an empty Main() procedure. Declare a string variable, and store the appropriate connection string for your database in this procedure:

   Sub Main()
       Dim sConnectionString As String _
           = "User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=(local)"
   End Sub
   					

6. Using this connection string, create a new OleDbConnection or SqlConnection object, and call its Open method to establish a connection to your database:

   Dim objConn As New SqlConnection(sConnectionString)
      objConn.Open()
   					

7. Create a SqlCommand or OleDbCommand object, and pass in the command that you want to run and the connection object that you created in the previous step. The following sample code passes in the INSERT statement:

   Dim sSQL As String = "INSERT INTO Employee " & _
     "(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" & _
     "VALUES ('MSD12923F', 'Duncan', 'W', 'Mackenzie', " & _ 
              "10, 82,'0877','2001-01-01')"
   Dim objCmd As New SqlCommand(sSQL, objConn)
   					

8. After you create the SqlCommand or the OleDbCommand object, you can call the ExecuteNonQuery method to run the command that it represents. ExecuteNonQuery is designed for commands that do not return any results (such as the DELETE, the UPDATE, and the INSERT statements). If the Execute statement runs without throwing an exception (see the following code), the command has been executed successfully against the database.

   objCmd.ExecuteNonQuery()
   					

9. Save your project. On the Debug menu, click Start to run your command against the database.

Back to the top

How to use parameters

Dim sSQL As String = "DELETE FROM Employee WHERE emp_id = @emp_id"
				

1. Create your OleDbConnection or SqlConnection object, as you did in the "How to Run a Command" section.
2. Replace the values with placeholders (for example, "@emp_id" or "@fname") so that your command text uses parameters. See the DELETE statement before these steps for an example.
3. Create your OleDbCommand or SqlCommand object, and pass in the connection object that you created in the first step and the command text that contains the parameter placeholders.
4. For each parameter, add a parameter object to the command object's parameters collection. For each parameter, you must specify a name and data type.

   objCmd.Parameters.Add("@emp_id", SqlDbType.Char, 9)
   					

5. Stored procedures can have parameters that return values and output parameters. You must also set a value for each input parameter before you can run the query:

   objCmd.Parameters.Item("@emp_id").Value = "MSD12923F"
   					

6. Run the query as follows:

   Try
       objCmd.ExecuteNonQuery()
       Console.WriteLine("Record Deleted")
   Catch e As Exception
       Console.WriteLine(e.ToString)
   End Try
   Console.WriteLine("Record Deleted")
   Console.Read()
   					

Back to the top

Complete code listing

Imports System
Imports System.Data
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        AddRecord()
        RemoveRecord()
        Pause()
    End Sub

    Sub Pause()
        Console.WriteLine("Press Enter To Continue...")
        Console.ReadLine()
    End Sub

    Sub AddRecord()
        Dim sConnectionString As String _
            = "User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=(local)"
        Dim objConn As New SqlConnection(sConnectionString)
        objConn.Open()
        Dim sSQL As String = "INSERT INTO Employee " & _
          "(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" & _
          "VALUES ('MSD12923F', 'Duncan', 'W', 'Mackenzie', " & _
                   "10, 82,'0877','2001-01-01')"

        Dim objCmd As New SqlCommand(sSQL, objConn)
        Try
            objCmd.ExecuteNonQuery()
        Catch e As Exception
            Console.WriteLine(e.Message)
        End Try
        Console.WriteLine("Record Added")
    End Sub

    Sub RemoveRecord()
        Dim sConnectionString As String _
            = "User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=(local)"
        Dim objConn As New SqlConnection(sConnectionString)
        objConn.Open()
        Dim sSQL As String = "DELETE FROM Employee WHERE emp_id = @emp_id"
        Dim objCmd As New SqlCommand(sSQL, objConn)

        objCmd.Parameters.Add("@emp_id", SqlDbType.Char, 9)
        objCmd.Parameters.Item("@emp_id").Value = "MSD12923F"

        Try
            objCmd.ExecuteNonQuery()
            Console.WriteLine("Record Deleted")
        Catch e As Exception
            Console.WriteLine(e.ToString)
        End Try
        Console.WriteLine("Record Deleted")
        Console.ReadLine()
    End Sub

End Module
				

Back to the top