Printable View
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.
You would want something more like 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
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 SupportstaffIDCode:local_sqlCommand.CommandText = "SELECT COUNT(ID) As TotalTickets,SupportStaffID FROM ITHelpdesk.dbo.Tickets group by 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% , 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..(:
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
i have this code under my dropdown list:Code: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
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..Code: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
please help someone!! :(
thanx!
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?
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?
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:
having this line,when i debug and step over,it goes to the "DBUpdateSupportStaffID" funtion and there comes the errorCode:clsTickets_Obj.DBUpdateSupportStaffID(clsTickets_Obj.DBSelect_NewTicketID)
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. "
error at line 386 :(Code: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
this is what i have in my update statement:
the primary key ID from the supportstaff table and its a foreign key in my tickets table!Code: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
please help me..thanx!(:
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.
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..
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?
yess it can allow duplicates..not unique key..ya there is records of that staff id..