Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 2012 > Visual Basic 2012 General Discussion
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Visual Basic 2012 General Discussion For any discussions about Visual Basic 2012topics which aren't related to a specific Wrox book
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Basic 2012 General Discussion section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 3rd, 2013, 10:01 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2013
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Post Export SQL database to Excel in VB 2012/VS 2012

I am hoping I put this thread in the right place. I'm new to these forums so please correct me if this is in the wrong location and I will move the thread or delete it.

I am trying to find a way to export an entire database to Excel using a query. I've searched Google endlessly trying to find a solution. It seems like all the queries just get the data from a table in the database as opposed to the entire database. I am hoping to find a way to export the entire database instead of getting data from one table in the database at a time, like most of the queries I found in my search seem to do.

Thanks for your time and help,
Kat

P.S. Here is what I have so far:

Code:
server = Me.cboSQL.Text

        If chkTrust.Checked Then
            strConnect = "Data Source=" & server & ";Trusted_Connection=True;Initial Catalog=" & cboCatalog.Text & ""
        Else
            strConnect = "Data Source=" & server & ";User ID=" & userText.Text & ";Password=" & userPass.Text & ";Initial Catalog=" & cboCatalog.Text & ""
        End If
        Using exportConn As New SqlConnection(strConnect)
            Dim xlApp As Microsoft.Office.Interop.Excel.Application
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value


            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("Sheet1")

            Dim sqlExport As String = ""  <---query is supposed to go here
            'Dim sqlExport2 As String = "Select * from Invoice_DT"

            Dim dscmd As New SqlDataAdapter(sqlExport, exportConn)
            'Dim dscmd2 As New SqlDataAdapter(sqlExport2, exportConn)
            Dim ds As New DataSet
            'Dim dt As New DataTable


            dscmd.Fill(ds)
            xlWorkSheet.Cells(1, 1) = ""  <---column headers go here
            xlWorkSheet.Cells(1, 2) = ""   <---and here
            For i = 0 To ds.Tables(0).Rows.Count - 1
                For j = 0 To ds.Tables(0).Columns.Count - 1
                    xlWorkSheet.Cells(i + 3, j + 1) = _
                        ds.Tables(0).Rows(i).Item(j)
                Next
            Next
            
            xlWorkSheet.SaveAs("C:\TestExcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            MsgBox("You can find the file C:\TestExcel.xlsx")
        End Using
ExportDBError:
        If Err.Number <> 0 Then
            MsgBox(Err.Source & "-->" & Err.Description, , "Error")
        End If

Last edited by KLDeWitt87; December 3rd, 2013 at 10:05 AM. Reason: added code
Reply With Quote
  #2 (permalink)  
Old December 3rd, 2013, 11:21 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2013
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Ignore this thread

Never mind, I just realized it will have to be done table by table.

Sorry for the unnecessary question.

Hope everyone has a great day! :)
Reply With Quote
  #3 (permalink)  
Old December 4th, 2013, 04:00 PM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2013
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Delete

Can an administrator delete this please?

Thank you!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
vb 2012 access napatel2007 Visual Basic 2012 General Discussion 0 May 30th, 2013 10:10 PM
Force SQL Server 2012 to Use Visual Studio 2012 Titera BOOK: Professional Microsoft SQL Server 2012 Administration 1 May 6th, 2013 02:22 PM
Completing Lessons in SQL 2012 SSDT ITGuy268 BOOK: Knight's Microsoft Business Intelligence 24-Hour Trainer 0 May 2nd, 2013 11:48 AM
Welcome to Beginning SQL Server 2012 Programming! patkinson BOOK: Beginning Microsoft SQL Server 2012 Programming 2 November 27th, 2012 05:37 PM



All times are GMT -4. The time now is 12:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.