Need help displaying single database values

[SheSaidImaPregy]

I need to figure out how to bind data and call it back anywhere on the page. Basically, this is what I am trying to do. Keep in mind that I am an asp programmer and am very new to ASP.NET.

I have build a custom login page that works. Now I need to retrieve the number of comments from a database and post it like I did in asp. Also, I need to retrieve a users id and post it in multiple places randomly on the page. Thanks.

I am converting an asp site to asp.net.

Sub btnLogin_Click(S As Object, E As EventArgs)
	Dim loginValid As String
	Dim conLogin As OdbcConnection
	Dim cmdSelectLoginfo As OdbcCommand
	Dim dtrReaderLogin As OdbcDataReader
	Dim conStringLogin As String
	Dim SQLString As String
	Dim cmdSelectComments As OdbcCommand
	Dim dtrReaderComments As OdbcDataReader
	Dim SQLComments As String
	Dim strUAID As String
	Dim arow as datarow
	conStringLogin = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")
	conLogin = New OdbcConnection( conStringLogin )
	SQLString = "SELECT UserID FROM Users WHERE UserName='" & txtUsername.Text & "' AND UserPassword='" & txtPassword.Text & "'"
	cmdSelectLoginfo = New OdbcCommand( SQLString, conLogin )
	conLogin.Open()
	dtrReaderLogin = cmdSelectLoginfo.ExecuteReader()
	if dtrReaderLogin.hasrows then
		conLogin.Close()
		Session("Login") = "Logged"
		hypAccount.NavigateURL = "/Vegas2/MyAccount/account.aspx?uaID=" & arow("UserID") & ""
		SQLComments = "SELECT COUNT(*) AS Comments FROM Comments WHERE ReadComment='No' AND UAID=" & arow("UserID") & ""
		cmdSelectComments = New OdbcCommand( SQLComments, conLogin )
		conLogin.Open()
		dtrReaderComments = cmdSelectComments.ExecuteReader()
		if dtrReaderComments.hasrows then
			hypComments.Visible = True
			hypComments.NavigateURL = "/Vegas2/MyAccount/comments.aspx?uaID=" & arow("UserID") & ""
			hypComments.Text = "New Comments!"
		else
			hypComments.Visible = False
		end if
		dtrReaderLogin.Close()
		dtrReaderComments.Close()
		conLogin.Close()
	else
		Session("Login") = "Failed"
		conLogin.Close()
		dtrReaderLogin.Close()
	end if
	If (Session("Login") = "Logged") then
		pnlLogin.Visible = False
		pnlLogged.Visible = True
		pnlForgotPass.Visible = False
	ElseIf (Session("Login") = "Failed") then
		pnlLogged.Visible = False
		pnlLogin.Visible = True
		pnlForgotpass.Visible = True
	Else
		pnlLogged.Visible = False
		pnlLogin.Visible = True
		pnlForgotpass.Visible = False
	End If
End Sub

[hollystyles]

Drag a GridView or Repeater control onto your form in design view. In the code behind (Do your databse code which you have shown you know how to do) then just pass the resulting reader to the DataSource property of the WebControl (GridView, Repeater whatever) and call it's DataBind() method.

Example:

GridVeiw1.DataSource = dtrReaderComments;
GridView1.DataBind();

That's basically it.

What worries me more is that this:

SQLString = "SELECT UserID FROM Users WHERE UserName='" & txtUsername.Text & "' AND UserPassword='" & txtPassword.Text & "'"

Leaves you completely wide open to Sql Injection! Ecapsulate the login inplementation in a seperate class file with property accessors for loginname and password and use a stored procedure in a public method that returns a boolean for success or failure.

And this:

hypAccount.NavigateURL = "/Vegas2/MyAccount/account.aspx?uaID=" & arow("UserID") & ""

