[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

OLEDB and Getrows

Posted on 2001-07-12
12
Medium Priority
?
649 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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