by Gehan Fernando (47 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB.NET
Difficulty: Unknown Difficulty
Originally Published: Thu 13th September 2007
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Handle Input / Output Parameters With .Net
API Declarations
.SqlClient
'SQL Procedure
'CREATE PROC GetCustomID (@PassValue NVARCHAR(100),@Type CHAR(1),@GetID INT OUTPUT)
'AS
' IF @Type = 'D'
' BEGIN
' SELECT @GetID = MAX(ISNULL(EmployeeID,0)) + 1 FROM
' Employees
' END
' ELSE
' BEGIN
' SELECT @GetID = MAX(ISNULL(EmployeeID,0)) + 1 FROM
' Employees
' WHERE FirstName LIKE @PassValue + '%'
' END
'GO
VBcoders
Private con As SqlConnection
Private com As SqlCommand
Private Type As Char = "D"c
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
con = New SqlConnection("Data Source=RAS7;Initial Catalog=Northwind;Integrated Security=True;Min Pool Size=5;Max Pool Size=20;Connect Timeout=30;Network Library=dbnmpntw")
con.Open()
com = New SqlCommand()
With com
.Connection = con
.CommandType = CommandType.StoredProcedure
.CommandText = "GetCustomID"
.Parameters.Add("@PassValue", SqlDbType.NVarChar, 100).Value = TextBox1.Text.ToString().Trim()
.Parameters.Add("@Type", SqlDbType.Char, 1).Value = Type.ToString().Trim()
.Parameters.Add("@GetID", SqlDbType.Int).Direction = ParameterDirection.Output
.ExecuteScalar()
TextBox2.Text = .Parameters.Item("@GetID").Value.ToString()
End With
com.Parameters.Clear()
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
com.Dispose()
con.Dispose()
End Try
End Sub
Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
If RadioButton1.Checked = True Then Type = "D"c
End Sub
Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
If RadioButton2.Checked = True Then Type = "N"c
End Sub
End Class
