Solved

database connectivity

Posted on 2002-04-06
17
198 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 40 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
 

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 49

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

 
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

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

Join & Write a Comment

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

760 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

23 Experts available now in Live!

Get 1:1 Help Now