Solved

More sql questions

Posted on 2014-04-14
10
200 Views
Last Modified: 2014-04-15
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
Comment
Question by:ICantSee
[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
  • 5
  • 3
  • 2
10 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40000170
Is there anything SQL Server related to your question?
0
 

Author Comment

by:ICantSee
ID: 40000194
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40000205
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
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!

 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40000327
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
 

Author Comment

by:ICantSee
ID: 40001315
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
 

Author Comment

by:ICantSee
ID: 40001324
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40001337
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
 

Author Comment

by:ICantSee
ID: 40001767
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
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 40001780
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
 

Author Closing Comment

by:ICantSee
ID: 40002180
OUTSTANDING. Thank you VERY MUCH.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use SSRS to email customers? 4 29
Do not display comma when no last name 8 48
SQL Server replace between tags 6 25
TDE for SQL Web Edition 1 40
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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