?
Solved

Running a stored procedure when selecting a row in a gridview

Posted on 2008-10-08
5
Medium Priority
?
159 Views
Last Modified: 2013-11-26
I have a grid view which displays a list of events what I want to do is click the select button to select the row and then this will run a stored procedure to add the currently logged on person to the event attendance table.  I have got so far but I am unsure how to pass the eventID to the stored procedure as a parameter.

Below is the code so far I need someone to tell me what I need to do to complete the Private Sub GrdTrainEvents_RowCommand section below:-

1. a module with database function

Public Sub pageAccessed(ByVal eventID As String)
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings(("ConnectionString")).ConnectionString)
        Dim sqlStr As String = "spInsertEventsAttendance"
        Dim sqlCommand As New SqlCommand(sqlStr, conn)

        sqlCommand.CommandType = CommandType.StoredProcedure
        sqlCommand.Parameters.Add("@eventID", SqlDbType.Char, 10).Value = eventID
        sqlCommand.Parameters.Add("@userID", SqlDbType.Char, 10).Value = userID

        conn.Open()
        sqlCommand.ExecuteNonQuery()
        conn.Close()
    End Sub

2. the code page behind the page contains the following

Private Sub loadingPage()
        Dim UserID As Integer

        UserID = Right(User.Identity.Name, 6)

        ds_QLP_Events.SelectParameters(0).DefaultValue = UserID
        GrdTrainEvents.DataBind()

    End Sub

    Private Sub GrdTrainEvents_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GrdTrainEvents.RowCommand
        If e.CommandName = "select" Then

            DatabaseFunctions.pageAccessed()
        End If
    End Sub

0
Comment
Question by:WNottsC
  • 2
3 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 22670375
You can make sqlStr = "EXECUTE spInsertEventsAttendance @EventID = " & eventid & ", @UserID = " & userid
Then you don't have to use the command parameters
0
 
LVL 7

Accepted Solution

by:
technofile earned 2000 total points
ID: 22670585
      You need to create a SQLParameter object and then add that. Try This.


         Dim sqlStr As String = "spInsertEventsAttendance"
        Dim sqlCommand As New SqlCommand(sqlStr, conn)
        Dim param As New SqlParameter
        sqlCommand.CommandType = CommandType.StoredProcedure

        param = New SqlParameter("@eventID", SqlDbType.Char, 10)
        param.Value = eventID
        sqlCommand.Parameters.Add(param)
        param = New SqlParameter("@userID", SqlDbType.Char, 10)
        param.Value = eventID
        sqlCommand.Parameters.Add(param)


        conn.Open()
        sqlCommand.ExecuteNonQuery()
        conn.Close()

0
 
LVL 7

Expert Comment

by:technofile
ID: 22670610

typo...
        param = New SqlParameter("@userID", SqlDbType.Char, 10)
        param.Value = userID <----{not eventID again}
        sqlCommand.Parameters.Add(param)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

840 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