RowDataBound(:

[BabyAngel]

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

[Shuja Ali]

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?

[BabyAngel]

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?

[Shuja Ali]

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.

[BabyAngel]

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

[Shuja Ali]

Yes that should be the right way of doing it.

[BabyAngel]

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

[BabyAngel]

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

[DataMiser]

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.

[BabyAngel]

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

[BabyAngel]

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

[BabyAngel]

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:

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:

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;

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:

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

[BabyAngel]

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

[DataMiser]

I am not sure what you are trying to do with these lines of 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]

Return NewTicket

[dglienna]

This is totally wrong:

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

is the same thing as:

QuotaAns > (TRUE) Or (TRUE)

or


QuotaAns > (FALSE) Or (FALSE)


not what you want

Use Select Case for SupportStaffID

[DataMiser]

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

[BabyAngel]

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

[DataMiser]

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

[BabyAngel]

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

[BabyAngel]

and btw..what is select case??

[DataMiser]

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

Example

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

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

[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??

[DataMiser]

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)

[DataMiser]

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.

[BabyAngel]

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

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

[BabyAngel]

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

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

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

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

[dglienna]

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

[BabyAngel]

erm i kind of dont get what you saying..

[DataMiser]

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

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

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:

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

[DataMiser]

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.

[DataMiser]

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


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.

[BabyAngel]

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% , 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??

[DataMiser]

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.

[BabyAngel]

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

[BabyAngel]

hello there..
i've got a new problem which i'm facing now..

i'm now working on a search function..i'm suppose to search the tickets based on the category..
i've got a dropdown list on my UI so user can select the category and click on the submit button to get the result..

i've done these codes below but it doesn't seem to work..can someone help me with this?thx

Sub SearchCat(Optional ByVal IsDataBaseSelect As String = "")
If IsDataBaseSelect <> "" Then
Dim clsTickets_Obj As New clsTickets

If lblCategory.Text <> "" Then
If ddl_Category.SelectedIndex <> -1 Then
lblCategory.Text = ddl_Category.SelectedItem.Text
End If
dt = clsTickets_Obj.DBSelect_ByTicketCategoryID(ddl_Category.SelectedValue)
'dt = clsTickets_Obj.DBSelect_ByTicketCategoryID(lblCategory.Text)
Else
dt = clsTickets_Obj.DBSelect_ByTicketCategoryID
End If

' Keep The Data on view state
ViewState("dt") = dt
GridView1.EditIndex = -1
Else
dt = DirectCast(ViewState("dt"), DataTable)
End If

GridView1.DataSource = dt
GridView1.DataBind()
End Sub


i have this code under my dropdown list:

Protected Sub ddl_Category_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddl_Category.PreRender
If Page.IsPostBack = False Then
Dim ClsTicketCategory_Obj As New clsTicketCategory

ddl_Category.DataSource = ClsTicketCategory_Obj.DBSelect()
ddl_Category.DataTextField = "Name0"
ddl_Category.DataValueField = "ID"
ddl_Category.DataBind()

' Set Default To Printers
If ddl_Category.Items.Count > 0 Then
If ddl_Category.Items.FindByText("NoteBooks").Text <> "" Then
ddl_Category.Items.FindByText("NoteBooks").Selected = True
' Putting the Title on the GUI
lblCategory.Text = ddl_Category.SelectedItem.Text
Else
ddl_Category.Items(0).Selected = True
' Putting the Title on the GUI
lblCategory.Text = ddl_Category.SelectedItem.Text
End If
End If
BindGridView("ChangeValue")
End If
End Sub


but when i run the page,i select the category,but i cant get the result which i expect..it actually retrieves all the data from the database..

please help someone!! :(
thanx!

[DataMiser]

I can't tell much from what you have posted I assume you are calling some functions that were not posted and that is where the actual selecting is done so there is nothing here to indicate what the problem may be.

On another note the part under the combo box looks rather odd.
It appears that If the text "NoteBooks" exists in the list then it will always be selected and if not then the first item will always be selected? Somehow I doubt that is the way you want it to work. If it is the way you want it then why even use a combo box?

[BabyAngel]

actually i dont want the default value to be notebooks when the page is run..i want the value to be "select"..i dont know how to make it that way..any ideas?

[BabyAngel]

hey ya..i managed to solve this issue!(:

i've got one more problem..i guess you remember my questions on the auto assign quota system..(:

i managed to solve that issue as in,i'm able to get the quota and the supportstaffid to whom the new ticket should be assigned to..
everything is working fine except that,after getting the new supportstaffid, i want to update my tickets database table to that id..

i've got this line of statement in behind my submit button:

clsTickets_Obj.DBUpdateSupportStaffID(clsTickets_Obj.DBSelect_NewTicketID)


having this line,when i debug and step over,it goes to the "DBUpdateSupportStaffID" funtion and there comes the error
which says :
"UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Tickets_SupportStaff'. The conflict occurred in database 'ITHelpdesk', table 'SupportStaff', column 'ID'.
The statement has been terminated. "


Line 384: local_sqlCommand.Parameters.Add(New SqlParameter("@xID", refID))
Line 385: 'local_sqlCommand.Parameters.Add(New SqlParameter("@xNewTicket", refID))
Line 386: local_sqlCommand.ExecuteNonQuery()
Line 387: local_dbConn.Close()
Line 388: End Sub

error at line 386 :(

this is what i have in my update statement:

Sub DBUpdateSupportStaffID(ByVal refID As Integer)
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 = "Update ITHelpdesk.dbo.Tickets set SupportStaffID = @xSupportStaffID where ID = @xID"
local_sqlCommand.CommandType = CommandType.Text
local_sqlCommand.Parameters.Add(New SqlParameter("@xSupportStaffID", SupportStaffID))
local_sqlCommand.Parameters.Add(New SqlParameter("@xID", refID))
'local_sqlCommand.Parameters.Add(New SqlParameter("@xNewTicket", refID))
local_sqlCommand.ExecuteNonQuery()
local_dbConn.Close()
End Sub


the primary key ID from the supportstaff table and its a foreign key in my tickets table!

please help me..thanx!(:

[DataMiser]

Where is the supportstaffid coming from? You are passing the id via refID but I do not see anythign that assigns the supportstaffid a value which could mean that it is empty and would likely cause this issue. If it is a public variable assigned elsewhere then maybe it has a value and maybe not but in the code posted there is no assignment.

Ideally supportstaffid should be passed as a parameter into the sub along with the ticketid.

The only other things I can think of is that the staffid assigned is not present in the staff table, or there is a restriction setup on your ticket table to not allow dupes in this column and the staffid is already present in another record.

[BabyAngel]

actually everythin works fine except that it doesn't update the ticket table with that new support staff id..i debugged my codes and my the suppoert staff id which the ticket should be assigned to is 70..but the tickets table didnt update that column..the staff table has a record with the staff id 70..

[DataMiser]

Is the ticket table set to allow duplicates in this column? How about your indexes on this table? Are there already records in the ticket table with a staffid of 70? Have you tried using a STaffID that is present in the staff table but not yet used in the tickets table?

[BabyAngel]

yess it can allow duplicates..not unique key..ya there is records of that staff id..