Executing SQL Server stored procedure using VB.NET 2005

I know how to run stored procedures using VB.NET 2003, but this code is not working in VB.Net 2005.
I have stored proc:
ALTER PROC [dbo].[proc_Enroll]
@RegID int
AS
UPDATE tbStudents
SET Groups = 'Admitted'
WHERE EmpID = @RegID
Description:
1. I want @RegID = txt Enroll.Text
2.I want to click btnEnroll and update the database
P.S. I can use GridView for updating, but I need only TextBox and Button
Thank you,
J_Kogan
 
J_KoganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff CertainCommented:
Assuming SQL Server:

Import System.Data.SqlClient

            Using conn As New SqlConnection("your connection string goes here")
                  Using cmd As New SqlCommand("proc_Enroll", conn)
                        cmd.CommandType = CommandType.StoredProcedure
                        ' TODO add type-checking for @RegId
                        cmd.Parameters.Add("@RegId", SqlDbType.Int).Value = txtEnroll.Text
                        cmd.ExecuteNonQuery()
                  End Using
            End Using
0
J_KoganAuthor Commented:
OK, Thank you
It isn’t finished.
This stored procedure can update my SQL Database, but this updating is not reflected on My Grid View.
For Example:
I use code to add New Student:
Protected Sub btnAddStudent_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddStudent.Click
       
       
        SqlDataSourceStudents.InsertParameters("EmpID").DefaultValue = txtEmpID.Text
        SqlDataSourceStudents.InsertParameters("StudentLN").DefaultValue = txtStudentLN.Text
        SqlDataSourceStudents.InsertParameters("StudentFN").DefaultValue = txtStudentFN.Text
        SqlDataSourceStudents.InsertParameters("Department").DefaultValue = ddlDepartment.Text
        SqlDataSourceStudents.InsertParameters("Groups").DefaultValue = ddlGroups.Text
        SqlDataSourceStudents.InsertParameters("ClassNum").DefaultValue = txtClassNum.Text
        SqlDataSourceStudents.InsertParameters("Comments").DefaultValue = txtCommentsSt.Text
        SqlDataSourceStudents.Insert()
       
    End Sub
And after one click I can see this New Student on My GridView,but then I Update my database with  your code (Thank you again) using stored procedure:
Protected Sub btnEnroll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEnroll.Click
       
        Dim con As New Data.SqlClient.SqlConnection("Data Source=DISPERSION;Initial Catalog=Training;Integrated Security=True")
        Dim cmd As New Data.SqlClient.SqlCommand("proc_Enroll", con)
        cmd.CommandType = Data.CommandType.StoredProcedure
        cmd.Parameters.Add("RegID", Data.SqlDbType.Int).Value = ddlEnroll.Text
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
       
    End Sub
This click can update my Database, but I can not see the reflection on GridView, I can see Reflection only if I click Edit Button on GridView.
I would like to click btnEnroll and see the result.
I used RaiseEvents for GridView (RowEditing, RowUpdating, RowUpdated), but they are not working.
Can you suggest me, how can I click btnEnroll and see updated field on my Grid View after execution of my stored procedure.
Thank you,
J_Kogan

0
adriankohwsCommented:
After executing your stored procedure,

 Dim eString() As String = {_
                                           txtEmpID.Text, _
                                           txtStudentLN.Text, _
                                           Value3, _
                                           Value4
                                           }
datagridview.Rows.Add(eString)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Jeff CertainCommented:
Did you know you can split points? Your original question asked how to run a stored procedure, which I answered. You then asked a second question, which adrian ably answered. Seems to me we should each get points.
0
adriankohwsCommented:
Yes. Author, actually, mine should consider as an assisted answer, your initial question is about Stored Procedure.
0
mseitCommented:
cnn.Open()
        cmd = New SqlCommand("MSE_OT_SP_UpdateMeter", cnn)
        trans = cnn.BeginTransaction
        cmd.Transaction = trans
        cmd.CommandType = CommandType.StoredProcedure
        'MsgBox(Me.lblNewRead.Text.Trim)
        With Me
            cmd.Parameters.Add("@EqNumber", .c1CboEqNumber.Text.Trim)
            cmd.Parameters.Add("@curentMeter1", .lblCurMeterRead.Text.Trim)
            cmd.Parameters.Add("@NewMeter1", .c1txtNewRead.Text.Trim)
            cmd.Parameters.Add("@USERID", Environment.UserName)
            cmd.Parameters.Add("@MECHINNAME", Environment.MachineName)
            cmd.Parameters.Add("@ITEMNMBR", .lblItemNumber.Text.Trim)
            cmd.Parameters.Add("@ModDate", CDate(.c1NewEntryDate.Text.Trim))
            If Me.rdoCurrReading.Checked = True Then cmd.Parameters.Add("@OldDate", CDate(.lblCurEntryDate.Text.Trim))
            If Me.rdoOldReading.Checked = True Then cmd.Parameters.Add("@OldDate", CDate(.c1EntryDt.Text.Trim))
            cmd.Parameters.Add("@met", intMeterFlag) '@EntryType
            If Me.rdoOldReading.Checked = True Then
                cmd.Parameters.Add("@EntryType", 1)
            ElseIf Me.rdoCurrReading.Checked = True Then
                cmd.Parameters.Add("@EntryType", 0)
            End If  'DEX_ROW_ID
            cmd.Parameters.Add("@DEX_ROW_ID", DEX_ROW_ID)
            DEX_ROW_ID = 0
            intMeterFlag = 0
        End With
        Try
            cmd.ExecuteNonQuery()
            trans.Commit()
cnn.Open()
        cmd = New SqlCommand("MSE_OT_SP_UpdateMeter", cnn)
        trans = cnn.BeginTransaction
        cmd.Transaction = trans
        cmd.CommandType = CommandType.StoredProcedure
        'MsgBox(Me.lblNewRead.Text.Trim)
        With Me
            cmd.Parameters.Add("@EqNumber", .c1CboEqNumber.Text.Trim)
            cmd.Parameters.Add("@curentMeter1", .lblCurMeterRead.Text.Trim)
            cmd.Parameters.Add("@NewMeter1", .c1txtNewRead.Text.Trim)
            cmd.Parameters.Add("@USERID", Environment.UserName)
            cmd.Parameters.Add("@MECHINNAME", Environment.MachineName)
            cmd.Parameters.Add("@ITEMNMBR", .lblItemNumber.Text.Trim)
            cmd.Parameters.Add("@ModDate", CDate(.c1NewEntryDate.Text.Trim))
            If Me.rdoCurrReading.Checked = True Then cmd.Parameters.Add("@OldDate", CDate(.lblCurEntryDate.Text.Trim))
            If Me.rdoOldReading.Checked = True Then cmd.Parameters.Add("@OldDate", CDate(.c1EntryDt.Text.Trim))
            cmd.Parameters.Add("@met", intMeterFlag) '@EntryType
            If Me.rdoOldReading.Checked = True Then
                cmd.Parameters.Add("@EntryType", 1)
            ElseIf Me.rdoCurrReading.Checked = True Then
                cmd.Parameters.Add("@EntryType", 0)
            End If  'DEX_ROW_ID
            cmd.Parameters.Add("@DEX_ROW_ID", DEX_ROW_ID)
            DEX_ROW_ID = 0
            intMeterFlag = 0
        End With
        Try
            cmd.ExecuteNonQuery()
            trans.Commit()

Open in new window

