Showing posts with label page. Show all posts
Showing posts with label page. Show all posts

Friday, March 23, 2012

How to use simple login form with SQL [newbie] ?

Heya Everyone !
I am new in Asp.net - I've read tutorial at this page, but i have some problems.
I want to have a login form at my website. I also want that login form is checking if user is in a user table (database). I mean simply login form - you write login and a password, than click button - site execute query (select * from user_table where user_id=login and user_password=password or something like this). But I have a problem with building this all code with sql connections. Can someone tell me how can I send query to my sql database ? And how can I display it? And once more - how set the cookies, but thats the other problem.

Hi there,

First of all, are you using VS 2003 or VS 2005? Because if you're using VS 2005, there are built in IDE to create a login form.

|||ronnyr --> i'm using VS 2005, but I want to have login through the sql database (simple CMS) not like this is in asp.net tutorial, where i'm using login form and changing parametrs in administration zone (I hope that you know what I mean).

I just want:
Two textboxes and simple button (like you have in html form). When I click my button, site should send a query which looks for user with user login the same as that in a textbox1 and with pass the same as that in a textbox2. If all is ok, user is logged in.|||And once more --> I have problem with:

Dim ConnectionString As String = System.Configuration.AppSettingsSection("ConnectionStrings")

How I should declare this string?
Im getting an error:
'AppSettingsSection' is a type in 'Configuration' and cannot be used as an expression.|||

So basically you just need to figure out the problem with your database connection.

Check this site:http://aspnet.4guysfromrolla.com/articles/110905-1.aspx

Hope this helps...

|||Okey, I've got connection. But I have another problems.

Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString
Dim test As String
test = Label1.Text

Using myConnection As New Data.SqlClient.SqlConnection(ConnectionString)
'Specify the SQL query
Const sql As String = "SELECT * FROM kontakty"

'Create a SqlCommand instance
Dim myCommand As New Data.SqlClient.SqlCommand(sql, myConnection)

'Get back a DataSet
Dim myDataSet As New Data.DataSet

'Create a SqlDataAdapter instance
Dim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand)
myAdapter.Fill(myDataSet)

'Bind the DataSet to the GridView
g.DataSource = myDataSet
g.DataBind()

'Close the connection
myConnection.Close()
End Using

And I have:
TextBox1, TextBox2, Button

How I should make that when I click button, query will be
"SELECT [user_id] FROM users WHERE [user_name]=TextBox1.Text, [user_pass]=TextBox2.Text"
This query is incorrect, but I hope that someone show me how is ok.|||And once more:
i can't see an Query Wizard (or Code builder wizard). I searched lotsof, but i didn't find anything. Anyone can help me how can I enableQuery Wizard? (VS 2005)|||

Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString
Dim test As String
test = Label1.Text

Using myConnection As New Data.SqlClient.SqlConnection(ConnectionString)
'Specify the SQL query
Const sql As String = "SELECT COUNT(*) FROM kontakty WHEREUsername=@.Username ANDPassword=@.Password"

'Create a SqlCommand instance
Dim myCommand As New Data.SqlClient.SqlCommand(sql, myConnection)
myCommand.AddParameter("@.Username",txtUsername.text)
myCommand.AddParameter("@.Password",txtPassword.text)

if myCommand.ExecuteScaler==0 then
txtError.text="Invalid Username or Password"
else
Session("Username")=txtUsername.text
response.redirect("somepage.aspx")
end if
End Using

Or something very similiar to that... (Sorry, coded by hand, no syntax check)

|||

I'm not sure what "Query wizard" you are referring to, but...

You can also do something similiar by dropping a sqldatasource object on the page, set it's connection properties, then on the selectcommand property, there will be a "..." button you can hit to help you make your query. Tell it you want to select something like the username from your users table, add a where clause to it whereusername=@.username andpassword=@.password, click the refresh parameters button, then tell it the @.username parameter comes from a control, tell it the controlid is txtUsername, tell it the @.password parameter comes from a control, tell it the controlid is txtPassword, then when you click the button, in it's click event do something similiar like this: (Sorry, I've never had to do this myself, so it's not going to be exact)

dim ds as dataset

ds=sqldatasource1.select

if ds.row(0).column(0).value=txtUsername.text then

' Do login stuff

else

'Do bad login stuff

end if

|||

Now that I've said all that, I'd recommend you don't do this, and use the built in log in form, and related controls. You can tie login form and controls to a SQL database extremely easily, and it'll take care of a HUGE chunk of the issues you'll face, such as:

How to hash a password into the database so it's secure.

How to allow a user to reset his password (in a fairly secure manner...)

Handle the cookies for "Remember me" type log ins.

Persist User preferences and profiles.

And it'll make it all much easier for you to do whatever it is your web app is supposed to do rather than concentrating on the unimportant stuff.

