How to Create Table in sql Server?

Question

0.00/5 (No votes)

See more:

i have to make an application for general shop keeper...as an Assignment

i have created all Tables Required.... except Account Table ...
what to do i dont know

in account table the shop keeper want to keep records of customer.

e.g when an old customer Arrive at shop i want the shop keeper to see this customer old account details before making any new transaction. means his previous unpaid charges etc...
i made one but it give problem when i

SQL

select sum(accountBalnce) from tblAccount where act_date=@april

it was aggregate of column ammountbalance if shopkeeper Minus some amount from this aggregate it was not possible to store back this amount.
My Table

SQL

clientID
act_date
OrderID
AccountID //Primary Key
AccountReceive
AccountBalance
TotalAccount

my problem is how many table should i make and how may columns should i make for account
i want to add all values of a client AmmountBalce Columns
then desplay in a preouscharges_textbox
and also want to know if i minus some amount from preouscharges_textbox how to save back the change made

if it's lengthy to answer then give me some tips
My C# Code:

C#

public void accountinfo()
       {
           try
           {
               conn.Open();
               string qrystr = "Select * from tblaccount where ClientId= " + txtid.Text + " and orderId= " + orderidstrip.Text;
               SqlCommand acountcmd = new SqlCommand(qrystr,conn);
               acountcmd.CommandType = CommandType.Text;
               SqlDataAdapter actdr = new SqlDataAdapter(acountcmd);
               SqlDataReader acoountdr = acountcmd.ExecuteReader(CommandBehavior.CloseConnection);
               while (acoountdr.Read()) { 
               txtrece.Text=acoountdr["AmountRece"].ToString();
               txtamtbal.Text = acoountdr["AmountBal"].ToString();
               txttotalleft.Text = acoountdr["totalleft_amount"].ToString();
               
               }
               conn.Close();
           }
           catch(Exception ex)
           {
               MessageBox.Show(ex.Message);
           }

Posted 13-Feb-14 1:48am

abdul manan 7

Updated 13-Feb-14 2:24am

v3

Add a Solution

2 solutions

Solution 2

if your using the newest sql or evn before that there is no need for go ...

Create table tblTesr
(
One varchar(100) not null,
Two varchar(100) not null,
Three varchar(100) not null,
Four varchar(100) not null
)

highlight from create to the last bracket(")") and then click on Execute

also note do not put everything in varchar... if it only gets int values then use int not null,

hope this helpa

a few command

select * from tblTest
drop table tblTesr // deletes table

Posted 13-Feb-14 3:21am

MashaJoey

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sohail awr · Accepted Answer · 2014-02-13T03:11:00

You can create two tables one is transaction table and one is summary table. Both scripts are here:

Table 1

USE [DBNAME]
GO

/****** Object: Table [dbo].[tbl_transactions] Script Date: 02/13/2014 19:21:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_transactions](
[InvoiceID] [nchar](10) NULL,
[act_date] [date] NULL,
[ItemID] [int] NULL,
[AccountID] [int] NULL,
[ItemAmount] [decimal](18, 0) NULL
) ON [PRIMARY]

GO

Table 2

USE [DBNAME]
GO

/****** Object: Table [dbo].[tbl_summary] Script Date: 02/13/2014 19:23:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_summary](
[AccountID] [int] NULL,
[InvoiceID] [int] NULL,
[Amount_Received] [decimal](18, 0) NULL,
[Amount_Balance] [decimal](18, 0) NULL,
[Invoice_paid] [bit] NULL
) ON [PRIMARY]

GO

At the time of saving data you save summary from your transaction tables

Steps:

1. First save data in your Table 1 (Transaction Table)

2. use this query in your select statement (Remember in your selection criteria you have not used group by clause and act_date have multiple values so use group by clause)

select SUM(itemamount) as total, InvoiceID from tbl_transactions where AccountID =1 group by InvoiceID

3. now insert above data in summary table.

4. Now you make a form for receiving payments where you input AccountID, InvoiceID and Received Amount.
5. For next visit when you enter AccountID select data from summary table according your accountID and invoice_paid.

for Save update functions please read my article http://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=8171172[^]