Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ASP.NET Databases

Posted on 2003-12-07
10
303 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
10 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 75 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 75 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This is about my first experience with programming Arduino.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

860 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