The connection's current state is closed. (Connect to sql server and display a record

[carrzkiss]

Hello All;
I would like to start off by saying hello, and that this is actually my first post to the group, even though I have been a member for many years.

I am a ASP Classic Programmer, professionally for a little over a year, I have developed some rather nice sites, including the one in my signature (Still a work in progress).

I have been forced to use ASP.NET (VB) for a FileUpload control, as it offers a self controlled way of creating Thumbnails, without the use of 3rd party controls, which is great, as my hosting server does not allow for 3rd party installs on their servers.
So.

The code below is what I have so far for connecting to the database and displaying a record is the "cookie" exist on the users computer.
Unfortunantly, this is not working, and to be honest with you, their is not a whole lot of resources available to show how to do this correctly.
So I am stuck with a lot of post and hoping that someone can actually assist me with this and tell me what I am doing wrong and maybe correct my code.

I am getting an error about the "Connection is closed"
So, I am at a complete loss, I have asked this question on other forums and to no avail, and it like no one knows what to tell me.
They try to give me things to try, but absolutely nothing has worked so far.

So.
This is what I ask if you all if possible:

If you want to re-write my code, I prefer something simple and basic, as the only thing that I am doing to checking rather the user is logged in, and if so, display a record from the database.
I am NOT in the need for the DataGrid, so please do not suggest it, as I am only needing to check this one thing.

I would like to also add, that I have successfully contect to my database with the FileUpload control and save file names of the images uploaded, so I know that my connection string is done properly>
But, for some reason the "SELECT" statement is causing a HUGE issue.
(It was easier to insert, than to select, I am not use to that)

Thank you
Carrzkiss

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.OleDb
Imports System.Data.SqlClient

Partial Class ReadVB
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim strmegaun As String = Request.Cookies("cookiename")("user")
         If (Request.Cookies("cookiename") Is Nothing) Then
            lblCookie.Text = "You must be a member and logged in to enter this area!"
        Else
            lblCookie.Text = Request.Cookies("cookiename")("user")
        End If
        ' End Sub
        '  Private Sub ConnectToSQL()

        Dim cn As New System.Data.SqlClient.SqlConnection

        cn.ConnectionString = "Data Source=COMPUTER-NAME;Database=DATABASENAME;User ID=USERNAME;Password=********;"
        cn.Open()



        Dim getUser As New System.Data.SqlClient.SqlCommand("@Username")
        Dim resultsReader As System.Data.SqlClient.SqlDataReader  ' Similar to RecordSet
        getUser.Connection = New System.Data.SqlClient.SqlConnection("")
        getUser.CommandText = "select Username from MegaUsers where username=?"
        getUser.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Username", strmegaun))
        getUser.Prepare()


        resultsReader = getUser.ExecuteReader  ' Executes the query and returns the "record set"

        While resultsReader.Read()  ' Continue looping until the reader has passed the last row of the "record set"
            Response.Write(resultsReader("Username").ToString())
        End While

        resultsReader.Close()  ' Close the reader
        resultsReader = Nothing

        cn.Close()
    End Sub
End Class

[Oxiegen]

Try replacing some of the code with this:

Dim cn As New System.Data.SqlClient.SqlConnection("your connectionstring")
            cn.Open()
            Dim getUser As New System.Data.SqlClient.SqlCommand("select Username from MegaUsers where username=?", cn)
            getUser.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Username", strmegaun))
            Dim resultsReader As System.Data.SqlClient.SqlDataReader
            resultsReader = getUser.ExecuteReader(CommandBehavior.CloseConnection)
            If resultsReader.HasRows Then
                While resultsReader.Read()
                    Response.Write(resultsReader("Username").ToString())
                End While
            End If
            resultsReader.Close()
            resultsReader = Nothing

[carrzkiss]

Thank you so very much for your reply.
I am getting the following error.

System.Data.SqlClient.SqlException: Incorrect syntax near '?'.

And the line is:

resultsReader = getUser.ExecuteReader(CommandBehavior.CloseConnection)

But the Error I think is pointing to:

Dim getUser As New System.Data.SqlClient.SqlCommand("select Username from MegaUsers where username=?", cn)

It looks ok to me, but then again, nothing has really looked good since I started this project.

Any idea's?
Thank you once again, it is going to be so nice to actually be able to show something once this runs error-free.

[carrzkiss]

Hold it.
Incorrect syntax near ?

This error means that there is no value being passed and the the string is empty.

I will have to do some checking on this one.
I will get back to you later on today, other than that, I replaced the
? with a username in the db and it compiled through, so I am happy.
I just need to get the cookie to read through to the string.

Any idea's on the code that I am using? It seems to be done right, as it does grab the contents of the cookie and display it to the page.

Carrzkiss

[carrzkiss]

Got it working.
Had to change

username=?
to
username=@username

And that did it.
THANK YOU!!!

Works great.
This is done and I am happy, I can not start the development process of the site. (Which will be done in ASP Classic)
By this weekend the site will go live, thanks to your 1-time post and code.
Thank You: Oxiegen

Carrzkiss