Nairaland Forum

Welcome, Guest: Join Nairaland / Login / Trending / Recent / New
Stats: 1236611 members, 1645955 topics. Date: Saturday, 19 April 2014 at 11:11 PM

Connecting To A Database Using Visual Basic - Programming - Nairaland

Nairaland Forum / Science/Tech / Programming / Connecting To A Database Using Visual Basic (42979 Views)

[Help Request]How Do I Upload Images Into My Database using Visual Basic? / Connecting To A Remote Database / ''no Row At Position 0'' When Retrieving Image From An Sql Database Using Vb.net (1) (2) (3) (4)

(0) (Reply) (Go Down)

Connecting To A Database Using Visual Basic by Zule(m): 4:16pm On May 30, 2006
Please, can anyone help me by giving out a complete tutorial on the step by step way a connecting to a database using VB?
Re: Connecting To A Database Using Visual Basic by smartsoft(m): 12:04pm On May 31, 2006
Create a datasource name for your Access database

Set db = DBEngine.Workspaces(0).OpenDatabase("dbsourcename", ,"DSN=dbsourcename;UID=MyUID;PWD=Mypassw"wink
============================================================
This is for SQL SERVER.

You can do that in the same way it is done in VB6 or any other languages.
Here is a quick sample:

NOTE: in Excel in your VB6 code editor go to Tools >> References and select MS ADO 2.x library before running this code

visual basic code:--------------------------------------------------------------------------------
Option Explicit

Dim adoConn As ADODB.Connection
Dim strConString As String

Private Sub Worksheet_Activate()
'================================
Dim adoRST As ADODB.Recordset
Dim strSQL As String

strConString = "Provider=SQLOLEDB.1;Initial Catalog=smartsoft;" _
& "User ID=sa;Password=password;Persist Security Info=True"

Set adoConn = New ADODB.Connection
adoConn.Open strConString

Set adoRST = New ADODB.Recordset
strSQL = "Select * from smartsoft"

adoRST.Open strSQL, adoConn

Do While Not adoRST.EOF
'populate your cells here
Loop

End Sub
Re: Connecting To A Database Using Visual Basic by parosky(m): 12:16pm On May 31, 2006
There are various ways by which you canconnect to a DB. If you are new to VB database programming, I will advise you to use Active X Data Object(ADO). There are 2 means of achieving this.
1. Through a ADO data control (Adodc). Place the adodc on your form and click the ellipses next to the connectionstring property. On the Provider tab of the window shown, select your data provider, e.g select Microsoft Jet OLEDB 3.51(or 4.0) depending on the version of microsoft access you are using, that is if you are connecting to microsoft Acess. On the connection tab, use brose to navigate to the path of your database (the access file*.mdb) This is the simplest you need to do, then click on Test Connection button to see you are connected to your database). Then set the Record source properties to indicatewhether you recordset will be from a table, stored procedure or a sql command.  You can then bind controls to displayvalue of each field that will be exposed by your recordset. NB Adodc is not in your tool box by default. You need to add it ( click project>> components then select Microsoft ADO DataControl 6.0 (OLEDB). then click apply button and OK. This show the adodc in your toolbox.

2. Similar to the first but gives you a whole lot of control over your data is to program directly against the ADO Object. You will not need to use the Adodc but you sould set a reference to an ADO object. Click Project >>Reference.
Then
(i) Declare an object variable whose type is ADODB.Connection in the general section of you code or module e.g Dim myConn as
  ADODB.Connection
.  then create the object by writing Set myConn = new ADODB.Connection
ii)Set the Connection object’s ConnectString property to reflect a valid OLE DB provider, or specify the provider string in the next step, as follows:
myConn .ConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;"
iii)Call the Connection object’s Open method. If you didn’t specify the ConnectString property in the preceding step, you need to pass the connection string as the first argument to the Open method, as in the following example. Better still reproduce the following format ( to reduce my typing explanations actually)
dim dbPath as string
myConn.Provider=“Provider=Microsoft.Jet.OLEDB.3.51;”
dbPath="c:\my documents\myDB.mdb"
myConn.Open dbPath

