Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

More sql questions

I currently have a page that displays a record from the "requests" table.

If the request is approved I want the "approved column updated accordingly.

This would mean that the statement would have to recognize the current requestID and update its corresponding approved column.

The requests are currently being displayed in a detailsview. I know that I could simply include the approved column in the datasource for the detailsview, but I want other things to happen when the approved button is clicked, so I want to tie it to the approved button instead.

Approve Request button
0
ICantSee
Asked:
ICantSee
  • 5
  • 3
  • 2
1 Solution
 
Brian CroweCommented:
Is there anything SQL Server related to your question?
0
 
ICantSeeAuthor Commented:
yes.

The database is a SQL 2008 R2 database. I am trying to find the correct update syntax to update the approved column of a particular record.

My thought is to work off of the requestID of the record being shown in the detailsview, but I don't know how to build that into an update statement.
0
 
Brian CroweCommented:
And you're probably developing on a Windows machine but you didn't include your OS as a topic.  What I mean is, is there a SQL related answer to your question?  Do you need help with a stored procedure or something?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Carl TawnSystems and Integration DeveloperCommented:
Assuming you only have a single column key, you can pull the DataKey value from your details view, which should contain the primary key for the record being displayed in the DetailsView:
Dim requestID As Integer = CType(DetailsView1.DataKey.Value, Integer)

'// Build query using requestID, etc

Open in new window

0
 
ICantSeeAuthor Commented:
BriCrowe,

I'm sorry, I am not following your question.

I am using a Windows 7 Pro laptop running Visual Studio 2012. My programming language is VB. The app that I am working on is connecting to a Windows Server 2008 R2 server running SQL Server 2008 R2 SP1.

I have a request table inside my database name "requests"

The request management page has a detailsview that is bound to a dropdownlist. When a staff member has a pending request, his or her name appears in the dropdown list.

A supervisor will select the name from the dropdown list and their request is displayed in a detailsview.

If he decides to approve the request he will click on the approve request button, the approved column will be checked in the corresponding record (the part I am having trouble with) , and email will be sent to the appropriate parties.

There is a "code behind" page that drives this. The statement I am looking for will go in that code behind page.

Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Private _strsql As String
    Private Property strsql As String
        Get
            Return _strsql
        End Get
        Set(value As String)
            _strsql = value
        End Set
    End Property

    Protected Sub ApproveButton_Click(sender As Object, e As EventArgs) Handles ApproveButton.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        'Write form data to the database
        con = New SqlConnection("Data Source=sqlserver\DATA;Initial Catalog=BT;Integrated Security=True")
        con.Open()
        strsql = 

        cmd.CommandText = strsql
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        MsgBox ("Your request has been sent. You will be updated on its status via e-mail.")
        con.Close()
        con.Dispose()
    End Sub

Open in new window


So I guess my answer is I am looking for an update statement (I think).

Thank you for anytime that you spend on this. All assistance is appreciated.
0
 
ICantSeeAuthor Commented:
Carl Tawn

I think this is what I am looking for, but need a little more info. My programming language is VB if that is helpful.

Dim requestID As Integer = CType(DetailsView1.DataKey.Value, Integer)

'// Build query using requestID, etc

would I complete this just by saying something like:

update approved where requestid=requestid
0
 
Carl TawnSystems and Integration DeveloperCommented:
Yes, basically. I'm assuming you have set the DataKeyName property of your DetailsView to be "RequestID".

If you have then you would build your SQL string something like:
'// retrieve the ID of the currently displayed record
Dim requestID As Integer = CType(DetailsView1.DataKey.Value, Integer)

'// Build query to update "Approved" column based on key field
strsql = "UPDATE Requests SET Approved=1 WHERE RequestID = " & requestID

...

Open in new window

0
 
ICantSeeAuthor Commented:
Carl Tawn,

That works perfectly.

how would we add the comments box text to the database too? its column name is simply comments and its control name is CommentsTextbox ?

I hope I am not pressing my luck. I do appreciate the help.
0
 
Carl TawnSystems and Integration DeveloperCommented:
Where does the comments data live? Is it another column in the Requests table, or is it somewhere else?

If it's in the same table then you can just append it as an extra column in the update query:
'// retrieve the ID of the currently displayed record
Dim requestID As Integer = CType(DetailsView1.DataKey.Value, Integer)

'// Build query to update "Approved" column based on key field
strsql = "UPDATE Requests SET Approved=1, Comments = '" + CommentsTextBox + "' WHERE RequestID = " & requestID

Open in new window

Note: using inline SQL like this is a bit dodgy, and you'd be better off (and safer) using a parameterised query.

For reference that would look something like:
strsql = "UPDATE Requests SET Approved=@Approved, Comments=@Comments WHERE RequestID=@RequestID"

cmd.CommandText = strsql
cmd.Parameters.AddWithValue("@Approved", 1)
cmd.Parameters.AddWithValue("@Comments", CommentsTextBox.Text)
cmd.Parameters.AddWithValue("@RequestID", requestID)

cmd.Connection = con
cmd.ExecuteNonQuery()

'// etc, etc

Open in new window

0
 
ICantSeeAuthor Commented:
OUTSTANDING. Thank you VERY MUCH.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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