VB Parameters to insert a row into a SQL database table

[rickjamesb]

Basically I'm just trying to learn how to use Visual Basic and parameters with SQL. I want to learn how to take user inputted text from visual basic and insert those values into a sql database table's row. So I created a very simple scenerio and hopefully someone can help me.

I'm using the "Region" table in the NorthWinds database. This table only has two attributes: RegionID & RegionDescription.

My VisualBasic form will only have two text boxes and a button: txtRegionID, txtRegionDescription, & btnInsert. I want the user to be able to type in a RegionID & RegionDescription into these two textbox field, and press the Insert button to insert these values into the database, in order to create a new row.

So here's my SQL procedure, i believe this code is correct:

CREATE PROCEDURE up_Region
@RegionID int,
@RegionDescription nchar(50)
AS
 
INSERT INTO Region (RegionID, RegionDescription)
VALUES (@RegionID, @RegionDescription)

And here's my Visual Basic code. The parameter lines are incorrect. I have no idea how to work with parameters, or how to work VB with SQL, and very little VB experience. Any help would be appreciated!

Imports System.data.sqlclient
Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        
Dim sqlCmd = New SqlCommand

' Open database connection
        If sqlConNorthWind.State = ConnectionState.Closed Then
            sqlConNorthWind.Open()
        End If

        ' Set SQL command specifics
        sqlCmd.Connection = sqlConNorthWind
        sqlCmd.CommandText = "up_Region"
        sqlCmd.CommandType = CommandType.StoredProcedure

        Parameter(sqlCmd, "@RegionID", SqlDbType.Int, 4, txtRegionID.Text, ParameterDirection.Input)
        Parameter(sqlCmd, "@RegionDescription", SqlDbType.NChar, 50, txtRegionDescription.Text, ParameterDirection.Input)

        sqlCmd.Parameters.clear()

        ' Close database connection
        If sqlConNorthWind.State = ConnectionState.Open Then
            sqlConNorthWind.Close()
        End If
    End Sub
End Class

 

[tperri]

Try this.

CREATE PROCEDURE up_Region
(
@RegionID int,
@RegionDescription nchar(50)
)
AS
 
INSERT INTO Region (RegionID, RegionDescription)
VALUES (@RegionID, @RegionDescription)

GO

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        
Dim sqlCmd = New SqlCommand


        ' Set SQL command specifics
        sqlCmd.Connection = sqlConNorthWind
        sqlCmd.CommandText = "up_Region"
        sqlCmd.CommandType = CommandType.StoredProcedure

        sqlCmd.Parameters.Add("@RegionID", SqlDbType.Int).Value = txtRegionID.Text
        sqlCmd.Parameters.Add("@RegionDescription", SqlDbType.NChar, 50).Value = txtRegionDescription.Text
' Open database connection
        If sqlConNorthWind.State = ConnectionState.Closed Then
            
            sqlConNorthWind.Open()
            sqlCmd.ExecuteNonQuery
            sqlConNorthWind.Close()


        End If

        ' Close database connection
        If sqlConNorthWind.State = ConnectionState.Open Then
            sqlConNorthWind.Close()
        End If
    End Sub
End Class

 

[rickjamesb]

Works!!! Thank you very much for taking the time to help. I appreciate it.