|
Some points that might help ...
You're not defining the maximum size of the text columns so they are all defaulting to 255.
Did you really want a Memo field for DaysOnJob?
For that matter, shouldn't LaborRate be adDouble not adWChar? Or an integer - linking to Labor_Rates.ID
Consider using the DAO library rather than the ADO extension library (which is not stable).
On the subject of using VB6 - now is definitely the time to stop using it! But don't assume that VB.NET is the natural progression into .NET. C# is really worth considering. If not then MacDonald publish a book for programmers transitioning from VB6 (The one I had was "The Book of Visual Basic 2005", but that's well out of date now).
|
|
|
|
|
Thanks for the pointers. I went about it all differently. I created the tables I need in Access 2007 and through code, I just copy the database to another directory. I found it to be less of a headache than creating all the tables through code. Thanks again
|
|
|
|
|
Yes - much less of a headache!
One thing to watch out for .. if in the future you need to change the schema of the database after you have rolled it out to users you will need to consider how to amend existing data... but by then you will have upgraded to .net
|
|
|
|
|
Hi,
I have order_tier database and I am adding tiers by checking what's the max number then add 1 to it. Now I have a problem when deleting because if the order has 4 tiers then it should have from 1 to 4, if the user deleted tier 4 then it will be 1 to 3, if the user deleted tier 2 then tier 3 should be 2 and 4 should be 3.. By this I mean it should have always a sequence without a gap in the middle and it should move from the bottom so if user deleted tier 6 of 7 tier's order then tier 7 will be 6, etc.
How can I implement this in using PL/SQL in MySQL?
DECLARE param_last_tier INT;
IF EXISTS (SELECT tier_number FROM order_tiers WHERE order_id = param_order_id) THEN
BEGIN
SET param_last_tier = (SELECT MAX(tier_number) FROM order_tiers WHERE order_id = param_order_id) + 1;
END;
ELSE
BEGIN
SET param_last_tier = 1;
END;
END IF;
INSERT INTO order_tiers (order_id, tier_type, tier_number, created_user)
VALUES (param_order_id, param_tier_type, param_last_tier, param_created_user);
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
I think what you are looking for is a way to emulate the sql row_number function within MySQL (or do you mean Oracle PL/SQL?)
This article[^] shows a method which I've used successfully in the past ... example posted here in case the link dies
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
employees
LIMIT 5;
As an aside, using Max() to determine the "next" number when inserting rows doesn't always work well in multi-user environments. You should have a look at auto_increment[^] instead
|
|
|
|
|
Hi All,
I want to check if the user exists on a database with domain and user registration "XXXCXXX-XXX\webxxxxxqa_iispool", if doesn't exists create one, if it exists then give user the roles to access the application db_owner or public on few databases on the server using user login properties by using the script.
I know we can do this by using Management Studio, is there any way to write it all as a script to run it when we have multiple databases. Any help is appreciated, link, suggestion or code snippet anything. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Anything you can do in SSMS can be done by script, you could put a stored proc in the Master database and run it from there. When you are in SSMS there is an icon to create a script on most actions, use this to jump start you requirements.
Most results from a google search of the operation you need will give you sample scripts that you can use, try these[^].
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
indian143 wrote: ... give user the roles to access the application db_owner ...
That's a very bad idea. The user should only have the permissions required by your application.
I assume your application doesn't need to modify the structure of tables, views, stored procedures, etc.? And it doesn't need to modify the users and permissions? So why give it those permissions?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I want to manage 10 major tables with 100 millions record each, each row have about 20 fields/colums, so how to manage these 10 interlinked tables.
Can MySql handle such large data?
|
|
|
|
|
|
Member 12069869 wrote: Can MySql handle such large data?
Yes. Of course that doesn't mean your application can. Nor that you can't mess it up by failing to design correctly.
Member 12069869 wrote: each row have about 20 fields/colums
Things that matter
- What is the maximum, average and variance for the row size (your data not db overhead.)
- What is the realistic growth rate? Realistic means that, not pie in the sky sales/marketing claims.
- What will the system be querying on most? Unless your growth rate is very big this is more relevant.
- Can data be aged? Thus will the above queries be mostly for the last month, or last three months versus spanning the entire data store
|
|
|
|
|
Well, this started as a basic request for all records, then I ran into a size limit with the web service, to I added pagination to the call. Next I could not project directly into a list(of, so I took the statement out
subscribers.ToList().Select(Function(cs) New ws_subscribers_list With
Now I just get 1 record returned. I don't have experience with IQueryable so I'm not sure how to proceed on this.
Public Shared Function load_subscribers( _
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As List(Of ws_subscribers_list)) As Integer
Dim pValue As Integer = 0
Dim context As New hx5Context()
Dim subscribers As IQueryable(Of CRM_SUBSCRIBERS) = context.crm_Subscribers
'Set the Sort Order of the request
subscribers = subscribers.OrderBy(Function(m) m.Name)
'Get a Single Page of Data
If (pPageIndex = 1) Then
subscribers = subscribers.Take(pPageSize)
Else
subscribers = subscribers.Skip((pPageIndex - 1) * pPageSize).Take(pPageSize)
End If
pResults = _
subscribers.Select(Function(cs) New ws_subscribers_list With
{
.SubscriberID = cs.SubscriberID,
.CustomerID = cs.CustomerID,
.Name = cs.Name,
.EmailAddress = cs.EmailAddress,
.Source = cs.Source,
.Subscribe = cs.Subscribe,
.DateCreated = cs.DateCreated,
.Status = cs.Status
}
).ToList()
pValue = pResults.Count()
Return pValue
End Function
|
|
|
|
|
Check how many records are in your table, and what value has been passed for the pPageSize parameter.
I'd be inclined to have the function return the List(Of T) directly, rather than using a ByRef parameter. The caller can always access the list's .Count property if they want to know how many records have been returned.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
There are 3103 records in the table.
I forgot that IQueryable just returns a query, that is not executed until you use it.
I ended up splitting it into 2 with the Page index because I didn't know how to write the expression in 1 shot. Not sure if it's kosher, but feedback will tell me I guess.
Thanks Richard!
Public Shared Function load_subscribers( _
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As List(Of ws_subscribers_list)) As Integer
Dim pValue As Integer = 0
Dim context As New hx5Context()
If (pPageIndex = 1) Then
pResults = _
(
From cs In context.crm_Subscribers
Order By cs.Name
Take (pPageSize)
Select New ws_subscribers_list With
{
.SubscriberID = cs.SubscriberID,
.CustomerID = cs.CustomerID,
.Name = cs.Name,
.EmailAddress = cs.EmailAddress,
.Source = cs.Source,
.Subscribe = cs.Subscribe,
.DateCreated = cs.DateCreated,
.Status = cs.Status
}
).ToList()
Else
pResults = _
(
From cs In context.crm_Subscribers
Order By cs.Name
Skip ((pPageIndex - 1) * pPageSize)
Take (pPageSize)
Select New ws_subscribers_list With
{
.SubscriberID = cs.SubscriberID,
.CustomerID = cs.CustomerID,
.Name = cs.Name,
.EmailAddress = cs.EmailAddress,
.Source = cs.Source,
.Subscribe = cs.Subscribe,
.DateCreated = cs.DateCreated,
.Status = cs.Status
}
).ToList()
End If
pValue = pResults.Count()
Return pValue
End Function
|
|
|
|
|
@GroupId int,
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@RegionCode varchar(30) = NULL
AS
BEGIN
DECLARE @AgencyTable TABLE (GpoId int, OfcId int, Total_Bills BIGINT, Bill_Value BIGINT, Commission BIGINT, Total_Amount BIGINT)
SET @EndDate = convert(datetime, convert(Varchar(12), @EndDate, 106) + ' 23:59:59PM')
INSERT @AgencyTable (GpoId, OfcId, Total_Bills, Bill_Value, Commission, Total_Amount)
SELECT bil.GroupId, Bil.SubOfficeId , isnull(COUNT(Bil.ConsumerNumber),0)
,ISNULL(SUM(Bil.C_Amount),0)
,ISNULL(SUM(Bil.Commission),0)
,ISNULL(SUM(Bil.C_Amount),0) - ISNULL(SUM(Bil.Commission),0)
FROM BillTxnSO as Bil inner join pp_offices ofc On bil.GroupId = ofc.Group_Id and bil.SubOfficeId = ofc.OfficeCode
Where bil.GroupId = @GroupId AND TransDate BETWEEN @StartDate AND @EndDate
Group by bil.GroupId, Bil.SubOfficeId
SELECT ofc.OfficeName as SubOffice_Name , isnull(gpo.Total_Bills,0)as Total_Bills , isnull(gpo.Bill_Value,0)as Bill_Value , isnull(gpo.Commission,0) as Commission, isnull(gpo.Total_Amount,0) as Total_Amount
FROM @AgencyTable gpo
INNER JOIN pp_offices ofc On ofc.Group_Id = gpo.GpoId and gpo.OfcId = ofc.OfficeCode
ORDER BY ofc.OfficeName
END
|
|
|
|
|
|
I need to wrap a stored procedure within the transaction and this stored procedure spans through 6 to 7 tables. So, there are high chances of dead lock in our application as we have more updates on tables. What is the best approach to handle transaction here?
|
|
|
|
|
Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread. Why don't you execute on procedure and keep the rest of the threads on wait.
In many ways, a Mutex may help you overcome the deadlock. You can, this way, handle what happens when there are multiple threads using the resources. I would still recommend, that you try to execute "One SQL Procedure" at a time. Keep the rest of the threads (or requests) on hold.
https://colinlegg.wordpress.com/2014/05/06/enforcing-mutex-on-a-sql-server-database/[^]
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
Afzaal Ahmad Zeeshan wrote: Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread.
Almost.
A deadlock[^] occurs when two or more competing threads are waiting for each other to release a lock.
For example:
- Thread 1 locks X;
- Thread 2 locks Y;
- Thread 1 tries to acquire a lock on Y - waits for thread 2 to release;
- Thread 2 tries to acquire a lock on X - waits for thread 1 to release;
The DBMS can usually detect the deadlock, and will kill the thread which has done the least work.
NB: There's nothing wrong with trying to access something locked by another thread, so long as the current thread doesn't currently hold any locks which would prevent that thread from finishing.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I created a procedure to stop users from submitting all invoices having zero value. But the challenge am having now is to create exception in this procedure so that three authorized users can submit all such zero invoices. Please see below the created procedure:
CREATE OR REPLACE PROCEDURE SIMS_ORN_LIVE.EV_INV_ITEM_VALUE_CHECK_SIMS(P_INVH_SYS_ID NUMBER )
AS
CURSOR C1 IS
SELECT Sum(INVI_FC_VAL) FROM OT_INVOICE_ITEM
WHERE INVI_INVH_SYS_ID=P_INVH_SYS_ID;
M_VAL NUMBER;
BEGIN
IF C1%ISOPEN THEN
CLOSE C1;
END IF;
OPEN C1;
FETCH C1 INTO M_VAL;
CLOSE C1;
IF Nvl(M_VAL,0)=0 THEN
RAISE_APPLICATION('OP',585844 ,'YOU ARE NOT AUTHORIZED TO SUBMIT INVOICES WITH PROMO ITEMS ONLY, KINDLY CONTACT ED-OPS, MR. LAWRENCE, OR MRS NWABUIKE. ',' ',' ',' ',' ',' ',' ',' ');
END IF;
END ;
/
|
|
|
|
|
This sort of business rule should be in your application not the database. Wrong tool for the job.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I have a Constraint as below, I need to keep the same Index but remove this Unique Constraint, can anybody please help me? Any help like a link, suggestion or code snippet, anything helps.
Thanks in advance.
ADD CONSTRAINT TableName_UniqueConst UNIQUE (ShortName, Name, SecondaryName, Address1, Address2, Address3);
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hello,
I Am beginer in asp.net and Sql server , i want to do a query to calculate student grade point and grade using the condition bellow.
WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'
WHEN EXAMS BETWEEN 55 AND 59 THEN 'C'
WHEN EXAMS BETWEEN 50 AND 54 THEN 'CD'
WHEN EXAMS BETWEEN 45 AND 49 THEN 'D'
WHEN EXAMS BETWEEN 40 AND 44 THEN 'E'
WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'
please helo me with this issue than you .
|
|
|
|
|
Aliyu Usman wrote: please helo me with this issue Sure what have you tried so far.
Hint
Select from the results table and group by the result values or use a CASE statement and then group by the grade.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hello,
bellow is the query i have tried
SELECT
ADMISSION_NO,
FULL_NAME,
EXAMS,
CASE
WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'
WHEN EXAMS BETWEEN 55 AND 59 THEN 'C'
WHEN EXAMS BETWEEN 50 AND 54 THEN 'CD'
WHEN EXAMS BETWEEN 45 AND 49 THEN 'D'
WHEN EXAMS BETWEEN 40 AND 44 THEN 'E'
WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'
END AS GP
FROM R_Upload_2
|
|
|
|
|