how can i export to excel, sql data that uses parameters

[radiance]

I have a small form which i allow the user to search based on dates and/or select the book from the list.

the export is a function that I call for the date search and the book selection.

the dates search works fine. i use text boxes, but since I am also using a select box for the books, I don't know what to do. I thought about a datareader, but I am not sure how to proceed. with my current code, I get the following error: 'ExecuteReader: Connection property has not been initialized.' so, I know I am really way off in left field. I also tried a hidden text field that will also display the value of the book, but that seemed redundant.

is this possible, or do i need to create 2 separate functions?

===CODE EXTRACT=====

'this is the button for the book selection (which doesn't work)

Protected Sub datesExportButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles datesExportButton.Click

runExportProg()
end sub

'this is the beginning of the function for the excel export, lot of code, so i won't paste it all

function runExportProg()As System.Data.DataTable

Using conn As New SqlConnection("Server=testServer;Initial Catalog=myTestTable;User ID=userxy; Password=password")

'the following will base the export on program name

If programList.SelectedValue.ToString <> "" Then

Dim mySqlCommand As New SqlCommand
Dim myDataReader As SqlDataReader

' mySqlCommand = New SqlCommand("select * from programDealCMS where worksheetID= @worksheetID;", conn)

Dim pullfilesbyProgram As String = "select * from programDealCMS where worksheetID= @worksheetID;"

mySqlCommand.Parameters.Add("@worksheetID", System.Data.SqlDbType.Int)
mySqlCommand.Parameters("@worksheetID").Value = programList.SelectedValue

'programID.Text = "@worksheetID".ToString

conn.Open()
'place reader here??
myDataReader = mySqlCommand.ExecuteReader()

Do While (myDataReader.Read())
programID.Text = myDataReader.Item("worksheetID").ToString()
Loop

'rest of excel data for export

End If

End Using

end function


thanks in advance

 

[ca8msm]

To loop through data, read faq855-5662. However, I'm not exactly sure what your problem is. Is connecting to the database the problem, is it adding parameters, is it reading the data or is it the export to excel you are having a problem with?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[radiance]

ohhh..ok. thanks for your quick response.

the problem is not connecting to the database. I know that my structure for this request is incorrect. I am having problem with the correct way to add the parameters, reading the data, and enabling it for use with the excel export.

 

[ca8msm]

To add parameters, check out the help files on the SQLCommand object.

Then, rather than looping through the rows, I'd use a GridView and set it's DataSource to the DataReader/DataTable.

To output to excel, set the Response.ContentType to "application/vnd.ms-excel".


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[radiance]

so i need to place another object, grid within my form? is there no other way, since I have the values within a drop-down?