|
Well, for good relational design, a good rule of thumb is to have separate tables for different subjects of data. An "Employee" is one subject, so you would have a table "Employees", and in that table an "EmployeeID" field that uniquely identifies any given record.
What is the other subject of data you are dealing with here? Is "TimeCard" the subject? Would "TimeCardEntry" be still another subject?
Maybe "TimeCard" would have fields like "TimeCardID", "EmployeeID" (to associate a single time card to a single employee), "SubmittedOn", "ApprovedBy", that sort of thing. Perhaps "TimeCardEntry" would have fields like "TimeCardID" (to associate back to a single time card), "StartingWorkDateTime", "EndingWorkDateTime", etc...
The display of data out of this kind of relational structure would then involve queries that join the three tables.
|
|
|
|
|
OK, think I got the idea and will restructure the database after I get over this roadblock.
I am gettng the following error when processing an insert command on the database:
The name 'intEmployeeID' is not permitted in this context. Only constants, expressions, or variables are allowed here. Column names ar not permitted
Fixed that part - Now I am trying to store just the time in the table in a column that is defined as a smalldatetime. What I am trying to insert is the DateTime.Now.(Something)..I looked up the definition for this and it has a lot of variables, but not one for just time...is there another struct, function, or collection I should be using?
Thanx in advance!
-- modified at 16:34 Wednesday 8th February, 2006
|
|
|
|
|
Hi there. Unfortunately, there isn't a field type for just a Time value in Sql Server. You could go ahead and store the time with a date in a smalldatetime or datetime field, or you could create a varchar field and store the time as a string.
|
|
|
|
|
Thanx for the quick reply!
I have tried using the DateTime.Now property but I get an error stating "Incorrect syntax near '4'" in a try/catch block. What the DateTime.Now is producing is 2/8/2006 4:26:08 PM, so I am guessing it is a space problem ( between the Date and Time ). How can I resolve this?
Thanx!
Jude
|
|
|
|
|
Hey, Jude. Don't make it bad. Take a sad song and make it better.
(sorry - couldn't resist)
Okay - now I'm guessing you're not using parameterized queries for your Insert statement. Using parameterized queries is a good thing. Not using them is a bad thing. Can you show the code snippet that is causing the error and I can confirm (or reject) this suspicion?
|
|
|
|
|
you are correct...let me change the the code...if it doesn't work then I'll post a snippet.
Thanx!
|
|
|
|
|
But when you change it, please do consider using real parameters... it is a big help to prevent
SQL injection attacks[^].
|
|
|
|
|
In other words, create a stored procedure with parameters?
|
|
|
|
|
That would be one way. Or you could use a regular insert statement with parameters. If you're using Sql Server for example, your CommandText could look like this:
INSERT INTO MyTable
(Column1, Column2, Column3)
Values
(@val1, @val2, @val3) In this example, @val1 , @val2 , and @val3 would be the parameters. Then you add SqlParameter objects to your SqlCommand 's Parameters collection (I like using the method AddWithValue like this psudo-code:
SqlCommand cmd = new SqlCommand(...);
cmd.CommandText = "INSERT INTO...";
cmd.Parameters.AddWithValue("@val1","theValue");
cmd.Parameters.AddWithValue("@val2",intOtherValue);
cmd.Parameters.AddWithValue("@val3",dateTimeThirdValue);
|
|
|
|
|
Ok...had to vpn to the office and grab the code and database.
Heres what I have in C# Code
SqlCommand cmdUpdateTime = new SqlCommand("SetTime",conn);
cmdUpdateTime.Parameters.Add("@EmpNumber",SqlDbType.Int).Value = m_intEmployeeID;
cmdUpdateTime.Parameters.Add("@TimeIn",SqlDbType.SmallDateTime).Value = DateTime.Now;
switch(DateTime.Now.DayOfWeek)
{
case DayOfWeek.Wednesday:
cmdUpdateTime.ExecuteNonQuery();
break;
}
conn.Close();
}
catch (SqlException caught)
{
MessageBox.Show(caught.Message,"Error");
}
and here is the Stored Procedure:
ALTER PROCEDURE dbo.SetTime
(
@EmpNumber int,
@TimeIn smalldatetime
)
AS
INSERT INTO Emptime(EmpID,WedIn)
VALUES(@EmpNumber,@TimeIn)
RETURN
What I am getting nw is the error:
Incorrect syntax near "SetTime"
???
Thanx for all your help!
|
|
|
|
|
Try setting the CommandType of your SqlCommand object to CommandType.StoredProcedure .
|
|
|
|
|
Thanx a bunch! Hopefully I may be able to pay the favor forward one day
|
|
|
|
|
|
Ok..Now I don't understand this...I am getting an error in the Stored Procedure Designer
ADO error:Incorrect syntax near DECLARE
ALTER PROCEDURE dbo.SetTime
/* (
)
*/
DECLARE @MySqlStr nvarchar(100)
RETURN
I had more commands and variablea, but I kept on getting the same error, so I cut it down to just that one line.
Here is the whole thing:
CREATE PROCEDURE db.SetTime
(
@EmpId int,
@TimeIn smalldatetime,
@NameOfTable nvarchar(10)
)
DECLARE @MySqlStr nvarchar(100)
SET @MySqlStr = "INSERT INTO " + @NameOfTable + "( EmpID,TimeIn) VALUES(@EmpId,@TimeIn)"
EXEC sp_sqlexec @MySqlStr
I googled and searched, but this error message seems very dynamic, and I do't get it...
Thanx again
|
|
|
|
|
Are you leaving out an AS keyword?
CREATE PROCEDURE myProc
(
@var1 int
,@var2 nvarchar(10)
)
AS
BEGIN
DECLARE @myVar nvarchar(100)
-- ... blah blah blah ...
RETURN
END
|
|
|
|
|
syntax,syntax,syntax...
Thanx
|
|
|
|
|
Ok Got it working (first module) as far as functionality. But I have a problem with what is actually writing to the database. Here is the command in question:
cmdUpdateTime.Parameters.Add("@TimeIn",SqlDbType.SmallDateTime).Value = DateTime.Now.ToShortTimeString();
Using the debugger, after this command, this parameter is in the format "HH:mm:ss AM/PM", but is being stored as "MM/DD/YYYY HH:mm:ss: AM/PM"
Why is this?
Thanx again ( and again...)
|
|
|
|
|
Hi,
I have 2 tables, t_a, t_b:
They are linked by a common_idd fieldd.
I want to find out the rows in t_a, but not in t_b.
My sql like:
select * from t_a where t_a.common_id not in (select common_id from t_b)
it works, but because there are so so many rows in both of the 2 tables, it runs so so so slow!!!
I am using Access, is there any way to implement the same function???
please help me!!
Thank a lot!
Alan Shen
MCAD for .NET Version
^~^~^~^~^~^~^~^~^~^~^
Great idea is the beginging of success!
|
|
|
|
|
Since it's Access, you may not be able to speed it up anyway!
But try this
select * from t_a
where not exists ( select t_b.common_id from t_b
where t_b.common_id != ta.common_id
)
As long as the field common_id is INDEXED on both tables it should be much faster.
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi,
I have created 2 tables in Visual Studio 2005, and am attempting to access the Database Diagrams. When I am not connected to our network domain, I get the message, "This database does not have a valid dbo user... Do you want to make yourself the dbo of this database in order to use database diagramming?" I then select "Yes", but then I receive the message that, "This database does not have a valid dbo user, or you do not have permissions to impersonate the dbo user".
As soon as I connect to our network, I have no problems. But If I want to take my work home, I cannot access the Database Diagrams.
My question is, how do I create a valid dbo user in Visual Studio 2005 SQL Express edition?
Thanks in advance,
Rashar
-- modified at 11:56 Wednesday 8th February, 2006
|
|
|
|
|
How can we retrive the day(Sunday,Monday....etc) of a date
|
|
|
|
|
You can use the following code:
<br />
lblDay.Text=Format(Now().Today, "dddd")<br />
|
|
|
|
|
Is it in C# or SQL?
In C#, use the DayOfWeek[^] property.
In T-SQL use DATEPART[^] to get the weekday part.
I hope this helps!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Hi all,
im having problems using a alias name to perform calculations in the select clause. My code is as follows:
SELECT<br />
<br />
CASE WHEN STK_SORT_KEY2 = 'TREND' THEN ([OD_UNITCST] * (1 - ISNULL(dbo.TrendDiscount.Discount, '0.32'))) ELSE 0 END as Expr2, <br />
[Expr2] / [OD_UNITCST] AS Expr11<br />
<br />
FROM...<br />
My question is how do use a field to store calculations which i can reference later in the select clause. I keep getting a Invalid name 'Expr2' error
Any ideas?
Thanks in advance,
|
|
|
|
|
I think you need to enter the full calculation for your Expr11 field. I don't think you can refer to it by the alias.
|
|
|
|
|