Solved

OLEDB and Getrows

Posted on 2001-07-12
12
595 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now