Solved

More sql questions

Posted on 2014-04-14
10
186 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
  • 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now