Solved

Using ADO to link SQL Server 7 and MovePrevious

Posted on 2000-04-12
8
257 Views
Last Modified: 2013-12-25
Dear Expert ;
Problem 1
 Assume that I have Customer Table:Cust_ID , Cust_Name. which created in SQL Server7.
And I want to use ADO to link with this table. Please explain me how to link  without Data Source Name.

Problem 2
If I want to use Moveprevious this table.. How to use...

Please answer me...


0
Comment
Question by:u3520384
  • 4
  • 3
8 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 2707225
This should get you started, add a reference to ADO 2.1 (if you have it) or 2.0 if not.

Paste this into a command button click event for example and run it.

Set the database and server names to reflect your situation.

    Dim cnnSQL As New ADODB.Connection
    Dim rstSQL As New ADODB.Recordset
    cnnSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<DatabaseName>;Data Source=<ServerName>"
    cnnSQL.Open
    rstSQL.Open "SELECT * FROM CustomerTable", cnnSQL, adOpenDynamic, adLockOptimistic
    With rstSQL
        .MoveFirst
        msgbox !CustID
        .MoveLast
        msgbox !CustID
        .MovePrevious
        msgbox !CustID
    End With
    rstSQL.Close
    cnnSQL.Close
    Set rstSQL = Nothing
    Set cnnSQL = Nothing
0
 

Expert Comment

by:saile01
ID: 2708046
dim DbDossier As ADODB.Connection
dim rsData As New ADODB.Recordset

Set DbDossier = New ADODB.Connection

DbDossier.Open "uid=sa;pwd=xxx;server=SQLSERVER;" & _
               "driver={SQL Server};database=NameOfDatabase;"

Set rsData = DbDossier.Execute("select * from CustomerTable"

msgbox rsData("Cust_Name")
' show the field Cust_Name


rsData.MoveNext
' go to the next record

.....

where "uid" is the sqlusername
"pwd" is the password for the sqluser
"SQLSERVER" is the name of your sql server
"NameOfDatabase" is the name of the database containt the CustomerTable table


0
 
LVL 43

Expert Comment

by:TimCottee
ID: 2708123
saile01, I can see that you are relatively new to EE so please don't take my comments to heart.

The EE site has a set of guidelines, which are at http://www.experts-exchange.com/info/usrcntr.htm. In brief though it is considered bad practice to submit an "answer" unless you can guarantee that it will solve the questioner's problem. Especially where in this situation you have in fact posted a similar answer to the comment already posted.

To pick up a few points within your suggestion, there is a missing bracket on the Set rsData line, also I think you missed the point of the original questioner, he/she asked to be able to use the .MovePrevious method on the returned recordset. The example that you give will open the default ForwardOnly recordset which will not permit this method to be used.

I would suggest that u3520384 rejects your proposed answer at this stage. This will allow other experts perhaps to "comment" on this question.

Should u3520384 choose at a later time to accept your "comment" as an answer then he/she is at liberty to do that. It may be that he/she prefers my suggestion or that an altogether different one may be thrown up by another expert and chosen in preference to either of ours.

0
 

Expert Comment

by:MIrfan
ID: 2709119
u3520384  to use move previous ,you should consider following things :

1.If you'r Recordset supports only scrolling forward only ,calling move first,move last,or move previous will generate a run-time error.So,make sure your cursor is not forward only,even if you specifed it as adOptimistic.

2.If you request a Client-side recordset and then check the cursor Type property,ADO will report that you are using a Static cursor.

3.Static cursor supports scrolling forward and backward.

4.Static cursors are not updatable in theory.But they can be updatable.If you need to update your recordset,let me know and I will show you how to update with static cursor.It is unclear form ur Question wether you wanna update or not.

5.see the following code where I am using move previous:

Mystring = "SELECT * From CustomerTable"
rsCustomers.Open Mystring,ConDatabase,&_
adOpenStatic

Do while Not RecStCustomers.BOF
 Debug.Print RecStCustomers.Fields ("CustomerName").Value
Loop

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Expert Comment

by:MIrfan
ID: 2709139
I forgot to put Move previous method in the Do While loop.Here is the code again :

Mystring = "SELECT * FromCustomerTable"

RecstCustomers.OpenMystring,ConDatabase,&_ adOpenStatic

Do while Not RecStCustomers.BOF
 Debug.Print RecStCustomers.Fields("CustomerName").Value
  RecStCustomers.MovePrevious
Loop






0
 

Accepted Solution

by:
MIrfan earned 150 total points
ID: 2718314
PROBLEM 1.

Dim ConString As String
  Dim MySQLString As String
  Dim MyConnection As ADODB.Connection
  Dim rsResults As ADODB.Recordset
 
 Set MyConnection = New ADODB.Connection
 Set rsResults = New ADODB.Recordset
 
'Build your connection string with out a DSN.
ConString = "Provider=SQLOLEDB;Data Source=YourServerName;" & _
"Initial Catalog=YourDatabaseName;User ID=YourID;Password=YourPassword;"
 
MySQLString = "select * from CustomerTable"

rsResults.CursorLocation = adUseClient
rsResults.CursorType = adOpenStatic
   
Set rsResults = MyConnection.Execute(MySQLString)

Problem 2:
By default the cursor will be pointing at the first Record.I am doing a check for Begining of File(BOF),which is recomended to do every time you use move Previous.So if you wanna use MovePrevious,use rsResult.MoveLast  first then use Move.Previous. Again read the comments I provide you earlier for using Move.Previous or MoveLast,it all depends on your Cursor.I hope this helps.

rsResults.MoveLast
Do while Not rsResults.BOF
 Debug.Print rsResults.Fields("CustomerName").Value
  rsResults.MovePrevious
Loop











0
 
LVL 43

Expert Comment

by:TimCottee
ID: 2734187
MIrfan:

Please see my comments to Saile01 earlier in this thread, the answer that you have given is substantially similar to that already posted by myself.

u3520384:

Please could you decide what to do with this thread, whilst I am not hassling to get the points, I believe that I have given a valid answer to your question and without anything from you to the contrary then the fact that now two other "New" "Experts" have posted essentially the same thing is a little annoying.
0
 

Expert Comment

by:MIrfan
ID: 2735087
You can have the points TimCotte, my whole purpose was to help u352038.Everyone has a right to post there answers and comments ,you dont have to get annoyed.I dont think our answers are subtantially same.The connection string will be same for everyone who is accessing SQL server ,except for there UID and Pswd.Plus I am using a client-side cursor,where as you are using server side cursor,Iam using a static cursor,you are using a dynamic cursor.And most important you do not perform a BOF check which is very essential when you are using Move Previous.As I see it my answer is complete and solid.I dont wanna get into an argument ,I am just clarifying the statement you claimed earlier about experts are writing the same answer.The spirit of this site is to share knowledge and help out each other.You can have the points and have a nice day & relax.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 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

19 Experts available now in Live!

Get 1:1 Help Now