Jumat, 25 Mei 2012

Memanggil Stored Procedure pada VB.NET dengan Parameter Input Output

Memanggil Stored Procedure pada VB.NET dengan OleDb SQL Server :

    Private Sub BtnUpdSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdSQL.Click
        Dim con As New SqlConnection
        con.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=btc;Integrated Security=True"
        con.Open()

        Dim cmd As New SqlCommand
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "hitungGaji"

        'parameter input SP "jmlgaji"=@kode dari textboxkodedepartemen.text
        cmd.Parameters.Add("@kodedept", OdbcType.VarChar)
        cmd.Parameters("@kodedept").Direction = ParameterDirection.Input
        cmd.Parameters("@kodedept").Value = TextBoxKodeDeptMySQL.Text

        cmd.Parameters.Add("@total", OdbcType.Double)
        cmd.Parameters("@total").Direction = ParameterDirection.Output

        cmd.ExecuteNonQuery()
        

        Dim intCount As Integer = 0
        Double.TryParse(cmd.Parameters("@total").Value, intCount)

        If intCount > 0 Then TextBoxJumlahMySQL.Text = intCount.ToString 

    End Sub


Pada Odbc, memanggil Stored Procedure tidak bisa dilakukan secara langsung dengan Tipe CommandType.StoredProcedure, harus menggunakan  Memanggil Stored Procedure dengan VB.NET pada Odbc MySQL :

    Private Sub ButtonCariMySQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonCariMySQL.Click
        Dim con As New OdbcConnection
        Dim cmd As New OdbcCommand
        Dim lreturn As Boolean = True

            'koneksi ke sql server---------------------------------
            con.ConnectionString = "driver={mysql odbc 3.51 driver};server=localhost;database=karyawan;uid=root;password= ;"
            con.Open()

            'nama stored procedure=jmlgaji---------------------------
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "call hitungGaji('" & TextBoxKodeDeptMySQL.Text & "',@total)"

            cmd.Parameters.Add("@total", OdbcType.Double)
            cmd.Parameters("@total").Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            'command mengambil hasil procedure hitungGaji
            Dim cmd2 As New OdbcCommand("SELECT @total", con)
            MsgBox(cmd2.ExecuteScalar.ToString)
    End Sub