Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

database connectivity

Posted on 2002-04-06
17
Medium Priority
?
207 Views
Last Modified: 2010-05-02
Hello ,
can anybody tell me how to connect to databse like SQL . as i am really new in this area please explain me with the code. that would be appreciated.

Thanks,
shri
0
Comment
Question by:shrihalbe
  • 8
  • 4
  • 3
  • +2
17 Comments
 
LVL 1

Accepted Solution

by:
justinhemphill earned 160 total points
ID: 6922791
You need to first create a connection to the database (Don't forget to add a reference to ADO from the menu):

Dim cnn as ADODB.connection
Set cnn = New ADODB.Connection
cnn.Open "Data Source = myServer;Initial Catalog=Northwind;Integrated Security=True"

where myServer is the name of the machine on which SQL Server is installed and Initial Catalog is the database that you would like to open.

Next you need to open a recordset on that connection:

Dim rstMyRecordset as ADODB.Recordset
Set rstMyRecordset = New ADODB.Recordset
rstMyRecordset.Open "SELECT * FROM Customers",cnn

Then to access the records in the table:

Msgbox rstMyRecordset("CustomerID")
rstMyRecordSet.MoveNext
Msgbox rstMyRecordset("CustomerID")
etc.
0
 
LVL 1

Expert Comment

by:surajdeo
ID: 6922836
hi,

  download the simple example code from follow site,

  http://suraj_deo_singh.tripod.com/visualbasic/DataBase.zip
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6923116
Please maintain your open questiosn.  For the record:

Questions Asked 72
Last 10 Grades Given A A A A A A A A A A  
Question Grading Record 59 Answers Graded / 59 Answers Received

Anthony
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:shrihalbe
ID: 6923411
Hello
  can you tell me how to insert delete and update records in the Database using visual basic code.

Thanks,
shri
0
 

Author Comment

by:shrihalbe
ID: 6923414
Hello : justinhemphill ,

what is the meaning of the "reference to ADO from the menu"
and what is ADO ? please let me know

Thanks,
shri
0
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 6924663
Hi shrihalbe,

See this:

http://www.microsoft.com/data/ado/default.htm

> "what is the meaning of the "reference to ADO from the menu"

Go to the Project > References, then select M$ ActiveX Data Object 2.5 Library to add the reference for ADODB objects.

You can also see the connection string setting at: http://www.able-consulting.com/ADO_Conn.htm

Cheers

0
 

Author Comment

by:shrihalbe
ID: 6925937
Hello ,
i got folowing code
can aybody explain me following part of the code

what is meaning of
  RS.CursorLocation = adUseClient
  RS.CursorType = adOpenStatic
Set RS.ActiveConnection = Nothing

please explain me above lines in the code.



Dim bConnOpen As Boolean
  Dim C As ADODB.Connection
  Dim RS As ADODB.Recordset
 
  On Error GoTo EH
 
  Set C = New ADODB.Connection
 
  C.Open "Provider=SQLOLEDB;Server=Planet-Notes1;Database=ScanTracker;UID=ScanTracker;PWD=ScanTracker1;Application Name=ProjectDB"
 
  bConnOpen = True
 
  Set RS = New ADODB.Recordset
 
  RS.CursorLocation = adUseClient
  RS.CursorType = adOpenStatic
 
  RS.Open sSQL, C
 
  Set RS.ActiveConnection = Nothing
 
  C.Close
 
  bConnOpen = False
 
  Set C = Nothing
 
  Set ExecSQL = RS
 
  Set RS = Nothing
 
  Exit Function
 
EH:

  If (bConnOpen) Then C.Close
  Set C = Nothing
 
  MsgBox "Error", vbCritical
 
thanks,
shrirang
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6925984
So I am to understand that you plan to leave your questions open?

Anthony
0
 
LVL 1

Expert Comment

by:justinhemphill
ID: 6925988
It looks like you are trying to create a disconnected recordset.

adUseServer (the default) use the cursor object on the server side (in this case, either supplied by the database or ASP), whereas the adUseClient uses a client side cursor. With adUseClient, the recordset becomes read only, so you cannot update. For some queries, this may be
acceptable.

There are also limitations to adUseClient. You must use a static cursor (once again, not usually a problem) and you cannot lock pessimistically.

If you open a dynamic recordset, it is basically a 'live' look at the database.  For example, say you execute a query "select CompanyName, AccountBalance FROM Accounts" that returns 100 records.  While you are moving through the recordset, a transaction generated in another application modifies the account balance for the 50th record.  The value of rs.Fields("AccountBalance") for that record is updated with the new information without requiring a Requery.

For the static recordset, all of the data in the recordset is moved from the server to the client at the time the recordset is created.  If the underlying data changes, the recordset is not impacted.  And no further calls are required to the server regardless of the movement through the recordset.
0
 

Author Comment

by:shrihalbe
ID: 6925991
no i am going to close soon

thaks,
shri
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6926006
So that there is no misunderstanding as to what I am taking about, here is a list of your open questions:

Background image ht and wd Date: 02/05/2002 03:14PM PST  
http://www.experts-exchange.com/jsp/qShow.jsp?ta=html&qid=20263560
Hyperlink Date: 01/28/2002 10:38AM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20260363
scheduled agent Filecopy Date: 08/15/2001 01:21PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20169099
Domino.doc Date: 08/08/2001 07:06AM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20165229
PDF Date: 06/26/2001 11:10AM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20141887
Acessing Address Book Date: 12/19/2000 08:01AM PST  
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20008402
creation date Date: 12/01/2000 09:16AM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=12044479
R5 installation on machine  having 4.6 Date: 11/16/2000 12:48PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=11891580

EE Moderators have left you messages on each and everyone of those questions and you have ignored them. This is a variations of the message sent to you by the EE Moderator:

<quote>
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize
these if still open in seven days.  Please post closing recommendations before that time.

Question(s) below appears to have been abandoned. Your options are:

1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the
participants why you wish to do this, and allow for Expert response.  This choice will include a refund
to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information
outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
  --> Post comments for expert of your intention to delete and why
  --> You cannot delete a question with comments, special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL
(question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer
process for further information, if needed.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and keep them all current with updates as the
collaboration effort continues, to track all your open and locked questions at this site.  If you are
an EE Pro user, use the Power Search option to find them.  Anytime you have questions which are LOCKED
with a Proposed Answer but does not serve your needs, please reject it and add comments as to why.  
In addition, when you do grade the question, if the grade is less than an A, please add a comment as
to why.  This helps all involved, as well as future persons who may access this item in the future to
seek help.
</quote>

Anthony
0
 

Author Comment

by:shrihalbe
ID: 6926008
Hello justinhemphill ,
 means static and disconnected recordset are same or different. please let me know.

THanks for your Help

Shri
0
 
LVL 1

Expert Comment

by:justinhemphill
ID: 6926110
A static recordset still has a connection established with the database even though you do not have a live view of the data, so you can issue update commands.  A disconnected recordset no longer has any such connection.  In this case, a connection would have to be re-established in order to update the database.
0
 

Author Comment

by:shrihalbe
ID: 6926215
Hello justinhemphill
is it possible to establish connection for disconnected recordset.

Thanks,
shri
0
 
LVL 1

Expert Comment

by:justinhemphill
ID: 6929083
rs.ActiveConnection = C
rs.Update
0
 

Author Comment

by:shrihalbe
ID: 6929436
hello justinhemphill

what is the meanning of ActiveConnection
THanks,
shri
0
 

Author Comment

by:shrihalbe
ID: 6934273
THanks justinhemphill ,

Appreciated for ur help

THanks,
shri
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

824 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