Execute Scalar to Label. Subquery returned more than 1 value

Question

So I have a label which shows the username of the user. I've used this value to return their ID which I then attach to a label. I used execute scalar to do this because I wasn't sure how else to get a single value on a label.

This works fine. I then use the ID from the label and put it in another table. I can do this twice and then the page crashes saying...

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

However I don't understand. I don't pull anything from the second table on the page. I don't know why it would affect it. I feel like I've tried everything. Taking out the line that posts the ID to the label lets the page run but I need it there.

    Label2.Text = User.Identity.Name
    Dim connetionString As String
    Dim cnn As SqlConnection
    Dim cmd As SqlCommand
    Dim sql As String
    connetionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\FYPMS_DB.mdf;Integrated Security=True"
    sql = "SELECT SupID FROM Supervisor WHERE (Email = @Email)"
    cnn = New SqlConnection(connetionString)
    Try
        cnn.Open()
        cmd = New SqlCommand(sql, cnn)
        cmd.Parameters.Add(New SqlParameter("@Email", User.Identity.Name))
        Dim supid1 As Int32 = Convert.ToInt32(cmd.ExecuteScalar())
        cmd.Dispose()
        cnn.Close()

        Label1.Text = supid1.ToString

    Catch ex As Exception
        MsgBox("Can not open connection ! ")
    End Try

End Sub

@Angus chung I am not sure what that means. This is in the page load event — evanna, Apr 11 '15 at 01:57
" I can do this twice" , the User.Identity.Name is still the same? — Angus Chung, Apr 11 '15 at 02:00
@evanna Try with `SELECT TOP 1 ....`, if that worked then I really suspect that sometimes your initial query return multiple rows — har07, Apr 11 '15 at 02:04
@AngusChung Ah yes. I can refresh the page and do it again using the same name. — evanna, Apr 11 '15 at 12:13
@har07 I have tried TOP 1 as well and it is still not working. So I'm assuming there is something else going wrong — evanna, Apr 11 '15 at 12:20

score 0 · Answer 1 · answered Apr 11 '15 at 02:35

This should return the first result for you. Also, it's a good idea to employ Using blocks for objects such as connections, commands, and readers.

    Using cn = New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\FYPMS_DB.mdf;Integrated Security=True")
        cn.Open()
        Using cmd = New SqlCommand("SELECT SupID FROM Supervisor WHERE Email = @Email", cn)
            cmd.Parameters.AddWithValue("@Email", User.Identity.Name)
            Using dr = cmd.ExecuteReader
                If dr.Read Then
                    Label1.Text = CInt(dr("SupID"))
                End If
            End Using
        End Using
    End Using

Hey, Yes this did return the first result just like it did to begin with but when more than two rows are entered into the Project table (the table that we need supid for) the same same happens. I don't understand because there are not querys on the page that pull data from the Project table, only add to it. — evanna, Apr 11 '15 at 12:48

score 0 · Answer 2 · answered Apr 11 '15 at 08:27

0

If you are not sure there are multiple rows for same email in that table, you can change the query to following, that will work for you with executescalar.

SELECT TOP 1 SupID FROM Supervisor WHERE (Email = @Email)

answered Apr 11 '15 at 08:27

Arindam Nayak

7,346
4
32
48

score 0 · Answer 3 · answered Apr 12 '15 at 21:03

0

Horribly sorry! But yes you were right! There was another query going on in the background that I never noticed that was affecting it all. So sorry

answered Apr 12 '15 at 21:03

evanna

1
5

Execute Scalar to Label. Subquery returned more than 1 value

3 Answers3