ASP.NET

ASP.NET RSS

Need help displaying single database values

Please support our ASP.NET advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Need help displaying single database values

Sep 27th, 2007

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.

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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
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

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Need help displaying single database values

Sep 28th, 2007

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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
GridVeiw1.DataSource = dtrReaderComments;
GridView1.DataBind();
GridVeiw1.DataSource = dtrReaderComments;
GridView1.DataBind();

That's basically it.

What worries me more is that this:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
SQLString = "SELECT UserID FROM Users WHERE UserName='" & txtUsername.Text & "' AND UserPassword='" & txtPassword.Text & "'"
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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
hypAccount.NavigateURL = "/Vegas2/MyAccount/account.aspx?uaID=" & arow("UserID") & ""
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.

Last edited by hollystyles; Sep 28th, 2007 at 9:34 am.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Need help displaying single database values

Sep 28th, 2007

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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
SQLString = "SELECT UserID FROM Users WHERE UserName=@userName AND UserPassword=@userPass"
 
conComm.parameters.Add("@userName", txtUserName.Text)
conComm.parameters.Add("@userPass", txtUserPass.Text)
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.

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Need help displaying single database values

Sep 28th, 2007

•

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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
SQLString = "SELECT UserID FROM Users WHERE UserName=? AND UserPassword=?"
 
conComm.parameters.Add("@userName", txtUserName.Text)
conComm.parameters.Add("@userPass", txtUserPass.Text)
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

Last edited by hollystyles; Sep 28th, 2007 at 11:41 am.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Need help displaying single database values

Sep 28th, 2007

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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
	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")
	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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
 DA.Fill(DS)  'after grabbing ONLY the UserID from the table, and only 1 userid is allowed.'
Session("UAID") = ????
 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.

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Need help displaying single database values

Sep 28th, 2007

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.

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
conLogin.Open()
strUAID = cmdSelectLoginfo.ExecuteScalar()
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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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"]
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!

Last edited by hollystyles; Sep 28th, 2007 at 12:07 pm.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Need help displaying single database values

Sep 28th, 2007

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

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Need help displaying single database values

Sep 28th, 2007

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!

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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
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