locked
Using DataTable RRS feed

  • Question

  • User-1890252405 posted

    i am creating my own login page for a website

    here is the code

            Dim myPath As String
            myPath = Server.MapPath("abc.mdb")
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
            Dim myCommand As New System.Data.OleDb.OleDbCommand
            'Dim pass As String
            'pass=select password from stutable where rollno=val(textbox1.text)
            Dim adp As New System.Data.OleDb.OleDbDataAdapter("select password from Stutable where rollno = " & TextBox1.Text, con)
            Dim dt As New System.Data.DataTable
            adp.Fill(dt)
            Dim expression As String
            expression = "rollno = " & TextBox1.Text
            Dim r As String
            r = cstring(dt.Select(expression))
            If (r = TextBox2.Text) Then
                LinkButton2.PostBackUrl = "C:\Documents and Settings\spatidar\Desktop\rit\WebSite4\insert.aspx.vb"
            Else
                LinkButton2.PostBackUrl = "C:\Documents and Settings\spatidar\Desktop\rit\WebSite4\userlogin.aspx.vb"
            End If
        End Sub
    End Class


            Dim myPath As String

            myPath = Server.MapPath("abc.mdb")


            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"

            Dim myCommand As New System.Data.OleDb.OleDbCommand

            'Dim pass As String

            'pass=select password from stutable where rollno=val(textbox1.text)

            Dim adp As New System.Data.OleDb.OleDbDataAdapter("select password from Stutable where rollno = " & TextBox1.Text, con)

            Dim dt As New System.Data.DataTable

            adp.Fill(dt)

            Dim expression As String

            expression = "rollno = " & TextBox1.Text

            Dim r As String

            r = cstring(dt.Select(expression))

            If (r = TextBox2.Text) Then

                LinkButton2.PostBackUrl = "C:\Documents and Settings\spatidar\Desktop\rit\WebSite4\insert.aspx.vb"

            Else

                LinkButton2.PostBackUrl = "C:\Documents and Settings\spatidar\Desktop\rit\WebSite4\userlogin.aspx.vb"

            End If


        End Sub

    End Class

    my problem is that how do i obtain the rollno from a table in the database as a string(in the code above iam retrieving as a datatable) so that i can compare with the user and validate him

    please help.............

    Wednesday, May 26, 2010 5:21 AM

Answers

