?
Solved

OLEDB and Getrows

Posted on 2001-07-12
12
Medium Priority
?
642 Views
Last Modified: 2012-05-04
Both 4GuysFromRolla and Charles Carroll recommend using OLEDB instead of ODBC and a DSNless connection.  Carroll also recommends using GetRows method to grab all the data at once and then parse them.  In his example for GetRows, however, Carroll uses DSN connection!  Being new to database, I do not have expertise to combine the two examples for a ASP-based DSNless OLEDB connection to Access database using GetRows method.  I am looking for a complete example code. Thanks.
0
Comment
Question by:Venkatagiri
[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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 12

Expert Comment

by:Paurths
ID: 6278816
hi Venkatagiri,

this page holds lots of examples for ASP:
http://www.asp101.com/samples/index.asp


and here is an example of 'GetRows'
http://www.asp101.com/samples/db_getrows.asp

hope this helps,
Ricky
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6278850
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6278963
also DSNless:

Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
file=server.MapPath("yourdatabasename.mdb")
dbGlobalWeb.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="&file
0
Industry Leaders: 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 5

Expert Comment

by:dgorin
ID: 6279159
GetRows() is an ADO function and works on the ADO recordset.  You can create ADO recordsets using many different connection strings.  Typically the connection method has no impact on the GetRows method.

By the time you get your ADO recordset, you really don't care how the connection was established (with a few exceptions).
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6280348
i'm kind of wondering why they recommend using a DSNless connection...
0
 
LVL 5

Expert Comment

by:dgorin
ID: 6281482
DSNless usually are a bit faster than DSN connections, and you can implement a DSNLess connection without bothering the server admin.

DSN's provide additional security in that the connection information isn't exposed in your code.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6281611
dgorin,

true about 'not bothering the server admin'

but still u need to have write/delete/execute permission on the directory where the db is stored.
Which then becomes hazardous, as u stated with exposure,  if someone retrieves the the path.
0
 

Author Comment

by:Venkatagiri
ID: 6281699
Thanks for your quick responses.  As I said before, I am a novice when it comes to databases.  The following code, which I put together from looking at several examples, does not work("one or more required parameters missing" error).  Can you debug it?

SQLstmt = "SELECT * FROM tblProducts WHERE " & SQLstmt
                                     ' Retrieve products using DSN-less database connection
     dim connTemp, rsTemp
     connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../database/mydb1.mdb") & ";Persist Security Info=False"
     set connTemp=server.createobject("adodb.connection")
     connTemp.open connStr
     set rsTemp=connTemp.execute(SQLstmt)

     If NOT rsTemp.EOF Then                    ' Get the number of items matching the search criteria
          AllData=rsTemp.getrows               ' Get all the data at one time
          NumRows=ubound(alldata,2)          ' Number of data records in AllData
     Else
          NumRows = 0                              ' SQL statement returned no data
     End if
     rsTemp.close
     set rsTemp=nothing
     connTemp.close
     set connTemp=nothing

0
 
LVL 12

Accepted Solution

by:
Paurths earned 600 total points
ID: 6281733
hi Venkatagiri ,
This is definitly wrong:
SQLstmt = "SELECT * FROM tblProducts WHERE " & SQLstmt


this might be a solution:
'Textfield
SQLstmt = "SELECT * FROM tblProducts WHERE ProductName = '" & SQLstmt & "'"

'Numeric field
SQLstmt = "SELECT * FROM tblProducts WHERE ProductID = " & SQLstmt


u set a criteria, but u did not clarify on what field the criteria should work.

cheers
Ricky
0
 

Author Comment

by:Venkatagiri
ID: 6282432
Oops, sorry, I did not include the previous two lines that were necessary to make sense of the code:

strType = Request.QueryString("Type")
SQLstmt = " fldType='" & strType & "'"

SQLstmt = "SELECT * FROM tblProducts WHERE " & SQLstmt
                                    ' Retrieve products using DSN-less database connection
    dim connTemp, rsTemp
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../database/mydb1.mdb")
& ";Persist Security Info=False"
    set connTemp=server.createobject("adodb.connection")
    connTemp.open connStr
    set rsTemp=connTemp.execute(SQLstmt)

    If NOT rsTemp.EOF Then                    ' Get the number of items matching the search criteria
         AllData=rsTemp.getrows               ' Get all the data at one time
         NumRows=ubound(alldata,2)          ' Number of data records in AllData
    Else
         NumRows = 0                              ' SQL statement returned no data
    End if
    rsTemp.close
    set rsTemp=nothing
    connTemp.close
    set connTemp=nothing

Needless to say that the above code does not work but I do not know why.
0
 

Author Comment

by:Venkatagiri
ID: 6282592
I figured out what was wrong with my code.  The "recordset object" was missing among several other problems.  Thanks for all of your input.  4GuysFromRolla (http://www.4guysfromrolla.com/new/) have a number of articles on OLEDB, DSN and other issues.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7087135
Hi Venkatagiri,
You've requested to delete this question, but its status has remained as 'Pending Delete' because one or more comments have been added.  Normally, the only way to fully delete such a Question is to post a message to Community Support and ask for assistance.

EE is making a one-time database sweep to purge the Pending Delete Questions automatically.  During this sweep:

    Venkatagiri -- To allow the deletion to proceed:  Do nothing.
    EXPERTS -- Please DON'T POST a comment except to contest this deletion.

In the future, please refer to http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp#8 for instruction on deleting questions.

DanRollins -- EE database cleanup volunteer
0

Featured Post

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!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 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