Read multiple column data from SQL query ( 1 row )

[frotaru]

Read multiple columns data from SQL query ( 1 row )

Hi all.
I am using an SQL express where I’ve designed a table named User_DB_Main
I have also created a procedure that makes some selections :

@UserId varchar(20),
@UserPassword varchar(30)

AS
		/*- Check if UserID exists*/
		SELECT COUNT(UID) AS uTrue FROM dbo.User_DB_Main WHERE UId = @UserId
		/*- Check if UserPassword matches*/
		SELECT COUNT(UID) AS uPass FROM dbo.User_DB_Main WHERE UId = @UserId AND UPass = @UserPassword
		/*- Select only required fields*/		
		SELECT UId AS qUserId, UAIL AS qUAIL, UPE AS qUPE,UPED AS qUPED,UPLUD AS qUPLUD,
			UPEDBW AS qUPEDBW, UPRTL AS qUPRTL,ULLI AS qULLI, ULC AS qULC, UType AS qUType 
			FROM
			dbo.User_DB_Main
			WHERE UId = @UserId

What I am trying to do is to sore each column value ( from the first row found ) in to a variable / string.

The vb code looks like this :

'Use the connection string found in Web.config file
        Dim User_DB_Conn As New SqlConnection(ConfigurationManager.AppSettings("SQLconn_User_DB"))

        'Call stored procedure designed for user authentication 
        Dim SQL_command As New SqlCommand("ValidateUserAccount_User_DB_Main", User_DB_Conn)
        SQL_command.CommandType = CommandType.StoredProcedure
        'Define strings / values passed in
        Dim UserId_String, UserPassword_String As SqlParameter
        UserId_String = SQL_command.Parameters.AddWithValue("@UserId", SqlDbType.Char)
        UserPassword_String = SQL_command.Parameters.AddWithValue("@UserPassword", SqlDbType.Char)
        'Set the direction of the parameters...input, output, etc
        UserId_String.Direction = ParameterDirection.Input
        UserPassword_String.Direction = ParameterDirection.Input
        'Set the value(s) of the parameters to the respective source controls
        UserId_String.Value = UsrID.Text
        UserPassword_String.Value = UsrPass.Text


        'Declare variables that will store values read from SQL

        'Main Data
        '                   uTrue   - "User is found"
        Dim uTrue As Integer
        '                   uPass   - "Password is valid"
        Dim uPass As Integer


        'Open DB
        If User_DB_Conn.State = ConnectionState.Closed Then
            User_DB_Conn.Open()
        End If
        'Create SQL Data Reader
        Dim SQL_Reader As SqlDataReader = SQL_command.ExecuteReader() '(CommandBehavior.CloseConnection)

        'Read data from SQL


        'Store values
        'Main data
        While SQL_Reader.Read
            uTrue = SQL_Reader("uTrue")
            uPass = SQL_Reader("uPass"
        End While

        SQL_Reader.Close()

It all works fine if I am trying to sore only 1 value something like :

While SQL_Reader.Read
            uTrue = SQL_Reader("uTrue")
        '    uPass = SQL_Reader("uPass"
        End While

A solution is to close the connection after storing 1 value and then executeReader again for storing the second … and so on.

Does anyone have a better idea ?
I am new in .NET.
Any help will be much appreciated.
Thank You

[SheSaidImaPregy]

uPass = SQL_Reader("uPass"

is incorrect. You need the ending )

uPass = SQL_Reader("uPass")

[frotaru]

It is not working…. The problem is that I am trying to read through multiple select statement results sets.

/*- Check if UserID exists*/
		SELECT COUNT(UID) AS uTrue FROM dbo.User_DB_Main WHERE UId = @UserId
		/*- Check if UserPassword matches*/
		SELECT COUNT(UID) AS uPass FROM dbo.User_DB_Main WHERE UId = @UserId AND UPass = @UserPassword
		/*- Select only required fields*/		
		SELECT UId AS qUserId, UAIL AS qUAIL, UPE AS qUPE,UPED AS qUPED,UPLUD AS qUPLUD,
			UPEDBW AS qUPEDBW, UPRTL AS qUPRTL,ULLI AS qULLI, ULC AS qULC, UType AS qUType 
			FROM
			dbo.User_DB_Main
			WHERE UId = @UserId

Somehow I’ found a workaround using NextResult … but still not working :)

If multiple result sets are returned, the DataReader provides the NextResult method to iterate through the result sets in order. The following example shows the SqlDataReader processing the results of two SELECT statements using the ExecuteReader method.

Private Sub RetrieveMultipleResults(ByVal connection As SqlConnection)
    Using connection
Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;" & _
          "SELECT EmployeeID, LastName FROM Employees", connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        Do While reader.HasRows
            Console.WriteLine(vbTab & reader.GetName(0) _
              & vbTab & reader.GetName(1))

            Do While reader.Read()
                Console.WriteLine(vbTab & reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop

            reader.NextResult()
        Loop
    End Using
End Sub

[SheSaidImaPregy]

But first.. why are you using 3 separate queries if you're pulling into one datareader anyway? Try this:

SELECT (SELECT COUNT(UID) FROM dbo.User_DB_Main WHERE UId = @UserId) AS uTrue, (SELECT COUNT(UID) FROM dbo.User_DB_Main WHERE UId = @UserId AND UPass = @UserPassword) AS uPass, UId AS qUserId, UAIL AS qUAIL, UPE AS qUPE,UPED AS qUPED,UPLUD AS qUPLUD, UPEDBW AS qUPEDBW, UPRTL AS qUPRTL,ULLI AS qULLI, ULC AS qULC, UType AS qUType FROM dbo.User_DB_Main WHERE UId = @UserId

Also, you do know that you do not need the AS command unless you want the column names to be different, right? Just making sure.

This will pull it out into 1 datareader. You can use the data reader to pull the 2 counts. Then if those counts equal what they should, bind the rest of the data:

if reader.HasRows then
  while reader.Read()
    uTrue = dtrReader("uTrue")
    uPass = dtrReader("uPass")

    if uTrue > 0 and uPass > 0 then
      'rest of your values
    else
      if uTrue < 1 then 'add error code for UserName
      if uPass < 1 then 'add error code for Password
    end if
  end while
end if

Something like that! :)

[SheSaidImaPregy]

And your second query you can combine like this:

"SELECT CategoryID, CategoryName FROM Categories UNION SELECT EmployeeID, LastName FROM Employees"

This will bypass all multiple recordsets. The one thing that you should worry about with splitting up recordsets is that if for some reason someone gets to use sql injection on you, all your queries will fail, except for the first one (maybe that one will pass).

It all comes down to how you would like to handle your queries. Obviously you have the right version for sql to run multiples, so then it is your choice whether or not you want to combine them or keep them separate. From experience though, and reading, I have realized that combined queries are quicker then separate queries, as long as they are NOT in a looped query. Example of a looped query:

SELECT UserName FROM Users WHERE UserID IN (SELECT UserID FROM Newsletters WHERE UserID IN (SELECT UserID FROM Addresses WHERE Address1<>NULL AND Address1<>'' AND State<>NULL AND State<>'' AND City<>NULL AND City<>'' AND ZipM<>NULL AND Zip<>''))

Something like that.

[frotaru]

Thank you very much!