Means I can assume the identity of anyone by randomly typing user id's in my address bar on the end of the querystring and causing mayhem!! Store the id in a session variable where it cannot be tampered with. Golden rule: Never never never never never under any circumstances what-so-ever blindly accept user input. Validate it *server* side not on the client and bin it if it's duff before it can do any harm.

[SheSaidImaPregy]

The querystring variable is just a cross reference. The uaid is stored in a session, but must match the querystring. It's just extra that is really, uneeded, but (to me anyway) provides better protection against hackers.

I was hoping I didn't have to use a datagrid or the like. I was hoping there was a way to declare the variable/string and recall it when needed. Seems just pointless extra code. Anyway, thanks a ton ^^

Oh, and for some reason maybe you can help on the sql part. I did the way I was supposed to by putting the following:

SQLString = "SELECT UserID FROM Users WHERE UserName=@userName AND UserPassword=@userPass"

conComm.parameters.Add("@userName", txtUserName.Text)
conComm.parameters.Add("@userPass", txtUserPass.Text)

But the authentication always failed. When I debugged by using response.write the SQL after opening it, it always appeared just as above, with the @userName and @userPass. It never substitued the required variables. Is there something else I need?

Oh and I am using OdbcConnection.

[hollystyles]

I was hoping I didn't have to use a datagrid or the like. I was hoping there was a way to declare the variable/string and recall it when needed. Seems just pointless extra code. Anyway, thanks a ton ^^

You'll have to expand on this, I'm not understanding it I'm afraid. In it's default config web controls on aspx pages keep their own data anyway via viewstate, is this what you're on about?

But the authentication always failed. When I debugged by using response.write the SQL after opening it, it always appeared just as above, with the @userName and @userPass. It never substitued the required variables. Is there something else I need?

I am assuming your actually passing the SQl String to the Command object? I don't see it in your code snippet. Also what database are you using? ODBC is like the trabant of data connection libraries. I think Odbc likes to have parameters like this:

SQLString = "SELECT UserID FROM Users WHERE UserName=? AND UserPassword=?"

conComm.parameters.Add("@userName", txtUserName.Text)
conComm.parameters.Add("@userPass", txtUserPass.Text)

And you have to add the params to the command's parameters collection in the exact order they appear in the Sql Statement.

P.S just using params does not make the input safe, you must use a RegularExpression validator or your own custom method Public Sub makeSafe(input As String) As String ... to weed out ; -- and words like DROP
http://msdn2.microsoft.com/en-us/library/bb355989.aspx

[SheSaidImaPregy]

Like, in ASP I could tap into the database once, and store the information in an array, or just say strField = rs("Field") and then call it any time on the page as <%= strField %>.

Heres the code for passing it along, and yes I am passing it to the command object:

conStringLogin = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")
	conLogin = New OdbcConnection( conStringLogin )
	SQLString = "SELECT UserID FROM Users WHERE UserName='" & txtUsername.Text & "' AND UserPassword='" & txtPassword.Text & "'"
	SQLString = "SELECT UserID FROM Users WHERE UserName=@userName AND UserPassword=@userPass"
	cmdSelectLoginfo = New OdbcCommand( SQLString, conLogin )
	cmdSelectLoginfo.Parameters.Add("@userName", txtUsername.Text)
	cmdSelectLoginfo.Parameters.Add("@userPass", txtPassword.Text)
	conLogin.Open()
	DA = New OdbcDataAdapter( SQLString, conLogin )
	DA.Fill(DS)
                'Now how to bind it to a key to reference in sub queries?'
                strUAID = DA("UserID")

One main reason why I want a string to hold the value is due to me referencing it about 4 times. I would hate to keep a connection open that long also. And if you could answer this for me.. how would I reference a value in a sub query? I will be using UAID in a query to check comments. Is my best bet just grabbing the query, setting the session to it, then referencing the session? If so, I just need to know how to set it to the session variable in VB. like:

DA.Fill(DS)  'after grabbing ONLY the UserID from the table, and only 1 userid is allowed.'
Session("UAID") = ????

thanks. You're saving me hours of work.

