Adding to Recordset using ADO not working.....HELP 2

[homesick]

This is what i have....i am using ado, but my connection is already bound using the properties box....is that ok or do i have to connect it using code...also, how do i know what provider i am using? i am using the built in DB in Visual Basic 6, does that make a diffrence? Striving to understand......



Private Sub Command1_Click()


Dim aname As String
Dim cname As String

msg = "This record will be added as a sale"
Style = vbOKCancel + vbQuestion + vbDefaultButton1
resonse = MsgBox(msg, Style, "Sale Confirmation&quot
If response = vbOK Then
tracker.AddNew
tracker!aname = straname
tracker!cname = strcname
tracker.Update

End If
If response = vbCancel Then
Combo5.SetFocus
End If

End Sub

 

[Sawatzky]

dear homesick,

first of all,
if you did copy and paste to populate the code-snippet, then you have to use Option Explicit in your code.
If not you have a few typos.

please verify that.

where do you DIM TRACKER and how ? This could be usefull to analyse your problem.

In fact every recordset must be opened with a valid connection to a database.

Since when is there a build in DB in VB 6??

regards Astrid

 

[homesick]

It is in Add-ins....Visual Data Manager...so eventhough i connected the DB through the properties control....i still have to write a code to open connection?

 

[bdfigler]

Homesick, please tell us more about "tracker".

 

[homesick]

it's a form where the user enters name, date, cx name, proucts and dollar amount. this seems ot be working but it is sending blank records ot my database...i'm jsut testing first with my Agent Name field=aname....



Private Sub Command1_Click()
Dim tracker As ADODB.Recordset
Dim aname As String

Dim ssql As String
Set tracker = CreateObject("adodb.recordset&quot

tracker.ActiveConnection = "provider=microsoft.jet.oledb.3.51;data source=a:\tracker.mdb"
ssql = "select * from tracker"
tracker.Open ssql, , adOpenStatic, adLockOptimistic, adExecuteNoRecords And adCmdText

With tracker
.AddNew
tracker!aname = straname

.Update
End With
MsgBox "Record added"

End Sub

 

[bdfigler]

Try:

Tracker.AddNew
Tracker("aname&quot = straname

 

[homesick]

Thanks for the tip but unfortunately it's still firing blanks....

 

[Sawatzky]

Dear homesick,

you dim a string variable aname that is never used, and you use a variable straname that is not dimensioned in this sub. Is it a global one?

where does it get its value?


regards Astrid

PS: make sure your first code line in each 'modul' or 'form' or 'class' is OPTION EXPLICIT

 

[homesick]

it gets the value from a combobox...that's where all the Agent's Names are stored....i don't understand your p.s...could you please explain?


Thanks again

 

[PGen]

Just write "OPTION EXPLICIT" in the top of your code (in the General section.

That way you will need to explicitly declare all variables u use.

That way VB will helpfully prompt you if u dont declare or mistype any var names.

You can probably set OPTION EXPLICIT as default somewhere.

In VB5 its in Tools->Options->Editor->Require Variable Declaration.

 

[homesick]

it is still saving a blank record.....i want to add the selected choice from aname, which is a combobox, to my database...

 

[FreakyLee]

Where are you assigning the value from the combo box to straname?

 

[homesick]

is that not how i addt the new record from the combobox to my database? combobox has a list of stored names. the agent chooses their name and submits it using the submit button on the form and then it's saved in the database....what do i do then?

 

[PGen]

If your combobox was named cboAgent then the following should return the value selected in a popup.

msgbox cboAgent.ItemData(cboAgent.ListIndex)


So use the following code:

Dim straname As String
straname = cboAgent.ItemData(cboAgent.ListIndex)
Tracker("aname&quot = straname

 

[homesick]

Pgen:

THANKS for the tip...

this is what i have but it sends a record of "0" to my database....

aname is the field name
combo5 is name of combobox

Private Sub Command1_Click()
Dim tracker As ADODB.Recordset
Dim straname As String

Dim ssql As String
Set tracker = CreateObject("adodb.recordset&quot

tracker.ActiveConnection = "provider=microsoft.jet.oledb.3.51;data
source=a:\tracker.mdb"
ssql = "select * from tracker"
tracker.Open ssql, , adOpenStatic, adLockOptimistic,
adExecuteNoRecords And adCmdText

straname = combo5.itemdata(conbo5.listindex)
tracker.AddNew
tracker("aname&quot = straname

tracker .Update
End With
MsgBox "Record added"

End Sub

 

[homesick]

this is returning "0" to my database.....can anyone help?

Private Sub Command1_Click()
Dim tracker As ADODB.Recordset
Dim aname As String

Dim ssql As String
Set tracker = CreateObject("adodb.recordset&quot

tracker.ActiveConnection = "provider=microsoft.jet.oledb.3.51;data
source=a:\tracker.mdb"
ssql = "select * from tracker"
tracker.Open ssql, , adOpenStatic, adLockOptimistic,
adExecuteNoRecords And adCmdText

With tracker
.AddNew
tracker!aname = straname

.Update
End With
MsgBox "Record added"

End Sub

 

[PGen]

Several things:
1. You have a typo in there "conbo5" should be "combo5"
2. You have an "End With" with out a "With"
3. The connection string should be "Data Source" not "datasource"
4. Yup sorry m8 use this instead, for the combo value: "combo5.Text" instead of the ordinal



This worked for me (however I used Jet version 4.0 and not 3.51, shouldn't make any difference tho)...

--------------------------------------------

Private Sub Command1_Click()

Dim tracker As ADODB.Recordset
Dim straname As String

Dim ssql As String
Set tracker = CreateObject("adodb.recordset&quot

tracker.ActiveConnection = "Provider=microsoft.jet.oledb.3.51;Data Source=a:\tracker.mdb;"
ssql = "select * from tracker"
tracker.Open ssql, , adOpenStatic, adLockOptimistic, adExecuteNoRecords And adCmdText

straname = combo5.Text

tracker.AddNew
tracker("aname&quot = straname
tracker.Update

MsgBox "Record added"

End Sub

--------------------------------------------


Can I recommend 2 things

1. use OPTION EXPLICIT so you dont trip up on typos
2. Use common object and variable naming conventions

e.g.

strName for String variables
cboName for Combo Boxes
txtName for Text Boxes
rsMembers for Recordsets
cnAccess for Connection objects

3. Name your database tables as the plural of their contents, ie trackers.


Hope this lot helps,
Will

 

[homesick]

Pgen:

THANKS A TON!! It works brilliantly and my headache is gone. Have a pint on me!!



Cheers,

Simon

p.s. thanks for the advice!

 

[PGen]

Must have been the asprin.

Just noticed your early question on connections...

Its up to you whether you do your connection setting in code or not. If you do you have more power at your fingertips, as ALL the properties, events and methods of the connection object are available.

Personally I use code simply because I can see whats going on without having to look elsewhere.

In professional data apps, connection objects are set once in the application (eg the connect/disconnect button in an FTP program).

The connection objects are DIMed global to the application and are therefore available from all functions and subs.

Recordset creation and SQL execute commands are performed on this connection on a per-task basis at function/sub level.

mail me if u want more on this: will@pocketgeneration.com

 

[PGen]

Sorry I didnt explain my last point...

A connection object is like a physical connection to a database and so consumes precious system resources.

If you are using one database, create only one connection to it, and leave the connection itself open intil you dont need the database anymore.

In a web or heavily loaded server environment this makes a noticable difference indeed to the server and application's performance.