Imports System.Data.
SqlClient
Public Class FrmCrud
Dim con As New SqlConnection("Data Source=DESKTOP-73NC9P8;Initial Catalog=students;Integrated
Security=True")
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim insertQuery As String = "INSERT INTO clases(st_name, address, roll_no, email) VALUES(@st_name,
@address, @roll_no, @email)"
executeQuery(insertQuery)
lblMessage.Text = "Registro insertado"
limpiar()
cargarGrid()
End Sub
Public Sub executeQuery(ByVal query As String)
Dim cmd As New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@id", txtId.Text)
cmd.Parameters.AddWithValue("@st_name", txtName.Text)
cmd.Parameters.AddWithValue("@address", txtAddress.Text)
cmd.Parameters.AddWithValue("@roll_no", txtRoll.Text)
cmd.Parameters.AddWithValue("@email", txtEmail.Text)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Sub
Public Sub limpiar()
txtName.Clear()
txtAddress.Clear()
txtRoll.Clear()
txtEmail.Clear()
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim updateQuery As String = "UPDATE clases SET st_name=@st_name, address=@address, roll_no=@roll_no,
email=@email WHERE id=@id"
executeQuery(updateQuery)
lblMessage.Text = "Registro Actualizado"
limpiar()
cargarGrid()
End Sub
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
limpiar()
End Sub
Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
End
End Sub
Private Sub FrmCrud_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cargarGrid()
cargarLista()
End Sub
Public Sub cargarGrid()
con.Open()
Dim com As String = "SELECT * FROM clases ORDER BY id DESC"
Dim adapt As New SqlDataAdapter(com, con)
Dim ds As New DataSet()
adapt.Fill(ds, "clases")
DataGridView1.DataSource = ds.Tables(0)
con.Close()
End Sub
Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles
DataGridView1.CellContentClick
Dim id As String
id = DataGridView1.Item(0, DataGridView1.CurrentRow.Index).Value
txtId.Text = id
txtName.Text = DataGridView1.Item(1, DataGridView1.CurrentRow.Index).Value
txtAddress.Text = DataGridView1.Item(2, DataGridView1.CurrentRow.Index).Value
txtRoll.Text = DataGridView1.Item(3, DataGridView1.CurrentRow.Index).Value
txtEmail.Text = DataGridView1.Item(4, DataGridView1.CurrentRow.Index).Value
End Sub
Public Sub cargarLista()
Dim command As New SqlCommand("SELECT id, st_name FROM clases", con)
Dim adapter As New SqlDataAdapter(command)
Dim table As New DataTable()
adapter.Fill(table)
cmbLista.DataSource = table
cmbLista.DisplayMember = "st_name"
cmbLista.ValueMember = "id"
End Sub
Private Sub btnLista_Click(sender As Object, e As EventArgs) Handles btnLista.Click
MsgBox(cmbLista.SelectedValue)
End Sub
End Class
SELECCIONAR DATOS DE 3 TABLAS
SELECT mt.*, ut.name, ct.titleFROM
Map_table mtINNER JOIN
User_table ut on mt.from_user_id = ut.idLEFT JOIN
Content_table ct on mt.content_id = ct.idWHERE
mt.to_user_id = 1
1. SELECT column-names
2. FROM table-name1 JOIN table-name2
3. ON column-name1 = column-name2
4. WHERE condition
1. SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
2. FROM [Order] JOIN Customer
3. ON [Order].CustomerId = Customer.Id
1. SELECT O.OrderNumber, CONVERT(date,O.OrderDate) AS Date,
2. P.ProductName, I.Quantity, I.UnitPrice
3. FROM [Order] O
4. JOIN OrderItem I ON O.Id = I.OrderId
5. JOIN Product P ON P.Id = I.ProductId
6. ORDER BY O.OrderNumber
INSERTAR DATOS CON PROCEDIMIENTO ALMACENADO
. Private Sub InsertNewRecord()
.
. sqlCon = New SqlConnection(strConn)
.
. Using (sqlCon)
.
. Dim sqlComm As New SqlCommand()
.
. sqlComm.Connection = sqlCon
.
. sqlComm.CommandText = "InsertDataIntoTable"
. sqlComm.CommandType = CommandType.StoredProcedure
.
. sqlComm.Parameters.AddWithValue("FirstName", txtName.Text)
. sqlComm.Parameters.AddWithValue("Surname", txtSurname.Text)
. sqlComm.Parameters.AddWithValue("Age", Integer.Parse(txtAge.Text))
.
. sqlCon.Open()
.
. sqlComm.ExecuteNonQuery()
.
. End Using
.
.
. LoadData()
.
. End Sub
Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
cmd.CommandText = "StoredProcedureName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1
sqlConnection1.Open()
reader = cmd.ExecuteReader()' Data is accessible through the DataReader object here.
sqlConnection1.Close()
USANDO EL DATAREADER
Dim sConnection As String = "server=(local);uid=sa;pwd=PassWord;database=DatabaseName"
Dim objCommand As New SqlCommand
objCommand.CommandText = "Select * From tablename"
objCommand.Connection = New SqlConnection(sConnection)
objCommand.Connection.Open()
Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()
If objDataReader.HasRows ThenDo While objDataReader.Read()
Console.WriteLine(" Your name is: " & Convert.ToString(objDataReader(0)))LoopElse
Console.WriteLine("No rows returned.")End If
objDataReader.Close()
objCommand.Dispose()
CRYSTAL REPORTS
Crear un form vacío, que contendrá el reporte viewver
Agregar CrystalReportViewer al formulario recién creado
Opciones, ShowLogo[No], ToolPanelView[None]
El procedimiento ya debe estar creado
CREATE PROCEDURE REPORTE_PERSONAS
AS
SELECT * FROM PERSONAS
Luego, en la carpeta o el proyecto (a la derecha) click derecho para Agregar->Nuevo
elemento
Luego, Izq. Sección Reporting->Crystal Report, en el nombre, ReportePersonasLaundry
En el asistente, se pone en blanco luego aceptar
En el panel del reporte creado, panel izquierdo primera opción:
“Campos de base de datos” -> Crear conexión->OLE DB ADO->SQL SERVER NATIVE CLIENT
SIGUIENTE, LUEGO ESPECIFICAR SERVIDOR, BASE DE DATOS, CHK SEGURIDAD
INTEGRADA->Siguiente, Finalizar
Debe aparecer en la conexión creada del Asistente de base de datos las tablas y
procedimientos (bajo OLE DB (ADO) )
DE LA LISTA, SELECCIONAR EL PROCEDIMIENTO, TRASLADAR AL PANEL DERECHO DE TABLAS
RELACIONADAS.
Con lo anterior, ya debe aparacer en Campos de Bases de datos (izq.), trasladamos
campos al area de trabajo o la página del reporte, organizamos.
Desde cualquier botón, FormReporte.show()
En el FormReporte, doble click para abrir el Sub loader y colocar este código:
Dim objReporte As New ReportePersonasLaundry
CrystalReportViewer1.ReportSource = objReporte
