locked
Getting data from an Access datasource RRS feed

  • Question

  • User559005402 posted

    I'm not sure this is right, but I think it is. Given:

     

    Dim myData As New AccessDataSource
    myData.DataFile = "~/App_Data/Library.MDB"
    mydata.SelectCommand = "SELECT * FROM users WHERE username = ? AND password = ?"
    mydata.SelectParameters.Add("username", txtUsername.Text.Trim)
    mydata.SelectParameters.Add("password", txtPassword.Text.Trim)
    myData.DataBind()

    How do you determine if the select produced any rows and how do you test the values in the returned row? With OLEDB I used a datareader but I can't seem to figure out how to do it with Access.

    Thanks 

    Monday, December 15, 2008 12:32 PM

Answers

  • User-1956254743 posted

    I think you need to pass the username and password from the login control to the command parameters , i changed the function code 

        Protected Function SiteSpecificAuthenticationMethod(ByVal UserName As String, ByVal Password As String) As Boolean
    MyDataSource.SelectCommand = "select User_Level from User_Names where User_Name = @UserName and Password = @Password "
    ' set the paramters values , it will not set automaticly because you are not using the datasource with a data presentation control
    MyDataSource.SelectParameters("User_Name").DefaultValue = Login1.UserName
    MyDataSource.SelectParameters("Password").DefaultValue = Login1.Password
    Dim rdrSql As OleDbDataReader = DirectCast(MyDataSource.Select(DataSourceSelectArguments.Empty), OleDbDataReader)

    Return rdrSql IsNot Nothing AndAlso rdrSql.HasRows
    End Function

      

    I also used another way in this thread :

    http://forums.asp.net/p/1276534/2427973.aspx#2427973 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 15, 2008 1:39 PM
  • User-821857111 posted

    Well excuse me Mike! As a total beginner maybe I'm not smart enough to ask the right question.
     

    Oops.I could have phrased what I said in a better way, couldn't I?  I owe you an apology.

    [:D]

    As I touched on in my previous response, the AccessDataSource control and the SqlDataSource controls are intended primarily for a RAD approach to development - prototyping, that sort of thing.  They are very easy to use for just dragging and dropping onto Design View and then using the Smart Tag to configure them.  However, for anything more complex, they can be a pain to work with.  The default mode is for them to grab data into a DataSet.  9 times out of 10, you don't need a DataSet, or the extra baggage that comes with it in terms of table and column definitions etc.  Where DataSets can be useful is when you want more than one resultset, and you might want to use Relations to link multiple DataTables within the DataSet, but the DataSource controls will only permit one DataTable to be used. Personally, I can't see the point in the AccessDataSource control at all: http://www.mikesdotnetting.com/Article.aspx?ArticleID=78. I would always use the SqlDataSource control even for Access.

    The article I linked to also shows how to put a connection string into the web.config, and use the AppData folder to ensure that only the name of the database needs to be changed when working with a different one.

    Yolu also need to decide which approach you actually want to take.  If you want a pure code-behind approach, then have a look at the code samples in this article: http://www.mikesdotnetting.com/Article.aspx?ArticleID=26, especially the Select example.  You will see that an OleDbDataReader is used.  You will also see that you can access the values as they are spewed from the Reader.  You can combine that with this: http://www.mikesdotnetting.com/Article.aspx?ArticleID=75 to resolve your immediate requirement.  It's the most "efficient" approach.  Alternatively, you could use a DataSource control, but if I were you, I would save some typing and drag and drop it onto the Designer.

    Hopefully that will get you started, but if not, I'm happy to answer any more questions you might have.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 16, 2008 2:33 PM

