Scribd
Upload
5 views5 pages

SQL Stored Procedures for Product Management

This code implements the basic functions of a product management system using SQL stored procedures. It defines classes and methods to connect to the database, load data into user controls, insert, search, update, and delete product records stored in the database by executing stored procedures.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views5 pages

SQL Stored Procedures for Product Management

This code implements the basic functions of a product management system using SQL stored procedures. It defines classes and methods to connect to the database, load data into user controls, insert, search, update, and delete product records stored in the database by executing stored procedures.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Imports [Link].

SqlClient
Imports [Link]

Public Class frmSPProducto


Private Sub cmbUnidadDeMedida_GotFocus(sender As [Link], e As
[Link])Handles [Link]
adapter = NewSqlDataAdapter
data = NewDataSet

[Link] = dbconnection
comando =NewSqlCommand("SPDisplayMemberCMBUnidadMedida", bdconexion)
[Link] = [Link]
For good practice, it was decided to create a stored procedure instead of the query to avoid SQL.
injection
[Link] = "Select * from tblunidadmedida order by name"
[Link] = command
[Link](data, "DataSetUnitMeasure")
[Link]()
DimtablaAsDataTable
table = [Link]("DataSetUnitMeasure")
[Link] = table
[Link] = "code"
[Link] ="nombre"
End Sub

Private Sub btnIngresar_Click(sender As [Link], e As [Link]) Handles


[Link]
Try
DEFINE VARIABLES AND ASSIGN THEM THE INPUT VALUE
Dim codigo As String = [Link]
Dim nombre As String = [Link]
DimunidadmedidaAs String= [Link]
Dim existence As Double = [Link]
Dim cost As Double = [Link]

CREATE COMMAND
[Link]()
command = NewSqlCommand("SPInsertProduct", dbConnection)
[Link] = [Link]

ASSIGN DATA TYPES TO THE PARAMETERS


