Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ASP.NET Databases

Posted on 2003-12-07
10
Medium Priority
?
327 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
[X]
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
  • 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
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 
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

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

721 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