I have the block of code below in my Page_Load event.
Instead of making that many calls to the server I'd like to use a DataReader and one SELECT statement and put the values into variables or an array of variables, whatever is best.
SELECT FirstName, LastName, Department FROM Users WHERE LogonName = @iUserName
iUserName is the logon name of one user so only one record or value per field is returned. I just wanted to make that clear. The return of the sql above would be like "David, Megnin, IT Department".
I just need to put each value into a variable I can use where needed.
Thanks a bunch.
Protected Sub Page_Load(ByVal...
Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("PurchaseReqConnectionString").ConnectionString)
Dim DBCmd1, DBCmd2, DBCmd3, DBCmd4 As New SqlCommand
Dim sql1, sql2, sql3, sql4 As String
sql1 = "SELECT [Department] FROM [Users] WHERE [LogonName] = '" & iUserName & "'"
sql2 = "SELECT [FirstName] FROM [Users] WHERE [LogonName] = '" & iUserName & "'"
sql3 = "SELECT [LastName] FROM [Users] WHERE [LogonName] = '" & iUserName & "'"
sql4 = "SELECT SUM(TotalDollars) FROM [PurchaseRequisitions].[dbo].[Requisitions] WHERE [SubmittedBy] = '" & iUserName & "'"
'Add UPDATE Statement
DBCmd1 = New SqlCommand(sql1, DBConn)
DBCmd2 = New SqlCommand(sql2, DBConn)
DBCmd3 = New SqlCommand(sql3, DBConn)
DBCmd4 = New SqlCommand(sql4, DBConn)
varDepartment = DBCmd1.ExecuteScalar() 'Convert.ToString(DBCmd1.ExecuteScalar())
varFirstName = DBCmd2.ExecuteScalar()
varLastName = DBCmd3.ExecuteScalar()
lblSumTotal.Text = DBCmd4.ExecuteScalar()
lblDepartment.Text = "Your Department: " & varDepartment
lblUserName.Text = " Requisitions submitted by: " & varFirstName & " " & varLastName
Session("Department") = varDepartment
Catch exp As Exception
'Close Database connection
DBConn = Nothing