none
SqlBulkCopy instance in VB 2005 Express : Cannot access destination table 'dbo.FromExcel' in SQL Server Express RRS feed

  • Question

  • Hi all,

     

    I have VB 2005 Express and SQL Server Express in my Microsoft XP Pro PC.  I got the following errors: (1) Cannot access destination table 'dbo.FromExcel' in SQL Server Express and (2) SqlException was unhandled - Invalid object name 'dbo.FromExcel' was pointing to the code statement  "rdr1 = cmd1.ExecuteReader ()" in the Button3_CLICK procure, when I executed the following program in VB 2005 Express:

     

    ////---SqlBulkCopy-Excel.cvs.file-to-SQL Server Express---////

    Imports System.Data.SqlClient

    Imports System.Data

    Public Class Form9

    Dim cnn1 As New SqlConnection

    Private Sub Form5_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    'Compute top-level project folder and use it as a prefix for

    'the primary data file

    Dim int1 As Integer = InStr(My.Application.Info.DirectoryPath, "bin\")

    Dim strPath As String = Microsoft.VisualBasic.Left(My.Application.Info.DirectoryPath, int1 - 1)

    Dim pdbfph As String = strPath & "northwnd.mdf"

    Dim cst As String = "Data Source=.\sqlexpress;" & _

    "Integrated Security=SSPI;" & _

    "AttachDBFileName=" & pdbfph

    cnn1.ConnectionString = cst

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    'Create a command to create a table

    Dim cmd1 As New SqlCommand

    cmd1.CommandText = "CREATE TABLE FromExcel (" & _

    "FirstName nvarchar(15), " & _

    "LastName nvarchar(20), " & _

    "PersonID int Not Null)"

    cmd1.Connection = cnn1

    'Invoke the command

    Try

    cnn1.Open()

    cmd1.ExecuteNonQuery()

    MessageBox.Show("Command succeeded.", "Outcome", _

    MessageBoxButtons.OK, MessageBoxIcon.Information)

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    Finally

    cnn1.Close()

    End Try

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    'Create a command to drop a table

    Dim cmd1 As New SqlCommand

    cmd1.CommandText = "DROP TABLE FromExcel"

    cmd1.Connection = cnn1

    'Invoke the command

    Try

    cnn1.Open()

    cmd1.ExecuteNonQuery()

    MessageBox.Show("Command succeeded.", "Outcome", _

    MessageBoxButtons.OK, MessageBoxIcon.Information)

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    Finally

    cnn1.Close()

    End Try

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

     

    'Declare FromExcel Data Table and RowForExcel DataRow

    Dim FromExcel As New DataTable

    Dim RowForExcel As DataRow

    FromExcel.Columns.Add("FirstName", GetType(SqlTypes.SqlString))

    FromExcel.Columns.Add("LastName", GetType(SqlTypes.SqlString))

    FromExcel.Columns.Add("PersonID", GetType(SqlTypes.SqlInt32))

    'Create TextFieldParser for CSV file from spreadsheet

    Dim crd1 As Microsoft.VisualBasic.FileIO.TextFieldParser

    Dim strPath As String = _

    Microsoft.VisualBasic.Left( _

    My.Application.Info.DirectoryPath, _

    InStr(My.Application.Info.DirectoryPath, "bin\") - 1)

    crd1 = My.Computer.FileSystem.OpenTextFieldParser _

    (My.Computer.FileSystem.CombinePath(strPath, "Book1.csv"))

    crd1.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited

    crd1.Delimiters = New String() {","}

    'Loop through rows of CSV file and populate

    'RowForExcel DataRow for adding to FromExcel

    'Rows collection

    Dim currentRow As String()

    Do Until crd1.EndOfData

    Try

    currentRow = crd1.ReadFields()

    Dim currentField As String

    Dim int1 As Integer = 1

    RowForExcel = FromExcel.NewRow

    For Each currentField In currentRow

    Select Case int1

    Case 1

    RowForExcel("FirstName") = currentField

    Case 2

    RowForExcel("LastName") = currentField

    Case 3

    RowForExcel("PersonID") = CInt(currentField)

    End Select

    int1 += 1

    Next

    int1 = 1

    FromExcel.Rows.Add(RowForExcel)

    RowForExcel = FromExcel.NewRow

    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException

    MsgBox("Line " & ex.Message & _

    "is not valid and will be skipped.")

    End Try

    Loop

    'Invoke the WriteToServer method fo the sqc1 SqlBulkCopy

    'object to populate FromExcel table in the database with

    'the FromExcel DataTable in the project

    Try

    cnn1.Open()

    Using sqc1 As SqlBulkCopy = New SqlBulkCopy(cnn1)

    sqc1.DestinationTableName = "dbo.FromExcel"

    sqc1.WriteToServer(FromExcel)

    End Using

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    Finally

    cnn1.Close()

    End Try

    'Read the FromExcel table and display results in

    'a message box

    Dim strQuery As String = "SELECT * " & _

    "FROM dbo.FromExcel "

    Dim str1 As String = ""

    Dim cmd1 As New SqlCommand(strQuery, cnn1)

    cnn1.Open()

    Dim rdr1 As SqlDataReader

    rdr1 = cmd1.ExecuteReader()

    Try

    While rdr1.Read()

    str1 += rdr1.GetString(0) & ", " & _

    rdr1.GetString(1) & ", " & _

    rdr1.GetSqlInt32(2).ToString & ControlChars.CrLf

    End While

    Finally

    rdr1.Close()

    cnn1.Close()

    End Try

    MessageBox.Show(str1, "FromExcel")

    End Sub

    End Class

    ***********************************************************************

    Please help and tell me (1) where and what code statements are wrong or incomplete in this program, (2) whether I need to create 'dbo.FromExcel' in SQL Server Express first, before I execute this program, (3) whether I need to do  "ColumnMappings" in this program, and (4) whether SqlBulkCopy in ADO.NET 2.0-SQL Server Express-VB 2005 Express is working perfectly (i.e. no bugs at all !!??).

     

    Thanks in advance,

    Scott Chang 

    Monday, August 6, 2007 3:46 PM