[RESOLVED] [2005] VB.NET passing parameters to Stored Procedure

[smilbuta]

Hello community,
I am working on learning Stored procedures and parameter passing.

This is My VB code.

Imports System.Data
Imports System.Data.SqlClient


Public Class Form1


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim nm As String = TextBox1.Text
        Dim rm As Integer = TextBox2.Text

        Dim mySqlConnection As SqlConnection = New SqlConnection(getConnectionString)

        Dim mysqlcommand As SqlCommand = New SqlCommand("InsertNames", mySqlConnection)
        mysqlcommand.CommandType = CommandType.StoredProcedure
        mySqlConnection.Open()
        mysqlcommand.Parameters.AddWithValue("@name", TextBox1.Text)
        mysqlcommand.Parameters.AddWithValue("@number", TextBox2.Text)

        mySqlConnection.Close()


    End Sub

    Private Function getConnectionString() As String
        Return "my connection information Bla bla"
    End Function
End Class

This is my Stored procedure on a SQL 2005 server.

USE [TestingArea]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [smilbuta].[InsertNames]
	-- Add the parameters for the stored procedure here
	@name varchar(50),
	@number int
AS
BEGIN
	SET NOCOUNT ON;

  INSERT INTO StProc (fName, uRoom) VALUES (@name, @Number)
END

THe project is simple 1 form.. two text boxes , 1 holding a name and the other a number. a button to execute the procedure and pass the parms to the sql server to inserrt using a Stored procedure.
The Issue is i get no errors at all.. yet my DB is not updated...

[kleinma]

that is because all you are doing is opening and closing your connection....

after you add the params to the command object, you need to call executenonquery to actually tell the command to execute against the database.

mysqlcommand.ExecuteNonQuery() 'EXECUTES STORED PROC

Also make sure you are properly disposing of your database objects when you are doing using them.

[smilbuta]

THank you again kleinma,

but this may sound nieve.. but what do you mean Disopose of my Database objects?.. how do i do this correctly? I understand how to discard or Nothing out my form and objects in VB but how would i do so on the SQL server??.. or am i just totaly confusing myself...

[kleinma]

I just mean most DB classes in .NET implement IDisposable, which means they have a dispose method to clean up their connection to unmanaged (non framework) resources.

Here is a small example of how one might structure a piece of code to make sure the objects are getting cleaned up, and to have valid error handling, and clean up the objects even if an error happens.

Dim MySQLConnection As SqlClient.SqlConnection = Nothing
        Dim MySQLCommand As SqlClient.SqlCommand = Nothing
        Try
            MySQLConnection = New SqlClient.SqlConnection("CONNECTION STRING HERE")
            MySQLCommand = New SqlClient.SqlCommand("InsertNames", MySQLConnection)
            'DO DATABASE STUFF HERE
        Catch ex As Exception
            'DO ERROR HANDLING HERE
        Finally
            'DO CLEANUP HERE
            If MySQLCommand IsNot Nothing Then
                MySQLCommand.Dispose()
                MySQLCommand = Nothing
            End If
            If MySQLConnection IsNot Nothing Then
                If MySQLConnection.State <> ConnectionState.Closed Then
                    MySQLConnection.Close()
                End If
                MySQLConnection.Dispose()
                MySQLConnection = Nothing
            End If
        End Try

[smilbuta]

I'll go flog myself now....THanx!