Dim param codigo As New SqlParameter("@codigo", [Link])
DimparamnombreAs NewSqlParameter("@nombre",[Link])
Dim param unidad medida As New SqlParameter("@unidadmedida",
[Link]
Dim parameterExistence As New SqlParameter("@existence", [Link])
Dim paramCosto As New SqlParameter("@costo", [Link])

EXECUTE PROCEDURES WITH ASSIGNED PARAMETERS


[Link] = codigo
[Link] = name
[Link] = unidadmedida
[Link] = existence
[Link] = cost

ASSIGN DATA TO THE PARAMETERS


[Link](paramcodigo)
[Link](paramname)
[Link](paramunidadmedida)
[Link](paramexistencia)
[Link](paramcosto)

CREATE ADAPTER bring the information


adaptador =NewSqlDataAdapter
[Link] = command
[Link] = dbConnection

CREATE DATASET
data = NewDataSet
[Link](data,"DataSetInsertProduct")
MsgBox("Record Inserted Successfully...")

this part was modified at the professor's request in the slide


different
comando =NewSqlCommand("SPListarProducto", bdconexion)
[Link] = [Link]

adaptador =NewSqlDataAdapter
[Link] = command
[Link] = dbConnection

datos =NewDataSet
[Link](data,"DataSetListarProducto")

[Link] = data
[Link] = ("DataSetListarProducto")

MessageBox("Record Inserted Successfully")

Catch exception as
MESSAGE IF AN ERROR OCCURS IN THE DATA INSERTION
MsgBox("The record could not be inserted...")
EndTry
[Link]()
End Sub

Private SubfrmSPProducto_Load(sender As [Link], e As [Link])


Handles [Link]
ASSEMBLE THE COMMAND TO EXECUTE
[Link]()
command = NewSqlCommand("SPListarProducto", dbConnection)
[Link] = [Link]

I EXECUTE THE COMMAND THROUGH THE ADAPTER


adapter = NewSqlDataAdapter
[Link] = command
[Link] = dbConnection

STORE COMMAND EXECUTION RESULT IN THE DATASET


data = NewDataSet
[Link](data, "DataSetListProducts")

READ THE DATA IN THE DATASET AND CHECK IF THERE ARE RECORDS
reading = [Link]
If([Link] = True) Then
LOAD THE DGV WITH THE DATA FROM THE DATASET
[Link] = data
[Link] = "DataSetListarProductos"
Else
MESSAGE WHEN THERE IS NO DATA IN THE DATASET
MsgBox("No Data")
End If
[Link]()
End Sub
Private Sub btnBuscar_Click(sender As [Link], e As [Link]) Handles
[Link]

DECLARE PRIVATE VARIABLES AND ASSIGN A VALUE TO THEM


Dim code As String
codigo = InputBox("Ingrese el Codigo del Producto a Buscar")

I OPEN THE CONNECTION AND ASSEMBLE THE COMMAND TO EXECUTE


[Link]()
command = NewSqlCommand("SPBuscarProducto", dbConnection)
[Link] = [Link]

Dim parametron As New SqlParameter("@codigo", [Link])

I GIVE THE VALUE OF THE VARIABLE TO THE PARAMETER OF THE SP (STORED PROCEDURE
STORED
[Link] = code
[Link](parameter)

CREATE THE ADAPTER THROUGH WHICH I EXECUTE THE COMMAND


adapter = NewSqlDataAdapter
[Link] = command
[Link] = dbConnection

I CREATE THE DATASET TO STORE THE EXECUTION RECORDS


data = NewDataSet()
[Link](data, "DatasetSearchProduct")

CHECK IF THERE ARE RECORDS IN THE DATASET


lectura = [Link]
If ([Link] = True) Then

LOAD THE DGV WITH THE DATASET RECORDS


[Link] = datos
[Link] ="DatasetSearchProduct"
Else
MsgBox("No Data")
End If

I CLOSE THE CONNECTION


[Link]()
End Sub

Private Sub btnEliminar_Click(sender As [Link], e As [Link]) Handles


[Link]
Dim código As String
codigo = InputBox("Ingrese el codigo del Producto que desea eliminar")
Try
[Link]()
command = NewSqlCommand("SPBuscarProducto", dbConnection)
[Link] = [Link]

Dim param1 As New SqlParameter("@codigo", [Link])

[Link] = code
[Link](parameter1)

lectura = [Link]
If([Link] = True) Then
[Link]()
[Link]()
command = NewSqlCommand("SPEliminarProducto", dbconnection)
[Link] = [Link]

Dim parametro As New SqlParameter("@codigo", [Link])

[Link] = code
[Link](parameter)

adapter = NewSqlDataAdapter
[Link] = comando
[Link] = dbConnection

datos =NewDataSet
[Link](data, "DataSetRemoveProduct")

command = NewSqlCommand("SPListarProducto", bdconnection)


[Link] = [Link]

adapter = NewSqlDataAdapter
[Link] = command
[Link] = bdconexion

data = NewDataSet
[Link](data, "DataSetListarProducto")

[Link] = data
[Link] ="DataSetListarProducto"

MsgBox("Se elimino correctamente el registro")


Else
MsgBox("Product not found")
End If
Catch as Exception
MsgBox("The record cannot be deleted")
EndTry
[Link]()
End Sub

Private Sub btnActualizar_Click(sender As [Link], e As [Link])


[Link]

Dim codigo As String = [Link]


Dim nombre As String = [Link]
DimunidadmedidaAs String= [Link]
Dim existence As Double = [Link]
DimcostoAs Double= [Link]
Try
[Link]()
comando =NewSqlCommand("SPActualizarProducto", bdconexion)
[Link] =[Link]

Dimparametro1As NewSqlParameter("@codigo",[Link])
Dimparametro2 As New SqlParameter("@nombre", [Link])
Dimparametro3As NewSqlParameter("@undmedida",[Link])
Dim parameterto4 As New SqlParameter("@existencia", [Link])
Dimparametro5 As New SqlParameter("@costo", [Link])

[Link] = code
[Link](parameter1)

[Link] = name
[Link](parameter2)

[Link] = unidadmedida
[Link](parameter3)

[Link] = existence
[Link](parametro4)

[Link] = costo
[Link](parameter5)

adapter = New SqlDataAdapter


[Link] = command
[Link] = dbConnection

data = NewDataSet
[Link](data, "DataSetUpdateProduct")

comando =NewSqlCommand("SPListarProducto", bdconexion)


[Link] = [Link]

adapter = New SqlDataAdapter


[Link] = command
[Link] = dbConnection

data = NewDataSet
[Link](data, "DataSetListProducts")

[Link] = datos
[Link] = "DataSetListarProducto"

The record has been updated successfully

Catch as Exception

The record could not be updated


EndTry
[Link]()
End Sub
End class

You might also like