' 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
BUSQUEDA VB
' Contenido del formulario:
' - 1 Text
' - 1 MSFlexGrid
' - 1 CommandButton
Option Explicit
Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command1_Click()
llenagrilla
End Sub
Private Sub Form_Load()
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
' - 1 Text
' - 1 MSFlexGrid
' - 1 CommandButton
Option Explicit
Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command1_Click()
llenagrilla
End Sub
Private Sub Form_Load()
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
Llenar una grilla
' Contenido del formulario:
'
' - 1 ComboBox
' - 1 MsFlexGrid
' - 1 commandButton
' - En caso de error agregar el componente adodc.
Option Explicit
Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command1_Click()
Dim tField As ADODB.Field
'
' Por si ya estaba abierta...
Set cnn = Nothing
Set rst = Nothing
'
' Crear los objetos
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
'
' abrir la base usando las credenciales de Windows
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=pubs"
'
' abrir el recordset indicando la tabla a la que queremos acceder
rst.Open "SELECT * FROM authors", cnn, adOpenDynamic, adLockOptimistic
'
' Asignar los nombres de los campos al combo
Dim i, j, ii
i = 1
Combo1.Clear
MSFlexGrid1.Row = 0
'
' obtener los campos de la consulta
For Each tField In rst.Fields
Combo1.AddItem tField.Name
MSFlexGrid1.Cols = MSFlexGrid1.Cols + 1
MSFlexGrid1.Col = i
MSFlexGrid1.Text = tField.Name
i = i + 1
Next
'
' llenar la grilla
rst.MoveFirst
While Not rst.EOF
j = j + 1
MSFlexGrid1.Rows = j + 1
MSFlexGrid1.Col = 0
MSFlexGrid1.Row = j
MSFlexGrid1.Text = j
For ii = 1 To i - 1
MSFlexGrid1.Col = ii
MSFlexGrid1.Row = j
MSFlexGrid1.Text = rst(ii - 1)
Next ii
rst.MoveNext
Wend
MSFlexGrid1.Cols = 10
Combo1.ListIndex = 0
'
' Cerrar el recordset y la conexión
rst.Close
cnn.Close
End Sub
'
' - 1 ComboBox
' - 1 MsFlexGrid
' - 1 commandButton
' - En caso de error agregar el componente adodc.
Option Explicit
Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command1_Click()
Dim tField As ADODB.Field
'
' Por si ya estaba abierta...
Set cnn = Nothing
Set rst = Nothing
'
' Crear los objetos
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
'
' abrir la base usando las credenciales de Windows
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=pubs"
'
' abrir el recordset indicando la tabla a la que queremos acceder
rst.Open "SELECT * FROM authors", cnn, adOpenDynamic, adLockOptimistic
'
' Asignar los nombres de los campos al combo
Dim i, j, ii
i = 1
Combo1.Clear
MSFlexGrid1.Row = 0
'
' obtener los campos de la consulta
For Each tField In rst.Fields
Combo1.AddItem tField.Name
MSFlexGrid1.Cols = MSFlexGrid1.Cols + 1
MSFlexGrid1.Col = i
MSFlexGrid1.Text = tField.Name
i = i + 1
Next
'
' llenar la grilla
rst.MoveFirst
While Not rst.EOF
j = j + 1
MSFlexGrid1.Rows = j + 1
MSFlexGrid1.Col = 0
MSFlexGrid1.Row = j
MSFlexGrid1.Text = j
For ii = 1 To i - 1
MSFlexGrid1.Col = ii
MSFlexGrid1.Row = j
MSFlexGrid1.Text = rst(ii - 1)
Next ii
rst.MoveNext
Wend
MSFlexGrid1.Cols = 10
Combo1.ListIndex = 0
'
' Cerrar el recordset y la conexión
rst.Close
cnn.Close
End Sub
Conexión a base de datos
' Contenido del formulario:
'
' - 1 ComboBox
' - 1 commandButton
' - En caso de error agregar el componente adodc.
Option Explicit
Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command1_Click()
Dim tField As ADODB.Field
'
' Por si ya estaba abierta...
Set cnn = Nothing
Set rst = Nothing
'
' Crear los objetos
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
'
' abrir la base usando las credenciales de Windows
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=pubs"
'
' abrir el recordset indicando la tabla a la que queremos acceder
rst.Open "SELECT * FROM authors", cnn, adOpenDynamic, adLockOptimistic
'
' Asignar los nombres de los campos al combo
With Combo1
.Clear
For Each tField In rst.Fields
.AddItem tField.Name
Next
.ListIndex = 0
End With
'
' Cerrar el recordset y la conexión
rst.Close
cnn.Close
'
End Sub
'
' - 1 ComboBox
' - 1 commandButton
' - En caso de error agregar el componente adodc.
Option Explicit
Private cnn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command1_Click()
Dim tField As ADODB.Field
'
' Por si ya estaba abierta...
Set cnn = Nothing
Set rst = Nothing
'
' Crear los objetos
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
'
' abrir la base usando las credenciales de Windows
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=pubs"
'
' abrir el recordset indicando la tabla a la que queremos acceder
rst.Open "SELECT * FROM authors", cnn, adOpenDynamic, adLockOptimistic
'
' Asignar los nombres de los campos al combo
With Combo1
.Clear
For Each tField In rst.Fields
.AddItem tField.Name
Next
.ListIndex = 0
End With
'
' Cerrar el recordset y la conexión
rst.Close
cnn.Close
'
End Sub
Suscribirse a:
Entradas (Atom)