|||Motley --> big thanks, but I have few problems ...

1.
myCommand.AddParameter("@.Username", user_login.Text)
I have an error:

AddParameter' is not a member of 'System.Data.SqlClient.SqlCommand'.

I don't know how to change this, cause in VS 2005 there are other way to this all functions (I hope you understand :) )

2.
You mean that this simple login form which I have in toolbox I can changed for my SQL database? Not as it normal that I put login form and it logged me to the other database.

3.
As an Query Wizard I mean this from point 6 till 28
http://www.asp.net/webmatrix/guidedtour/section91/createloginpage.aspx
There Wizard make self a login procedure.

Thanks for this help which I already get :)
|||

1. It should have been:

myCommand.Parameters.Add(New system.data.sqlclient.sqlparameter("@.Username",user_login.text))

2. Yes, you can have the login control access a different database, by specifying the provider in your web.config. If your database is on SQL Server 2005, the web.config admin pages can create the tables and stored procedures for you automatically, or you can use a small batch file that is included with VS 2005 to create them for you on a SQL Server 2000 database.

3. That is a feature of Web Matrix, which is a different IDE than VS 2005.

Monday, March 19, 2012

How To Use Return Value from SqlDataSource

I have the following code in my page:

<asp:SqlDataSourceID="SqlHolidayDateRange"runat="server"ConnectionString="(OurConnectionString)"

SelectCommand="CountHolidays"SelectCommandType="StoredProcedure">

<SelectParameters><asp:ControlParameterControlID="WeekEndingDatePicker"Name="EndDate"PropertyName="SelectedDate"/><asp:ParameterDirection="ReturnValue"Name="RowCount"Type="Int32"/></SelectParameters></asp:SqlDataSource>

I need to place RowCount, the returned integer value from the stored procedure CountHolidays into a field on the web page. How in the world do I access that data?

Everything I've been able to find works if I'm defining my own command object and writing this thing from scratch. What I - and you -have to work with is what's above.

Hi,

Add OnSelected="SqlHolidayDateRange_Selected" to SqlDataSource then you can get return value using

Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);

Hope this helps.

|||

Like this:

voidSqlHolidayDateRange_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RowCount = Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);

//add your code.......

}

|||I'm sorry, but I'mreally new at this. Neither of your answers make much sense to me. Where do I add those things? In the page, in the codebehind, or what?|||

Doesn't matter.

Add OnSelected="SqlHolidayDateRange_Selected" in your page:

<asp:SqlDataSourceID="SqlHolidayDateRange"runat="server"ConnectionString="(OurConnectionString)"

SelectCommand="CountHolidays"SelectCommandType="StoredProcedure">

OnSelected="SqlHolidayDateRange_Selected"

<SelectParameters>

<asp:ControlParameterControlID="WeekEndingDatePicker"Name="EndDate"PropertyName="SelectedDate"/>

<asp:ParameterDirection="ReturnValue"Name="RowCount"Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

And add the following to you code-behinde:

void SqlHolidayDateRange_Selected(object sender, SqlDataSourceStatusEventArgs e) {int RowCount = Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);//add your code.......}

Then you can display RowCount .

You can take a look atSqlDataSource in QuickStart Tutorials.

Monday, March 12, 2012

How to use Joins in SQL statements in .aspx page?

Hi can someone help me?

I'm using Access database and Microsoft VS2005 (VB)

I need to take data from 2 different tables in 1 database. But i can't seems to get it right!

I have:
- dbWarehouse.mdb
- tblReadDetails and tblStock

I need to use "Barcode" from tblReadDetails, compare it with "Barcode" in tblStock then retrieve all the column in tblStock and put it in a webpage.aspx list box.

I tried the following code...

cn.ConnectionString = strConn
cn.Open()

cmd =New OleDbCommand("SELECT tblStock.Barcode, tblStock.Item, tblStock.Dept, tblReadDetails.Barcode FROM tblStock JOIN tblReadDetils WHERE tblReadDetails.Barcode = tblStock.Barcode", cn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

While dr.Read()
lstBarcode.Items.Add(dr2.Item("tblStock.Barcode"))
lstItem.Items.Add(dr2.Item("tblStock.Item"))
lstDept.Items.Add(dr2.Item("tblStock.Dept"))
EndWhile

but it prompt me the following error...

OleDbException was unhandled by user code
Syntax error in FROM clause

What's wrong with this code?

Regards and thanks!

Hi rinaxanir,

Try this:

SELECT tblStock.Barcode, tblStock.Item, tblStock.DeptFROM tblStockINNERJOIN tblReadDetailsON tblStock.Barcode = tblReadDetails.Barcode

Hope this helps,

Adam

|||

Thanks Adam!

It's perfect!

Rina