All replies

  • User-1956254743 posted

    I think you need to pass the username and password from the login control to the command parameters , i changed the function code 

        Protected Function SiteSpecificAuthenticationMethod(ByVal UserName As String, ByVal Password As String) As Boolean
    MyDataSource.SelectCommand = "select User_Level from User_Names where User_Name = @UserName and Password = @Password "
    ' set the paramters values , it will not set automaticly because you are not using the datasource with a data presentation control
    MyDataSource.SelectParameters("User_Name").DefaultValue = Login1.UserName
    MyDataSource.SelectParameters("Password").DefaultValue = Login1.Password
    Dim rdrSql As OleDbDataReader = DirectCast(MyDataSource.Select(DataSourceSelectArguments.Empty), OleDbDataReader)

    Return rdrSql IsNot Nothing AndAlso rdrSql.HasRows
    End Function

      

    I also used another way in this thread :

    http://forums.asp.net/p/1276534/2427973.aspx#2427973 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 15, 2008 1:39 PM
  • User-1199946673 posted

    This won't return anything, since password is a reserved word, so either change the fieldname or if you use reserved words as field or tablename, you should surround them with brackets:

    SELECT * FROM users WHERE username = ? AND [password] = ?
     Here you can find more information how to connect with Access Databases with ASP.NET
    Monday, December 15, 2008 2:02 PM
  • User559005402 posted

    This won't return anything, since password is a reserved word, so either change the fieldname or if you use reserved words as field or tablename, you should surround them with brackets:

    SELECT * FROM users WHERE username = ? AND [password] = ?
     Here you can find more information how to connect with Access Databases with ASP.NET
     

    Hmm . . .

    Actually it does work as shown except I changed the databind to a SELECT. Here is my code in it's present form. 
        Protected Sub cmdSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
    		Dim myData As New AccessDataSource
    		myData.DataFile = "~/App_Data/Library.MDB"
    		mydata.SelectCommand = "SELECT * FROM users WHERE username = ? AND password = ?"
    		mydata.SelectParameters.Add("username", txtUsername.Text.Trim)
    		myData.SelectParameters.Add("password", txtPassword.Text.Trim)
    		myData.Select(DataSourceSelectArguments.Empty)
    
    		If 1 = 1 Then
    			Session("username") = DirectCast(myData.[Select](DataSourceSelectArguments.Empty), Data.DataView)(0)("username").ToString
    			Session("email") = DirectCast(myData.[Select](DataSourceSelectArguments.Empty), Data.DataView)(0)("email").ToString
    			Session("authlevel") = DirectCast(myData.[Select](DataSourceSelectArguments.Empty), Data.DataView)(0)("authlevel")
    			Label1.Text = DirectCast(myData.[Select](DataSourceSelectArguments.Empty), Data.DataView)(0)("username").ToString()
    			Label2.Text = DirectCast(myData.[Select](DataSourceSelectArguments.Empty), Data.DataView)(0)("authlevel").ToString()
    			Label3.Text = DirectCast(myData.[Select](DataSourceSelectArguments.Empty), Data.DataView)(0)("email").ToString()
    			Response.Redirect("default.aspx")
    		Else
    			Session("authlevel") = 0
    			lblBadLogin.Text = "Sorry, your login was unsuccessful. Please remember that Usernames and Passwords are case sensitive."
    		End If
    
    	End Sub

     Note the condition in the IF statement which is always true. Note also that I am not using the standard login control. My problem is that if I try to run the DirectCast statements when the SELECT did not produce a record, it will error out. So I need a way to determine if the SELECT produced a result (the login is successful.)

     

    Monday, December 15, 2008 6:14 PM
  • User559005402 posted

    I think you need to pass the username and password from the login control to the command parameters , i changed the function code 

        Protected Function SiteSpecificAuthenticationMethod(ByVal UserName As String, ByVal Password As String) As Boolean
    MyDataSource.SelectCommand = "select User_Level from User_Names where User_Name = @UserName and Password = @Password "
    ' set the paramters values , it will not set automaticly because you are not using the datasource with a data presentation control
    MyDataSource.SelectParameters("User_Name").DefaultValue = Login1.UserName
    MyDataSource.SelectParameters("Password").DefaultValue = Login1.Password
    Dim rdrSql As OleDbDataReader = DirectCast(MyDataSource.Select(DataSourceSelectArguments.Empty), OleDbDataReader)

    Return rdrSql IsNot Nothing AndAlso rdrSql.HasRows
    End Function

      

    It seems like OleDbDataReader is not compatible with an AccessDatasource. The Dim rdrSQL statement fails with a message that says it cannot cast a dataview as a datareader.

    Monday, December 15, 2008 6:27 PM
  • User-821857111 posted

    Dim myData As New AccessDataSource
     

    I'm sorry, but this seems a nonsense to me.  Why create an AccessDataSource control in code?  The whole point of these controls is that they are simply design time widgets for RAD development.  They are not meant to be instantiated in code-behind.  Try using normal ADO.NET.  Incidentally, the default behaviour of this control is that it uses a DataSet internally, unless you set it's mode to DataReader (which you should be doing for this usage).


     

    Tuesday, December 16, 2008 2:44 AM
  • User-821857111 posted

    It seems like OleDbDataReader is not compatible with an AccessDatasource. The Dim rdrSQL statement fails with a message that says it cannot cast a dataview as a datareader.
     

    To build on one thing I mentioned in my other reply, the default behaviour is to use a DataSet internally.  More info can be found here: http://www.mikesdotnetting.com/Article.aspx?ArticleID=45

     

    Tuesday, December 16, 2008 2:46 AM
  • User559005402 posted

    Dim myData As New AccessDataSource
     

    I'm sorry, but this seems a nonsense to me.  Why create an AccessDataSource control in code?  The whole point of these controls is that they are simply design time widgets for RAD development.  They are not meant to be instantiated in code-behind.  Try using normal ADO.NET.  Incidentally, the default behaviour of this control is that it uses a DataSet internally, unless you set it's mode to DataReader (which you should be doing for this usage).

    Well excuse me Mike! As a total beginner maybe I'm not smart enough to ask the right question. The reason I am using an AccessDataSource is because I'm accessing Access data. Seems like a natural to me. Why AccessDataSource over OLEDB? Because I don't have to fool around trying to figure out what the proper connection string would be. Also, all the samples in you blog are based on markup code not code behind so they are no help. Now, perhaps the right question should be  - - - If you need to extract data from an Access.mdb using code behind, what is the best and most efficient method given all the new tools, classes, and processes available in NET?

    Thanks

    Tuesday, December 16, 2008 10:27 AM
  • User-821857111 posted

    Well excuse me Mike! As a total beginner maybe I'm not smart enough to ask the right question.
     

    Oops.I could have phrased what I said in a better way, couldn't I?  I owe you an apology.

    [:D]

    As I touched on in my previous response, the AccessDataSource control and the SqlDataSource controls are intended primarily for a RAD approach to development - prototyping, that sort of thing.  They are very easy to use for just dragging and dropping onto Design View and then using the Smart Tag to configure them.  However, for anything more complex, they can be a pain to work with.  The default mode is for them to grab data into a DataSet.  9 times out of 10, you don't need a DataSet, or the extra baggage that comes with it in terms of table and column definitions etc.  Where DataSets can be useful is when you want more than one resultset, and you might want to use Relations to link multiple DataTables within the DataSet, but the DataSource controls will only permit one DataTable to be used. Personally, I can't see the point in the AccessDataSource control at all: http://www.mikesdotnetting.com/Article.aspx?ArticleID=78. I would always use the SqlDataSource control even for Access.

    The article I linked to also shows how to put a connection string into the web.config, and use the AppData folder to ensure that only the name of the database needs to be changed when working with a different one.

    Yolu also need to decide which approach you actually want to take.  If you want a pure code-behind approach, then have a look at the code samples in this article: http://www.mikesdotnetting.com/Article.aspx?ArticleID=26, especially the Select example.  You will see that an OleDbDataReader is used.  You will also see that you can access the values as they are spewed from the Reader.  You can combine that with this: http://www.mikesdotnetting.com/Article.aspx?ArticleID=75 to resolve your immediate requirement.  It's the most "efficient" approach.  Alternatively, you could use a DataSource control, but if I were you, I would save some typing and drag and drop it onto the Designer.

    Hopefully that will get you started, but if not, I'm happy to answer any more questions you might have.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 16, 2008 2:33 PM
  • User-821857111 posted

    With OLEDB I used a datareader but I can't seem to figure out how to do it with Access
     

    To help clear up a bit of confusion here - the AccessDataSource also uses OleDb behind the scenes.  It resolves the database path to a valid connection string and does exactly what your OleDb code does, but hides it.

     

    Tuesday, December 16, 2008 2:36 PM
  • User559005402 posted

    OK. I seem to have it working with a combination of markup and code behind. Specifically,

    <asp:SqlDataSource 
    				ID="SqlDataSource1" 
    				runat="server" 
    				ConnectionString="<%$ ConnectionStrings:AccessConnection %>" 
    				ProviderName="<%$ ConnectionStrings:AccessConnection.ProviderName %>" 
    				
    				SelectCommand="SELECT * FROM [users] WHERE (([username] = ?) AND ([password] = ?))" 
    				DataSourceMode="DataReader">
    
    				<SelectParameters>
    					<asp:ControlParameter ControlID="txtUsername" 
    						Name="username" 
    						PropertyName="Text" 
    						Type="String" />
    					<asp:ControlParameter ControlID="txtPassword" 
    						Name="password" 
    						PropertyName="Text" 
    						Type="String" />
    				</SelectParameters>
    
    			</asp:SqlDataSource>
     and  
    	Protected Sub cmdSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
    		Dim oReader As OleDbDataReader = DirectCast(SqlDataSource1.Select(DataSourceSelectArguments.Empty), OleDbDataReader)
    		
    		If oReader IsNot Nothing Then
    			oReader.Read()
    			If oReader.HasRows Then
    				lblBadLogin.Text = "Login was successful."
    				Session("authlevel") = oReader("authlevel")
    				Session("username") = oReader("username")
    				oReader.Close()			' Call Close when done reading.
    				Response.Redirect("default.aspx")
    			Else
    				Session("authlevel") = 0
    				lblBadLogin.Text = "Sorry, your login was unsuccessful. Please remember that Usernames and Passwords are case sensitive."
    			End If
    			oReader.Close()			' Call Close when done reading.
    		Else
    			Session("authlevel") = 0
    			lblBadLogin.Text = "Sorry, your login was unsuccessful. Please remember that Usernames and Passwords are case sensitive."
    		End If
    	End Sub

     Is this what you were suggesting?

    And sorry if I was too quick to take offense. Thanks for your help.

    Tuesday, December 16, 2008 3:52 PM
  • User-821857111 posted

    For the DataSource control option, that looks pretty good to me.  Except that  Access is not case sensitive....


    Tuesday, December 16, 2008 4:08 PM
  • User-1199946673 posted

    And now that you know more about using DataSources with Access, the next step might be to use the build in membership providers with Access to authenticate users...

    Using the Microsoft Access Providers to Replace the Built-In SQL Server Providers

     

    Tuesday, December 16, 2008 7:22 PM