Solved

Executing SQL Server stored procedure using VB.NET 2005

Posted on 2007-04-11
8
921 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18893347
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
ID: 18898757
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
ID: 18931950
After executing your stored procedure,

 Dim eString() As String = {_
                                           txtEmpID.Text, _
                                           txtStudentLN.Text, _
                                           Value3, _
                                           Value4
                                           }
datagridview.Rows.Add(eString)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18940458
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
 
LVL 10

Expert Comment

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

Expert Comment

by:mseit
ID: 22466187
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
ID: 22466192
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
ID: 22466202
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…

733 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