All replies

  • User-1618234021 posted

    Hi

    If you want to verify the user then simple use the following query:

    "select * from stutable where rollno='" & (textbox1.text & "' and password='" & textbox2.text & "'"

    Then execute it using OleDbDataReader. i.e

    dim dr as OleDbDataReader = myCommand.ExecuteReader()

    If (dr.Read()) Then

    'User is valid

    End if

    Wednesday, May 26, 2010 6:12 AM
  • User-1071856410 posted

    While convetring the value in DataTable to string, We need to access it like this:

    r = dt.Rows(i)(j).ToString()


    Where i = row index and j = column index

     

    Wednesday, May 26, 2010 6:59 AM
  • User-1199946673 posted

    If you want to verify the user then simple use the following query:

    "select * from stutable where rollno='" & (textbox1.text & "' and password='" & textbox2.text & "'"

     

    First of all, concatenating strings is not good coding practice, use parameterized queries instead:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    The next problem is that password is a reserved word in Jet. Reserved words shouldn't be used as fieldnames, or if you use them anyway. you should enclose them in brackets (SELECT ... WHERE [password] = ....)

    The last thing is that itsn't neccesary to use a DataReader, You can simply create a Count query that retrieves the number of records and use ExecuteScalar.

    Dim result As Integer
    Using conn as New OleDbConnection("your connectionstring")
      Using comm as New OleDbCommand("SELECT COUNT(*) FROM stutable WHERE rollno = ? AND [password] = ?", conn)
        conn.Open()
        comm.Parameters.AddWithValue("rollno", Textbox1.Text)
        comm.Parameters.AddWithValue("password", Textbox2.Text)
        result = comm.ExecuteScalar()
      End Using
    End Using
    If result = 1 Then
      'User is valid
    End If
    
    
    


     

     

    An article on this topic can be found here:

    http://www.mikesdotnetting.com/Article/75/Simple-Login-and-Redirect-for-ASP.NET-and-Access

    But you could also use the build in membership provides using an Access Database

    http://imar.spaanjaars.com/404/using-the-microsoft-access-providers-to-replace-the-built-in-sql-server-providers

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 26, 2010 7:18 AM
  • User-1199946673 posted

            Dim myPath As String

            myPath = Server.MapPath("abc.mdb")


            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"

     

    When you put your Access database in the root of your website, it can be downloaded directly if anybody guesses the url. You should place it into the App_Data folder, which will protect it from downloading. When you've done that, your connectionstring will be:

    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=|DataDirectory|abd.mdb"

    the |DataDirectory| directive point to the App_Data folder!

    http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

    Wednesday, May 26, 2010 7:28 AM
  • User-1618234021 posted


    The last thing is that itsn't neccesary to use a DataReader, You can simply create a Count query that retrieves the number of records and use ExecuteScalar.


    Selecting count(*) might not always help. Beucase lot of times we need to put users profile properties in session after successful authentication like emailAddress, firstname etc. So using reader will definitly help.

    Wednesday, May 26, 2010 7:29 AM
  • User-1199946673 posted

    Selecting count(*) might not always help. Beucase lot of times we need to put users profile properties in session after successful authentication like emailAddress, firstname etc. So using reader will definitly help.
     

    Off course, when you need data from the database, you should use a reader. But in this case, the only requirement that is asked is to validate a user, ExecuteScalar will do just fine! However, my other 2 remarks are much more important!

    Wednesday, May 26, 2010 7:33 AM
  • User-1890252405 posted

     LinkButton2.PostBackUrl = "C:\Documents and Settings\spatidar\Desktop\rit\WebSite4\insert.aspx.vb"

            Else

                LinkButton2.PostBackUrl = "C:\Documents and Settings\spatidar\Desktop\rit\WebSite4\userlogin.aspx.vb"

            End If



    does the above code work because it doesnt work for me

    please suggest some alternatives

    Friday, May 28, 2010 1:32 AM
  • User-1071856410 posted

    You should be giving the Virtual path for PostBackUrl and not Physical path 

    Something like this if WebSite4 is your root directory:

    LinkButton2.PostBackUrl = "insert.aspx"
    
    LinkButton2.PostBackUrl = "userlogin.aspx"
    
    


     

    Friday, May 28, 2010 2:01 AM
  • User-1890252405 posted

    it still doesnt work stays in the same page :-(

    Friday, May 28, 2010 2:16 AM
  • User-1071856410 posted

    it still doesnt work stays in the same page :-(
     

    You mean even after setting the PostBackUrl for the link button, when you click the Link Button, It stays in the same page ?

    Could you debug and see whether PostbackUrl is correctly set for the LinkButton and that LinkButton's click event is firing

    Friday, May 28, 2010 2:34 AM
  • User-1890252405 posted

    the click event is firing but the page remains the same!!Embarassed

    Friday, May 28, 2010 4:07 AM
  • User-1890252405 posted

    i got it!!!!!Cool

    here is the code

    If result = 1 Then
                'User is valid  
                'LinkButton1.PostBackUrl = "~/insert.aspx.vb
                'LinkButton1.Attributes.Add("PostBackUrl", "insert.aspx")
                Response.Redirect("~/menu.aspx")
            Else
                'LinkButton1.PostBackUrl = "~/userlogin.aspx"
                'LinkButton1.Attributes.Add("PostBackUrl", "userlogin.aspx")
                Response.Redirect("~/userlogin.aspx")
            End If

    If result = 1 Then

                'User is valid  

          

                Response.Redirect("~/menu.aspx")

            Else

       

                Response.Redirect("~/userlogin.aspx")

            End If

    Friday, May 28, 2010 4:12 AM
  • User-1071856410 posted

    Now I understand where you stood. Actually you wanted to redirect to a different page after checking login information.

    But what you tried earlier was an entirely different thing. You were trying to set PostBackUrl property for a LinkButton.

    Once you set a PostBackUrl property for your LinkButton, What happens is next time when you click that LinkButton, The current page will post back to the specified page.

    ie) In your scenario, Suppose you set the PostBackUrl of LinkButton to 'insert.aspx' as you tried before, What happens is next time when someone clicks the LinkButton, the current page will post back to 'insert.aspx'

    Hope you got the difference between two approaches.

    Just thought this information may help you in future.

    Friday, May 28, 2010 4:42 AM
  • User-1890252405 posted

    one more thing!! please

    can you help me too pass the rollno obtained in this page too another page and then retrieve a record based on that roll no

    Friday, May 28, 2010 4:59 AM
  • User-1071856410 posted

    You can use Query string for this,

            Response.Redirect("Default2.aspx?RollNo=" + rollNo);
    

    And Then in second page, You can retrieve the value like this,

    protected void Page_Load(object sender, EventArgs e)
        {
            if (Request.QueryString["RollNo"] != null)
            {
                string rollNo = Request.QueryString["RollNo"];
            }
        }



     

    Friday, May 28, 2010 5:11 AM
  • User-1890252405 posted

    do you need semi colon at the end please help on how to retrieve the records if dont mind!!!!!!

    please!!!!!!

    Friday, May 28, 2010 5:30 AM
  • User-1071856410 posted

    do you need semi colon at the end
     

    Sorry that was C# code. Here's the VB Equivalent:

    Passing value from Page 1 :

    Response.Redirect("Default2.aspx?RollNo=" & rollNo) 

    Retrieving value in Page 2 :

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) 
        If Request.QueryString("RollNo") IsNot Nothing Then 
            Dim rollNo As String = Request.QueryString("RollNo") 
        End If 
    End Sub 
    


     

     

     

    Friday, May 28, 2010 5:41 AM
  • User-1199946673 posted

    You can use Query string for this,
     

    Think again?

    The rollno is the userID of the user that's logged in. Do you really want to use a querystring to obain data? That means that onces I logged in, I can use any rollno I want to get data form someone else. I strongly advice to use the build in membership provider (see my previous post) instead of reinventing a wheel that isn't round!!!!

    Friday, May 28, 2010 6:02 AM
  • User-1890252405 posted

    the code is not working

       Response.Redirect("userview.aspx?RollNo=" & TextBox1.Text)


    then in the target page



    dim rollno1 as string

    Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If Request.QueryString("RollNo") IsNot Nothing Then
                rollno1 = Request.QueryString("RollNo")
                TextBox1.Text = rollno1
            End If
        End Sub

    Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

            If Request.QueryString("RollNo") IsNot Nothing Then

                rollno1 = Request.QueryString("RollNo")

                TextBox1.Text = rollno1



            End If

        End Sub


    but the text box is showing nothing!

    Friday, May 28, 2010 6:17 AM
  • User-1071856410 posted

    but the text box is showing nothing!
     

    Which one ?

    Just debug and see whether TextBox in first page is having value. If it has, It will be avilable in second page also.

    Try building the url first

            Dim url As String
            url = "Default3.aspx?RollNo=" & TextBox1.Text
    
            Response.Redirect(url)

    Debug and see what is the URL being built.

     

    Friday, May 28, 2010 7:19 AM
  • User-1199946673 posted

    the code is not working
     

    You started this thread with:

    "I am creating my own login page for a website"

    Making me believe that you wanted to build a secure website! But what you're doing now isn't secure at all! When you would have read the article on the build in membership provider with Access it would have taken you maybe 1 hour to set up a secure website without a single line of coding.

    Friday, May 28, 2010 7:45 AM
  • User-1618234021 posted

    Hi

    After retrieving the rollno from the database, put it in the session and move to the next page like the following:

    Session("rollno")=rollno
    Response.Redirect("NextPage.aspx")


    Now on any page, you can get the rollno from session and use it in the query like the following:

    Dim s as string
    s="select * from tableName where rollno=" & Session(rollno).ToString()

    You can exexute the above query to get the data.



    Saturday, May 29, 2010 2:18 AM