VB6 + SQL2000 Trying to update Foreign Key value from Primary Key

Question

VB6 + SQL2000 Trying to update Foreign Key value from Primary Key

mcguirestick

Grasshopper

Points: 18
More actions
July 22, 2008 at 8:43 am

#194189

Hi,
This is totally baffling me.
I have 2 Tables that i fill from a VB6 programme using ADODB.
Table 1 have a Autonumber Primary Key called LeaveNo.
And i fill this Table1 with data (Personal Data) i then use a second Table2 to store dates and have a foreign key called LinkID.
I have Linked the Primary Key and the Foreign key in SQL.
When i write new personal data to Table 1 i want to be able to add as many dates as i want to Table 2 but have the LinkID field automatically have the LeaveNo value inserted.
When the code runs all i get in the LinkID Field is NULL
The VB Code i am using is below
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
cnn.Open strConnect
SQLQuery = "SELECT * FROM Leave, Dates;"
rst.Open CStr(SQLQuery), cnn, adOpenDynamic, 3
' Open recordset
rst.AddNew
rst.Fields("Name") = Form1.Text2.Text
rst.Fields("StaffType") = Form1.Combo3.Text
rst.Fields("Station") = Form1.Combo2.Text
rst.Fields("AppliedForOn") = Format(Date, "medium date") + " " + Format(Time, "hh.mm.ss")
rst.Fields("AppliedFor") = Form1.Text1.Text
rst.Fields("TypeOfLeave") = Form1.Combo1.Text
rst.Fields("TotalHours") = Form1.Text7.Text
rst.Fields("SentFrom") = SentFrom
If Form1.Text9.Text = "" Then Form1.Text9.Text = " "
rst.Fields("Comments") = Form1.Text9.Text
rst.Fields("SecurityNumber") = LeaveID
rst.Fields("Sector") = Sector
rst.Fields("MainStation") = MainStation
rst.Fields("DrcLocation") = Form1.Text11.Text
rst.Fields("Status") = "1"
rst.Update
Let I = Form1.List1.ListCount
f = 0
Do While I <> f
rst.AddNew
rst.Fields("DatesApplied") = Form1.List1.List(f)
p = 0
a = 11
Do While p <> 1
CheckDate = Mid$(Form1.List1.List(f), a, 1)
If CheckDate = " " Then
p = 1
UsaDate = Left$(Form1.List1.List(f), a)
UsaDate = LTrim(UsaDate)
Else
End If
a = a - 1
Loop
'DateConverter
rst.Fields("Dates") = UsaDate
FrmPrint.List1.AddItem (Form1.List1.List(f))
TempHours = Right(Form1.List1.List(f), 2)
TempHours = LTrim(TempHours)
rst.Fields("Approved") = "NotAudited"
rst.Fields("ReasonRefused") = " "
rst.Fields("Cancelled") = CancelDates
rst.Fields("NoHours") = TempHours
TempStr = temp2 & "ID" & f
rst.Fields("DateID") = TempStr
rst.Update
f = f + 1
Loop
rst.Close
Thanks in advance any help you can give
Terry

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

So far as I know, this is not an "automatic" feature of either VB6, SQLServer or ADO.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

This:

SQLQuery = "SELECT * FROM Leave, Dates;"

is simply going to crossjoin the two tables and give you a single recordset.

You need to open two individual recordsets. When you execute the .Update of the first, you will be able to get the ID from it and use this in the second recordset.

There is nothing in ADO that will magically understand your data model and properly update your foreign key for you.

mcguirestick Grasshopper Points: 18 More actions · Answer 3

Hi,

Thanks for the info.

Just one last question is it better to open two recordset at the same time.

Or do it the way i am doing it at the moment of open table1, write, close / Open, read LeaveNo, close / open, write date data, close