Retreive data from sql database and display it in excel sheet

See more:

ADO

MS-Excel

Hi,

I am trying with the below code to retrieve data from sql server database and display it in excel sheet. But its not working fine.

Getting exception as "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" in the expression RecordSet.open(SQL,con).

Below is the code which I used,

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("SQLConnectionString").ConnectionString)
con.Open()
Dim SQL = "select username,password,id from tbl_Login_Details "
Dim Recordset = Server.CreateObject("ADODB.Recordset")

Recordset.Open(SQL,con)

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset(Recordset)
'Save the Workbook and Quit Excel
oBook.SaveAs("C:\Book1.xls")
oExcel.Quit()
'Close the connection
Recordset.Close()
con.Close()
End Sub