how to import data from SQL database to excell?

Please support our VB.NET advertiser: Intel Parallel Studio Home
Reply

Join Date: Apr 2008
Posts: 30
Reputation: gabanxx is an unknown quantity at this point 
Solved Threads: 0
gabanxx gabanxx is offline Offline
Light Poster

how to import data from SQL database to excell?

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


  1. Private Sub fncExcelExport(ByVal strSql As String)
  2.  
  3. '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) & "'")
  4.  
  5.  
  6.  
  7. Dim dsExcelExport As New DataSet
  8. Dim daExcelExport As New SqlDataAdapter
  9. Dim Excel As New Excel.Application
  10. Dim intColumn, intRow, intColumnValue As Integer
  11. Dim strExcelFile As String
  12. Dim strFileName As String
  13. Dim conCurrent As New SqlConnection
  14.  
  15. conCurrent = New SqlConnection("Server=(local);user id=sa;password=;Initial Catalog=ROP1")
  16. 'conCurrent = New SqlConnection("server=;(local) catalog=ROP1;User ID=sa;Password=")
  17. daExcelExport = New SqlDataAdapter(strSql, conCurrent)
  18. varCommand = New SqlCommand(strSql, varConnection)
  19. daExcelExport = New SqlDataAdapter(varCommand)
  20. daExcelExport.Fill(dsExcelExport)
  21.  
  22. Dim strAppPath = System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf("\") + 1)
  23.  
  24. With Excel
  25. .SheetsInNewWorkbook = 1
  26. .Workbooks.Add()
  27. .Worksheets(1).Select()
  28. 'For displaying the column name in the the excel file.
  29. For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
  30. .Cells(1, intColumn + 1).Value = dsExcelExport.Tables(0).Columns(intColumn).ColumnName.ToString
  31. Next
  32. 'For displaying the column value row-by-row in the the excel file.
  33. For intRow = 0 To dsExcelExport.Tables(0).Rows.Count - 1
  34. For intColumnValue = 0 To dsExcelExport.Tables(0).Columns.Count - 1
  35. .Cells(intRow + 1, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
  36. Next
  37.  
  38. Next
  39. strFileName = InputBox("Please enter the file name.", " ")
  40. strExcelFile = strAppPath & "tbl_rop"
  41. .ActiveWorkbook().SaveAs(strExcelFile)
  42. .ActiveWorkbook.Close()
  43. .AddChart()
  44.  
  45.  
  46. End With
  47. MessageBox.Show("File exported sucessfully.", "Exporting done", MessageBoxButtons.OK, MessageBoxIcon.Information)
  48. NormalExit:
  49. Excel.Quit()
  50. Excel = Nothing
  51. GC.Collect()
  52. Exit Sub
  53. End Sub
Reply With Quote Quick reply to this message  
Join Date: Dec 2006
Posts: 645
Reputation: binoj_daniel is an unknown quantity at this point 
Solved Threads: 17
binoj_daniel's Avatar
binoj_daniel binoj_daniel is offline Offline
DaniWeb Expert

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

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

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

 
0
  #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 Quick reply to this message  
Reply

This thread is more than three months old.
Perhaps start a new thread instead?
Message:



Other Threads in the VB.NET Forum
Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC