SQLDataReader not reading data!

[blivori]

Hi,

Can anyone tell me why datareader is no reading user_id from this code?

I debugged and the user_id_select remains 0 and I am getting no errors.

  Protected Sub btnAddUser_Click(sender As Object, e As EventArgs) Handles btnAddUser.Click

            Dim conn As New SqlConnection("Data Source=BRIAN-PC\SQLEXPRESS;Initial Catalog=master_db;Integrated Security=True")
            Dim searchComm As String = "SELECT user_id FROM users WHERE username=@username"
            Dim user_id_select As New Integer

            Dim searchSQL As New SqlCommand

            conn.Open()

            searchSQL = New SqlCommand(searchComm, conn)
            searchSQL.Parameters.AddWithValue("@username", txtUserSearch.Text.ToString)

            Dim datareader As SqlDataReader = searchSQL.ExecuteReader()

            datareader.Read()

            While datareader.Read

                user_id_select = datareader("user_id")

            End While


            datareader.Close()
            conn.Close()

            Dim addComm As String = "INSERT user_id, project_id INTO user_project VALUES (@UserID,@ProjectID)"

            Dim addSQL As New SqlCommand

            conn.Open()

            addSQL = New SqlCommand(addComm, conn)

            addSQL.Parameters.AddWithValue("@UserID", Convert.ToInt32(user_id_select))
            addSQL.Parameters.AddWithValue("@ProjectID", Convert.ToInt32(Session("project_id")))

            Dim datareader2 As SqlDataReader = searchSQL.ExecuteReader()




            datareader.Close()
            conn.Close()




        End Sub

[Dili1234]

Answer to blivori: you have to open the connection before selecting the data..........Inline Code Example Here

myCommand = New SqlCommand(" Select Quantity FROM Product_Details Where P_Id='" & ComboBox2.Text & "'", myConnection)
                Dim dr As SqlDataReader = myCommand.ExecuteReader
                While dr

                    TextBox4.Text = Val(dr(0))
                     End While
                dr.Close()
                myConnection.Close()

[Dili1234]

dieterdirk33 can you illustrate your problem more clear manner....

[G_Waddell]

Hi,
They've cross posted on your post...
I suspect your query is only returning a single record. You are reading the results in two different places though. You have the following line (#16):
datareader.read() This will read in the record from the datareader but you don't do anything with it...
Then you put a loop

while Datareader.read
    user_id_select = datareader("userid")
End While

BUT because you already read the record, when you get to your loop the code will see that you have come to the end of your datareader records and not enter into the loop.

I think what you wanted to do was something like this:

If datareader.HasRows then
    while datareader.read
        user_id_select = datareader("userid")
    End while
End if

OR:

If datareader.hasrows then
    datareader.read
    user_id_select = datareader("userid")
end if

[Dili1234]

do you need to check whether that data is existing?

[Reverend Jim]

Unrelated to the question but worth noting - txtUserSearch.Text is already a String so you don't need to use the ToString method. Just use

txtUserSearch.Text

[Dili1234]

The coding I put you is working properly.I took it from my final project..................