RowDataBound(:

[BabyAngel]

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..

[DataMiser]

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.

[BabyAngel]

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%
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!

[DataMiser]

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.

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

[BabyAngel]

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!

[dglienna]

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¢...

[BabyAngel]

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

[BabyAngel]

  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

[DataMiser]

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?

[BabyAngel]

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!(:

[BabyAngel]

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 :

  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:

    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!

[dglienna]

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?

[BabyAngel]

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!(:

[DataMiser]

  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.

[BabyAngel]

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:

 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:

  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!