ASP.NET Databases

Hi folks,

In ASP, it used to be nice and easy to connect to a database, open a recordset and loop through all the records to - for example - match a record with an item on a form, or a string of some kind.

In ASP.NET however, it doesn't seem there is an easy way to do this. For example, let's say I have a table of users, when someone connects to my server I want them to fill out a form. If the name matches one on my database, redirect to page X, otherwise page Y.

All of the .NET asp articles I find seem to relate to displaying data to the end-user. But what if I don't want to show them the results, I just want to work with a database?

Here's the old code I'm talking about:

Dim Conn      
      Set Conn = Server.CreateObject("adodb.connection")
      Conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & myPath & ";"      
      Dim RS
      Set RS = Server.CreateObject("ADODB.Recordset")
      
      SQL = "SELECT * FROM Users"      
      rs.Open sql, conn, 3, 2

               Do WHile Not RS.EOF

                   'Do something

               Rs.MoveNext
               Loop

ANY Suggestions please?! Thank you.


LVL 1
icxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cookreCommented:
Alas, I don't know the capabilities of ASP.NET in this regard, but, perhaps this snippit of C#.NET will help

OleDbConnection DBConn=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
                                                                      Data Source="+DBPath+"\\MigPkg.mdb");

OleDbCommand DBCmd=new OleDbCommand("select * from ManagerLocs",DBConn);
DBConn.Open();
OleDbDataReader DBReader=DBCmd.ExecuteReader();
DBReader.Read();

// GetString(i) gets the i-1th field
TestManagerShare=DBReader.GetString(0);
TestManagerDirectory=DBReader.GetString(1);
CertManagerShare=DBReader.GetString(2);
CertManagerDirectory=DBReader.GetString(3);
CertManagerObjDir=DBReader.GetString(4);
ProdManagerShare=DBReader.GetString(5);
ProdManagerDirectory=DBReader.GetString(6);
DBReader.Close();
DBConn.Close();

// Get list of pending migrations
DBCmd.CommandText="select * from Migrations";
DBConn.Open();
DBReader=DBCmd.ExecuteReader();
while (DBReader.Read())
       {
       // ZSERVICE
       TmpStr=DBReader.GetString(0).ToString()+"                                                  ";
       BldLine=TmpStr.Substring(0,40);
       // CR
       TmpStr=DBReader.GetString(1).ToString()+"                    ";
       BldLine=BldLine+TmpStr.Substring(0,20);
       // From loc
       TmpStr=DBReader.GetString(2).ToString();
       if (TmpStr.Substring(0,1)=="T") TmpStr="Test->Cert";
                                 else  TmpStr="Cert->Prod";
       BldLine=BldLine+TmpStr+" ";
       // PendingSince
       BldLine=BldLine+DBReader.GetDateTime(5).ToString();
       this.lbDefaultReq.Items.Add(BldLine);
       }
this.lbDefaultReq.Refresh();
DBReader.Close();
DBConn.Close();
this.lbChosenReq.Items.Clear();
this.lbChosenReq.Refresh();
Arthur_WoodCommented:
are you using ORACLE , SQL Server or Access for your backend?

AW
cookreCommented:
I presumed icx was going against an Access DB because of the *.mdb, but am of no real help wrt ASP.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

icxAuthor Commented:
In ASP, it was a simple matter to choose whether to use Access or SQL - all you really had to modify was the connection string.

For this example, however, let us assume we're connecting to an SQL Database since that is the backend for most of my projects.

To cookre: thanks for your help, however C# isn't much use to me. It's gotta be ASP.NET using VB.NET.
Your time is appreciated though. Sorry, I should have stated that in my description.
Arthur_WoodCommented:
in VB.NET (with the appropraite References)

Dim  DBConn as OLEDCConnection =new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
                                                                      Data Source="+DBPath+"\\MigPkg.mdb");

Dim DBCmd as OleDbCommand=new OleDbCommand("select * from ManagerLocs",DBConn);
DBConn.Open();
Dim  DBReader as OleDBDataReader =DBCmd.ExecuteReader();
while DRREaders.Read()

    ' do something with the fields of each record, ONE RECORD per Read

End While
DBConn.Close()


AW

ovidiucraciunCommented:
Hi icx,

ASP.NET it should be easier to do the task not harder and indeed is :)
all it did for you is to come up with a managed API and to separate the code from the HTML/DHTML. well it add some more kul things but in your case it should affect you. In asp.net is the same if you wanna go against a SQL Server or a Access all you have to do is to change the connection string.

It looks like Arthur answered to your question.
I want to make one suggestion though.  the query you execute can be

"if exists (select * from ManagerLocs where UserName='" + strUserName + "') select 'LOGGED IN' else select 'ACCESS DENIED'"

where strUserName is a string variable in which you keep the user name entered by the client; if as result you get 'LOGGED IN' it means the user entered the correct data. note that this way you can verify more then one field. advantages? speed. why? the loop is done on the server side and the round trip to the database server and IIS server contains only a string ('LOGGED IN' or 'ACCESS DENIED') insted of the entire table.

Enjoy,
O.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
icxAuthor Commented:
Hi to Arthur_Wood and Ovidiucraciun,

Many thanks for both your input. I have yet to test these out, but will try to do so tomorrow and award points as quickly as possible.

Both your inputs are valued and appreciated.

Thank you,

Richard.
icxAuthor Commented:
OK,

I'm afraid Arthur's code did not work. Generated all kinds of errors. However, it did point me in the right direction: using DataReaders is - it seems - just what I needed.

Ovidiucraciun - your comment about the manner in which the query is executed was useful to me.

As neither of you solved the problem for me individually, I will split the points between you (75pts each). I hope this is satisfactory.

For the benefit of other readers, I have posted the solution I found below:

        Dim dtrUsers As System.Data.SqlClient.SqlDataReader
        SqlConnection1.Open()

        dtrUsers = SqlCommand1.ExecuteReader

        While dtrUsers.Read

            If LCase(dtrUsers("Username")) = txtUsername Then
                Response.Write("The user has been matched, successfully.")
            End If

        End While

        SqlConnection1.Close()

Arthur_WoodCommented:
>As neither of you solved the problem for me individually, I will split the points between you (75pts each). I hope this is satisfactory.


well, go ahead and accept an answer, and SPLIT the points.

AW
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.