Trying to connect to SQL 6.5 with ODBC Connector

[fowlerlfc]

I'm trying to connect to a SQL 6.5 database. I get build errors on the following line: For Each dr In dsname.Tables(0)
The error is: Expression is of type 'System.Data.DataTable', which is not a collection type.

Forgive me if this is a simple problem, I'm new to ASP.NET.

Here's my code:

Dim db As String
db = Request.QueryString("dbname")
        'Response.Write(db)



        Dim strconnection As String = "Driver={SQL Server};" & _
                                    "Server=xxxxxxxx;" & _
                                    "Database=" & db & ";" & _
                                    "Uid=xxxxxx;" & _
                                    "Pwd=xxxxxx"

        Dim odbcconn As New OdbcConnection(strconnection)
        Dim daname As New OdbcDataAdapter("select * from tblemployee where logindatetime > 'Jan 1 1950 8:00AM'",     odbcconn)
        Dim dsname As DataSet
        daname.Fill(dsname, "tblemployee")

        Dim dr As DataRow
        For Each dr In dsname.Tables(0)

            Response.Write("<a href=name.aspx?name=" & dr.Item(0) & ">" & dr.Item(0) & "</a><br>")

        Next

 

[RiverGuy]

dsname.Tables(0).Rows()

 

[fowlerlfc]

well, that does away with my build error, but it returns a null error on the dataset when i load that page. i know that there's data there. i think the problem lies in how i'm building the connection string dynamically by using a variable from another page. i've built a test page in the ide and used a non-dynamic connection string and it runs just fine. is there something wrong with the way i've done my coding or is there a better way to do this?

 

[ca8msm]

There's nothing wrong with building the string that way if you have the need to connect to multiple databases based on user input. However it is your job to make sure that the database string that is passed through is correct. A quick way of making sure it is correct is using the code I posted in thread855-1010719. You could then put the different potential database connections is a dropdownlist as opposed to letting the user type it out - this will stop potential spelling errors etc. Passing it through as a querystring has the potential for all sorts of errors (e.g URLEncode/Decode etc)

Also you may want to check out faq855-5662 as this may help you with the actual database looping.



----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.