[RESOLVED] VB.net TableAdapter Stored Procedure to fill SQL Table using SqlBulkCopy

[Fedaykin]

I'm filling a DataTable with a Stored Procedure and attempting to send the results to an SQL Table. I'm getting the error:
A first chance exception of type 'System.InvalidCastException' occurred in Project Manager.exe

I've tried using 2 or 3 different methods to do this, but I really am out of ideas. I'm not sure I'm using the TableAdapter correctly here:

 Dim StartDate As Date = Me.DateTimePicker1.Text
        Dim EndDate As Date = Me.DateTimePicker2.Text
        Dim Detail As String = "detail"

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim TableAdapter As New MANEXONTIMEDataSet2TableAdapters.QkViewCustomerOntimeTableAdapter
        TableAdapter.GetData(dateStart:=StartDate, dateEnd:=EndDate, type:=Detail)

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'copy the contents of the DataTable to the SQL table OnTimeChart 
        Dim sqCon As New SqlClient.SqlConnection("Server=QCG13\SQLSTANDARD;Database=QCGMAIN; Trusted_Connection=True;")
        sqCon.Open()
        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqCon)
            bulkCopy.DestinationTableName = "OnTimeChart"

            Try
                ' Write from the source to the destination.
                bulkCopy.WriteToServer(TableAdapter)

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using
        sqCon.Close()

[techgnome]

If you're getting an invalid cast exception it means you're passing pizza when it's expecting an apple. unless you can point out the line, that's about as close to an explanation as any one is going to likely get.


-tg

[Fedaykin]

The TableAdapter fills sucessfully and I can see the data in rows during debug.

The line that fails is:
bulkcopy.WriteToServer(TableAdapter)

I've updated my code to make a DataSet, and tried to pass that but it also fails.

[techgnome]

Clearly you've not read the documentation... nor are you paying attention to the hints given in the intellisense in the editor...
Documentation: http://msdn.microsoft.com/en-us/libr...v=vs.110).aspx

None of the overload WriteToServer methods take an adaptor, nor do any of them take a dataset... so why are trying to pass those in? You jsut traded the pizza for ice cream... it's still looking for an apple, orange, lemon, or a watermelon...

so... read the documentation, see what your options are, then use one of them to perform the action.


-tg

[Fedaykin]

Clearly, I am a new learner of VB.net and I am not understanding what I'm reading.

My first attempt to write this code involved a DataTable as the documentation clearly states:

WriteToServer(DataTable) Copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

My attempt failed. So I tried again using an adapter, which clearly failed as well.

Then I came to this forum for some insight where I've been presented with a link to documentation that anyone could look up with google.

Clearly, we all know to read the documentation first. If the documentation could resolve all issues, clearly we would not need a forum.

Clearly.

[Fedaykin]

Here is my attempt at using a DataTable. My Adapter must be used to fill the DataTable beforehand as it is from a Stored Procedure.

This fails at the bulkCopy.WriteToServer(dt) line with 'System.InvalidOperationException', which baffles me because the documentation states WriteToServer(DataTable) should work.

        Dim dt As New DataTable
        Dim StartDate As Date = Me.DateTimePicker1.Text
        Dim EndDate As Date = Me.DateTimePicker2.Text
        Dim Detail As String = "detail"

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim sqCon As New SqlClient.SqlConnection("Server=QCG13\SQLSTANDARD;Database=QCGMAIN; Trusted_Connection=True;")
        Dim sqCmd As New SqlClient.SqlCommand
        sqCon.Open()

        Dim TableAdapter As New MANEXONTIMEDataSet2TableAdapters.QkViewCustomerOntimeTableAdapter
        TableAdapter.GetData(dateStart:=StartDate, dateEnd:=EndDate, type:=Detail)
        TableAdapter.Adapter.Fill(dt)

        Using bulkCopy As SqlBulkCopy = _
            New SqlBulkCopy(sqCon)
            bulkCopy.DestinationTableName = "OnTimeChart"

            Try
                ' Write from the source to the destination.
                bulkCopy.WriteToServer(dt)

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using
        sqCon.Close()

[Fedaykin]

Found it! This code is successful, finally. The Stored Procedure was sneaking in two columns I didn't notice, so my destination table did not have the correct column mapping. Once I corrected the mapping the bulkcopy fills the SQL Table.

Dim dt As New DataTable
        Dim StartDate As Date = Me.DateTimePicker1.Text
        Dim EndDate As Date = Me.DateTimePicker2.Text
        Dim Detail As String = "detail"

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim sqCon As New SqlClient.SqlConnection("Server=QCG13\SQLSTANDARD;Database=QCGMAIN; Trusted_Connection=True;")
        Dim sqCmd As New SqlClient.SqlCommand
        sqCon.Open()

        Dim TableAdapter As New MANEXONTIMEDataSet2TableAdapters.QkViewCustomerOntimeTableAdapter
        TableAdapter.GetData(dateStart:=StartDate, dateEnd:=EndDate, type:=Detail)
        TableAdapter.Adapter.Fill(dt)

        Using bulkCopy As SqlBulkCopy = _
            New SqlBulkCopy(sqCon)
            bulkCopy.DestinationTableName = "OnTimeChart"

            Try
                ' Write from the source to the destination.
                bulkCopy.WriteToServer(dt)

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using
        sqCon.Close()

[techgnome]

Here is my attempt at using a DataTable. My Adapter must be used to fill the DataTable beforehand as it is from a Stored Procedure.

This fails at the bulkCopy.WriteToServer(dt) line with 'System.InvalidOperationException', which baffles me because the documentation states WriteToServer(DataTable) should work.

you never mentioned you had used a datatable... just the adaptor (as evidenced by the code) and a dataset... never once did you mention that had tried the datatable, or that you had read the documentation.l.. otherwise the tone of my reply would have been different. Your post is just one of many I deal with day in and day out (although I have to wonder sometimes why) where we're dealing with incomplete information... The problem you were asking about was a cast exception... so the answer is, pass in the datatable it's asking for... not an adaptor, not a dataset. Now... had you said that you had used a datatable, and that it failed I would have told you to remove the error handling, and try again, and see what the details of the exception it (not just the ex.message in the output window) because the root of the problem would have been in there (it should have been along the lines of the source table columns not matching the destination)...


-tg

[Fedaykin]

My original post states I'm using a DataTable, I did not know I was using it in an improper manner, now I do. My knowledge of VB is limited as I have been coding for less than a year. Errors and omissions are likely in any novice's posts.

I went out of my way to make a well formed question, I provided as much detail as I could including the error message and I posted my attempt at the code, which is just that. An attempt.

I didn't even know about the error handling providing such detail in debug, I just accidentally stumbled upon it and it showed me exactly what the problem was. I've learned something new.

Slow down and be patient in your replies, especially when you see the poster has made an honest attempt.