This connect you to the database against which you can now give query to retrieve your recordset. But I feel you are new to database programming. You can ask some more question to let me know I've been helpful and to tell me area where you need more help. I'll check back tommorrow
Re: Connecting To A Database Using Visual Basic by Zule(m): 5:55pm On Jun 07, 2006
parosky:

There are various ways by which you canconnect to a DB. If you are new to VB database programming, I will advise you to use Active X Data Object(ADO). There are 2 means of achieving this.
1. Through a ADO data control (Adodc). Place the adodc on your form and click the ellipses next to the connectionstring property. On the Provider tab of the window shown, select your data provider, e.g select Microsoft Jet OLEDB 3.51(or 4.0) depending on the version of microsoft access you are using, that is if you are connecting to microsoft Acess. On the connection tab, use brose to navigate to the path of your database (the access file*.mdb) This is the simplest you need to do, then click on Test Connection button to see you are connected to your database). Then set the Record source properties to indicatewhether you recordset will be from a table, stored procedure or a sql command. You can then bind controls to displayvalue of each field that will be exposed by your recordset. NB Adodc is not in your tool box by default. You need to add it ( click project>> components then select Microsoft ADO DataControl 6.0 (OLEDB). then click apply button and OK. This show the adodc in your toolbox.

2. Similar to the first but gives you a whole lot of control over your data is to program directly against the ADO Object. You will not need to use the Adodc but you sould set a reference to an ADO object. Click Project >>Reference.
Then
(i) Declare an object variable whose type is ADODB.Connection in the general section of you code or module e.g Dim myConn as
ADODB.Connection
. then create the object by writing Set myConn = new ADODB.Connection
ii)Set the Connection object’s ConnectString property to reflect a valid OLE DB provider, or specify the provider string in the next step, as follows:
myConn .ConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;"
iii)Call the Connection object’s Open method. If you didn’t specify the ConnectString property in the preceding step, you need to pass the connection string as the first argument to the Open method, as in the following example. Better still reproduce the following format ( to reduce my typing explanations actually)
dim dbPath as string
myConn.Provider=“Provider=Microsoft.Jet.OLEDB.3.51;”
dbPath="c:\my documents\myDB.mdb"
myConn.Open dbPath

This connect you to the database against which you can now give query to retrieve your recordset. But I feel you are new to database programming. You can ask some more question to let me know I've been helpful and to tell me area where you need more help. I'll check back tommorrow
sorry, for not replying as at when due it was owing to the fact that i've been very very busy with my school.
As you said i'm a newbie vb. and i will also like to know if u have any e-book on vb
do u?
IF reply= true then

pls, send it to my box

Else

End If
once again i would like to thank u for doing me that favour
Re: Connecting To A Database Using Visual Basic by parosky(m): 10:01pm On Jun 09, 2006
You don't seem like a newbie cos I've seen some of your contributions. I'm still looking for your email adddress so I can attach one to you or through this site, I'm not browsing on my system now. Take care.
Re: Connecting To A Database Using Visual Basic by Zule(m): 7:06pm On Jun 12, 2006
Posted on: June 09, 2006, 10:01:09 PMPosted by: parosky
Insert Quote
You don't seem like a newbie because I've seen some of your contributions. I'm still looking for your email adddress

@ parosky your right in what u said but one must always lets him self down in ohter to learn from the people above him
any way this is mail email address i am waiting for the e-books to be farwarded thanks
Re: Connecting To A Database Using Visual Basic by parosky(m): 2:05pm On Jun 16, 2006
Zule:

@ parosky your right in what u said but one must always lets him self down in ohter to learn from the people above him

