[RESOLVED] Need help connecting database to VB application

[AdRock952]

Can anyone please help me with connecting my databse to my VB application (not quite finished yet)?

I have the connection string saved as a public constant (in module) called get_customer so i can reuse it, but I am getting errors when running the program.

It works if i use Adodc1.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\FFES.mdb" and Adocdc1.recordsource = "SELECT * FROM Customer" but it still doesn't display in text boxes or labels

If anyone could please have a look and tell me where i'm going wrong iwould be very grateful.
I haven't done anything differently to another vb application i made about a year ago and that still works OK becuase I have compared them side-by-side.

[zubairkhan]

What error u r getting ?. Is error in connecting database or in data retreiving in recordset?

[AdRock952]

datasource name not found and no default driver specified

[techgnome]

TRy using .OPEN on the ADODC after setting the connectionstirng.

-tg

[AdRock952]

TRy using .OPEN on the ADODC after setting the connectionstirng.

-tg

didn't work

[Hack]

didn't work

How did you use it?

Post exactly what you ran as that should work just fine.

[AdRock952]

this is the code for the module
Public Const FFES_CUSTOMER As String = "SELECT [Customer].Customer_ID, [Customer].Customer_Name, [Customer].Contact_Name, [Customer].Address1, [Customer].Address2, [Customer].County, [Customer].Postcode, [Customer].Telephone_No, [Customer].Email FROM [Customer]"
Public get_customer As String
'Public Const MyHelpFile As String = App.Path & "\help.htm"
Sub Main()
'path to the database (saves typing in each time connection to database is required)
get_customer = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\FFES.mdb"
End Sub

This is the code I am using in the form load
Public Sub EditCustomer()
Unload frmPrinted
frmCustomer.Show
frmCustomer.SetFocus
ClearCustomer
frmCustomer.fraList.Caption = "Edit Customer"
frmCustomer.fraNew.Caption = "New Customer Details"
frmCustomer.fraConfirm.Caption = "Confirm Customer Details"
'Database connection
frmCustomer.Adodc2.ConnectionString = get_customer
frmCustomer.Adodc2.CommandType = adCmdUnknown
frmCustomer.Adodc2.RecordSource = FFES_CUSTOMER
Set frmCustomer.dgdCustomer.DataSource = frmCustomer.Adodc2
frmCustomer.txtCustomerName.DataField = "Customer_Name"
frmCustomer.txtContactName.DataField = "Contact_Name"
frmCustomer.txtAddress1.DataField = "Address1"
frmCustomer.txtAddress2.DataField = "Address2"
frmCustomer.txtCounty.DataField = "County"
frmCustomer.txtPostcode.DataField = "Postcode"
frmCustomer.txtTelephone.DataField = "Telephone_No"
frmCustomer.txtEmail.DataField = "Email"
frmCustomer.Adodc2.Refresh

End Sub

I have done this before with no problems so I don't understand why it's not working

[Hack]

Set a reference to the Microsoft ActiveX Data Objects X.X Library (where X.X is the version on your machine

Option Explicit
 
PUblic ADOCn As ADODB.Connection
Public ConnString As String
 
 
Public Sub ConnectToDb
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0; _
        Data Source=" & App.Path & "\FFES.mdb";" & _
        "Persist Security Info=False"
 
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
End Sub
 
Private Sub Form_Load()
ConnectToDb
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
'close the database connection
ADOCn.Close
'destroy it
Set ADOCn = Nothing
End Sub

Your database is now open, and you have a public connection object (ADOCn) which can be used anywhere, on any form, in your project. It will remain open for as long as your program is running. When your program shuts downs, it will close the connection for you.

[AdRock952]

Thanks...it no longer crashes but i have another question.
How do I add the database to a datagrid
Do i still do

ADOCn.CommandType = adCmdUnknown
ADOCn.RecordSource = "SELECT * FROM Customer" 'or whatever it is
Set DataGrid1.DataSource = ADOCn
ADOCn.Refresh

[Hack]

Thanks...it no longer crashes but i have another question.
How do I add the database to a datagrid
Do i still do

ADOCn.CommandType = adCmdUnknown
ADOCn.RecordSource = "SELECT * FROM Customer" 'or whatever it is
Set DataGrid1.DataSource = ADOCn
ADOCn.Refresh

I haven't used data bound controls of any type for years and years, so I'm not completely sure. Did you try this to see if it still works, and if it doesn't, what it happening.

[AdRock952]

I got the connection working in the end.

But do you know how I can get a record of the datagrid to display in labels when i click on a record on the datagrid.

I can display a record in the labels but i want it so when i click on the right side of the datagrid the labels change

[zubairkhan]

write following code on datagrid CLICK event

for instance

label1.caption = myRs!name