RowDataBound(:

hello(:
i need another help for this prob..
i hav to knw hw to use rowDataBound(:

objective - gotta generate an email for the user who has created ticket on a problem wid all the details.

i'm able to retrieve all the records of the tickets bt i want to display only the specific record of the user.

how do i start abt doin tht??

pls help any help much appreciated..
thx(:

You should be doing the filtering of the records on the database side. Write a proper query that will give you the desired record.

What is it that you want to do with RowDataBound event?

hello Shuja Ali or any1 else who knows the answer to this qn,pls help

i have these codes which does the calculations for auto assigning of the tickets to the support staff to attend to their ticket.

but i'm not sure if i'm supposed to write these codes in the code-behind or in the classes where the sql statements are defined??

any idea?

Anything that has to do with the database should be written in the database side of the code or if you have Business Layer (classes that define business logic), then you should write it there.

i don't have a business layer class..i don't know what is that.. :(
i was told to put the codes in the classes page where i have the sql statements..
so is that the right way??

Yes that should be the right way of doing it.

okay..thanx so much..
another one mre qn!(:

the task which i'm doing is to actually assign the ticket submitted bny the user to a support staff automatically so they can attend to it..these support staffs have their own quotas..
so i came out with this formula to implement this concept..

the formula is -> QuotaAns = (1 / (TotalTickets + 1)) * Quota

the quotaAns will determine who to give the ticket to..this formula muz calculate for all the support staff and see who has the highest quota will receive the ticket..

for example: if support Staff A's Quota is 50% and B's quota is 30% and C's quota is 20%
the formula will be implemented this way: QuotaAns = (1/(1+1))*50%

so this will be used for all the support staff and who has the highest quotaAns gets the ticket.
the 1 always remains the same because its the one new ticket..total ticket is for that particular staff which will be accumilated..

i've got to implement this in my codes and i did this :

Public Function CalculateQuota() As Double
Dim newTicket As Integer = 1
Dim TotalTickets As Integer
Dim Quota As Double
Dim QuotaArr As String
Dim QuotaAns As Double

QuotaAns = (1 / (TotalTickets + 1)) * Quota

If QuotaAns < ((SupportStaffID = "10") And (SupportStaffID = "11") And (SupportStaffID = "15")) Then
End If

End Function

but i'm stuck half way..i don't know how to move on..any help pls??

i edited and came out with another kind of codes.
do u think its correct??
can u help me pls??
thx

Public Function CalculateQuota() As Double
Dim newTicket As Integer = 1
Dim TotalTickets As Integer
Dim Quota As Double
Dim QuotaArr() As Double
Dim QuotaAns As Double
Dim SupportStaffID As Integer
Dim i As Integer

For i = (SupportStaffID = 10) To (SupportStaffID = 15)
QuotaAns = (1 / (TotalTickets + 1)) * Quota
QuotaArr(i) = QuotaAns
Next i

If QuotaArr(i) > (SupportStaffID = 11) Or (SupportStaffID = 15) Then
newTicket = (SupportStaffID = 10)
ElseIf QuotaArr(i) > (SupportStaffID = 10) Or (SupportStaffID = 15) Then
newTicket = (SupportStaffID = 11)
Else
newTicket = (SupportStaffID = 15)
End If

End Function

Quota has no value assigned to it in the code posted.

Your if statements look like they need to be part of your loop.

Not sure about the for statement you are using, never seen one done like that before.

I also see no need for the QuotaArr. If the if statements where within the loop you could simply use the QuotaAns.

NewTicket is not used once assigned so I have no idea what it is for but once the statements are placed in the loop it will only conatin the value for the last run of the loop.

Your function does not return a value.

SupportStaffID is also not assigned but you are checking its value. Its value will always be 0 if you do not assign it.

Seems like maybe this needs to be a parameter passed to the function rather than a local variable.

ohh okok..
i kind of get what you are saying..
i'll try to go work on the codes again..
and see if it works..thanx alot for your help..
i'll get back soon!(:

hey i tried putting my if statements into the for loop and passed my supportStaffID parameter into the function and took out the QuotaArr which u found it redundant if its inside my loop..
but nth works..i'll post my codes here..if u don't mind,pls help me check and work with me to solve this pls..thanx alot(:

ok this is my UI for submit ticket :
i have the different fields such as the subject,description,due date,the category type etc..
and a submit button

as soon as the user fills in all the fields and clicks on submit,the system will save the records in the database and automatically assign the ticket to a support staff depending on the quota for each support staff..the quota is saved in the database and i've gotta retrieve the value from the database.

datz y i came out wid the formula :
QuotaAns = (1 / (TotalTickets + 1)) * Quota
1 stands for that one new ticket juz submitted and the total tickets stands for the total tickets the particular staff has currently and the quota amount should be retrieved from the database..
quotaAns is the value which i should compare with the other staffs to determine to who the new ticket should go to.

and i put in the codes in the classes whr i have the sql statements..
i have this sql statement to retrieve the number of total ticket each staff has:

Code:

---

Public Function DBSelect_TotalTickets(Optional ByVal SupportStaffID As Integer = 0) As DataTable
        Dim local_dbConn As New SqlConnection
        Dim local_sqlCommand As New SqlCommand

        local_dbConn = db_conn.DBConn
        local_sqlCommand.Connection = local_dbConn

        local_sqlCommand.CommandText = "SELECT COUNT(ID) FROM ITHelpDesk.dbo.Tickets where SupportStaffID = @xSupportStaffID"
        local_sqlCommand.CommandText = CommandType.Text

        Dim sql_adapter As New SqlDataAdapter(local_sqlCommand)
        Dim dt As New DataTable
        sql_adapter.Fill(dt)
        If dt.Rows.Count > 0 Then
            dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
        End If

        local_dbConn.Close()
        Return dt

    End Function

---

and i have this sql statement to retrieve the ID of the newest ticket inserted:

Code:

---

  Public Function DBSelect_NewTicketID() As DataTable
        Dim local_dbConn As New SqlConnection
        Dim local_sqlCommand As New SqlCommand

        local_dbConn = db_conn.DBConn
        local_sqlCommand.Connection = local_dbConn

        local_sqlCommand.CommandText = "SELECT MAX(ID) FROM ITHelpDesk.dbo.Tickets"
        local_sqlCommand.CommandText = CommandType.Text

        Dim sql_adapter As New SqlDataAdapter(local_sqlCommand)
        Dim dt As New DataTable
        sql_adapter.Fill(dt)
        If dt.Rows.Count > 0 Then
            dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
        End If

        local_dbConn.Close()
        Return dt

    End Function

---

and this the function i have to do the calculation;

Code:

---

  Public Function CalculateQuota(Optional ByVal SupportStaffID As Integer = 0) As Double
        Dim newTicket As Integer = 1
        Dim TotalTickets As Integer
        Dim Quota As Double
        ' Dim QuotaArr() As Double = Nothing
        Dim QuotaAns As Double
        Dim i As Integer

        For i = 10 To 15
            QuotaAns = (1 / (TotalTickets + 1)) * Quota
            'QuotaArr(i) = QuotaAns

            If QuotaAns > (SupportStaffID = 11) Or (SupportStaffID = 15) Then
                newTicket = (SupportStaffID = 10)

            ElseIf QuotaAns > (SupportStaffID = 10) Or (SupportStaffID = 15) Then
                newTicket = (SupportStaffID = 11)
            Else
                newTicket = (SupportStaffID = 15)
            End If
        Next i


    End Function

---

and this the code i have behind my button:

Code:

---

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim clsSupportStaffQuota_Obj As New clsSupportStaffQuota
        clsSupportStaffQuota_Obj.CalculateQuota()
    End Sub

---

i dunno what is going wrong here..please do help me..
it'll seriously be appreciated..
thx alot..do reply me asap..
thanx thanx!(:

i debugged my codes under the calculateQuota function..
but al the values are 0..
how do i pass in the value..
i seriously having so much of headache with this..
pls help me.. :(

I am not sure what you are trying to do with these lines of code

Code:

---

If QuotaAns > (SupportStaffID = 11) Or (SupportStaffID = 15) Then
                newTicket = (SupportStaffID = 10)

            ElseIf QuotaAns > (SupportStaffID = 10) Or (SupportStaffID = 15) Then
                newTicket = (SupportStaffID = 11)
            Else
                newTicket = (SupportStaffID = 15)
            End If

---

If I am not mistaken what you are in effect doing with this code is saying

If (QuotaAns > True) Or (SupportStaffID = 15) then
or
If (QuotaAns > False) Or (SupportStaffID = 15) then ' Depending on the value of supportstaffid

NewTicket=True ' or maybe NewTicket=False depending on the value passed in supportstaffid.

I doubt that this is what you are wanting to do here

As written it appears that the first part of the if will almost if not always be true Assuming QuotaAns > 0 or SupportStaffID=15

What value are you actually trying to assign to new ticket?

You also are not returning a value.

For example if you wanted the value of NewTicket returned to the calling code you would add a line near the bottom [just above the end fucntion]

Code:

---

Return NewTicket

---

This is totally wrong:

Code:

---

QuotaAns > (SupportStaffID = 11) Or (SupportStaffID = 15)

---

is the same thing as:

Code:

---

QuotaAns > (TRUE) Or (TRUE)

---

or

Code:

---

QuotaAns > (FALSE) Or (FALSE)

---

not what you want

Use Select Case for SupportStaffID

When you call the function you need to specify the SupportstaffID as it is currently it is always 0 and you need to use the return value. If you are not going to use a return value then you should be using a sub rather than a function.

Example

Code:

---

MyRetunredData=clsSupportStaffQuota_Obj.CalculateQuota(MySupportStaffID)

---

You also still have another issue where you are setting NewTicket during each pass of the loop. NewTicket will only have the last value assigned which is also something I think you would not be wanting here.

hmmm ok..i've changed it to sub now..
and i have another doubt..
in the formula,for the variable "TotalTickets"
how do i get the value??
i have the sql statement,but i don't know how to parse in the value..
pls help

I do not know enough about what you are trying to do to answer that question

Code:

---

If QuotaAns > (SupportStaffID = 11) Or (SupportStaffID = 15) Then
                newTicket = (SupportStaffID = 10)

            ElseIf QuotaAns > (SupportStaffID = 10) Or (SupportStaffID = 15) Then
                newTicket = (SupportStaffID = 11)
            Else
                newTicket = (SupportStaffID = 15)
            End If

---

what i'm trying to do here is actually :
if the quotaAns is greater then supportStaffID 11 and 15, the new ticket must be assigned to SUpportStaffID 10
and the next line is:
if the quotaAns is Greater then SupportSTaff ID 10 and 15, the new ticket must be assigned to SupportSTaffID 11
or else assign the new ticket to SUpportStaffID15

but i cant get this thing ryte..how?my formula is also not working

and btw..what is select case??

It is like and If ElseIF End IF block but easier to read

Example

Code:

---

Select Case MYVar
    Case 1
        'Do Something
    Case 2
        ' Do Something Different
    Case 3,4,5
        'Do Something Different
    Case Else
        'Do Something Else
End Select

---

Is the same as

Code:

---

If MYVar=1 Then
    'Do Something
ElseIF MyVar=2 then
    ' Do Something Different
ElseIf MyVar=3 or MyVar=4 or MyVar=5 Then
      'Do Something Different
Else
      'Do Something Else
End IF

---

yep i'm also trying that out..so sorry to trouble u so much..
but i seriously need your help..

but the problem now is :
i cant get my formula working..
because the totalTicket variable in my formula couldn read..
i want to know how to make the formula to get the value..
is it from the sql statement??

Quote:

---

Originally Posted by BabyAngel

what i'm trying to do here is actually :
if the quotaAns is greater then supportStaffID 11 and 15, the new ticket must be assigned to SUpportStaffID 10
and the next line is:
if the quotaAns is Greater then SupportSTaff ID 10 and 15, the new ticket must be assigned to SupportSTaffID 11
or else assign the new ticket to SUpportStaffID15

but i cant get this thing ryte..how?my formula is also not working

---

So SupportStaffID is an array?

If so then instead of (SupportStaffID=15) you may be wanting SupportStaffID(15)

Quote:

---

Originally Posted by BabyAngel

yep i'm also trying that out..so sorry to trouble u so much..
but i seriously need your help..

but the problem now is :
i cant get my formula working..
because the totalTicket variable in my formula couldn read..
i want to know how to make the formula to get the value..
is it from the sql statement??

---

I can not answer that. I do not know how you get your tickets nor how many you have or where that data may come from. This may need to come from your database or from user input but you are in a better position to answer that question than I would be.

yes it should be coming from the database..
i have this sql statement in my class to count how many tickets each support staffs has

Code:

---

    Public Function DBSelect_TotalTickets(Optional ByVal SupportStaffID As Integer = 0) As DataTable
        Dim local_dbConn As New SqlConnection
        Dim local_sqlCommand As New SqlCommand

        local_dbConn = db_conn.DBConn
        local_sqlCommand.Connection = local_dbConn

        local_sqlCommand.CommandText = "SELECT COUNT(ID) FROM ITHelpDesk.dbo.Tickets where SupportStaffID = @xSupportStaffID"
        local_sqlCommand.CommandText = CommandType.Text

        Dim sql_adapter As New SqlDataAdapter(local_sqlCommand)
        Dim dt As New DataTable
        sql_adapter.Fill(dt)
        If dt.Rows.Count > 0 Then
            dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
        End If

        local_dbConn.Close()
        Return dt

    End Function

---

but how do i call this in my formula??

nope supportStaffID must retrieve from database..not an array..i just tried putting the statement when the ID is equals to 15..but not wroking as well..

hey ya..
i tried another method to actually try to get the values in my formula..
so i changed my dim statements to this so that i can call the select statements and get the values..

Code:

---

  Dim newTicket As Integer = (Convert.ToString(Me.DBSelect_NewTicketID))
        Dim TotalTickets As Integer = (Convert.ToString(Me.DBSelect_TotalTickets))
        Dim Quota As Double = (Convert.ToString(Me.DBSelectQuota))
        Dim QuotaAns As Double

        QuotaAns = (1 / (TotalTickets + 1)) * Quota

---

and then i debugged my programe and i had this error message sayin
"Line 1: Incorrect syntax near '1'."

Code:

---

  Dim sql_adapter As New SqlDataAdapter(local_sqlCommand)
        Dim dt As New DataTable
        sql_adapter.Fill(dt)
        If dt.Rows.Count > 0 Then
            dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
        End If

---

thrid line from the start shows this error..
any idea what is it??

1 is NOT a Double, which is a problem. You can use a variable, or convert it. Look up TryCast()

erm i kind of dont get what you saying..

Quote:

---

Originally Posted by BabyAngel

hey ya..
i tried another method to actually try to get the values in my formula..
so i changed my dim statements to this so that i can call the select statements and get the values..

Code:

---

  Dim newTicket As Integer = (Convert.ToString(Me.DBSelect_NewTicketID))
        Dim TotalTickets As Integer = (Convert.ToString(Me.DBSelect_TotalTickets))
        Dim Quota As Double = (Convert.ToString(Me.DBSelectQuota))
        Dim QuotaAns As Double
 
        QuotaAns = (1 / (TotalTickets + 1)) * Quota

---

and then i debugged my programe and i had this error message sayin
"Line 1: Incorrect syntax near '1'."

Code:

---

  Dim sql_adapter As New SqlDataAdapter(local_sqlCommand)
        Dim dt As New DataTable
        sql_adapter.Fill(dt)
        If dt.Rows.Count > 0 Then
            dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
        End If

---

thrid line from the start shows this error..
any idea what is it??

---

3rd line in which piece of code?

What is the local_SqlCommand being passed?

note that it says '1' if it is a numeric field you should not be using the 's as they are for text fields and date fields not numbers.

Also I have no idea why you are using doubles here. I would think that dealing with tickets you would always want a whole number e.g. Integer rather than a fp number.

Why are you using convert.Tostring when you are assigning to a numeric variable? :confused:

3rd line from the second piece of code which starts with "dim sql_adapter"

i used double for the quotaAns and quota because they are in percentage..quota can be in decimals also that's why i dim it as double.

my tickets are dim as whole number as in integers.

then will it be possible if i change the convert.toString to Convert.toInt16??

because i seriously don't know how to solve this issue..have been trying lots of ways but to no avail.

please help!:(

thanx..

I have been able to point out a few issues that I saw but I really do not know what you are actually trying to do so I do not know how to tell you it should be done.

I know you want to give tickets to groups based on a set quota
I do not know how many tickets or how you need to use the quota.

If you have say 1000 tickets and need to distribute them among the groups according to the quota number then it is simple math Total * quota

If you need to see how many they have gotten previously and how many are currently available and then determine how many to give to each then that is quite abit different.

If you want to step through the tickets like you were dealing a hand of cards then that too is quite a bit different. All should be easy enough to do but I can't rreally help much without a clear picture of what you are starting with and what result you are looking for.

If you could describe your goal here in as much detail as possible then perhaps myself or someone else here can be of more assistance.

ok let me explain this to you...
this is a new IT Helpdesk system..

this system allows users to submit a ticket on any problem they face.

so they have to fill in like under which category is the prob.the subject and description etc...

as soon as they click on submit,the ticket should automatically be assigned to a support staff..

there are currently 3 support staffs..and each with a different quota..

example: support staff A has a quota of 40&#37;
Support staff B has a quota of 30%
Support staff C has a quota of 30%

the ticket should be assigned to one of the staff using the formula :
quotaAns = (1 / TotalTickets the particular staff has +1) * Quota

example for support staff A :
the formula will be used like this -> quotaAns = (1 / (1 +1)) * 40%

the total ticket is 1 because currently there is only one ticket submitted..there isn't any data in the tickets table now since its a new system..

lets say there is previously 5 tickets submitted..
so i hav to get among the 5 tickets,which ticket was done by this support staff A and put the value in the total tickets..

so i have to do the calculation to find which staff has the highest quota so i cann assign the ticket to them..

its like the highest quota get to do the ticket first..

so that is what my whole concept is about..

i hope you get the concept..please help me.. :(

thanx!

I only have a minute right now but I think I have a better understanding of what you are trying to do.

Your total tickets in this case would be all the tickets that have been processed so far + the new one.

This is not the exact code you should use but should give you an Idea of a workable solution.

Code:

---

Total=StaffATotal+StaffBTotal+StaffCTotal+1

StaffATarget=total*.40
StaffBTarget=total*.30
StaffCTarget=Total*.30

Select case True
  Case StaffATarget>StaffATotal
      GiveTicketToA
  Case StaffBTarget>StaffBTotal
      GiveTicketToB
  Case StaffCTarget>StaffCTotal
      GiveTicketToC
    Case Else
        GiveTicketToA
End Select

---

okay..
thanx alot..
i shall go try this out in my codes..
i'll get back if any problem again..
so sorry to trouble u this much..
but your help is really appreciated!:)

Who designed that system? It wouldn't even fly as homework, if you ask me.
You're basing new calls on the number of calls done in the past?

Just my 2¢...

excuse me??
i don't quite get what your trying to ask...
can u rephrase your question?
thanx

Code:

---

  Public Function DBSelect_TotalTickets(ByVal refSupportStaffID As Integer) As DataTable
        Dim local_dbConn As New SqlConnection
        Dim local_sqlCommand As New SqlCommand

        local_dbConn = db_conn.DBConn
        local_sqlCommand.Connection = local_dbConn

        If refSupportStaffID = 0 Then
            local_sqlCommand.CommandText = "SELECT COUNT(ID) FROM ITHelpdesk.dbo.Tickets"
        Else
            local_sqlCommand.CommandText = "SELECT COUNT(ID) FROM ITHelpdesk.dbo.Tickets where SupportStaffID = @xSupportStaffID"
            local_sqlCommand.Parameters.Add(New SqlParameter("@xSupportStaffID", refSupportStaffID))
        End If
        local_sqlCommand.CommandText = CommandType.Text

        Dim sql_adapter As New SqlDataAdapter(local_sqlCommand)
        Dim dt As New DataTable
        sql_adapter.Fill(dt)
        If dt.Rows.Count > 0 Then
            dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
        End If

        local_dbConn.Close()
        Return dt

    End Function

---

can someone tell me why i'm getting this error:
SqlException-Line 1: Incorrect syntax near '1'.

this error pops out at the line "sql_adapter.Fill(dt)"

how do i solve this??please help someone..:(

thanx

Looks like your parameter is being added as text but should be numeric.

Why are you returning a data table here when all you are getting is a single number?

hey thanx for replying..
actually i've got the answer about why i was getting this error..

instead of typing this -> local_sqlCommand.CommandType = CommandType.Text

i went to type wrongly like this -> local_sqlCommand.CommandText = CommandType.Text

so this caused the error..i managed to solve it..

will get back if there i encounter any problem..

thanx alot!(:

hello there..

i've got a question..

i am using the for loop method to do this looping..

i need to use the loop because, with that TicketCategoryID i have got another 2 more supportStaff under that category..

so i want to loop to get all the supportStaffID under that Particular ticket category..

what i've done: i submitted a ticket under a categoryID ' 3 ' and so i want to get the supportStaffID's under that CateogryID 3

so i want to use the loop to get and i got this codes :

Code:

---

  Public Function CalculateQuota(ByVal TicketCategoryID As Integer) As DataTable
        Dim dt As New DataTable

        For i As Integer = 0 To dt.Rows.Count - 1
            dt = Me.DBSelect_CatID(TicketCategoryID)
            dt = Me.DBSelect_TotalTickets(SupportStaffID)
        Next


        Return dt

    End Function

---

and i debugged,but its not running inside the loop..please help me check!


i also tried another method:

Code:

---

    Public Function CalculateQuota(ByVal TicketCategoryID As Integer) As DataTable
        Dim dt As New DataTable
        dt = Me.DBSelect_CatID(TicketCategoryID)
        For i As Integer = 0 To dt.Rows.Count - 1

            dt = Me.DBSelect_TotalTickets(SupportStaffID)
        Next


        Return dt

    End Function

---

but this when i debug,it goes into the for loop but doesn't go to the sql query..it just goes to the get method and the result is still 0..

how do i do this??please help..

thanx!

Quote:

---

Originally Posted by BabyAngel

i don't quite get what your trying to ask...
can u rephrase your question?
thanx

---

Is this a homework assignment? If so, it's not very good, except as an exercise. If anybody WROTE this according to specs, then the designer should be shot.

How far back do you want to go? What if someone has 4000 calls and the next guy has 3 calls?

its actually a project assignment for an intranet portal of this company..

we were told to do this ITHelpdesk system with the given task..

so this one of it..actually i totally agree with what you say..

haha..anyway thanx!(:

Quote:

---

Code:

---

  Public Function CalculateQuota(ByVal TicketCategoryID As Integer) As DataTable
        Dim dt As New DataTable

        For i As Integer = 0 To dt.Rows.Count - 1
            dt = Me.DBSelect_CatID(TicketCategoryID)
            dt = Me.DBSelect_TotalTickets(SupportStaffID)
        Next


        Return dt

    End Function

---

---

I can't make any sense of the code above. You are assigning a datatable [the same datatable] inside a loop and returning the datatable when the loop is complete. If this worked it would return only the last instance of the dt=.... SupportStaffID which would be just a single integer in a data table. I can't see where this would be of any use at all and a huge waste of resources.

I do not know what CatID is so not sure how that comes into play.

You could generate a query that returns the sum() or count() of the tickets [depending on how they are stored in the db [1 per record or 1 record per staffid with the total thus far] In the case of the former you would need to use count() and for the latter sum() would be used. You would select the staffid field along with the count or sum and group by staffid. The rsult will be 1 record per staffid containing the staffid and the total tickets for that staffid.

In the case of what we have saw in previous posts you would get a recordset with 3 records 2 fields each [staffid,TicketCount] The ticket count fromt hese 3 fields could then be placed into the StaffTotalA,B,C Vars that were in the previous example.

that is the problem..i have problem in using the for loop because i am not quite sure if my code is correct..

i want to know how to put in my code in the for loop to get the staffID..

the catID is actually the categoryID..its defined as DBSelect_CatID because this function has the sql query to get the supportStaffID and the quota
which looks like this:

Code:

---

Public Function DBSelect_CatID(ByVal refTicketCategoryID As Integer) As DataTable
        Dim local_dbConn As New SqlConnection
        Dim local_sqlCommand As New SqlCommand

        local_dbConn = db_conn.DBConn
        local_sqlCommand.Connection = local_dbConn

        If refTicketCategoryID = 0 Then
            local_sqlCommand.CommandText = "SELECT * FROM ITHelpdesk.dbo.SupportStaffQuota"
        Else
            local_sqlCommand.CommandText = "SELECT SupportStaffID,TicketQuota FROM ITHelpdesk.dbo.SupportStaffQuota where TicketCategoryID = @xTicketCategoryID "
            local_sqlCommand.Parameters.Add(New SqlParameter("@xTicketCategoryID", refTicketCategoryID))
        End If
        local_sqlCommand.CommandType = CommandType.Text

        Dim sql_adapter As New SqlDataAdapter(local_sqlCommand)
        Dim dt As New DataTable
        sql_adapter.Fill(dt)

        local_dbConn.Close()
        Return dt

    End Function

---

i have used the count() to get the total tickets..but the problem now is, i cant get the supportSTaffID,without that data, i wont be able to count the tickets for each supportStaff..
my count() sql query looks liek this:

Code:

---

  Public Function DBSelect_TotalTickets(ByVal refSupportStaffID As Integer) As DataTable
        Dim local_dbConn As New SqlConnection
        Dim local_sqlCommand As New SqlCommand

        local_dbConn = db_conn.DBConn
        local_sqlCommand.Connection = local_dbConn

        If refSupportStaffID = 0 Then
            local_sqlCommand.CommandText = "SELECT COUNT(ID) FROM ITHelpdesk.dbo.Tickets"
        Else
            local_sqlCommand.CommandText = "SELECT COUNT(ID) FROM ITHelpdesk.dbo.Tickets where SupportStaffID = @xSupportStaffID"
            local_sqlCommand.Parameters.Add(New SqlParameter("@xSupportStaffID", refSupportStaffID))
        End If
        local_sqlCommand.CommandType = CommandType.Text

        Dim sql_adapter As New SqlDataAdapter(local_sqlCommand)
        Dim dt As New DataTable
        sql_adapter.Fill(dt)

        local_dbConn.Close()
        Return dt

    End Function

---

i cant get any records at all..please help!! :(

i seriously dunno how to come out with this thing!please help!

Do you have one record per ticket or is there a field that has the number of tickets in it? If you have a field with the number of tickets what is the name of the field and does or will it have a value in it greater than 1?

Also how does the catid come into play here? I understand what the abrev means just not why it is used nor for what in terms of this calculataion. Seems like it is not needed unless I missed something.

Assuming for the sake of example there is one record per ticket and each record has the SupportStaffid and a field called id then.

Instead of this.

Code:

---

If refSupportStaffID = 0 Then
            local_sqlCommand.CommandText = "SELECT COUNT(ID) FROM ITHelpdesk.dbo.Tickets"
        Else
            local_sqlCommand.CommandText = "SELECT COUNT(ID) FROM ITHelpdesk.dbo.Tickets where SupportStaffID = @xSupportStaffID"
            local_sqlCommand.Parameters.Add(New SqlParameter("@xSupportStaffID", refSupportStaffID))
        End If

---

You would want something more like this.

Code:

---

local_sqlCommand.CommandText = "SELECT COUNT(ID) As TotalTickets,SupportStaffID  FROM ITHelpdesk.dbo.Tickets group by SupportStaffID"

---

This would result in a 3 records if you have at least one record for each supportstaffid in the database at the time. It will contain 2 fields TotalTickets and SupportstaffID

Once you have the datatable then you can read the records one by one in a loop and check the total for each staffid. You could also do it one at a time but it is better to get it in a single query.

there is only one record per ticket..

the catID is needed because currently the system has different categories..and each support staff are assgined to different category with a different quota..

for example there is category Apple, Banana and Orange
and there are 3 support STaff A,B and C
so its like for Category Apple - SupportStaff A's quota is 30&#37; , SupportSTaff B's quota is 40% and supportStaff C's quota is 30%
den for Category Banana - SupportStaff A's quota is 50%, SupportStaff B's Quota is 30% and SupportStaff C's quota is 20%
so lets say,when the user submitted the ticket under a category Apple, we have to get the catID so that we can know what is the quota assigned for the staff in that particular category..

this is how the catID plays a part..


and for ur 2nd reply, the sql thingy..
i must call this function behind my submitButton is it??

Ok so you would need to add the catid field into that query as well either as part of the where clause if you only want the data for one catid or as part of both the select and group by clause if you want all the totals.

As far as where to put it. That is hard for me to say. I do not really know enough about your overall code to know.

If it were me and I had to do something along the lines of what you have been describing [assuming I understand it now] I would probably create a function that would take a catid as a parameter and return the staffid to which the ticket should be assigned.

This function would include both the required query and the calculations based on the result of that query and the return would simply be the staff id that should get the new ticket. This function would likely be called from a command button or perhaps even be automated in some way. Not enough detail to say.

okay..

i kind of get what you are saying..
i shall go try to figure out something out of this

thanks a million for all the help you have given me.. :D

i go start trying out this thing now..(: