|
Hi, Careful! Long read. Im SORRY! 
SENARIO:
I have a senario where there are 3 related tables. One is called Request, the other Quote, and the last is called QuoteRequest.
Each quote can have many requests in it.
Each request can belong to more than one quote.
When I want to save a Quote record, I select several Requests from a Datagrid using checkboxes. Those requests will be assigned to that quote.
The Requests which are visible in that datagrid for selection, are the ones which have NOT been Quoted before.
Here's the problem!
Let's say a request has been quoted. The TimeEstimate of the request is 5 days. Then someone changes that TimeEstimate to 9 days. This means that 4 days are still unquoted!
There is one thing I MUST do before I can display those unquoted days back on the data grid. I MUST store the initial TimeEstimate of ANY quoted requestin the QuoteRequest Table. This table will keep a track of all the requests which have been quoted. For instance it may have the following entries:
ID----RequestID----QuoteID----NoOfDaysEstimated<br />
1--------1-----------1---------------5---------<br />
2--------1-----------2---------------4---------
QUESTION:
What I want to do is simply this:
I want to STORE the TimeEstimate FROM the Request table INTO the QuoteRequest Table WHENEVER the AddQUOTE Stored Procedure is executed. This will effectively be populating the QuoteRequest Table with records of all the Requests which have been Quoted, and hence will allow me to display this info in a datagrid.
I don't know how to go about doing that.
If you do go through this, I thank you. If you can suggest something, I will be forever grateful.
|
|
|
|
|
Dayekh wrote: I want to STORE the TimeEstimate FROM the Request table INTO the QuoteRequest Table WHENEVER the AddQUOTE Stored Procedure is executed. This will effectively be populating the QuoteRequest Table with records of all the Requests which have been Quoted, and hence will allow me to display this info in a datagrid.
I don't know how to go about doing that.
When you run the stored procedure, does it not already populate QuoteRequest rows? If it does then it should be simple to add an extra column. The request table still contains an TimeEstimate, so that value is copied into the QuoteRequest table, so that if the TimeEstimate on the request table changes, you still have the TimeEstimate as was when the quote was created (via the QuoteRequest table)
I don't see the difficulty, so I'm guessing there is something pertinent to the problem that hasn't been explained.
|
|
|
|
|
Thank you so much for the reply mate..
Ok, let me elaborate. You are right in saying what you have said. But the problem is, I'm not a good SP programmer.
I can do insert/update/delete SPs, but in this instance, I think I will need an insert SP within an insert SP if you see my meaning. If the solution is trivial, then I apologise.
Colin Angus Mackay wrote: When you run the stored procedure, does it not already populate QuoteRequest rows?
No it doesn't, because I have not programmed it to do so yet.
So once again, 3 Tables. Request, Quote, and QuoteRequest. The first 2 have a SP each for data storage.
The Quote Request Table has the following fields:
QuoteRequestID<br />
RequestID<br />
QuoteID<br />
NoOfQuoteDays
I want the INSERT QUOTE SP to contain the code for INSERTING the data from the TimeEstimate field in the Request Table, INTO the NoOfEstimatedDays field of the QuoteRequest Table.
I also want the INSERT QUOTE SP to store the Relevant RequestID of THAT TimeEstimate, AND also the QuoteID of the Quote being created currently into the QuoteRequest Table.
I hope that made sense 
|
|
|
|
|
Dayekh wrote: I can do insert/update/delete SPs, but in this instance, I think I will need an insert SP within an insert SP if you see my meaning.
I'm confused. What do you mean by an INSERT STORED PROCEDURE? There are stored procedures and that is it. What happens inside of them is up to you. You can perform multiple operations in a stored procedure.
e.g.
CREATE PROCEDURE dbo.MyStoredProc
@a int,
@b int,
@c int,
@d int,
@e int,
@f int,
@g int,
AS
-- Do and insert
INSERT MyTable(a,b,c) VALUES (@a, @b, @c);
-- Do an update
UPDATE MyOtherTable
SET d = @d, e = @e, f = @f
WHERE g = @g;
GO
|
|
|
|
|
I'm sorry, I'm just not expressing myself correctly.
I am aware of everything you just wrote. What I meant was a SP for inserting data into a table, a SP for updating data in a table, etc..
I have managed to get things moving with the help of a colleague. I thank you very much for your attention and efforts.
If you are really curious as to what the solution is, I will post it.
Take care mate..
|
|
|
|
|
I want to import a txt CSV file into a view in SQL 2000.
that can be done easily using VBA but the problem is that I want to check the validity of data e.g checking the serial no if its numeric , check the date if its in correct format...and I want to use a sql trigger for that so that each time theres an insert , before that it should chk the validity of data , etc . .
i am trying to do it ,, but a little jumpstart is all i need ,, thanks in advance
imran
|
|
|
|
|
What you need to do is a pre-insert trigger (or INSTEAD OF).
The steps are:
Create a view that mimics the table that you are trying to insert into.
Attach a trigger to the view. This trigger will be an INSTEAD OF trigger.
In the trigger, do the pre-validation and then insert.
For example:
CREATE TRIGGER MyTrigger ON dbo.vwMyView<br />
INSTEAD OF INSERT AS<br />
<br />
SET NOCOUNT ON<br />
<br />
DECLARE @MyVal NVARCHAR(50)<br />
<br />
SELECT @MyVAL = MyVal FROM INSERTED<br />
<br />
IF @MyVAL IS NOT NULL<br />
BEGIN<br />
INSERT INTO MyTable VALUES (MyVAL) FROM INSERTED<br />
END
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thank you verymuch it worked...
but pardon me for bugging u again ...
actually I need to check that the serialnumber should must not have '-' sign in it and the date should be in proper format, and CSV is actually being imported by access VBA acimport thingee ..
now its ok ,, it will import and fire the trigger as soon as it sees some inserts going on ....
Now my question is could you please explain it a little more ,, as what code should I write in order to do these two things, and also I want to check that If ItemName is already present in the table (another table) then It should insert only, otherwise no inserts ...
sorry for being lazy here and over inquisitive here but .. please help me I have tried really hard but couldnt do it ..
please help
imran
|
|
|
|
|
thank you verymuch it worked...
but pardon me for bugging u again ...
actually I need to check that the serialnumber should must not have '-' sign in it and the date should be in proper format, and CSV is actually being imported by access VBA acimport thingee ..
now its ok ,, it will import and fire the trigger as soon as it sees some inserts going on ....
Now my question is could you please explain it a little more ,, as what code should I write in order to do these two things, and also I want to check that If ItemName is already present in the table (another table) then It should insert only, otherwise no inserts ...
sorry for being lazy here and over inquisitive here but .. please help me I have tried really hard but couldnt do it ..
please help
imran
|
|
|
|
|
I have been assigned some work to generate particular queries and am having problems on how to JOIN the tables and structure the WHERE clause appropriately. Here Goes
The System is a School Administration System with a VB6 front-end and Access 2000 Backend. The database was already designed so I cant re-design it, I'm just trying to create the queries.
I have to come up with a query that returns Exam Papers not written by a particular Student in a particular class.
(I will attach the database file minus the data and some of its structure)
Ok here us the description of the Database structure.
There are Students, each uniquely identified by StudentID.
There is a SubjectClass table which has data on every class in the school, uniquely identified by SubjectClassID
There is a SubjectClassStudents table which is a collection of all students in classes. Each Student has a corresponding
SubjectClassID,StudentID and SubjectClassStudentsID to uniquely identify each record.
There is an Exams table as well. Each Exam has its corresponding SubjectClass. Each record therefore has
ExamID,SubjectClassID to uniquely identify it.
Each Exam can have several Papers. So the Exam table also has a NumberOfPapers field
Each Paper has its own unique PaperID,and ExamID to uniquely identify it
There is a PaperMarks table to store all the marks of Papers written
Here is the structure on text (table names in bold, fields seperated by "|")
Students
StudentID | StudentName
SubjectClass
SubjectClassID | SubjectClassName
SubjectClassStudents
SubjectClassStudentsID | SubjectClassID | StudentID
Exam
ExamID | SubjectClassID | ExamName | NumberOfPapers
Paper
PaperID | ExamID | PaperName
PaperMarks
PaperMarksID |PaperID | StudentID
The problem now:
Find the Papers NOT written by a Particular Student in a Particular SubjectClass
I have a sample database but dont know how i can post it here.
|
|
|
|
|
try out following query it might work
select a.StudentID,a.StudentName,b.StudentID,b.SubjectClassID,c.SubjectClassID,
c.SubjectClassName,d.ExamID,d.ExamName,d.SubjectClassID,e.PaperID,e.ExamID,
e.PaperName,f.PaperID,f.StudentID from Students a,SubjectClassStudents b,SubjectClass c,Exam d,Paper e,PaperMarks f where b.StudentID=a.StudentID and
c.SubjectClassID=b.SubjectClassID and d.SubjectClassID=b.SubjectClassID and
e.ExamID=d.ExamID and f.PaperID=e.PaperID and f.StudentID <> a.StudentID group by
a.StudentID,a.StudentName,b.StudentID,b.SubjectClassID,c.SubjectClassID,
c.SubjectClassName,d.ExamID,d.ExamName,d.SubjectClassID,e.PaperID,e.ExamID,
e.PaperName,f.PaperID,f.StudentID;
rahul
|
|
|
|
|
Will definitely try it out.
Thanks
|
|
|
|
|
Hello All,
Iam trying to create a table T1 which has a column called 'col1'.
I create a enumeration table called 'e1' like
CREATE TABLE e1<br />
( id INT NOT NULL,<br />
name VARCHAR(20) NOT NULL,<br />
description VARCHAR(20) NOT NULL,<br />
enabled BOOLEAN NOT NULL<br />
);
The id is set to 1,2,3,4,5
The 'col1' column in table T1 will hold any of these enumerations from table e1.
How should col1 be created (what datatype ?)
How is a value assigned to col1 from e1 ?
Is my table e1 declaration/creation correct?
Thanks in advance.
|
|
|
|
|
So you want col1 to only be able to have a value equivalent to one of the values used in the e1 column called id? This sounds like a look-up table.
One way to do this would be to use foreign keys. You specify col1 as a foreign key to table e1. I have called col1 e1ID and made it an int.
So create the e1 table using a primary key:
CREATE TABLE e1
( id INT NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(20) NOT NULL,
enabled BIT NOT NULL,
CONSTRAINT [PK_e1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Create the T1 table with a foreign key relationship to e1:
CREATE TABLE [T1](
[T1ID] [int] NOT NULL,
[E1ID] [int] NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(20) NOT NULL,
enabled BIT NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [T1] WITH NOCHECK ADD CONSTRAINT [FK_e1] FOREIGN KEY([E1ID])
REFERENCES E1 ([ID])
GO
Insert some rows into e1 for testing:
INSERT INTO [e1] ([id],[name],[description],[enabled])
VALUES (1, 'Test1','Desc1',1)
INSERT INTO [e1] ([id],[name],[description],[enabled])
VALUES (2, 'Test2','Desc2',1)
INSERT INTO [e1] ([id],[name],[description],[enabled])
VALUES (3, 'Test3','Desc3',1)
Test inserting into T1. First insert will work, second will fail:
INSERT INTO T1 ([T1ID],[E1ID],[name],[description],[enabled])
VALUES(1,2,'TTest1','TDesc1',1)
INSERT INTO T1 ([T1ID],[E1ID],[name],[description],[enabled])
VALUES(1,8,'TTest8','TDesc8',1)
|
|
|
|
|
Hi, I'm new with SQL2005.
I restored my DB (from SQL2000) to my new server but cannot access my
diagrams, this is the error it gives me.
Database diagram support objects cannot be installed because
this database does not have a valid owner. To continue, first use the
Files page of the Database Properties dialog box or the
ALTER AUTHORIZATION statement to set the database owner to a valid
login, then add the database diagram support objects.
I tried changing the DB owner
but no luck. What is the criteria for a DB owner, I must be doing
something wrong.
you can't forget something you never knew...
"Watching Migthy Joe Young made me hate my life..................................I want a gorilla!" A. Havemann
|
|
|
|
|
|
Hi all,
I have a sql statement that occur error when I run in sql server 2005....
"The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes."
update
TMP_TABLE_1
set
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A,
TMP_TABLE_2 B
where
A.CLASS_NO *= B.STUDENT_NO
and A.REQUEST_ID = @requestID
But How can I change the sql to Right outer join or left outer join.
Please kindly help.
Thanks
|
|
|
|
|
update
TMP_TABLE_1
set
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A LEFT JOIN
TMP_TABLE_2 B
ON A.CLASS_NO = B.STUDENT_NO
where
<s>A.CLASS_NO *= B.STUDENT_NO
and</s> A.REQUEST_ID = @requestID
|
|
|
|
|
|
Hi,
Every time i retrieve information from a large scale database from asp source, i get the information in 4 Sec. and with a lot of dataTable exception in the debugger mode.
If i use the query directly in the sql mode i get it all after 10 Sec.
I tried to change the connection string timeout but it made no difference.
can someone help me please?
|
|
|
|
|
What do you mean by "debugger mode" versus "sql mode"?
Lior S wrote: can someone help me please?
Show us your code, it will help us determine what exactly it is that you are doing.
|
|
|
|
|
Lior S wrote: lot of dataTable exception
are u using DataReader.Read or DataAdapter.Fill ?
ThaScorpion
|
|
|
|
|
Hello,
I was wondering if anyone is having this problem and knows the how and why of it. I have a Microsoft Access table, where one column is in the Date/Time format (format set to general). When I try to update this table (i.e. adding a new row) using C#, I get a datatype mismatch error. Is there anyway to directly pass this information to Access? I am using oledb.
I've found a work around by converting my date/time in c# to a string and storing it in my database as a string. But if possible, I'd like to keep it as a date/time format in my database.
Thanks in advance!
 over-worked, under-paid, glorified secretary 
|
|
|
|
|
It would help if you could post a sample of the code that is not working, along with a description of the table structure (is the field a date/time type, or a text datatype with display format set to general date?).
|
|
|
|
|
Hehe, oops, here is some of the trouble code:
userDS.UserSessionInfo.AddUserSessionInfoRow(CurrentUser,myDate,false,false,"",false,"");
when myDate is:
DateType myDate = DateTime.Now;
and the table structure for UserSessionInfo is:
UserSessionPkey --> int (autoincrement row, primary key)
UserPkey --> int (foreign key)
SessionDate --> dateType
ErrorsGen --> boolean
Training --> boolean
TrainingType --> string
Testing --> boolean
TestingType --> string
The above doesn't work and generates a datetype mismatch type error. But when I change it to the following, it works fine:
string myDate = DateTime.Now.ToString();
and the table structure for UserSessionInfo is:
UserSessionPkey --> int (autoincrement row, primary key)
UserPkey --> int (foreign key)
SessionDate --> string
ErrorsGen --> boolean
Training --> boolean
TrainingType --> string
Testing --> boolean
TestingType --> string
In my MS Access database, the column for SessionDate is of type Date/Time, with no formatting specified. When I change myDate to be a string, it works fine, even though I did not change the type in the Access database.
I know I've got a work around, by using strings, but I'm curious if anyone knows why this is happening.
TIA!
-- modified at 17:17 Sunday 19th November, 2006
~~~~~~~~~~~~
Over-worked, under-paid, glorified "secretary"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
