[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Executing SQL Server stored procedure using VB.NET 2005

Posted on 2007-04-11
8
Medium Priority
?
925 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 1500 total points
ID: 18931950
After executing your stored procedure,

 Dim eString() As String = {_
                                           txtEmpID.Text, _
                                           txtStudentLN.Text, _
                                           Value3, _
                                           Value4
                                           }
datagridview.Rows.Add(eString)
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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