[hollystyles]

Use ExecuteScalar() on the command object it is very efficient, returns just the first value in the first row of the result set. No need for an expensive DataAdapter and DataSet.

conLogin.Open()
strUAID = cmdSelectLoginfo.ExecuteScalar()

Like, in ASP I could tap into the database once, and store the information in an array

Just use a DataTable object

Dim dt As DataTable
con.Open()
	DA = New OdbcDataAdapter( SQLString, con )
	DA.Fill(dt)
con.Close()
DA.Dispose;

//dt is now available full of data but disconnected from the database

Dim strSomeVal As String
strSomeVal = dt.Rows[0]["ColumnName"]

Those square brackets [] in that last statement may need to be parentheses () in VB syntax. I'm not sure cos VB syntax makes my teeth jangle ! yech!

[SheSaidImaPregy]

Perfect! Thanks a ton!! I couldn't find this anywhere. :)

[SheSaidImaPregy]

Hey, thanks for all your help. This is my complete code... is there any bad stuff in here that could cause major problems with me, even with security? And do I have to close Scalars()? Thanks a ton, once again!

Sub btnLogin_Click(S As Object, E As EventArgs)
	Dim loginValid As String
	Dim conLogin As OdbcConnection
	Dim cmdSelectLoginfo As OdbcCommand
	Dim dtrReaderLogin As OdbcDataReader
	Dim conStringLogin As String
	Dim SQLString As String
	Dim cmdSelectComments As OdbcCommand
	Dim dtrReaderComments As OdbcDataReader
	Dim SQLComments As String
	Dim strUAID As String
	Dim intComments As Integer
	conStringLogin = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")
	conLogin = New OdbcConnection( conStringLogin )
	SQLString = "SELECT UserID FROM Users WHERE UserName=? AND UserPassword=?"
	cmdSelectLoginfo = New OdbcCommand( SQLString, conLogin )
	cmdSelectLoginfo.Parameters.Add("?userName", (txtUsername.Text.Trim()).ToString())
	cmdSelectLoginfo.Parameters.Add("?userPass", (txtPassword.Text.Trim()).ToString())
	conLogin.Open()
	dtrReaderLogin = cmdSelectLoginfo.ExecuteReader()
	if dtrReaderLogin.hasrows then
		dtrReaderLogin.Close()
		strUAID = cmdSelectLoginfo.ExecuteScalar()
		Session("UAID") = strUAID
		conLogin.Close()
		Session("Login") = "Logged"
		hypAccount.NavigateURL = "/Vegas2/MyAccount/account.aspx?uaID=" & strUAID & ""
		SQLComments = "SELECT COUNT(*) AS intComments FROM Comments WHERE ReadComment='No' AND UAID=" & strUAID & ""
		cmdSelectComments = New OdbcCommand( SQLComments, conLogin )
		conLogin.Open()
		intComments = cmdSelectComments.ExecuteScalar()
		if intComments >= 1 then
			hypComments.Visible = True
			hypComments.NavigateURL = "/Vegas2/MyAccount/comments.aspx?uaID=" & strUAID & ""
			if intComments > 1 then
				hypComments.Text = intComments & " New Comments!"
			else
				hypComments.Text = intComments & " New Comment!"
			end if
		else
			hypComments.Visible = False
		end if
		dtrReaderLogin.Close()
		conLogin.Close()
	else
		Session("Login") = "Failed"
		conLogin.Close()
		dtrReaderLogin.Close()
	end if
	If (Session("Login") = "Logged") then
		pnlLogin.Visible = False
		pnlLogged.Visible = True
		pnlForgotPass.Visible = False
	ElseIf (Session("Login") = "Failed") then
		pnlLogged.Visible = False
		pnlLogin.Visible = True
		pnlForgotpass.Visible = True
	Else
		pnlLogged.Visible = False
		pnlLogin.Visible = True
		pnlForgotpass.Visible = False
	End If
End Sub