• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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.


0
icx
Asked:
icx
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
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();
0
 
Arthur_WoodCommented:
are you using ORACLE , SQL Server or Access for your backend?

AW
0
 
cookreCommented:
I presumed icx was going against an Access DB because of the *.mdb, but am of no real help wrt ASP.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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.
0
 
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

0
 
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.
0
 
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.
0
 
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()

0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now