sábado, 20 de agosto de 2011

VB.NET ADO: Uso del COMMAND para llamar un Procedimiento Almacenado






Imports System.Data
Imports System.Data.SqlClient

Public Class InsertarCliente
    Dim cn As New SqlConnection

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            cn.ConnectionString = "Data source=visoal76;" & _
                "Initial Catalog=Northwind; Integrated Security=True"
            cn.Open()
            Dim cmd As New SqlCommand("Insert_customers", cn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@CustomerID", SqlDbType.NChar).Value = TxtCustomerid.Text
            cmd.Parameters.Add("@companyname", SqlDbType.NVarChar)
            cmd.Parameters.Add("@contactname", SqlDbType.NVarChar)
            cmd.Parameters.Add("@contacttitle", SqlDbType.NVarChar)
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar)
            cmd.Parameters.Add("@City", SqlDbType.NVarChar)
            cmd.Parameters.Add("@Region", SqlDbType.NVarChar)
            cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar)
            cmd.Parameters.Add("@Country", SqlDbType.NVarChar)
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar)
            cmd.Parameters.Add("@Fax", SqlDbType.NVarChar)

            cmd.Parameters("@customerid").Value = TxtCustomerid.Text
            cmd.Parameters("@companyname").Value = TxtCompanyname.Text
            cmd.Parameters("@contactname").Value = TxtContactName.Text
            cmd.Parameters("@contacttitle").Value = TxtContactTitle.Text
            cmd.Parameters("@Address").Value = TxtAddress.Text
            cmd.Parameters("@City").Value = TxtCity.Text
            cmd.Parameters("@Region").Value = TxtRegion.Text
            cmd.Parameters("@PostalCode").Value = TxtPostalCode.Text
            cmd.Parameters("@Country").Value = TxtCountry.Text
            cmd.Parameters("@Phone").Value = TxtPhone.Text
            cmd.Parameters("@Fax").Value = TxtFax.Text
            cmd.ExecuteNonQuery()
            MessageBox.Show("Dato Insertado Correctamente")
            Call Limpiar()
            cn.Close()
        Catch xcpSQL As SqlException
            MessageBox.Show("Mensaje: " & xcpSQL.Message.ToString & " Numero: " & xcpSQL.Number)
        Catch xcpSQL As Exception
            MessageBox.Show("Error de aplicacion")
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Call Limpiar()
    End Sub
  
Private Sub Limpiar()
        For Each ocontrol As Control In Me.Controls
            If TypeOf ocontrol Is TextBox Then
                ocontrol.Text = ""
            End If
        Next
    End Sub

End Class

3 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. Muy bueno, justo lo que nadie sabia explicar. adjunto ejemplo de procedimeinto almacenado que solo ejecuta y envia correos segun seleccion de bdd

    Dim company = "01"
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim cn As New SqlConnection
    Try
    cn.ConnectionString = "Data Source=ge.cl,60;Initial Catalog=Maximise;Persist Security Info=True;User ID=sdefwdc;Password=tre.45,E99"
    cn.Open()
    Dim cmd As New SqlCommand("Proyecto_sin_actividades", cn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@company", SqlDbType.NVarChar) '.Value = company

    cmd.ExecuteNonQuery()
    MessageBox.Show("Correo enviado exitosamente")

    cn.Close()
    Catch xcpSQL As SqlException
    MessageBox.Show("Mensaje: " & xcpSQL.Message.ToString & " Numero: " & xcpSQL.Number)
    Catch xcpSQL As Exception
    MessageBox.Show("Error de aplicacion")
    End Try
    End Sub

    ResponderEliminar