469,267 Members | 979 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

Database, inserts, etc

Hi. I'm using VB 6.0, and I'm connecting to an Access database. I open two
tables which are joined.

I'm trying to insert a new record, and only have one of the tables affected.
I'm not having much success. I can open the records, surf through them,
etc. I find the .ADDNEW ado function doesn't work properly when you have
multiple tables open.

Each table has an auto numbered field which serves as the primary key
(students.studentCode, and instructors.instructorCode)
Dim db As New Connection
Dim adoRecordSet As New Recordset

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & _
"\ddt.mdb;Mode=ReadWrite|Share Deny None;Persist Security
Info=False"

adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode =
instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic

db.Execute ("INSERT INTO students (students.firstName,
students.lastName, students.instructorCode) " _
& "VALUES ('First', 'Last', '7')")
Why does this insert not work? The syntax looks right to me. When I use it
I get error -2147217900 (that's a pretty big negative number)

Is there a better way to add a new record? How do I make sure that my
record set can see the new record, and hence have my controls be able to
display the new record? IE: I press the Next or Previous controls to
traverse the records, will my new record show?
Also, for deleting records is this my best bet:
db.Execute ("DELETE FROM students WHERE studentCode = " & txtStudentCode)

Adrian
Jul 17 '05 #1
4 4955

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:3d*******************@news20.bellglobal.com.. .

Ok, I got a new record inserted, db.Execute ("INSERT INTO students
(firstName, lastName, instructorCode) " _
& "VALUES ('New 2', 'Addition 2', '7')")

But using adorecordset.movenext or adorecordset.movelast cannot traverse to
the new entry.

I tried adorecordset.requery. This should show the new record, but does
not. If I add a new record, close the program, then reopen it, the new
record is loaded with the query.

After I do an insert, how do I get the adorecordset (Recordset object) to
include this new row?

Dim db As New Connection
Dim adoRecordSet As New Recordset

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & _
"\ddt.mdb;Mode=ReadWrite|Share Deny None;Persist Security
Info=False"

adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode =
instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic

db.Execute ("INSERT INTO students (students.firstName,
students.lastName, students.instructorCode) " _
& "VALUES ('First', 'Last', '7')")

Also, I'm loading the data into forms using:

Set txtFirstName.DataSource = adoRecordSet
txtFirstName.DataField = "students.firstName"

Set txtLastName.DataSource = adoRecordSet
txtLastName.DataField = "students.lastName"

And I'm traversing the records with statements like adoRecordSet.MoveFirst,
adoRecordSet.MoveLast etc.

Adrian
Jul 17 '05 #2
When you do a requery than you do a movelast to see the newest record

greets John
"Adrian Parker" <ad***********@NOSPAMsympatico.ca> schreef in bericht
news:G6********************@news20.bellglobal.com. ..

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:3d*******************@news20.bellglobal.com.. .

Ok, I got a new record inserted, db.Execute ("INSERT INTO students
(firstName, lastName, instructorCode) " _
& "VALUES ('New 2', 'Addition 2', '7')")

But using adorecordset.movenext or adorecordset.movelast cannot traverse to the new entry.

I tried adorecordset.requery. This should show the new record, but does
not. If I add a new record, close the program, then reopen it, the new
record is loaded with the query.

After I do an insert, how do I get the adorecordset (Recordset object) to
include this new row?

Dim db As New Connection
Dim adoRecordSet As New Recordset

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & _ "\ddt.mdb;Mode=ReadWrite|Share Deny None;Persist Security
Info=False"

adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode = instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic

db.Execute ("INSERT INTO students (students.firstName,
students.lastName, students.instructorCode) " _
& "VALUES ('First', 'Last', '7')")

Also, I'm loading the data into forms using:

Set txtFirstName.DataSource = adoRecordSet
txtFirstName.DataField = "students.firstName"

Set txtLastName.DataSource = adoRecordSet
txtLastName.DataField = "students.lastName"

And I'm traversing the records with statements like

adoRecordSet.MoveFirst, adoRecordSet.MoveLast etc.

Adrian

Jul 17 '05 #3

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:G6********************@news20.bellglobal.com. ..

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:3d*******************@news20.bellglobal.com.. .

Ok, I got a new record inserted, db.Execute ("INSERT INTO students
(firstName, lastName, instructorCode) " _
& "VALUES ('New 2', 'Addition 2', '7')")

But using adorecordset.movenext or adorecordset.movelast cannot traverse to the new entry.
adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode = instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic


As I read it, your new record has instructorCode 7, and your recordset
is filtering for records with instructorCode 1, so it is correctly not
showing the new record after a refresh.

Jul 17 '05 #4

"Steve Gerrard" <no*************@comcast.net> wrote in message
news:s8********************@comcast.com...

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:G6********************@news20.bellglobal.com. ..

"Adrian Parker" <ad***********@NOSPAMsympatico.ca> wrote in message
news:3d*******************@news20.bellglobal.com.. .

Ok, I got a new record inserted, db.Execute ("INSERT INTO students
(firstName, lastName, instructorCode) " _
& "VALUES ('New 2', 'Addition 2', '7')")

But using adorecordset.movenext or adorecordset.movelast cannot

traverse to
the new entry.
adoRecordSet.Open "select * from students, instructors " _
& "WHERE students.instructorCode = 1 AND students.instructorCode = instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic


As I read it, your new record has instructorCode 7, and your recordset
is filtering for records with instructorCode 1, so it is correctly not
showing the new record after a refresh.


I noticed after.

My big concern now is how do I do a query, which finds the value of just one
field, and stores that in a small variable (int or something). I don't want
to use an entire recordset just to get one field.

Adrian
Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by salamol | last post: by
10 posts views Thread by shsandeep | last post: by
10 posts views Thread by rich | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.