The program creates a table called Contact, then creates a stored procedure that is used to populate the table.
The issue that I am having is that when the stored procedure executes, I get an error. The error message is: "System.Data.SqlClient.SqlException: Invalid object name 'AdventureWorks.Contact'"
I have tried several different approaches to this issue, and haven't been able to figure out how to populate the table through data stored in the AdventureWorks database. I am using SQL Server 2005 Express.
I was able to successfully populate the data using hard-coded values in this manner:
- Code: Select all
cmd.CommandText = _
"CREATE PROCEDURE AddContacts AS" & vbCrLf & _
"INSERT INTO Contact" & vbCrLf & _
"(ContactID) " & _
"VALUES('ExampleIDNo')"
So, I know the problem is with the 'SELECT' statement that is trying to query data in the SQL AdventureWorks database.
Here is the code that creates the stored procedure:
- Code: Select all
Protected connectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim dbConnection As New SqlConnection(connectionString)
Dim strSQL As String = _
"USE HowToDemo" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM HowToDemo.dbo.sysobjects " & _
"WHERE Name = 'AddContacts' " & _
"AND TYPE = 'p')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP PROCEDURE AddContacts" & vbCrLf & _
"END "
' A SqlCommand object is used to execute the SQL commands.
Dim cmd As New SqlCommand(strSQL, dbConnection)
Try
' Open the connection, execute the command, and close the connection.
' It is more efficient to ExecuteNonQuery when data is not being
' returned.
dbConnection.Open()
cmd.ExecuteNonQuery()
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Try
cmd.CommandText = _
"CREATE PROCEDURE AddContacts AS" & vbCrLf & _
"INSERT INTO Contact" & vbCrLf & _
"(ContactID) " & _
"SELECT ContactID FROM AdventureWorks.Contact "
cmd.ExecuteNonQuery()
dbConnection.Close()
' Show the controls for the next step.
lblArrow3.Visible = True
lblStep4.Enabled = True
btnCreateView.Enabled = True
MessageBox.Show("Stored Procedure 'AddContacts' successfully " & _
"created.", "SPROC Creation Status", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
The code that executes the stored procedure is:
- Code: Select all
Dim strSQL As String = "EXECUTE HowToDemo.dbo.AddContacts"
Try
' The SqlConnection class allows you to communicate with SQL Server.
' The constructor accepts a connection string as an argument. This
' connection string uses Integrated Security, which means that you
' must have a login in SQL Server, or be part of the Administrators
' group for this to work.
Dim dbConnection As New SqlConnection(connectionString)
' A SqlCommand object is used to execute the SQL commands.
Dim cmd As New SqlCommand(strSQL, dbConnection)
' Open the connection, execute the command, and close the connection.
' It is more efficient to ExecuteNonQuery when data is not being
' returned.
dbConnection.Open()
cmd.ExecuteNonQuery()
dbConnection.Close()
' Show the controls for the next step.
lblArrow5.Visible = True
lblStep6.Enabled = True
btnDisplay.Enabled = True
MessageBox.Show("Table successfully populated.", _
"Data Addition Status", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
