SQL - Selecionando informações de Tabelas relacionadas e exibindo-as em um Grid.

Por estes dias recebi um e-mail solicitando ajuda para um problema aparentemente simples envolvendo seleção de informações entre tabelas relacionadas usando SQL . Simples para quem já conhece , mas para quem está começando agora com o Visual Basic essa questão pode ser igual a ter que cruzar o mar vermelho.E ai ?

O problema era o seguinte: Temos um banco de dados com três tabelas relacionadas; eu quero criar um formulário que possua duas combos e um grid ; as combos terão as informaçoes de duas das minhas tabelas , bem , ao selecionar as informações nas combos eu quero que os dados da terceira tabela , relacionados a minha seleção , sejam exibidos no Grid . Todos entenderam o xis da questão ?

Vamos supor que você é o analista-programador ( ) de uma escola , esta escola tem um sistema que gerencia as informações sobre os alunos, cursos, notas, professores, etc... (O sistema foi feito em VB , claro ! e adivinhe por quem ???). Bem, neste sistema você tem um banco de dados chamado escola.mdb (que nome mais sem inspiração!) e no banco de dados você tem dezenas, centenas (nossa !!) de tabelas. Você recebeu a tarefa de criar um módulo para o sistema , e neste módulo você deverá ter a informação dos alunos da escola e dos cursos que cada aluno faz, de forma que ao selecionar um aluno e um curso os sistema exibirá as notas para o aluno/curso selecionado por ano/bimestre.

Você , como bom analista-programador , analisou o banco de dados e descobriu que a tabela tblalunos contém as informações sobre todos os alunos da escola , que a tabela tblcursos tem as informações sobre os cursos e que a tabela tblnotas tem as informações sobre a nota para cada aluno/curso em cada ano/bimestre. Bem ai perdeu a graça , ficou fácil demais , já que você não vai precisar nada mais do que selecionar as informações existentes.Então você se animou e verificou que os relacionamentos entre as tabelas era o seguinte:

Essas tabelas estão contidas no arquivo Escola.mdb

No relacionamento um-para-vários um campo comum relaciona registros em uma tabela 'um' (possui o campo chave primária), com uma tabela 'vários' (possui o campo chave externa). Isto irá impor o seguinte :

-Todo o registro na tabela 'vários' terá que ter um registro na tabela 'um'

- Nenhum registro poderá ser excluido da tabela 'um' se houver um registro relacionado na tabela 'vários'

Mas você não se contentou somente em obter os relacionamentos entre as tabelas , você ainda descobriu que:

E então, Eureka !! () ai o formulário do projeto surgiu na sua mente e você tratou logo de desenhar a interface.(puxa você é bom mesmo !). Veja abaixo o seu formulário.

Controles usados no seu formulário:

- 2 controles Frames - Frame1 e Frame2 que conterão as caixas de combinação

- 2 controles combobox - Combo1 e combo2 de onde iremos selecionar o nome do aluno e o nome do curso

- 1 controle DBGrid - Grid , onde iremos exibir as notas do aluno/curso selecionado

- 2 controles image- image1 e image2 - Um para processar a Consulta outro para Encerrar o sistema.

- 1 Controle Data Control - Data1 - onde iremos vincular a nossa fonte de dados resultante da consulta SQL.

E como vai funcionar o seu projeto ? Quer explicar , por favor ...e seja didático hein !

O projeto deverá funcionar como descrito abaixo :

  1. Quando da execução do sistema haverá a carga do formulário acima , que será o único formulário do projeto.Iremos chamá-lo de frmprinc.
  2. Ao carregar o formulário o sistema irá preencher as caixas de combinação com o nome dos alunos ( combo1), e com o nome dos cursos ( combo2 ) .
  3. O usuário irá clicar na combo1 selecionando um aluno e na combo2 selecionando um curso
  4. A seguir o usuário clica no botão de comando Processar e o sistema irá montar uma consulta SQL com os dados selecionados e irá exibir o resultado , as notas , no Grid .

Então vamos fazê-lo funcionar , pô ! ( )

Primeiro problema - Como eu vou preencher as comboBox para mostrar os nomes dos alunos e os cursos ?

Pode parecer uma pergunta simples , afinal , preencher uma comboBox com dados é barbada ! (é mesmo ?), mas dependendo do modo utilizado iremos percorrer um caminho mais ou menos difícil. Então , se você pensou em preencher a comboBox - combo1 - com o nome dos alunos usando a tabela - tblalunos - , eu só queria lhe chamar a atenção para um detalhe - a tabela tblnotas não possui referencia nome de aluno mas sim a seu código.

A mesma pergunta feita acima aplica-se ao preenchimento da ComboBox - combo2 - com o nome dos cursos, pois a tblnotas não faz referência ao nome do curso mas sim ao seu código.

Se você esta pensando em mudar a estrutura da tabela - tblnotas - e incluir o nome do aluno e do curso , excluindo os campos codaluno e codcurso eu já vou lhe adiantando : não faça isto ! (Por que ? )

Ora, se eu não posso mudar a estrutura da tabela - tblnotas - e ela não contêm referência ao nome do aluno e do curso. Como eu vou fazer ???

Bem , que tal se o usuário ao carregar o programa visse nas combos o nome dos alunos e dos cursos e quando ele fizesse a escolha pelo aluno e pelo curso o sistema automaticamente pegasse o codigo do aluno e o codigo do curso escolhido. Seria idéia não ? Então é exatamente isto que vamos fazer . Como ?????????

Usando a propriedade ItemData da ComboBox. Esta propriedade permite associar um número inteiro a cada item de um ComboBox ou ListBox. Ela é um vetor de valores inteiros com o mesmo número de itens da Combo ou do ListBox.

Assim vejamos um exemplo de como preencher uma comboBox usando esta propriedade:

Private Sub Form_Load ()
   ' preenchendo um ListBox e atribuindo os valores á propriedade Itemdata
   List1.AddItem "Jose"
   List1.ItemData(List1.NewIndex) = 1
   List1.AddItem "Maria"
   List1.ItemData(List1.NewIndex) = 2
   List1.AddItem "Mauro"
   List1.ItemData(List1.NewIndex) = 3
   List1.AddItem "Cintia"
   List1.ItemData(List1.NewIndex) = 4
End Sub

Private Sub List1_Click ()
   ' Buscando o nome do aluno e seu indice correspondente na lista
   Msg = List1.ItemData(List1.ListIndex) & " "
   Msg = Msg & List1.List(List1.ListIndex)
   Label1.Caption = Msg
End Sub

No exemplo acima estamos preenchendo uma ListBox (para ComboBox seria idêntico) com o nome de alguns alunos , e atribuindo via propriedade ItemData um número que pode ser o código de identificação de cada aluno. Neste caso estamos associando-o ao mais novo item , o qual é determinado pela propriedade NewIndex. Na ListBox o nome fica visível , e quando o usuário selecionar um aluno pelo nome , o seu código será obtido através da leitura do item da ListBox : List1.ItemData(List1.ListIndex).

Como esta tarefa deve ser feita no inicio da carga do formulário , iremos usar o evento Form_load , para : abrir a base de dados, configurar a largura das colunas do grid e encher as comboBox. Vejamos o código que realiza isto abaixo:

Private Sub Form_Load()
Dim caminho As String
Dim Arquivo As String

 caminho = App.Path
 If Right(caminho, 1) <> "\" Then caminho = caminho & "\"
  Arquivo = caminho & "escola.mdb"

 Set db = DBEngine.Workspaces(0).OpenDatabase(Arquivo)
 Data1.DatabaseName = Arquivo
' Configura a largura das colunas do grid
 DBGrid1.Columns(0).Width = 1700
 DBGrid1.Columns(1).Width = 1700
 DBGrid1.Columns(2).Width = 1700
'Preenche as comboBox
 enche_combo Combo1, "tblalunos", "nome", "codaluno"
 enche_combo Combo2, "tblcursos", "nomecurso", "codcurso"

End Sub

Observe que para preencher as combos usamos uma chamada á função enche_combo para tornar nosso código mais elegante e menos repetitivo.( hummm !) Quer ver o código da função enche_combo ? ( que pergunta ...)

Public Sub enche_combo(combo As Control, Data As String, campo As String,_
 Optional indice As Variant)
'-- cria variável recordset temporária
Dim arqtemp As Recordset
'-----limpa combo
combo.Clear
'-----abre tabela como Snapshot (economiza memoria)
Set arqtemp = db.OpenRecordset(Data, dbOpenSnapshot)
'--inicia loop através da tabela---
If arqtemp.RecordCount > 0 Then
 Do Until arqtemp.EOF
    combo.AddItem arqtemp(campo)
     If Not IsMissing(indice) Then
       combo.ItemData(combo.NewIndex) = arqtemp(indice)
     End If
    arqtemp.MoveNext
 Loop
Else
  MsgBox "Não há dados ..."
  Exit Sub
End If
'---fecha recordset e seleciona primeiro opcao na combo
arqtemp.Close
combo.ListIndex = 0
'-----limpa memoria
Set arqtemp = Nothing
End Sub

Basicamente a função faz o seguinte: recebe o nome da tabela, o nome do campo e o nome do índice que usamos na tabela, para encher a combo e atribuir via propriedade ItemData a cada nome o seu índice correspondente.(Nossa função é mais genérica pois permite preencher uma combo sem usar este recurso - note que o indíce é opcional )

Tudo bem , as combos ja estao preenchidas , agora eu quero fazer com que ao selecionar um aluno e um curso , as notas deste aluno para este curso sejam mostradas no grid por ordem crescente de ano e decrescente de bimestre. ( exigente eu hein ! ). E ai ? . Se você pensou - "Já sei vou usar SQL !!!" ( 1 a zero para você).

Após feita a seleção os usuário deverá clicar no botão - Processa. Ai , primeiro você deve verificar se o usuário realmente selecionou aluno e curso e em caso positivo voce deve montar uma instrução SQL que deverá ser processada e passada ao Grid via DataControl. Vejamos o código para estas ações:

Código do botão Processar - Se as combos estiverem preenchidas então chama a procedure Monta_sql, caso contrário emite aviso ao usuário para que a seleção seja feita.

Private Sub Image1_Click()
  If Combo1.ListIndex <> -1 And Combo2.ListIndex <> -1 Then
    monta_sql
  Else
    MsgBox "É necessário selecionar um aluno e um curso !!!"
  End If
End Sub

Código da Procedure Monta_sql - Monta a instrução e atualiza o DbGrid.

Private Sub monta_sql()
Dim sql As String

sql = "SELECT tblnotas.ano, tblnotas.bimestre, tblnotas.nota "
sql = sql & " FROM tblcursos INNER JOIN (tblalunos INNER JOIN tblnotas ON_ 
tblalunos.codaluno = tblnotas.codaluno) ON tblcursos.codcurso = tblnotas.codcurso "
sql = sql & " WHERE tblnotas.codaluno=" & Combo1.ItemData(Combo1.ListIndex)
sql = sql & " AND tblnotas.codcurso=" & Combo2.ItemData(Combo2.ListIndex)
sql = sql & " ORDER BY tblnotas.ano DESC , tblnotas.bimestre DESC;"

Data1.RecordSource = sql
Data1.Refresh

If Data1.Recordset.EOF Then
    Me.DBGrid1.Enabled = False
Else
    Me.DBGrid1.Enabled = True
End If

End Sub
Private Sub DBGrid1_Error(ByVal DataError As Integer, Response As Integer)
If DATAERR <> 0 Then
    MsgBox "Ocorreu o um erro ao executar esta operação!"
End If
Response = 0

End Sub

O resultado deve ser algo parecido com:

Voilá !!! Eis ai a tão esperada consulta extraindo informações de tabelas relacionadas. Este exemplo é simples , mas creio que através dele você poderá fazer coisas muito mais complexas.

Agora , para dar um toque de classe e mostrar também uma propriedade interessante do DbGrid, que tal se pudessemos alterar no próprio grid a nota do aluno ,e mais ainda se pudessemos fazer uma critica de modo a permitir somente a entrada de números com ate 3 algarismos e de notas no intervalo de zero a 10 ( se nao ia dar muito na cara ! ). Pois vamos lá:

Para permitir a atualização no Grid configure as propriedades: AllowAddNew, AllowDelete e AllowUpdate ativas como mostrado abaixo:

Bem agora falta o código que vai atuar realizando a tarefa: Usaremos o evento BeforeColUpdate com a ação sendo checada para a coluna 2 (ColIndex=2) 

Private Sub DBGrid1_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant,_
 Cancel As Integer)
If ColIndex = 2 Then
    If Not IsNumeric(DBGrid1) Then
        MsgBox "Somente números poderão ser digitados neste campo", vbCritical
        DBGrid1 = 0
    End If
    If DBGrid1.Columns(2).Value > 10 Then
       MsgBox "Valor do conceito nao pode ser superior a 10 ! "
       dgbrid1 = 0
    End If
End If
End Sub

Para não permitir valores númericos com mais de 4 digitos usamos o evento Change do DbGrid e verificamos quantos caracteres foram digitados e se forem 4 ou mais damos um BACKSPACE (BS) via SendKeys.

Private Sub DBGrid1_Change()
If Len(DBGrid1.Columns(2)) >= 4 Then
    SendKeys "{BS}"
End If
End Sub

Finalmente quando o usuário teclar ENTER ( codigo 13) então o cursor irá para a linha de baixo (DOWN) via SendKeys.

Private Sub DBGrid1_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
    SendKeys "{DOWN}"
End If
End Sub

A visão final para o usuário digitando um número maior que 10 , seria algo como:

E por hoje é só. . Para pegar o projeto clique aqui: sql_db.zip (5 KB)

retorna