从SQL数据库检索数据并将其显示在Excel工作表中 [英] Retreive data from sql database and display it in excel sheet

问题描述

我正在尝试使用以下代码从sql server数据库中检索数据并将其显示在excel工作表中.但是它不能正常工作.

在表达式RecordSet.open(SQL，con)中获取异常，例如"参数类型错误，超出可接受范围或彼此冲突".

以下是我使用的代码，

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

推荐答案

这里有一个有关如何在ADO中创建连接和记录集的示例:
http://www.webcheatsheet.com/ASP/database_connection_to_MSSQL.php [ http://www.w3schools.com/ado/ado_recordset.asp [类似的讨论 [

Have a sample on how to create a connection and recordset in ADO here:
http://www.webcheatsheet.com/ASP/database_connection_to_MSSQL.php[^]
http://www.w3schools.com/ado/ado_recordset.asp[^]

BTW, based on similar issues faced and resoled suggests the object type to be strongly declared, look here:
Similar discussion[^]

这篇关于从SQL数据库检索数据并将其显示在Excel工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！