Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Location
    Philippines
    Posts
    4

    Unanswered: working with tables

    people of the world i need youre help. the scenario is... you have two tables in ms sql namely Hr_persInfo and Leave_Bal under Hr_persInfo Pers_Code... and so on.. under the Leave_Bal Bal_EmpCode and so on. the problem is that the Pers_Code and the Bal_EmpCode should have the same value but i dont know on how to work with different tables in ms sql (sorry for being noob but im trying my best to work it out) i'll be waiting for ur replies tia

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    use INNER JOIN

    select *
    from Hr_persInfo p inner join Leave_Bal l
    on p.Pers_Code = l.Bal_EmpCode
    -----------------
    KH


  3. #3
    Join Date
    May 2006
    Location
    Philippines
    Posts
    4
    thank u khtan but that didnt resolve my problem
    again i'll clear the scenario and make it a more detailed one

    Hr_persinfo
    -Pers_Code
    -Pers_Lname
    -Pers_Fname

    Leave_Bal
    -Bal_EmpCode
    -Bal_Type
    -Bal_NoLeaves
    -Bal_Availed
    -Bal_Balances

    the value of the Pers_Code in the first table is the same as the value of Bal_EmpCode in the second table... i still cant figure out the way on how to link this two tables... can somebody out there help me with these? im having a headache figuring out the solution to this little problem for you guyz! thanks again! i'll be waiting for your replies!

  4. #4
    Join Date
    May 2006
    Location
    Philippines
    Posts
    4
    by the way... im using ms sql and not my sql just making clarifications ^_^
    Last edited by zhyrence; 05-04-06 at 00:38.

  5. #5
    Join Date
    May 2006
    Location
    Philippines
    Posts
    4
    here's a brief preview of my current working code:

    #####these block of code works just fine upto the end if
    Sub fillTxtbox()

    strSQL = "select Pers_Fname, Pers_Lname, Pers_Dateh from Hr_persInfo where Pers_Code = '" & Replace(Trim(txtECode.Text), "'", "''") & "'"
    Set rsTemp = New ADODB.Recordset
    rsTemp.Open strSQL, cn, adOpenStatic
    If rsTemp.RecordCount Then

    txtEName.Text = rsTemp!Pers_LName & ", " & rsTemp!Pers_Fname
    txtDEmployed.Text = rsTemp!Pers_DateH
    Else

    MsgBox "No records Found!", vbCritical + vbOKOnly, "Message"
    txtECode.Enabled = True
    txtReasons.Enabled = False
    dtDate1.Enabled = False
    dtDate2.Enabled = False
    dtDate3.Enabled = False
    lblEHours.Visible = False
    lblDays.Visible = False
    txtDays.Visible = False
    txtEHours.Visible = False

    End If ####upto here######

    '#### this is the part that is not working properly
    strSQL = "select * from Hr_persInfo p inner join Leave_Bal l on p.Pers_Code = l.Bal_EmpCode"
    Set rsTemp = New ADODB.Recordset
    rsTemp.Open strSQL, cn, adOpenStatic
    If rsTemp.RecordCount Then

    txtVLBefore.Text = rsTemp!Bal_Noleaves


    End If
    '##### upto here

    End Sub

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is not working about it? The SQL Syntax is simple and valid.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Quote Originally Posted by zhyrence
    here's a brief preview of my current working code:

    #####these block of code works just fine upto the end if
    Sub fillTxtbox()

    strSQL = "select Pers_Fname, Pers_Lname, Pers_Dateh from Hr_persInfo where Pers_Code = '" & Replace(Trim(txtECode.Text), "'", "''") & "'"
    Set rsTemp = New ADODB.Recordset
    rsTemp.Open strSQL, cn, adOpenStatic
    If rsTemp.RecordCount Then

    txtEName.Text = rsTemp!Pers_LName & ", " & rsTemp!Pers_Fname
    txtDEmployed.Text = rsTemp!Pers_DateH
    Else

    MsgBox "No records Found!", vbCritical + vbOKOnly, "Message"
    txtECode.Enabled = True
    txtReasons.Enabled = False
    dtDate1.Enabled = False
    dtDate2.Enabled = False
    dtDate3.Enabled = False
    lblEHours.Visible = False
    lblDays.Visible = False
    txtDays.Visible = False
    txtEHours.Visible = False

    End If ####upto here######

    '#### this is the part that is not working properly
    strSQL = "select * from Hr_persInfo p inner join Leave_Bal l on p.Pers_Code = l.Bal_EmpCode"
    Set rsTemp = New ADODB.Recordset
    rsTemp.Open strSQL, cn, adOpenStatic
    If rsTemp.RecordCount Then

    txtVLBefore.Text = rsTemp!Bal_Noleaves


    End If
    '##### upto here
    End Sub
    Im not sure what exactly is ur problem.
    I think that red marked line is wrong. 'if condition' only checks boolean values.
    change that line to,
    if rsTemp.RecordCount>0 then


    Another suggestions close the first recordset as soon as possible.
    Last edited by mallier; 05-04-06 at 10:20.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •