user311509 - Developer IT

How to Set Customer Table with Multiple Phone Numbers? - Relational Database Design

- by user311509

CREATE TABLE Phone ( phoneID - PK . . . ); CREATE TABLE PhoneDetail ( phoneDetailID - PK phoneID - FK points to Phone phoneTypeID ... phoneNumber ... . . . ); CREATE TABLE Customer ( customerID - PK firstName phoneID - Unique FK points to Phone . . . ); A customer can have multiple phone numbers e.g. Cell, Work, etc. phoneID in Customer table is unique and points to PhoneID in Phone table. If customer record is deleted, phoneID in Phone table should also be deleted. Do you have any concerns on my design? Is this designed properly? My problem is phoneID in Customer table is a child and if child record is deleted then i can not delete the parent (Phone) record automatically.

Read the article

Question About DateCreated and DateModified Columns - MS SQL Server

- by user311509

CREATE TABLE Customer ( customerID int identity (500,20) CONSTRAINT . . dateCreated datetime DEFAULT GetDate() NOT NULL, dateModified datetime DEFAULT GetDate() NOT NULL ); When i insert a record, dateCreated and dateModified gets set to default date/time. When i update/modify the record, dateModified and dateCreated remains as is? What should i do? Obviously, i need to dateCreated value to remain as was inserted the first time and dateModified keeps changing when a change/modification occurs in the record fields. In other words, can you please write a sample quick trigger? I don't know much yet... Any help will be appreciated.

Read the article

How Can I Create Reports in a Custom C#.NET Windows Application? - General Question

- by user311509

Assume i have a custom Windows application written in C#. This application has only the following functionalists, add, edit, delete and view. For example, a user can add a sale, change sales record, delete a sale record or view the whole sales record. I need to add some reporting functionalists e.g. i want a user to print the sales of a certain customer from 2008 to 2009 into pdf, what all products a certain customer has purchased from us and so on. I will only include the basic common report requests that are usually needed in the office. Any other kind of reports that are requested inconsistently, i would do it manually from my side at the back end and send the results manually to the requester. What i would do is: If a user wants more info of a certain customer, a special window box appears for that customer. This window box will have different controls that allows user to request more info such as, print customer purchases from ..... to ..... (user chooses the dates) and user will view results in pdf or so. Of course, at the back scene i will write an appropriate SQL Query with parameters that meets a certain function. Is this how it should be done? I have heard about SQL Reporting, i don't know anything about it yet. I will check it out. Anyhow, your suggestions won't harm. I'm still a student, so i don't have practical experience yet. I hope my question is clear enough. Thank you.

Read the article

Question About DateCreated and DateModified Columns - SQL Server

- by user311509

CREATE TABLE Customer ( customerID int identity (500,20) CONSTRAINT . . dateCreated datetime DEFAULT GetDate() NOT NULL, dateModified datetime DEFAULT GetDate() NOT NULL ); When i insert a record, dateCreated and dateModified gets set to default date/time. When i update/modify the record, dateModified and dateCreated remains as is? What should i do? Obviously, i need to dateCreated value to remain as was inserted the first time and dateModified keeps changing when a change/modification occurs in the record fields. In other words, can you please write a sample quick trigger? I don't know much yet...

Read the article

Is My DataTable Snippet Written Correctly?

- by user311509

public static DataTable GetDataTable(SqlCommand sqlCmd) { DataTable tblMyTable = new DataTable(); DataSet myDataSet = new DataSet(); try { //1. Create connection mSqlConnection = new SqlConnection(mStrConnection); //2. Open connection mSqlConnection.Open(); mSqlCommand = new SqlCommand(); mSqlCommand = sqlCmd; //3. Assign Connection mSqlCommand.Connection = mSqlConnection; //4. Create/Set DataAdapter mSqlDataAdapter = new SqlDataAdapter(); mSqlDataAdapter.SelectCommand = mSqlCommand; //5. Populate DataSet mSqlDataAdapter.Fill(myDataSet, "DataSet"); tblMyTable = myDataSet.Tables[0]; } catch (Exception ex) { } finally { //6. Clear objects if ((mSqlDataAdapter != null)) { mSqlDataAdapter.Dispose(); } if ((mSqlCommand != null)) { mSqlCommand.Dispose(); } if ((mSqlConnection != null)) { mSqlConnection.Close(); mSqlConnection.Dispose(); } } //7. Return DataSet return tblMyTable; } I use the above code to return records from database. The above snippet would run in web application which expected to have around 5000 visitors daily. The records returned reach 20,000 or over. The returned records are viewed (read-only) in paged GridView. Would it be better to use DataReader instead of DataTable? NOTE: two columns in the GridView are hyperlinked.

Read the article

How to Auto-Increment Non-Primary Key? - SQL Server

- by user311509

CREATE TABLE SupplierQuote ( supplierQuoteID int identity (3504,2) CONSTRAINT supquoteid_pk PRIMARY KEY, PONumber int identity (9553,20) NOT NULL . . . CONSTRAINT ponumber_uq UNIQUE(PONumber) ); The above ddl produces an error: Msg 2744, Level 16, State 2, Line 1 Multiple identity columns specified for table 'SupplierQuote'. Only one identity column per table is allowed. How can i solve it? I want PONumber to be auto-incremented.

Read the article

Should I Use GUID or IDENTITY as Thread Number?

- by user311509

offerID is the thread # which represents the thread posted. I see in forums posts are represented by random numbers. Is this achieved by IDENTITY? If not, please advice. nvarchar(max) will carry all kind of texts along with HTML tags. CREATE TABLE Offer ( offerID int IDENTITY (4382,15) PRIMARY KEY, memberID int NOT NULL REFERENCES Member(memberID), title nvarchar(200) NOT NULL, thread nvarchar(max) NOT NULL, . . . );

Developer IT