Help with an update query

Hi All,

We have a budgeting table and a budgetAssistant table, I have 3 employee ID's,
I need to assign each of them as budget assistant. Do you know of a quick
update query I could write to accomplish this? The budget and
budgetAssistant tables both have a budgetID field. Any ideas?

I wanted to select those employees, then select all the budget then loop
through and add those 3 to each budget as a budgetAssistant.

Thanks,

~CKPerhaps if you included a bit more information, a psuedo example of
information in the tables would help a little.
CK wrote:

Quote:

Originally Posted by

Hi All,
>
We have a budgeting table and a budgetAssistant table, I have 3 employee ID's,
I need to assign each of them as budget assistant. Do you know of a quick
update query I could write to accomplish this? The budget and
budgetAssistant tables both have a budgetID field. Any ideas?
>
I wanted to select those employees, then select all the budget then loop
through and add those 3 to each budget as a budgetAssistant.
>
Thanks,
>
~CK

|||This might give you some ideas.

INSERT budgetAssistant (budgetID, employee)
SELECT B.budgetID, Add3.employee
FROM budgeting as B CROSS JOIN
(select 123 as employee UNION ALL
select 345 UNION ALL
select 567) AS Add3

Roy Harvey
Beacon Falls, CT

On Tue, 24 Oct 2006 19:53:31 GMT, "CK" <c_kettenbach@.hotmail.com>
wrote:

Quote:

Originally Posted by

>Hi All,
>
>We have a budgeting table and a budgetAssistant table, I have 3 employee ID's,
>I need to assign each of them as budget assistant. Do you know of a quick
>update query I could write to accomplish this? The budget and
>budgetAssistant tables both have a budgetID field. Any ideas?
>
I wanted to select those employees, then select all the budget then loop
>through and add those 3 to each budget as a budgetAssistant.
>
>Thanks,
>
>~CK
>

|||Thanks Roy!

I used this and it worked as well

INSERT INTO BudgetAssistant (BudgetID, EmployeeID)

SELECT * FROM (
SELECT BudgetID, EmployeeID FROM Budget, (
SELECT EmployeeID FROM TeamMember WHERE TeamID=96) A)tmp

"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:mb6tj212ahvgci94m8m4a1nfo0vg361jre@.4ax.com...

Quote:

Originally Posted by

This might give you some ideas.
>
INSERT budgetAssistant (budgetID, employee)
SELECT B.budgetID, Add3.employee
FROM budgeting as B CROSS JOIN
(select 123 as employee UNION ALL
select 345 UNION ALL
select 567) AS Add3
>
Roy Harvey
Beacon Falls, CT
>
On Tue, 24 Oct 2006 19:53:31 GMT, "CK" <c_kettenbach@.hotmail.com>
wrote:
>

Quote:

Originally Posted by

>>Hi All,
>>
>>We have a budgeting table and a budgetAssistant table, I have 3 employee
>>ID's,
>>I need to assign each of them as budget assistant. Do you know of a quick
>>update query I could write to accomplish this? The budget and
>>budgetAssistant tables both have a budgetID field. Any ideas?
>>
>I wanted to select those employees, then select all the budget then loop
>>through and add those 3 to each budget as a budgetAssistant.
>>
>>Thanks,
>>
>>~CK
>>

Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

AS

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm