VB6+SQL2000 2x Tables Update Foreign Key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

VB6+SQL2000 2x Tables Update Foreign Key

Author

Topic

mcguirestick
Starting Member

1 Post

Posted - 2008-07-22 : 10:47:45

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 14:30:05

you need to use the LeaveNo as output parameter and get the generated value using SCOPE_IDENTITY. I think it would be better for you to wrap the full code as a stored procedure and call it from vb.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources