' Contenido del formulario:
' - 1 Text para busqueda
' - 1 MSFlexGrid
' - 1 CommandButton
' - 3 Text para nuevo registro renombrar como id, titulo y precio
' - 1 ComboBox para nuevo registro (busca/agrega) renombrar como autor
' - 1 CommandButton para guardar nuevo registro renombrar como salvar
Dim n As Variant
Option Explicit
Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub autor_Click()
n = Split(autor.Tag, ",")
End Sub
Private Sub Command1_Click()
llenagrilla
End Sub
Sub llenagrilla()
' Por si ya estaba abierta...
Set cnn = Nothing
Set rst = Nothing
' Crear los objetos
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Dim ni As Integer, strcnn As String, sql As String
Dim tField As ADODB.Field
' abrir la base usando las credenciales de Windows
strcnn = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=pubs"
cnn.Open strcnn
' abrir el recordset indicando la tabla a la que queremos acceder
If Text1.Text = "" Then
sql = "SELECT titles.title, authors.au_lname, titles.price " & _
"FROM authors INNER JOIN " & _
"titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN " & _
"titles ON titleauthor.title_id = titles.title_id"
rst.Open sql, cnn, adOpenDynamic, adLockOptimistic
Else
sql = "SELECT titles.title, authors.au_lname, titles.price " & _
"FROM authors INNER JOIN " & _
"titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN " & _
"titles ON titleauthor.title_id = titles.title_id " & _
"WHERE titles.title like '%" & Text1.Text & "%'"
rst.Open sql, cnn, adOpenDynamic, adLockOptimistic
End If
MSFlexGrid1.Rows = 1
MSFlexGrid1.FixedCols = 0
ni = 1
If Not rst.EOF Then
MSFlexGrid1.Cols = rst.Fields.Count
MSFlexGrid1.ColAlignment(2) = 4
MSFlexGrid1.ColWidth(0) = 2000
MSFlexGrid1.ColWidth(1) = 1000
MSFlexGrid1.ColWidth(2) = 1000
Do While Not rst.EOF
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(ni, 0) = rst.Fields.Item(0).Value 'rst!COMUNA
MSFlexGrid1.TextMatrix(ni, 1) = rst!au_lname
MSFlexGrid1.TextMatrix(ni, 2) = IIf(IsNull(rst!price) = False, rst!price, "")
ni = ni + 1
rst.MoveNext
Loop
End If
rst.Close
cnn.Close
End Sub
Private Sub Form_Load()
Set cnn = Nothing
Set rst = Nothing
' Crear los objetos
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Dim ni As Integer, strcnn As String, sql As String
Dim tField As ADODB.Field
' abrir la base usando las credenciales de Windows
strcnn = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=pubs"
cnn.Open strcnn
sql = "SELECT au_lname , au_id FROM authors order by au_id"
rst.Open sql, cnn, adOpenDynamic, adLockOptimistic
autor.Clear
autor.Tag = ""
Do While Not rst.EOF
autor.AddItem rst!au_lname
If autor.Tag <> "" Then autor.Tag = autor.Tag + ","
autor.Tag = autor.Tag + rst!au_id
'Text1.Text = autor.Tag
'n = autor.AddItem("aa", 1) 'rst!au_lname, rst!au_id
rst.MoveNext
Loop
rst.Close
cnn.Close
llenagrilla
End Sub
Private Sub salvar_Click()
Dim sql As String, strcnn As String
Set cnn = Nothing
Set rst = Nothing
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
' Dim ni As Integer, strcnn As String, sql As String
' Dim tField As ADODB.Field
strcnn = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=pubs"
cnn.Open strcnn
If titulo = "" Or precio = "" Or autor = "" Then
MsgBox "Llene todos los campos"
Exit Sub
End If
sql = "insert into titles (title_id,title,price) values ('" & id.Text & "' , '" & Me.titulo.Text & "' , " & Me.precio & ")"
rst.Open sql, cnn, adOpenDynamic, adLockOptimistic
'rst.Close
sql = "insert into titleauthor (au_id, title_id) values('" & n(autor.ListIndex) & "' , '" & id.Text & "')"
rst.Open sql, cnn, adOpenDynamic, adLockOptimistic
'rst.Close
cnn.Close
End Sub