• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 927
  • Last Modified:

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
 
0
J_Kogan
Asked:
J_Kogan
  • 3
  • 2
  • 2
  • +1
1 Solution
 
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now