Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ASP.NET Databases

Posted on 2003-12-07
10
Medium Priority
?
333 Views
Last Modified: 2010-04-17
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
Comment
Question by:icx
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 22

Expert Comment

by:cookre
ID: 9894039
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9894258
are you using ORACLE , SQL Server or Access for your backend?

AW
0
 
LVL 22

Expert Comment

by:cookre
ID: 9894312
I presumed icx was going against an Access DB because of the *.mdb, but am of no real help wrt ASP.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:icx
ID: 9894911
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
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 225 total points
ID: 9897445
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
 
LVL 3

Accepted Solution

by:
ovidiucraciun earned 225 total points
ID: 9900472
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
 
LVL 1

Author Comment

by:icx
ID: 9900720
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
 
LVL 1

Author Comment

by:icx
ID: 9903667
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10316423
>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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Progress
Screencast - Getting to Know the Pipeline

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question