User Name Password Register
DaniWeb IT Discussion Community
All
What is DaniWeb IT Discussion Community?
You're currently browsing the VB.NET section within the Software Development category of DaniWeb, a massive community of 427,382 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,037 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our VB.NET advertiser: Programming Forums
Views: 538 | Replies: 2
Reply
Join Date: Apr 2008
Posts: 30
Reputation: gabanxx is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
gabanxx gabanxx is offline Offline
Light Poster

how to import data from SQL database to excell?

  #1  
Jun 2nd, 2008
can u spot the error in this code..


 Private Sub fncExcelExport(ByVal strSql As String)

        'fncExcelExport("SELECT * FROM tbl_rop WHERE Name_User ='" & (TextBox1.Text) & "', Date_of_birth= '" & (TextBox2.Text) & "' , Hobbies='" & (TextBox3.Text) & "' , Phone_Number ='" & (TextBox4.Text) & "' where Name_User = '" & (TextBox1.Text) & "'")



        Dim dsExcelExport As New DataSet
        Dim daExcelExport As New SqlDataAdapter
        Dim Excel As New Excel.Application
        Dim intColumn, intRow, intColumnValue As Integer
        Dim strExcelFile As String
        Dim strFileName As String
        Dim conCurrent As New SqlConnection

        conCurrent = New SqlConnection("Server=(local);user id=sa;password=;Initial Catalog=ROP1")
        'conCurrent = New SqlConnection("server=;(local) catalog=ROP1;User ID=sa;Password=")
        daExcelExport = New SqlDataAdapter(strSql, conCurrent)
        varCommand = New SqlCommand(strSql, varConnection)
        daExcelExport = New SqlDataAdapter(varCommand)
        daExcelExport.Fill(dsExcelExport)

        Dim strAppPath = System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf("\") + 1)

        With Excel
            .SheetsInNewWorkbook = 1
            .Workbooks.Add()
            .Worksheets(1).Select()
            'For displaying the column name in the the excel file.            
            For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
                .Cells(1, intColumn + 1).Value = dsExcelExport.Tables(0).Columns(intColumn).ColumnName.ToString
            Next
            'For displaying the column value row-by-row in the the excel file.            
            For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
                For intColumnValue = 0 To dsExcelExport.Tables(0).Columns.Count - 1
                    .Cells(intRow + 1, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
                Next

            Next
            strFileName = InputBox("Please enter the file name.", " ")
            strExcelFile = strAppPath & "tbl_rop"
            .ActiveWorkbook().SaveAs(strExcelFile)
            .ActiveWorkbook.Close()
            .AddChart()


        End With
        MessageBox.Show("File exported sucessfully.", "Exporting done", MessageBoxButtons.OK, MessageBoxIcon.Information)
NormalExit:
        Excel.Quit()
        Excel = Nothing
        GC.Collect()
        Exit Sub
    End Sub
AddThis Social Bookmark Button
Reply With Quote  
Join Date: Dec 2006
Location: United States
Posts: 612
Reputation: binoj_daniel is an unknown quantity at this point 
Rep Power: 3
Solved Threads: 15
binoj_daniel's Avatar
binoj_daniel binoj_daniel is offline Offline
DaniWeb Expert

Re: how to import data from SQL database to excell?

  #2  
Jun 2nd, 2008
On which line are you getting error? Can you paste the error?
Reply With Quote  
Join Date: Apr 2008
Posts: 30
Reputation: gabanxx is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
gabanxx gabanxx is offline Offline
Light Poster

Re: how to import data from SQL database to excell?

  #3  
Jun 2nd, 2008
this the line which the error occur...
daExcelExport.Fill(dsExcelExport)

i think the error come from the connection bridge that i created earlier at form_load....

fncExcelExport("SELECT Name_User ='" & (TextBox1.Text) & "', Date_of_birth= '" & (TextBox2.Text) & "' , Hobbies='" & (TextBox3.Text) & "' , Phone_Number ='" & (TextBox4.Text) & "' where Name_User = '" & (TextBox1.Text) & "'")
Reply With Quote  
Reply

Only community members can participate in forum threads. You must register or log in to contribute.

DaniWeb VB.NET Marketplace
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

Thread Tools Display Modes

Other Threads in the VB.NET Forum

All times are GMT -4. The time now is 4:08 am.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC