|
I want to make a software which can create a SQL database from scratch with defined tables. (Difficult: Creating database)
Please give me coding of how to create a simple database with two/three tables and two/three columns (with full details as available in SQL Enterprise Manager >>> Table Design) in each table in VB.NET.
|
|
|
|
|
|
Thanx its what I wanted.
Thanx again.
Will you please tell me in between Access & SQL Server, which will you prefer.
And for both how you will deploy (make setup) your application.
|
|
|
|
|
Hi there. Access and SQL Server serve different purposes, and for their purposes, I like them both.
Access is a desktop database. For single-user, desktop application use, Access is terrific. With a properly indexed database, even complicated queries in Access are resolved quickly. Access' reporting tool is great and easy to use. Access is used a lot as a front-end with other database engines providing the back-end; for example, I have a Sql Server database for which I developed a front-end in Access to manage. Access can also be used well as an analytical tool, for analyzing data from a variety of sources - again, desktop-application use.
SQL Server is a client-server database engine, supporting multiple users in a much more robust way than the JET engine (the database engine behind Microsoft Access). As such, SQL Server is a better choice than Access as the back-end database for web applications. SQL Server, like most client-server RDBMS', supports transactions which are essential for many business applications.
If I am writing a .NET desktop application (WinForms), as a single-user application, and I know the end user has Microsoft Office licensed, I have no problems choosing JET for the back end database (even using Access to create the database... or even using Access as the application platform itself and not using .NET!). If I am writing multi-user applications or web applications... if I need more granular security (like protecting individual columns of a table)... if I want the power of stored procedures, triggers, transactions... these are all reasons to use SQL Server.
|
|
|
|
|
Great Explaination!
Thanx I learn a lot from your explanation.
I want to develop a single user application & am switching to Access. Thanx again for helping me choosing either Access or SQL Server.
GoodBye
|
|
|
|
|
Hi all!
I am trying to convert a program from 4G database managemet software(TAS 5.1). The database design in that environment allowed you to have arrays as fields. To give you more detail, I am converting the timesheet application we have to sql/c#. In the former, I had one key for the employee ID and 7 arrays for each date/time in/time out/lunch break/vacation/ and total hours, for each day of the week. Now, I am trying to build this in sql. But sql does not allow colums that are arrays(?) Am I correct there?
If so, what would be a better database design? One table with columns for each day of the week, or a table for employees that has a relation ship to tables for each day of the week?
Thanx on advance!
|
|
|
|
|
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]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
