How To Store Ticket Ages?
Dec 28, 2007
Hello there. I've been debating the best way to store the age of a ticket in a queue. Here's a little overview:
The application has a form that has users enter the current count and oldest ages of tickets in several queues. The format in the application they pull this data from stores it as Day:Hour:Minute (0:04:59)
The first time around, I stored them as datetime. But since you can't store a Day as zero (some tickets may be 0 days, 23:54 Hours old) I stored the day as a year. Well that proved to be stupid, because the averages in reports were way off. Next I stored the age as text, like this: 10459 (1 Day, 04:59 Hours old). Now my problem is when using that number in a report, I have to convert it to decimal and multiply the day by 24 and add it to the hour, just so they will be weighted correctly.
This is a mess... and I've searched all over the internet for examples, but every search result is for storing a persons age, not the age of a ticket.
Any one have any ideas?
View 2 Replies
ADVERTISEMENT
Jul 3, 2007
When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si!
View 7 Replies
View Related
Aug 2, 2007
Hey all! First post-- I apologize for the newbness of the question, but I'm having an issue with a basic query... here goes:
I want to calculate the percentages of statuses found from two tables. Let say T1 is my list of statuses:
T1
Passed
Failed
Not Completed
and T2 is the table where I derive my total # of statues for each id and how many in each status.
T2
ID, Desc, Status , numtimesinstatus
1, Test1, Failed, 3
1, Test1, Passed, 1
3, Blah, Failed, 5
3, Blah, Not Completed, 1
What I'd like to do is get the percentages for each available status in T1, from T2-- I can do this; however, I'm having trouble in the cases where a particular ID hasn't been in one of the status (in other words, calculating a 0%). What I'd like to get from these two tables is:
Result
ID, Desc, Status, %age
1, Test1, Failed, 75.0
1, Test1, Passed, 25.0
1, Test1, Not Completed, 0.0
3, Blah, Failed, 83.3
3, Blah, Passed, 16.67
3, Blah, Not Completed, 0.0
Can anyone shed some light? Thanks!
View 5 Replies
View Related
Jan 19, 2004
Hope someone can help me with this because its driving me potty!
I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-3 minutes) and then fails, I'm assuming because it times out. I then check the SQL by excecuting it via query analyzer and it again takes ages but will work eventually (I'm assuming because this bypasses the timeout settings, but changing these isn't on).
This happens randomly, the scripts will be working fine and then fail a few times before magically working again!
Any ideas? Perhaps some database features that commonly cause this problem? The problem only occurs with one database, all our others are fine but we can't spot any differences!
Any help or tips would really be appreciated.
Thanks.
View 5 Replies
View Related
May 30, 2007
I need to provide a way to handle groups that buy tickets in bulk lots, say 20 at a time. Users can later register with a "ticket code". I need to tie these two together and provide a way for groups to buy more tickets at a later time.
There should be a code that can be generated for each ticket that a group buys. This ticket is then to be given to a user to register with. A rogue user should not find it easy to guess the code. So, I want to generate a code for each group as they register, that is a random 6 or 7 digit, alphanumeric string and then something similar for a ticket. See table definitions below.
So for a ticket code, we could have something like this:
A23486D_TR34, where A23486D would be the group code
comments/suggestions/ideas welcome
At present Ive built two tables, [groups] and [group_tickets]
[group]
id int(10) unsigned
name varchar(45)
phone varchar(10)
email varchar(45)
contact_namevarchar(100)
signup_datedatetime
last_updatedatetime
group_codevarchar(6)
addr1 varchar(45)
addr2 varchar(45)
city varchar(45)
state char(2)
zip varchar(10)
[group_ticket]
id int(10) unsigned
group_idint(10) unsigned
purchase_datedatetime
fill_datedatetime
ticket_notestext
ticket_code varchar(12)
View 1 Replies
View Related
Mar 7, 2007
I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").
The data is stored something similar to the following...
Ticket Date Status
------ ---- ------
333 14/03/2005 10:24:19 "to on hold"
333 14/03/2005 15:23:01 "from on hold"
334 14/03/2005 11:14:11 "to on hold"
334 14/03/2005 16:26:15 "from on hold"
335 15/03/2005 10:10:15 "to on hold"
335 15/03/2005 11:15:35 "from on hold"
335 15/03/2005 13:26:10 "to on hold"
335 15/03/2005 14:30:59 "from on hold"
335 16/03/2005 14:00:05 "to on hold"
335 16/03/2005 16:45:15 "from on hold"
336 16/03/2005 10:10:15 "to on hold"
336 16/03/2005 12:45:12 "from on hold"
This is the result i'd like to see....
Ticket Start Hold End Hold Time Suspended
------ ---------- -------- --------------
333 14/03/2005 10:24:19 14/03/2005 15:23:01 datediff(...
334 14/03/2005 11:14:11 14/03/2005 16:26:15 datediff(...
335 15/03/2005 10:10:15 15/03/2005 11:15:35 datediff(...
335 15/03/2005 13:26:10 15/03/2005 14:30:59 datediff(...
335 16/03/2005 14:00:05 16/03/2005 16:45:15 datediff(...
336 16/03/2005 10:10:15 16/03/2005 12:45:12 datediff(...
Has anybody done something along the same lines? If so, could you point me in the right direction?
Thanks in anticipation
Jon
View 6 Replies
View Related
Apr 4, 2007
Hi,
I am new to Reporting Services. I want to use Reporting Services 2005 in our application.
My custom web application is on one machine and Reporting Services 2005 is on other machine. I am using Forms Authentication and using Single Sign On for login
If my custom web application and Reporting Services are on same machin I can get "Authentication Ticket" issued by ReportingServices2005 to the Report server.
But in this case I am not able to get "Authentication Ticket" on Report Server since it is on other machine.
I am using ReportViewer control in my web application to display Reports and using LogonUser method to get the Authentication Ticket.
How can I pass CookieAuthentication ticket from my Custom Web Application to Report Server?
Is there any work around to pass Authentication Cookie across Domain or any other solution for this?
Regards
Amit
View 3 Replies
View Related
Mar 14, 2014
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef
HQ 123 1 Jim 4444 ABCDE
HQ 123 2 Bob 5555 ABCDE
HQ 123 3 Jim 6666 ABCDE
HQ 123 4 Bob 7777 ABCDE
HQ 124 1 Jenny 8888 FGHIJ
HQ 124 2 Jenny 9999 FGHIJ
HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE
Jim 4444/
6666
Bob 5555
7777
--BookingRef FGHIJ
Jenny 8888/
9999/
3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
View 4 Replies
View Related
Mar 21, 2006
My question takes two parts; firstly, is the new table that I'm proposing going to handle the business logic I describe below, and secondly, if I put the new table in, how in hell do I use it?
Right; present schema attached.
The idea, which I hope is fairly clear from the schema, is that you send it a buch of parameters about the event, admission date, etc, and it will return all tickets matching those parameters. An example stored proc for this is below:
CREATE PROCEDURE [dbo].[getSingleTicketsByParameters]
@eventIdINT,
@standIdINT,
@admissionDateIdINT,
@bookingDateIdINT,
@concessionIdINT,
@bookingMinQuantityIdINT,
@bookingMaxQuantityIdINT,
@membershipIdINT
AS
SET NOCOUNT ON
SELECT
[tblTickets].[id],
[tblTickets].[booking_date_id],
[tblTickets].[booking_min_quantity_id],
[tblTickets].[booking_max_quantity_id],
[tblTickets].[ticket_concession_id],
[tblTickets].[membership_id],
[tblTickets].[event_id],
[tblTickets].[stand_id],
[tblTickets].[admission_date_id],
[tblTickets].[price],
[tblTickets].[availability],
[tblTickets].[description],
[tblTickets].[admin_description],
[tblTickets].[ticket_open],
[tblTickets].[ticket_live],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date],
[tblTicketConcessions].[concession_name],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblAdmissionDates].[description],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblMemberships].[membership_name]
FROM [tblTickets]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
AND ([tblEvents].[id]=@eventId OR @eventId=0)
AND ([tblStands].[id]=@standId OR @standId=0)
AND ([tblTicketConcessions].[id]=@concessionId OR @concessionId=0)
AND ([tblAdmissionDates].[id]=@admissionDateId OR @admissionDateId=0)
AND ([tblBookingDates].[id]=@bookingDateId OR @bookingDateId=0)
AND ([tblBookingMinQuantities].[id]=@bookingMinQuantityId OR @bookingMinQuantityId=0)
AND ([tblBookingMaxQuantities].[id]=@bookingMaxQuantityId OR @bookingMaxQuantityId=0)
AND ([tblMemberships].[id]=@membershipId OR @membershipId=0)
GO
So. It's all about to get horribly, horribly complex (well, it is to me) so take a deep breath.
Tickets are subject to quotas. However, quotas are subject to... well, at the moment they can be based on event, stand, admission date, concession, or any combination of these. They can also be based on an individual ticket. All quotas, however, are annual; they only apply to ticket purchases in the same year. For example:
- you can't buy more than 4 tickets for date A, in stand B at event C, per year.
- you can't buy more than 2 tickets for stand D per year.
- you can't buy more than 4 of ticket number 123.
Now, I'm thinking that all I need to manage this is one table, and it's going to look a little like this:
tblQuotas
id INT PK
ticket_id INT FK
event_id INT FK
stand_id INT FK
admission_date_id INT FK
quota INT
So, if I put a record in there with an event, stand and admission date - and a quota - then I've met the first business rule that I described above. If I put in a record with just a stand id and a quota, then I've met the second sort. If I put in one with just a ticket id and a quota, then I've met the third.
Now we return to that big SQL statement above. The one that says "get me all eligible tickets that match these parameters". And it's got to get a lot bigger because I now need to not only join in tblQuotas, to see if any quotas apply to the ticket I've chosen (or to the event, stand, etc that make it up), but I've also got to join in tblBasket, and tblOrders, and tblUsers, to find out how much of any particular quota they've already used up in previous orders. Although that's only orders placed in the current year, mind. And of course I also now haveto pass the users ID in as a parameter so I can look up their order history.
Your head's hurting too, right?
So... this is where I've got to:
SELECT SUM(ticket_quantity) FROM [tblBasket]
INNER JOIN [tblTickets] ON [tblBasket].[ticket_id] = [tblTickets].[id]
INNER JOIN [tblOrders] ON [tblBasket].[order_id] = [tblOrders].[id]
WHERE
[tblTickets].[stand_id] = @standId
AND [tblOrders].[user_id] = @userId
AND ([tblOrders].[order_date] BETWEEN '2006/01/01' AND '2006/12/31')
What I want to do is incorporate that into the big SQL query up top, in such a way to make it only return tickets that not only match the ticket parameters but that also aren't linked to stands, admission dates or anything else, that the user has reached their quota on.
Oh, and I'm in a bit of a hurry so do try and get a move on won't you? ;)
But seriously - how do I put those two SQL querys together? Do I need one for each paramater that might have a quota attached, or is there a quicker way? All suggestions and advice, up to and including "get an easier job, dude", received gratefully :)
View 2 Replies
View Related
Jul 6, 2015
I ran into a Kerberos authentication issue because of a missing AOAG SPN. Some of the tickets that granted me access to the nodes of the AOAG cluster were using the encryption type that I would expect. However, the MSSQLSvc SPNs were not using what I would expect!
klist
#XX> Client Somebody@somedomain.com
Server: RPCSS/MySQLServer@somedomain.com
KerbTicket Encryption Type: AES-256-CTS-HMAC-SHA1-96
#XX> Client Somebody@somedomain.com
Server: MSSQLSvc/MySQLServer@somedomain.com
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
#XX> Client Somebody@somedomain.com
Server: MSSQLSvc/MyAOAGListener@somedomain.com
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
I can't seem to figure out what the next step should be, and the infrastructure admins are stumped as well. How to proceed?
View 5 Replies
View Related
Jan 21, 2014
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
View 5 Replies
View Related
Nov 20, 2013
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
View 13 Replies
View Related
Nov 13, 2006
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
View 7 Replies
View Related
Jul 31, 2006
Hi allI am using .jpg images in my application, I am currently storing all my images in a file location, and placing their file path in the database. The problem with this is that I cannot display the images in crystal reports (version with vs.net 2005). I can however display the images if I store them as bytes in an image data type in my db.So the question is should I store either the file path of the bytes of the image in the database table? What are the contraints, I know that size is one, but will speed & performance be affected?Mike55.
View 2 Replies
View Related
Sep 6, 2007
I want to store only dd-MMM part of date in a DateTime field. Is it possible? If I take a varchar field, how to query while converting it to date.
View 4 Replies
View Related
Jul 6, 2006
Hi everybody.
I am using SQL Server 2000.
My users table looks lie this:
userID (primary key)
firstName
lastName
userName
userCode
Industry
Company
Office
dateCreated
Password
…
Every time that I create a new user I have to create userCode (for internal use).
userCode should be created from first letter of the FirstName, first letter from LastName and a number.
Example:
firstName: lastName: userCode:
Joe Blow jb1
Jake Brinks jb2
Ann Smith as1
…
If same userCode already exist the in the same Industry and in the same Company and in the same Office then createCode function should increment the number (the third character of the userCode) up to 9
I never did this before.
Any help would be highly appreciated.
Alex.
View 12 Replies
View Related
May 4, 2007
I need some help writing my request, I tried both of the following but it does'st seem to work. Your sugestion would be appreciated.SELECT count(LeadId) FROM dbo.Cl_Leads Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') and InsertDate BETWEEN @dtStart AND @dtEnd
I also tried
SELECT count(LeadId) FROM (SELECT * FROM dbo.Cl_Leads WHERE InsertDate BETWEEN @dtStart AND @dtEnd)Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') This return and error: Incorrect syntax near the keyword 'Where'.
View 6 Replies
View Related
Feb 17, 2008
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
View 4 Replies
View Related
Feb 28, 2008
Hi all,
I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters???
Thanks.
Kabir
View 2 Replies
View Related
Feb 28, 2008
In an ASP.NET app, where is the preferred place (or your preferred place, I should say) to store any SQL Statements (updates, selects, stored procedure calls, etc.) you have? I have been using a key/value pair in AppSettings in my web.config but just feel like that isn't the best way. What say you?
View 4 Replies
View Related
Mar 5, 2008
I have asp.net web application which interface with SQL server,
I want to run store procedure query of SQL using my asp.net application.
How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server.
for Instance Dim connections as string,
Dim da as dataset, Dim adpt as dataadapter. etc
if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application
thank you
maxmax
View 2 Replies
View Related
Jan 27, 2004
hi everyone :)
I've got a store procedure(sp) on the database side ( MS SQL 2000 )
and want to access the sp via ASP.net
so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.
I've tested the SP and it does work fine
can you just give me a template of how to access the sp from ASP.net
mant thanks indeed
M
View 2 Replies
View Related
Feb 11, 2004
I'm creating a table for maintenance records.
In each record many of the values are simply checkboxes.
In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?
-David
View 1 Replies
View Related
Apr 26, 2004
Hi All,
I want to store files (any kind of file) in DB and retrieve them.What is the best method to do it and also please post sample code as I have never done that.
Thanks,
Kumar.
View 1 Replies
View Related
Sep 13, 2004
I want to learn Store procedure whats the best way to start!
Any idea!
Thx
View 2 Replies
View Related
Nov 8, 2004
Hi,
I'm developing a site in asp.net and one of the main parts is to manage a timesheet.
Now the problem i'm facing is; how do I save this timesheet in a database in a way that it's easy to save and easy to query from.
I'm thinking about problems as i.e. 1st of a month is middle of the week, so I can't save it like this:
Month Week Mon Tue Wed Thu Fri Sat Sun
1 5 .. .. .. .. .. .. ..
2 1 .. .. .. .. .. .. ..
Anyway, I got stuck to find the best method, maybe someone has made something like this?
Thanks in advance!
Pieje
View 2 Replies
View Related
Jan 7, 2005
I am doing an insert and I get the error "Cast from type 'DBNull' to type 'Integer' is not valid." if the contact name already exist.
I dont see my error so if someone could enlight me I would appreciate.
Thanks
"****************STORE PROC***********************
ALTER procedure dbo.Add_Cl_Contact
(
@ContactNamenvarchar(75),
@Departmentnvarchar (50)=null,
@Titlenvarchar(50)=null,
@Phone1char(20)=null,
@Phone2char(20)=null,
@Phone3char(20)=null,
@Ext1char(10)=null,
@Ext2char(10)=null,
@Ext3char(10)=null,
@Faxnvarchar(50)=null,
@Emailnvarchar(50)=null,
@ContactTypeIDint=null,
@Resultinteger OUTPUT
AS
if Exists
(
Select *
From Cl_Contacts
Where ContactName = @ContactName
)
Return 1
Else
insert into Cl_Contacts (
ContactName,
Department,
Title,
Phone1,
Phone2,
Phone3,
Ext1,
Ext2,
Ext3,
Fax,
ContactTypeID,
Email
)
values (
@ContactName,
@Department,
@Title,
@Phone1,
@Phone2,
@Phone3,
@Ext1,
@Ext2,
@Ext3,
@Fax,
@ContactTypeID,
@Email
)
Select @Result = SCOPE_IDENTITY()
return 0
"****************END STORE PROC***********************
'******************************************************************
'******************************************************************
Public Function Add_Cl_Contact(ByVal strContactName As String, _
ByVal strDep As String, ByVal strEmail As String, ByVal strExt1 As String, ByVal strExt2 As String, _
ByVal strExt3 As String, ByVal strFax As String, ByVal strPhone1 As String, ByVal strPhone2 As String, _
ByVal strPhone3 As String, ByVal strTitle As String, ByVal iContactTypeId As Integer, _
ByRef iResult As Integer, ByRef strError As String) As Boolean
'******************************************************************
Dim bSuccess As Boolean = True
Dim connect As New SqlConnection(strConnection)
Dim cmdSelect As New SqlCommand("Add_Cl_Contact", connect)
Dim paramReturnValue As SqlParameter
cmdSelect.CommandType = CommandType.StoredProcedure
'PARAM
cmdSelect.Parameters.Add("@ContactName", strContactName)
cmdSelect.Parameters.Add("@Department", strDep)
cmdSelect.Parameters.Add("@Title", strTitle)
cmdSelect.Parameters.Add("@Phone1", strPhone1)
cmdSelect.Parameters.Add("@Phone2", strPhone2)
cmdSelect.Parameters.Add("@Phone3", strPhone3)
cmdSelect.Parameters.Add("@Ext1", strExt1)
cmdSelect.Parameters.Add("@Ext2", strExt2)
cmdSelect.Parameters.Add("@Ext3", strExt3)
cmdSelect.Parameters.Add("@Fax", strFax)
cmdSelect.Parameters.Add("@Email", strEmail)
cmdSelect.Parameters.Add("@ContactTypeID", iContactTypeId)
paramReturnValue = cmdSelect.Parameters.Add("@Result", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.Output
paramReturnValue = cmdSelect.Parameters.Add("ReturnValue", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.ReturnValue
Try
connect.Open()
cmdSelect.ExecuteNonQuery()
iResult = cmdSelect.Parameters("@Result").Value
connect.Close()
If cmdSelect.Parameters("ReturnValue").Value = 0 Then
strError = "Contact has been added"
Else
strError = strContactName & " already exist!"
bSuccess = False
End If
Catch ex As Exception
bSuccess = False
strError = ex.Message
Finally
If connect.State = ConnectionState.Open Then
connect.Close()
End If
End Try
Return bSuccess
End Function
View 3 Replies
View Related
Apr 11, 2005
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance.
NetAdventure
View 1 Replies
View Related
Jun 29, 2005
For a reason that I dont see my store proc is always
returning 0 records but if I use a commandType.text instead of
StoredProcedure then I get my results. So I must have some kind
of error somewhere.
Store Proc
ALTER PROCEDURE dbo.Get_Cl_IssuesBySystemID
@SystemId nvarchar(255)
AS
SET NOCOUNT ON
SELECT t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue
FROM dbo.IssueTracker_Issues t1
LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2
ON t1.IssueId = t2.IssueId
Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%@SystemId%'
order by t1.DateCreated
SET NOCOUNT OFF
Function
Public Function Get_Cl_IssuesBySystemID(ByVal strSystemId As String) As DataView
Dim objDs As New DataSet
Dim objDv As New DataView
Dim connect As New SqlConnection(strConnection)
'***For CommanType.Text***
Dim strSelect As String
strSelect = "SELECT t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue" & _
" FROM dbo.IssueTracker_Issues t1" & _
" LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2" & _
" ON t1.IssueId = t2.IssueId" & _
" Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%" &
strSystemId & "%'" & _
" order by t1.DateCreated"
Dim cmdSelect As New SqlCommand(strSelect, connect)
cmdSelect.CommandType = CommandType.Text
'***End For CommanType.Text***
'***For CommanType.StoredProcedure***
'Dim cmdSelect As New SqlCommand("Get_Cl_IssuesBySystemID", connect)
'cmdSelect.CommandType = CommandType.StoredProcedure
'PARAM
'cmdSelect.Parameters.Add("@SystemId", strSystemId)
'***End For CommanType.StoredProcedure***
Dim objAdapter As New SqlDataAdapter(cmdSelect)
Try
objAdapter.Fill(objDs)
objDv = objDs.Tables(0).DefaultView
Catch objErr As Exception
Throw New Exception(objErr.Message)
End Try
Return objDv
End Function
View 2 Replies
View Related
Apr 24, 2006
Hi,
I have a FormView with a couple of TextBoxes, a DropDownList and two CheckBoxes. I have connected a SQLDataSource to the FormView with an InsertCommand to store the values entered into the different conrtrols. My problem is that the boolean values from the two CheckBoxes doesn't get stored in the Database (SQL Server Express). The columns in the DB are of type "bit", should I choose a different type? I don't see any errors just a NULL value in the DB.
I'm probably missing the obvious taken that I'm really fresh to this, so any help would be appreciated...
Code below:
<asp:SqlDataSource
id="SqlDataSource1" Runat="Server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT TOP 1 * FROM Games"
InsertCommand="INSERT INTO Games (GameID, GameDate, Opposition, Result, FirstTeamGame, HomeGame) VALUES (@GameID, @GameDate, @Opposition, @Result, @FirstTeamGame, @HomeGame)" >
<InsertParameters>
<asp:Parameter Name="GameID" />
<asp:Parameter Name="GameDate" />
<asp:Parameter Name="Opposition" />
<asp:Parameter Name="Result" />
<asp:Parameter Name="FirstTeamGame" Type=Boolean />
<asp:Parameter Name="HomeGame" Type=Boolean />
</InsertParameters>
</asp:SqlDataSource>
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" OnItemInserted="FormView1_ItemInserted">
<HeaderTemplate>
<table border="1" style="border-collapse:collapse">
<tr style="background-color:#E0E0E0">
<th><asp:Label ID="Label1" Text="Manage" Width="90px" Runat="Server"/></th>
<th><asp:Label ID="Label4" Text="ID" Width="60px" Runat="Server"/></th>
<th><asp:Label ID="Label2" Text="MatchDate" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label3" Text="Opponent" Width="118px" Runat="Server"/></th>
<th><asp:Label ID="Label5" Text="Result" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label6" Text="First Team Game" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label7" Text="Home Game" Width="80px" Runat="Server"/></th>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<td><asp:Button ID="Button1" Text="New" CommandName="New" Runat="Server" Font-Size="8pt" Width="45px" Height="22px" /></td>
</tr>
</table>
</ItemTemplate>
<InsertItemTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<td>
<asp:Button ID="Button2" Text="Insert" CommandName="Insert" Runat="Server" Font-Size="8pt" Width="45px" Height="22px" OnClick="Button2_Click"/>
<asp:Button ID="Button3" Text="Cancel" CommandName="Cancel" Runat="Server" Font-Size="8pt" Width="45px" Height="22px"/>
</td>
<td>
<asp:TextBox id="AddGameID" Text='<%# Bind("GameID")%>' Runat="Server" Font-Size="8pt" Width="60" OnDataBinding="CreateNewGame" />
</td>
<td><asp:TextBox id="AddGameDate" Text='<%# Bind("GameDate")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>
<td><asp:DropDownList id="AddOpposition" Runat="Server" Font-Size="8pt" Width="118px" OnLoad="fillMe"/></td>
<td><asp:TextBox id="AddResult" Text='<%# Bind("Result")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>
<td><asp:CheckBox id="AddIsFirstTeamGame" Text='<%# Bind("FirstTeamGame")%>' Runat="Server" Font-Size="8pt" Width="80" /></td>
<td><asp:CheckBox id="AddIsHomeGame" Text='<%# Bind("HomeGame")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>
<td><asp:HiddenField id="Opposition" Value='<%# Bind("Opposition")%>' Runat="Server" /></td>
</tr>
</table>
</InsertItemTemplate>
<HeaderStyle Wrap="False" Font-Bold="True" />
<EmptyDataTemplate>
<table border="1" style="border-collapse:collapse">
<tr style="background-color:#E0E0E0">
<th><asp:Label ID="Label1" Text="Hantera" Width="90px" Runat="Server" Font-Size="10px" /></th>
<th><asp:Label ID="Label4" Text="ID" Width="60px" Runat="Server"/></th>
<th><asp:Label ID="Label2" Text="MatchDatum" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label3" Text="Motståndare" Width="118px" Runat="Server"/></th>
<th><asp:Label ID="Label5" Text="Resultat" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label6" Text="A-lags Match" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label7" Text="HemmaMatch" Width="80px" Runat="Server"/></th>
</tr>
</table>
</EmptyDataTemplate>
</asp:FormView>
View 1 Replies
View Related
Jun 8, 2006
Hello, I am very new to ASP.NET and SQL Server. I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me.
Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally.
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo
EmployeeID and SerialNo are primary keys.
I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work
CREATE PROCEDURE dbo.inventoryinfo AS
DECLARE @EmployeeID int DECLARE @SerialNo varchar(50)
IF( @SerialNo is NULL)
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID
ELSE
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNoGO
Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie
View 1 Replies
View Related
Jun 19, 2002
I'm running out of space on my C. Is it possible to store additional DBs on another drive or do I need to load SQL server onto the new drive?
View 6 Replies
View Related
Aug 30, 2000
I want to know when does SQL flushes Store Procedure from the cache.
Thank You,
Piyush Patel
View 1 Replies
View Related