Well nobody knows it all. In a forum like this what you gain will depend on what people percieve as the level you are. If you make people believe you are totally new they will be giving you low stuffs. Let people know where you are so that the vibrations will be at a level that will benefit you, not that we'll be telling a guru who is being "humble" about variable declaration.
I'm still looking for your email address.
Re: Connecting To A Database Using Visual Basic by Zule(m): 3:12pm On Jun 17, 2006
ok,
parosky:

Well nobody knows it all. In a forum like this what you gain will depend on what people percieve as the level you are. If you make people believe you are totally new they will be giving you low stuffs. Let people know where you are so that the vibrations will be at a level that will benefit you, not that we'll be telling a guru who is being "humble" about variable declaration.
I'm still looking for your email address.
@parosky my email address is( zule_jojo at yahoo dot co dot uk)
Re: Connecting To A Database Using Visual Basic by larryshow: 9:03am On Jul 18, 2006
hellooo seniors, though am new here but yet i will like to contribute . i hv been looking toward an opportunity like this where by nigerians will be able to express and share ideals. its good to hnow something like this exist my contribution is attached.

Re: Connecting To A Database Using Visual Basic by parosky(m): 10:07am On Jul 18, 2006
Larryshow
I was only able to download a blank word document. May be you'll help attach it again or just copy and past it directly. At times opening attachment is a distraction to some people
Re: Connecting To A Database Using Visual Basic by dag(m): 2:53pm On Sep 01, 2006
hi people
the lot posted so far is a good primer for database connectivity, but going up from here to where the technology is heading u really have to understand what microsoft wants u to know. OOP is hoopla of the developers world and all that has been said is an implementation of certain classes represented in the ADO Object model. so its all in the model, and the model for ADO is smply
1.connection object
2.command object
3.recordset object

all conn to a database wld basically obey this model, like it has been correctly outlined in earlier postings, if u look carefully ull observe the truth about the model. so just get the model into ur head and ur good to go, study the properties and methods exposed by these objects and ull better understand what they do best and get intouch with oop its the koko for developers.
Re: Connecting To A Database Using Visual Basic by femi_ko(m): 3:04pm On Mar 11, 2008
hope u got the problem solved
Re: Connecting To A Database Using Visual Basic by abhi_cool: 6:28pm On Mar 23, 2013
erm... saw the posts... i am a beginner at databases programming and am trying to make up a project using databases. i did it using adodc.. here is what steps i followed...
1.added an OLEDB component to my form named it as newcon, set its ConnectionString property as "Provider=Microsoft.Jet.OLEDB.4.0;userID=Admin;Data Source=" &App.Path & "\newdb.mdb; Persist security info=False"
2. set CommandType as 2-adCmdTable
3.set Record Source as emp1(here emp1 is the table with 3 fields namely ID, salary and empname)
4. took 3 textboxes with data source as newcon and attached each with an individual field of the table.
5.included a command button for submit.
6. typed the following code

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Command1_Click()
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & App.Path & "\newdb.mdb;Persist Security info=False"wink
con.Execute ("insert into emp1 values('" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "')"wink
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
End Sub

Private Sub Form_Load()
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
End Sub


on executing this i am persistantly getting "data type mismatch in criteria expression" as a runtime error..
what can be the problem?? please help its frustating...
 
Re: Connecting To A Database Using Visual Basic by adebayosun02: 11:25pm On Aug 23, 2013
Good day please great programmer

Am a beginner on Vb 2010, please can some one help me with a code to use in counting the number of numeric value between

between two numeric variables using vb.

(0) (Reply)

I Want To Learn Programming. Which Language Should I Start With? / How To Deface A Website With Basic HTML Coding In Just 5 Minutes

(Go Up)

Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health
religion celebs tv-movies music-radio literature webmasters programming techmarket

Links: (0) (1) (2) (3) (4) (5) (6) (7) (8) (9)

Nairaland - Copyright © 2005 - 2014 Oluwaseun Osewa. All rights reserved. See Nairalist and How To Advertise
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.