0
mseitCommented:
venkey:
This stored procedure can update my SQL Database,
For Example:

 cnn.ConnectionString = ""
        cnn.Open()
        cmd = New SqlCommand("SP_UpdateMeter", cnn)
        trans = cnn.BeginTransaction
        cmd.Transaction = trans
        cmd.CommandType = CommandType.StoredProcedure
               With Me
            cmd.Parameters.Add("@EqNumber", .c1CboEqNumber.Text.Trim)
            cmd.Parameters.Add("@curentMeter1", .lblCurMeterRead.Text.Trim)
            cmd.Parameters.Add("@NewMeter1", .c1txtNewRead.Text.Trim)
            cmd.Parameters.Add("@USERID", Environment.UserName)
            cmd.Parameters.Add("@MECHINNAME", Environment.MachineName)
            cmd.Parameters.Add("@ITEMNMBR", .lblItemNumber.Text.Trim)
            cmd.Parameters.Add("@ModDate", CDate(.c1NewEntryDate.Text.Trim))
            If Me.rdoCurrReading.Checked = True Then cmd.Parameters.Add("@OldDate", CDate(.lblCurEntryDate.Text.Trim))
            If Me.rdoOldReading.Checked = True Then cmd.Parameters.Add("@OldDate", CDate(.c1EntryDt.Text.Trim))
            cmd.Parameters.Add("@met", intMeterFlag) '@EntryType
            If Me.rdoOldReading.Checked = True Then
                cmd.Parameters.Add("@EntryType", 1)
            ElseIf Me.rdoCurrReading.Checked = True Then
                cmd.Parameters.Add("@EntryType", 0)
            End If  'DEX_ROW_ID
            cmd.Parameters.Add("@DEX_ROW_ID", DEX_ROW_ID)
        End With
        Try
            cmd.ExecuteNonQuery()
            trans.Commit()
MsgBox("Successfully Updated ", MsgBoxStyle.ApplicationModal, "IT-Help")
            cnn.Close()
       Catch ex As SqlException
            trans.Rollback()
            cnn.Close()
            MsgBox("Unable to update" & vbCrLf & ex.Message, MsgBoxStyle.Critical, "IT-HELP")
        End Try

Open in new window

0
mseitCommented:
L.S.Venkatesh
  how to call the stroed procedure in vb.net.   This stored procedure can update my SQL Database
For Example:

 cnn.ConnectionString = "user id=sa;Pwd=sa123;data source=db1;persist security info=False;initial catalog=eva25"
        cnn.Open()
        cmd = New SqlCommand("SP_UpdateMeter", cnn)
        trans = cnn.BeginTransaction
        cmd.Transaction = trans
        cmd.CommandType = CommandType.StoredProcedure
        'MsgBox(Me.lblNewRead.Text.Trim)
        With Me
            cmd.Parameters.Add("@EqNumber", .c1CboEqNumber.Text.Trim)
            cmd.Parameters.Add("@curentMeter1", .lblCurMeterRead.Text.Trim)
            cmd.Parameters.Add("@NewMeter1", .c1txtNewRead.Text.Trim)
            cmd.Parameters.Add("@USERID", Environment.UserName)
            cmd.Parameters.Add("@MECHINNAME", Environment.MachineName)
            cmd.Parameters.Add("@ITEMNMBR", .lblItemNumber.Text.Trim)
            cmd.Parameters.Add("@ModDate", CDate(.c1NewEntryDate.Text.Trim))
            If Me.rdoCurrReading.Checked = True Then cmd.Parameters.Add("@OldDate", CDate(.lblCurEntryDate.Text.Trim))
            If Me.rdoOldReading.Checked = True Then cmd.Parameters.Add("@OldDate", CDate(.c1EntryDt.Text.Trim))
            cmd.Parameters.Add("@met", intMeterFlag) '@EntryType
            If Me.rdoOldReading.Checked = True Then
                cmd.Parameters.Add("@EntryType", 1)
            ElseIf Me.rdoCurrReading.Checked = True Then
                cmd.Parameters.Add("@EntryType", 0)
            End If  'DEX_ROW_ID
            cmd.Parameters.Add("@DEX_ROW_ID", DEX_ROW_ID)
            DEX_ROW_ID = 0
            intMeterFlag = 0
        End With
        Try
            cmd.ExecuteNonQuery()
            trans.Commit()
            MsgBox("Successfully Updated ", MsgBoxStyle.ApplicationModal, "IT-Help")
            With Me
                .c1CboEqNumber.Text = ""
                .lblItemNumber.Text = ""
                .lblCurMeterRead.Text = 0
                .c1txtNewRead.Text = 0
            End With
            cnn.Close()
            Me.rdoMeter1.Checked = False
            Me.rdoMeter2.Checked = False
        Catch ex As SqlException
            trans.Rollback()
            cnn.Close()
            MsgBox("Unable to update" & vbCrLf & ex.Message, MsgBoxStyle.Critical, "IT-HELP")
        End Try

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.