|
I am a beginner in designing database. I tried to create 2 tables Users and Topics for a tiny demo forum. The tables are like this:
[tbl_Users]
username
password
email
...
[tbl_Topics]
topicID
title
content
...
1 user can post 0..M topics, 1 topic belongs to only 1 user.
1 user can view 0..M topics (including other's topics). 1 topic can be viewed by 1..M users
I have some questions:
1. Do I have to create a small table name [tbl_User_Topic] between those 2 tables?
2. Do I need to create a UserID field for [tbl_Users] and use this as a primary key instead of username field
3. Should topicID field be auto increament number or just an integer number?
4. If topicID is not an auto number, then what mechanism should I use to create topicID?
Thanks for helping,
|
|
|
|
|
oohungoo wrote: 1. Do I have to create a small table name [tbl_User_Topic] between those 2 tables?
If this is one-to-many relationshot, no
oohungoo wrote: 2. Do I need to create a UserID field for [tbl_Users] and use this as a primary key instead of username field
Depends on your design. I would prefer using UserID
oohungoo wrote: 3. Should topicID field be auto increament number or just an integer number?
Again depends on your design. You need the key value so it must get the value from somewhere (identity, custom logic, lookup table...). Identity should be the easiest.
oohungoo wrote: 4. If topicID is not an auto number, then what mechanism should I use to create topicID?
See previous.
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
put a id(int) for the user table, and id_user in topic table as well(foregn key). PUT a int id in EVERY table ALLWAYS.
remove password and user name, you are not going to process it are you?
your questions are quite simply i don´t know what to say really.
are you sure that database is that short? if yes keep it can see anything wrong.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Dear ALL,
I am not getting SQL Server Databse files in the installer
It gives only 2 options of SQL Server Native Client & SQL server Setup support files,
i have checked the files & it was installed over my home PC Easily but its not getting successful over my office PC, i have 4, 5 different DVD's of it & tried over 3,4 different PC but not getting the trouble identified , also i have checked added program list in control panel , no SQL instance was found before, kindly help me in this regard.
|
|
|
|
|
These must be installed before the actual database engine can be installed (in some machines they may already exists in which case this step is skipped). Have you installed those?
The need to optimize rises from a bad design
|
|
|
|
|
|
You could use sysmail_configure_sp with LoggingLevel parameter to get more detailed info on mail queue. Also sysmail_help_queue_sp will give you good info. With these procedures I believe that you can identify where the problem is (in mail queue, in SSIS...)
Also check from history that Send Mail Task is actually run when package fails.
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
I am working on a project to make an existing existing WINFORM application support both Oralce and SQL SERVER.Now it supports SQL server.SO i want the GURUS on codeproject to give me the best suggestions and approaches.We have converted all the existing tables and Stored Procedures into Oracle .The main issues we are having is with the Data types like Money,Date that are incompatible between SQL and Oracle .Since the application is already running in most of our Customer sites, we cannot make changes in UI or Business Logic.We have to handle them efeciently in DAL.Also it has to be compatible with both ORACLE and SQL server .
SO please share me your experiences and Ideas.If you need any furthur Information on specifics please let me know
Thanks in advance
Pankaj
|
|
|
|
|
I would suggest looking at the Microsoft Enterprise Library 3.1.
That works in a very generic way and alows you to define the connection in the config file. You can define multiple connections so one for Oracle and another to SQL then when you create a connection specify which one you want by name.
The API is centered around the System.Data objects and interfaces and all the hard work tweaking the calls to the database engine are handled for you behind the scenes. There are plenty of examples in the documentation and you should be up and running fairly quickly.
Hope this helps
Ian Cox
|
|
|
|
|
maybe this is usefull:
http://www.dotnetjohn.com/articles.aspx?articleid=244
suc6
|
|
|
|
|
Both the suggestions i have recieved were extremely helpful.The main issue i have been having is the product is already there and in many places of code it takes SQL specific datatypes from the UI which are then passed onto DAL and then stored into Database .We have used functions,views and SP a lot .We want to take the approcah that requires least disturbance on code in UI and Business logic layer.We want to handle it in DAL .Can you give me some instances of cases where SP , Data sets Functions are incompatible between SQL Server and Oracle ??
I know a few cases but i would like to know experts opinion oon that too.
THanks
|
|
|
|
|
I m new to VC++ , I have a vc++ mfc application which i want connect with MySQL database.. earlier it dont have any database connectivity. Can anybody guide step by step to connect it to databse. I have installed MySQL 5 on my system n also installed MySQL connector 5.1 .
In my application , added these two lines of code
#include "afxdb.h"
then in a function void OnClickOk()
{
CDatabase db;
int i;
db.OpenEx(_T("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=kcc_trades; User=kats;Password=db_logon;Option=3;"));
i = db.IsOpen();
}
but it gives the errors
error C2065: 'CDatabase' : undeclared identifier
1>.\ChangeLimitsForm.cpp(574) : error C2146: syntax error : missing ';' before identifier 'db'
1>.\ChangeLimitsForm.cpp(574) : error C2065: 'db' : undeclared identifier
1>.\ChangeLimitsForm.cpp(576) : error C2228: left of '.OpenEx' must have class/struct/union
1> type is ''unknown-type''
1>.\ChangeLimitsForm.cpp(577) : error C2228: left of '.IsOpen' must have class/struct/union
1> type is ''unknown-type''
and if I put the cursor on CDatabse db; then it shows CDatabase as class and cursor on db shows CDatabase db means memeber ...... also db. shows me the list of available function from which I selected OpenEx....... i mean to say that it identifies CDatabase as class then why it is giving error....
Can anybody help me!!!!!!!!!!
Please I m struggling with these from the last one week.......
thanks
|
|
|
|
|
Hi there! I have an app and I have a local database within the app. After I finished building, I was testing the app at my local computer and it was working fine. After that I installed on a computer without any kind of sql server instance installed on it and it didn't run at all and was giving me error that the remote connections are not allowed. Is it possible the app not to run cause there is no instance of sql server or it's something else?? Thx ahead for your advices
|
|
|
|
|
You have to install the client side components of SQL on the client box, and enable one of the network transports on both the server and client boxes. Your connection string would then need to point to the server box. If you expected to use SQL as a LOCAL database server (same box, no network connection to a central server box running SQL) then you do need to install it on that box
|
|
|
|
|
Hi,
I have been trying to get rid of the following error message since last 4 days and there is no progress yet. Here is the error message, code and related table structure. Please help me to solve the problem. I use SQL SERVER EXPRESS 2008 with Visual Studio 2008. The strored procedures are not functioning if they called from inside a web page  but when I use ADO.NET to initiate a stored procedure, it is running. I perfomed extensive search in google but I couldn't find an accurate solution to problem.
ERROR MESSAGE
<br />
<br />
Server Error in '/' Application.<br />
--------------------------------------------------------------------------------<br />
<br />
Procedure or function StoredProcedure1 has too many arguments specified. <br />
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. <br />
<br />
Exception Details: System.Data.SqlClient.SqlException: Procedure or function StoredProcedure1 has too many arguments specified.<br />
<br />
Source Error: <br />
<br />
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. <br />
<br />
Stack Trace: <br />
<br />
<br />
[SqlException (0x80131904): Procedure or function StoredProcedure1 has too many arguments specified.]<br />
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826<br />
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747<br />
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194<br />
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392<br />
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204<br />
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954<br />
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162<br />
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175<br />
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137<br />
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386<br />
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +227<br />
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +86<br />
System.Web.UI.WebControls.ListView.HandleInsert(ListViewItem item, Boolean causesValidation) +277<br />
System.Web.UI.WebControls.ListView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +652<br />
System.Web.UI.WebControls.ListView.OnBubbleEvent(Object source, EventArgs e) +233<br />
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37<br />
System.Web.UI.WebControls.ListViewItem.OnBubbleEvent(Object source, EventArgs e) +112<br />
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37<br />
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118<br />
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166<br />
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10<br />
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13<br />
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36<br />
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565<br />
<br />
<br />
<br />
<br />
--------------------------------------------------------------------------------<br />
Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053 <br />
CODE IN WEB PAGE
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HolidayDBConnectionString %>" <br />
InsertCommand='StoredProcedure1' <br />
<br />
<br />
SelectCommand="SELECT DISTINCT [ActivityName] FROM [Activities] ORDER BY [ActivityName]" <br />
InsertCommandType="StoredProcedure"><br />
<InsertParameters><br />
<asp:Parameter Name="GetActivityName" Type="String" Size="50"/><br />
</InsertParameters><br />
</asp:SqlDataSource><br />
<br />
STORED PROCEDURE
<br />
ALTER PROCEDURE dbo.StoredProcedure1<br />
<br />
(<br />
@GetActivityName nvarchar(50)<br />
)<br />
<br />
AS<br />
INSERT INTO [HolidayDB].[dbo].[Activities]<br />
([ActivityName])<br />
VALUES<br />
(@GetActivityName)<br />
<br />
RETURN<br />
<br />
TABLE STRUCTURE
<br />
PK ActivityNumber Bigint (IDENTITY) ALLOW NULLS FALSE<br />
ActivityName nvarchar(50) ALLOW NULLS FALSE<br />
<br />
<br />
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
The parameter name for the stored procedure is '@GetActivityName' te '@' is missing in the web page specification...
|
|
|
|
|
Thanks for responding. I tried it with @ sign but it didn't work out too. I think something is wrong with SQL SERVER EXPRESS 2008 (may be related to a policy of something).
I am not an expert about SQL SERVER. If query is working fine but storedprocedures are not called, it means to me that the problem is related to SQL SERVER. I enabled CLR and it didn't work out too.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
Since im not sure if im an expert or not, im not sure whether to answer. You may prefer to not limit yourself in future. you never know who might give you the right answer.
Just in case it helps, there appears to be nothing wrong with the extracts of code that you've posted. You certainly seem to have defined the SqlDataSource OK. Perhaps its something the control that is bound to this datasource is doing "under the hood" as it were. eg/ if you've bound to a DetailsView and you have a bunch of other fields bound to the datasource, it will try to turn them into parameters too.
|
|
|
|
|
J4amieC wrote: you never know who might give you the right answer.
You got my five.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
In Management studio, modify the table definition and then select from the menu Table Designer / Generate Change Script... This gives you details what Management Studio is trying to do and possibly why.
I tested the same modification and it just added the column by altering the table, as expected.
The column to be added cannot be NOT NULL if default is not present at the same time so make sure that you define both when designing the table in Management Studio.
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Hmm, must be me then...
I have testet this on two different database on the same server, with the same result.
I should say that the server is running SQL 2005 and as the headline indicates im using SQL Manager 2008
I know that the column being added may not be NOT NULL if the default value is not present, so thats not an ussue here.
well i guess i must look further, for this one...
but thanks for the reply Mika
With great code, comes great complexity, so keep it simple stupid... 
|
|
|
|
|
No problem...
I made a simple test with Management Studio 2008 against SQL Server 2005 Express Edition and still ok. Here's what was generated:
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Test ADD
ToBeAdded nchar(10) NOT NULL CONSTRAINT DF_Test_ToBeAdded DEFAULT N'Some kind of value...'
GO
COMMIT
However I do have SP 2 on SQL 2005. Perhaps that would be the problem?
Mika
The need to optimize rises from a bad design
|
|
|
|
|
|
What I did in Management Studio was the following:
- selected the table from Object Explorer
- from Context menu for the selected table: "Design"
- added the necessary field and a default value for the field, but I didn't save
- then from the menu bar I selected "Table Designer" and then "Generate Change Script..."
- the code block in the previous post was copied from the dialog that opened
[Addition]
The build number for me is different: 9.00.3068.00 so I take it there are some differences in the engine.
[/Addition]
Hope this helps,
Mika
The need to optimize rises from a bad design
modified on Wednesday, September 24, 2008 3:45 PM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
