OLEDB and Getrows

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.
VenkatagiriAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaurthsConnect With a Mentor Commented:
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
 
PaurthsCommented:
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
 
PaurthsCommented:
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
PaurthsCommented:
also DSNless:

Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
file=server.MapPath("yourdatabasename.mdb")
dbGlobalWeb.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="&file
0
 
dgorinCommented:
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
 
PaurthsCommented:
i'm kind of wondering why they recommend using a DSNless connection...
0
 
dgorinCommented:
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
 
PaurthsCommented:
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
 
VenkatagiriAuthor Commented:
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
 
VenkatagiriAuthor Commented:
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
 
VenkatagiriAuthor Commented:
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
 
DanRollinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.