Table management

I am confused plz help me

Page 1 of 1

10 Replies - 888 Views - Last Post: 16 August 2009 - 08:25 PM Rate Topic: -----

#1 javedparamount  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 28-July 09

Table management

Post icon  Posted 10 August 2009 - 09:47 PM

I am new with sql server and even at "dreamincode" too. I have a problem that i explain by fallowing example. Plz help me. Thx in advance.

Example:

There are 3 persons "Ada", "Charlis", "Bilgate" each of them having rental property managed by a firm "Mir Enterprises" that collect monthly rent from leasee(who is using property and pay monthly rent) and hand over to owners. The Enterprises now wants to make a website containing rent record through which Ada, Chalis and Bilgate can get information about their monthly rent e.g( recieved amount, Date, cheque No etc) by entering their user name and password. This record is saved in a table.

Problem is Here:

Ada has one flate (e.g: in sector A)
Charlis has Two flats (one in sector A and other in sector G)
Bilgate has Three flats(in Sector B, sector C and Sector D)

if each of them had only one flat then there would b one field in talbe, named "Address" But here how to store the Address of more than one flats in table And having option on front end to select which flat's rental record to be displayed.
I mean if Bilgate select "Sector B" then information about that flat should b displayed , if he select "Sector C" then info about that flat should b displayed.

This post has been edited by javedparamount: 10 August 2009 - 09:52 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Table management

#2 CamoDeveloper  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 20
  • View blog
  • Posts: 250
  • Joined: 12-June 09

Re: Table management

Posted 10 August 2009 - 11:46 PM

You could make three columns in the table, and name them something like this:
- flatsAddressA
- flatsAddressB
- flatsAddressC

Make them NULL by default and if you're going to use the same select statement for all three (which I suggest) then do something like:
SELECT
	*
FROM tbl_name


Otherwise you can select just the desired sector info based on what field is not NULL, something like:
SELECT
	*
FROM tbl_name WHERE flatsAddressA IS NOT NULL


These are just examples of what you could do. It would be more efficient to use one SELECT statement for all the addresses.

~Camo
Was This Post Helpful? 0
  • +
  • -

#3 javedparamount  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 28-July 09

Re: Table management

Posted 11 August 2009 - 10:04 PM

View PostCamoDeveloper, on 10 Aug, 2009 - 10:46 PM, said:

You could make three columns in the table, and name them something like this:
- flatsAddressA
- flatsAddressB
- flatsAddressC

Make them NULL by default and if you're going to use the same select statement for all three (which I suggest) then do something like:
SELECT
	*
FROM tbl_name


Otherwise you can select just the desired sector info based on what field is not NULL, something like:
SELECT
	*
FROM tbl_name WHERE flatsAddressA IS NOT NULL


These are just examples of what you could do. It would be more efficient to use one SELECT statement for all the addresses.

~Camo



First of all thank you for your interest. I am still confused. Plz check the fallowing situation:
suppose fallowing are Nine fields in Table:

(1)ID(prim-key) (2)Name_Owner (3)Monthly_Rent (4)Received_Amount (5)Dated (6)FlatAddressA (7)FlatAddressB (9)FlatAddressC

Now each FlatAddress has its own "monthly_rent", "received_amount", etc. (e.g: flataddressA : Name_Owner=bilgates, monthly_rent=5000 While flataddress B : Name_owner=bilgates, monthly_rent=4000) How to store this record against ID(prim_key) 000123. Plz help me. Thank You
Was This Post Helpful? 0
  • +
  • -

#4 CamoDeveloper  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 20
  • View blog
  • Posts: 250
  • Joined: 12-June 09

Re: Table management

Posted 11 August 2009 - 11:38 PM

I'm confused. Is this how your table looks like?
| ID | Name_Owner | Monthly_Rent | Recieved_Amount | Dated | FlatAddressA | FlatAddressB | FlatAddressC |
| 1  | bilgates   | 5000		 |	  -		  |  -	|	  -	   |	   -	  |	  -	   |
| 2  | bilgates   | 4000		 |	  -		  |  -	|	  -	   |	   -	  |	  -	   |



Do you want each different addresses on their own ID or do you want them combined? I'm just trying to figure out what you're trying to accomplish.

~Camo
Was This Post Helpful? 0
  • +
  • -

#5 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Table management

Posted 12 August 2009 - 07:35 AM

Why not make a separate table that has this information? Don't have a column for each Flat, as what happens when you need to add one?

Here's one thing you can do:

Table1 - Users
UserID - Primary Key (numeric)
User Name
etc.

Table2 - Flats
FlatID - Primary Key (numeric)
Flat Name
etc.

Table3 - Payment Details
paymentid - Primary Key
userid - Foreign key from Table1
FlatID - Foreign key from Table2
Payment information


This will allow you to store the details in table3, it doesn't matter how many flats each user has and it allows for adding more flats in the future.

When working on projects like this it's always good to account for expandability in the future.
Was This Post Helpful? 0
  • +
  • -

#6 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 296
  • View blog
  • Posts: 1,537
  • Joined: 07-April 08

Re: Table management

Posted 12 August 2009 - 01:25 PM

ForcedSterilizationsForAll's answer would be the best as it encompass good design, however if you want them to be in one table then you can set it up like so:



Each ID is linked to a FLAT for a specific person. So your data would flow as so:

Ada has one flate (e.g: in sector A)
Charlis has Two flats (one in sector A and other in sector G)
Bilgate has Three flats(in Sector B, sector C and Sector D)


| ID | Name_Owner | Monthly_Rent | Received_Amount | Dated | FlatAddress  | Flat_Sector | 
| 1  | bilgates   | 5000		 |	  -		  |  -	|	  -	   |	 B	  |
| 2  | bilgates   | 4000		 |	  -		  |  -	|	  -	   |	 C	  |
| 3  | AdA		  | XXXXX	 |	 -		  |  -	|	 -	   |	 A	  |



Then you can use the following query to gain the wanted information

Select * FROM [databasename].dbo.[tablename] WHERE ID = [id]

or you can get it like so:

SELECT * FROM [databasename].dbo.[tablename] WHERE Name_Ownder = 'bilgates' AND Flat_Sector = 'B'

This post has been edited by rgfirefly24: 12 August 2009 - 01:27 PM

Was This Post Helpful? 0
  • +
  • -

#7 javedparamount  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 28-July 09

Re: Table management

Posted 12 August 2009 - 09:14 PM

View PostCamoDeveloper, on 11 Aug, 2009 - 10:38 PM, said:

I'm confused. Is this how your table looks like?
| ID | Name_Owner | Monthly_Rent | Recieved_Amount | Dated | FlatAddressA | FlatAddressB | FlatAddressC |
| 1  | bilgates   | 5000		 |	  -		  |  -	|	  -	   |	   -	  |	  -	   |
| 2  | bilgates   | 4000		 |	  -		  |  -	|	  -	   |	   -	  |	  -	   |



Do you want each different addresses on their own ID or do you want them combined? I'm just trying to figure out what you're trying to accomplish.

~Camo



first of all thank you for helping me. In the table you created, you entered two different IDs(1 & 2) for same user bilgates and repeated bilgates entery in the column "Name_Owner" . I dont want to do so. I want bilgates to enter his ID and Name on front end and then having option for selection of FlatAddress. May be a list will be used containing FlatAddressA & FlatAddressG. When he select any one item from this list and press "submitt" button , complete rent information should b displayed against that item(flatAddress). Now what will be the best table layout? have u understand what do i want to do?
Was This Post Helpful? 0
  • +
  • -

#8 javedparamount  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 28-July 09

Re: Table management

Posted 13 August 2009 - 01:00 AM

View PostForcedSterilizationsForAll, on 12 Aug, 2009 - 06:35 AM, said:

Why not make a separate table that has this information? Don't have a column for each Flat, as what happens when you need to add one?

Here's one thing you can do:

Table1 - Users
UserID - Primary Key (numeric)
User Name
etc.

Table2 - Flats
FlatID - Primary Key (numeric)
Flat Name
etc.

Table3 - Payment Details
paymentid - Primary Key
userid - Foreign key from Table1
FlatID - Foreign key from Table2
Payment information


This will allow you to store the details in table3, it doesn't matter how many flats each user has and it allows for adding more flats in the future.

When working on projects like this it's always good to account for expandability in the future.



Thank you for ur cooperation. Now i am using 3 tables and and on front end using vb.net . when i press "submitt" button an error message appear (incorrect syntex) fallowing is my code

Imports System.Data.SqlClient
Imports System.data

Partial Class RentDetail
	Inherits System.Web.UI.Page
	Dim qurstr As String
	Dim sqlcomd As SqlCommand
	Dim connstr As String = "Data Source=.;Initial Catalog=Rent Record;Integrated Security=True"
	Dim da As New SqlDataAdapter
	Dim ds As New DataSet
	Dim dr As SqlDataReader


	Protected Sub btnsubmitt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmitt.Click

		Using sqlconn As New SqlConnection(connstr)
			qurstr = "select User.User_ID, User.User_Name, property1.property_Address, property1.monthly_rent, payment.paid_amount, payment.dated, payment.pending_amount from user JOIN property1 on user.user_id=property1.user_id JOIN payment on property1.property_id=payment.property_id"
			da = New SqlDataAdapter(qurstr, sqlconn)
			da.Fill(ds)
			GridView1.DataSource = ds.Tables(0)
			' GridView1.DataSource = ds.Tables(1)
			' GridView1.DataSource = ds.Tables(2)
			GridView1.DataBind()


		End Using
	End Sub



i am using datagridview that will display data from tables when i click "submitt" button.
i think here is some error in sql statment. plz guide me. Thx

This post has been edited by javedparamount: 13 August 2009 - 01:02 AM

Was This Post Helpful? 0
  • +
  • -

#9 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 296
  • View blog
  • Posts: 1,537
  • Joined: 07-April 08

Re: Table management

Posted 13 August 2009 - 05:48 AM

on what line does it say the error is happening? also can you post the exact error message

also I would suggest getting Sql Server Management Studio and trying to run the query from SQL Server directly to see what results set you get.

This post has been edited by rgfirefly24: 13 August 2009 - 05:52 AM

Was This Post Helpful? 0
  • +
  • -

#10 javedparamount  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 28-July 09

Re: Table management

Posted 14 August 2009 - 08:40 PM

View Postrgfirefly24, on 13 Aug, 2009 - 04:48 AM, said:

on what line does it say the error is happening? also can you post the exact error message

also I would suggest getting Sql Server Management Studio and trying to run the query from SQL Server directly to see what results set you get.



Fallowing is the exact error message:

sqlExeption was unhandled by usercode
Line1: Incorrect syntax near '.'.


Note: I tried the query in "Sql Query analyzer" but same error message displayed

This post has been edited by javedparamount: 14 August 2009 - 09:11 PM

Was This Post Helpful? 0
  • +
  • -

#11 javedparamount  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 28-July 09

Re: Table management

Posted 16 August 2009 - 08:25 PM

i am thankfull to all of you who showed interest to solve my problem. I've solved it but i do not know what was error in code. However now it is working.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1