Solved

Executing SQL Server stored procedure using VB.NET 2005

Posted on 2007-04-11
8
918 Views
Last Modified: 2013-11-26
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
 
0
Comment
Question by:J_Kogan
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
 

Author Comment

by:J_Kogan
Comment Utility
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
 
LVL 10

Accepted Solution

by:
adriankohws earned 500 total points
Comment Utility
After executing your stored procedure,

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

Expert Comment

by:Jeff Certain
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 10

Expert Comment

by:adriankohws
Comment Utility
Yes. Author, actually, mine should consider as an assisted answer, your initial question is about Stored Procedure.
0
 

Expert Comment

by:mseit
Comment Utility
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
 

Expert Comment

by:mseit
Comment Utility
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
 

Expert Comment

by:mseit
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now