Need expert's opinion on table schema...

Hello,
I have seen different types of table schemas employed in
applications and I would like to get the opinions of the
SQL experts...
First some definitions:
LU = Lookup
IP = Index Person
IA = Index Account
Background sample info:
Fred has two accounts number 9999 and 8888
Joe has one account 7777
I can represent these accounts in the following
table examples:
(probably lots more ways to do this, but I am interested
in these two architectures, but would be willing to
entertain other schemas)
Example 1:
Person LU Account
IP Name IP IA IA Num
1 Fred 1 1 1 9999
2 Joe 1 2 2 8888
2 3 3 7777
Example 2:
Person Account
IP Name IA IP Num
1 Fred 1 1 9999
2 Joe 2 1 8888
3 2 7777
My questions to the people who live and breath SQL,
What are the pros and cons to the above table schemas?
Please be specific and if necessary write me in a
separate email : Mike@.e-liss.org
Thanks
Mike"mike" <Mike@.e-liss.org> wrote in message
news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
> Hello,
> I have seen different types of table schemas employed in
> applications and I would like to get the opinions of the
> SQL experts...
> First some definitions:
> LU = Lookup
> IP = Index Person
> IA = Index Account
> Background sample info:
> Fred has two accounts number 9999 and 8888
> Joe has one account 7777
>
> I can represent these accounts in the following
> table examples:
> (probably lots more ways to do this, but I am interested
> in these two architectures, but would be willing to
> entertain other schemas)
>
> Example 1:
> Person LU Account
> IP Name IP IA IA Num
> 1 Fred 1 1 1 9999
> 2 Joe 1 2 2 8888
> 2 3 3 7777
>
> Example 2:
> Person Account
> IP Name IA IP Num
> 1 Fred 1 1 9999
> 2 Joe 2 1 8888
> 3 2 7777
>
Example 1 uses a linking table, and is a more general structure than Example
2. Using a linking table it is possible to model relationships 1-1 1-many
or many-many. Using a foregn key you can only model 1-1 or 1-many. From
just that, you should prefer Example 2. One of the guiding principles of
data modeling is to use the most specific model that meets your needs.
From a performance point of view, Example will be superior as well. Notice
that you can transform Example 1 into Example 2.
CREATE VIEW v_Account
as
select Account.IA, LU.IP, Account.Num
from Account join LU
on Account.IA = LU.IA
But to add or delete anaccount will require 2 operations instead of one.
Also queries will have to perform an additional and unnecessary join.
Or, think of it this way:
LU has a 1-1 relationship with Account. Whenever you see a datamodel with a
1-1 relationship, the related should be merged. There are exceptions, but
all rules have execptions.
David|||>--Original Message--
>"mike" <Mike@.e-liss.org> wrote in message
>news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
>> Hello,
>> I have seen different types of table schemas employed
in
>> applications and I would like to get the opinions of
the
>> SQL experts...
>> First some definitions:
>> LU = Lookup
>> IP = Index Person
>> IA = Index Account
>> Background sample info:
>> Fred has two accounts number 9999 and 8888
>> Joe has one account 7777
>>
>> I can represent these accounts in the following
>> table examples:
>> (probably lots more ways to do this, but I am
interested
>> in these two architectures, but would be willing to
>> entertain other schemas)
>>
>> Example 1:
>> Person LU Account
>> IP Name IP IA IA Num
>> 1 Fred 1 1 1 9999
>> 2 Joe 1 2 2 8888
>> 2 3 3 7777
>>
>> Example 2:
>> Person Account
>> IP Name IA IP Num
>> 1 Fred 1 1 9999
>> 2 Joe 2 1 8888
>> 3 2 7777
>>
>Example 1 uses a linking table, and is a more general
structure than Example
>2. Using a linking table it is possible to model
relationships 1-1 1-many
>or many-many. Using a foregn key you can only model 1-1
or 1-many. From
>just that, you should prefer Example 2. One of the
guiding principles of
>data modeling is to use the most specific model that
meets your needs.
>From a performance point of view, Example will be
superior as well. Notice
>that you can transform Example 1 into Example 2.
>CREATE VIEW v_Account
>as
>select Account.IA, LU.IP, Account.Num
>from Account join LU
> on Account.IA = LU.IA
>But to add or delete anaccount will require 2 operations
instead of one.
>Also queries will have to perform an additional and
unnecessary join.
>Or, think of it this way:
>LU has a 1-1 relationship with Account. Whenever you
see a datamodel with a
>1-1 relationship, the related should be merged. There
are exceptions, but
>all rules have execptions.
>David
>
>.
>
David,
Are there any significant draw backs to not using Ex 1 ?
If not, the what is the purpose of the LU table?
So Fred and Joe can share the same account ?
ie:
IP IA
1 1
2 1
Where in example 2 this is not possible ?
Thanks
Mike|||"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:043e01c3b134$23540330$a001280a@.phx.gbl...
> >--Original Message--
> >
> >"mike" <Mike@.e-liss.org> wrote in message
> >news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
> >> Hello,
> >>
. . .
> David,
> Are there any significant draw backs to not using Ex 1 ?
> If not, the what is the purpose of the LU table?
No.
> So Fred and Joe can share the same account ?
> ie:
> IP IA
> 1 1
> 2 1
> Where in example 2 this is not possible ?
Exactly right. It just allows modeling different kinds of relationships.
David

Need advice on database planning. Thank You.

Hello,
I am working on a web site which will use SQL 2005.
I am planing my first SQL database and I am looking for advice.
1. There will be two types of users: students and professors.
2. Both users types will have login information.
(Username, Password, AccessLevel)
3. The remaining information on students and professores is different.
Student (Name, Email, Phone, ...) / Professor (Name, Email, Phone,
Subjects, ...)
4. Professors can publish documents.
Each document has some info (Type, Title, Description, ...)
My plan in this moment is to:
A. Create the tables Students, Professors, Login and Documents.
B. Students table would be connected to Login table.
Professors table would be connected to Login table and Documents
table.
C. The field [Type] in documents table should include the type or
should I create a table DocumentsTypes where I add codes for each
type.
I have seen this. What is the advantage?
Can someone give me some advice?
Thank you Very Much,
MiguelYou should be using ASP.NET 2.0 as it now supports SQL providers for
Membership, Roles, and Profiles. Most of it is all automated and you won't
have to worry about the design in the context you are discussing at the
moment.
<%= Clinton Gallagher
METROmilwaukee (sm) "A Regional Information Service"
NET csgallagher AT metromilwaukee.com
URL http://metromilwaukee.com/
URL http://clintongallagher.metromilwaukee.com/
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:ODVd5NmAGHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am working on a web site which will use SQL 2005.
> I am planing my first SQL database and I am looking for advice.
> 1. There will be two types of users: students and professors.
> 2. Both users types will have login information.
> (Username, Password, AccessLevel)
> 3. The remaining information on students and professores is different.
> Student (Name, Email, Phone, ...) / Professor (Name, Email, Phone,
> Subjects, ...)
> 4. Professors can publish documents.
> Each document has some info (Type, Title, Description, ...)
>
> My plan in this moment is to:
> A. Create the tables Students, Professors, Login and Documents.
> B. Students table would be connected to Login table.
> Professors table would be connected to Login table and Documents table.
> C. The field [Type] in documents table should include the type or
> should I create a table DocumentsTypes where I add codes for each type.
> I have seen this. What is the advantage?
> Can someone give me some advice?
> Thank you Very Much,
> Miguel
>

Need advice on database plan. Thank You.

Hello,

I am working on a web site which will use SQL 2005.
I am planing my first SQL database and I am looking for advice.

1. There will be two types of users: students and professors.
2. Both users types will have login information.
(Username, Password, AccessLevel)
3. The remaining information on students and professores is different.
Student (Name, Email, Phone, ...) / Professor (Name, Email, Phone, Subjects, ...)
4. Professors can publish documents.
Each document has some info (Type, Title, Description, ...)

My plan in this moment is to:
A. Create the tables Students, Professors, Login and Documents.
B. Students table would be connected to Login table.
Professors table would be connected to Login table and Documents table.
C. The field [Type] in documents table should include the type or
should I create a table DocumentsTypes where I add codes for each type.
I have seen this. What is the advantage?

Can someone give me some advice?

Thank you Very Much,
Miguel

I would recommend you start with the aspnetdb sample database first. That will take care of your login, username, password, email, password retrieval, and roles.

Create the roles you need like student, professor, admin.

For part c which isn't really related to login/users, the difference is that while you can store the Type as a varchar, doing so is prone to accidentally entering an invalid entry. If you ever need to add another document type in the future, it is best to store all the values in a table (with a primary key, autoincrementing number is good for this), then store the key in the documents table. You can then put a foreign key constraint on the DocumentType field in the documents table so that it will only allow valid values in that field. Secondly, it will allow you to build your website so that you can populate a dropdown with the values the user can pick. Should you ever need to add another document type, just add it to the table (Directly or through another web page), and now your dropdowns will contain the new value, and the database will allow that value to be put into the DocumentType column automatically.

If you use the varchar type field, you will most likely put all possible values into the dropdowns in your code, and if you ever need to add another value, you will need to change your code, recompile the website and redeploy it again.

There is also a minor performance improvement with using the key as well since it will be an int field (4 bytes), verses a longer (and variable length) varchar field. Computers can change/compare int fields EXTREMELY quick. Computers look at strings as a sequence of chars (Or better algorythms may look at them as a sequence of ints, followed by some chars). In either case, varchars are many times slower.

Need a Suggestion for creating Tables ?

One of my Customer deals in different Items, and every Item has different
specifications and types.
For example:
Item A has different categories, types, colours
Item B has different colours, sizes, thickness, weight
Item C has different colours, qualities, yarn counts, widths, types, weave,
design type
Now how can I create my Product Table(s) which should accomodate all above.
How many tables I have to create, is it possible that I create one or two
tables and use self aliasing, if possible, how ?
One easy way is to create different tables for all the required
specifications, but what, if customer deals with 100s of specifications all
over, and another customer whom I sell this product, deals with another 100
specifications which are totally different than the last customer ?
Please give me your best solutions so that I can easily use these tables in
my Inventory Application, without changing the design again and again.
I hope you understand what I am trying to say ?
I am developing my Application in VB.Net 2005.
Best Regards,
LuqmanSee if this helps. Read about "Entity Supertypes and Subtypes".
http://72.14.203.104/search?q=cache...s&ct=clnk&cd=17
AMB
"Luqman" wrote:

> One of my Customer deals in different Items, and every Item has different
> specifications and types.
> For example:
> Item A has different categories, types, colours
> Item B has different colours, sizes, thickness, weight
> Item C has different colours, qualities, yarn counts, widths, types, weave
,
> design type
> Now how can I create my Product Table(s) which should accomodate all above
.
> How many tables I have to create, is it possible that I create one or two
> tables and use self aliasing, if possible, how ?
> One easy way is to create different tables for all the required
> specifications, but what, if customer deals with 100s of specifications al
l
> over, and another customer whom I sell this product, deals with another 10
0
> specifications which are totally different than the last customer ?
> Please give me your best solutions so that I can easily use these tables i
n
> my Inventory Application, without changing the design again and again.
> I hope you understand what I am trying to say ?
> I am developing my Application in VB.Net 2005.
>
> Best Regards,
> Luqman
>
>|||Any practical example of table(s) and queries will be helpful.
Best Regards,
Luqman
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7DF777AC-4DE8-4BE0-BB98-946BB6FDC3B5@.microsoft.com...
> See if this helps. Read about "Entity Supertypes and Subtypes".
> http://72.14.203.104/search?q=cache...s&ct=clnk&cd=17
>
> AMB
> "Luqman" wrote:
>

Need a hand...

i've been writing an app for my company that has a front end screen in which the user types in all infos needed (ie. name, address, city, state, etc - 82 fields to be exact) then clicks an "add" button, which i put at the bottom, and have that data dumped into a SQL table... the page is then redirected to another page which reads the record just added and displays all the fields that the user submitted...

here is my front end "add" screen code behind written in C#:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

publicpartialclassVinny_Credit_Application_AddCredApp : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

}

protectedvoid SubmitButton_Click(object sender,EventArgs e)

{

string ConnectionString =ConfigurationManager.ConnectionStrings["ws2dbConnectionString2"].ConnectionString;

SqlConnection connection =newSqlConnection(ConnectionString);

SqlCommand cmd =newSqlCommand(@."INSERT INTO CreditAppFile (

GI_RB_Div,

GI_RB_ExistCust,

GI_TB_LegalFirmName,

GI_TB_Address,

GI_TB_City,

GI_DDL_State,

GI_TB_Zip,

GI_TB_TradeName,

GI_TB_BillAddress,

GI_TB_BillCity,

GI_DDL_BillState,

GI_TB_BillZip,

GI_TB_ContName,

GI_TB_Title,

GI_TB_MobilePager,

GI_TB_Email,

GI_TB_Fax,

GI_TB_BusDesc,

GI_TB_BusStartDate,

GI_TB_BusPhone,

GI_RB_PuchOrdsReq,

GI_RB_BusType,

GI_RB_LiensJudges,

GI_RB_TaxExempt,

GI_TB_FedIDNO,

GI_RB_PriorBankrupt,

GI_TB_PriorBankr,

BFR_TB_ContPhone,

BFR_TB_AcctNum1,

BFR_TB_AcctNum2,

BFR_TB_CurrBal1,

BFR_TB_CurrBal2,

BFR_TB_CurrBal3,

BFR_TB_CurrBal4,

BFR_RB_AcctType,

TR_TB_ContName1,

TR_TB_ContAddr1,

TR_TB_ContCity1,

TR_DDL_ContSt1,

TR_TB_ContZip1,

TR_TB_ContPhone1,

TR_TB_Cont_Acct1,

TR_TB_ContName2,

TR_TB_ContAddr2,

TR_TB_ContCity2,

TR_DDL_ContSt2,

TR_TB_ContZip2,

TR_TB_ContPhone2,

TR_TB_Cont_Acct2,

TR_TB_ContName3,

TR_TB_ContAddr3,

TR_TB_ContCity3,

TR_DDL_ContSt3,

TR_TB_ContZip3,

TR_TB_ContPhone3,

TR_TB_Cont_Acct3,

PI_TB_Name1,

PI_TB_HomeAddr1,

PI_TB_BDay1,

PI_TB_SSNum1,

PI_TB_HomePh1,

PI_TB_PrcntOwn1,

PI_TB_NetWorth1,

PI_TB_AnnInc1,

PI_TB_MonHouPay1,

PI_TB_Name2,

PI_TB_HomeAddr2,

PI_TB_BDay2,

PI_TB_SSNum2,

PI_TB_HomePh2,

PI_TB_PrcntOwn2,

PI_TB_NetWorth2,

PI_TB_AnnInc2,

PI_TB_MonHouPay2,

PI_TB_Name3,

PI_TB_HomeAddr3,

PI_TB_BDay3,

PI_TB_SSNum3,

PI_TB_HomePh3,

PI_TB_PrcntOwn3,

PI_TB_NetWorth3,

PI_TB_AnnInc3,

PI_TB_MonHouPay3

)

VALUES (

@.GI_RB_Div,

@.GI_RB_ExistCust,

@.GI_TB_LegalFirmName,

@.GI_TB_Address,

@.GI_TB_City,

@.GI_DDL_State,

@.GI_TB_Zip,

@.GI_TB_TradeName,

@.GI_TB_BillAddress,

@.GI_TB_BillCity,

@.GI_DDL_BillState,

@.GI_TB_BillZip,

@.GI_TB_ContName,

@.GI_TB_Title,

@.GI_TB_MobilePager,

@.GI_TB_Email,

@.GI_TB_Fax,

@.GI_TB_BusDesc,

@.GI_TB_BusStartDate,

@.GI_TB_BusPhone,

@.GI_RB_PuchOrdsReq,

@.GI_RB_BusType,

@.GI_RB_LiensJudges,

@.GI_RB_TaxExempt,

@.GI_TB_FedIDNO,

@.GI_RB_PriorBankrupt,

@.GI_TB_PriorBankr,

@.BFR_TB_ContPhone,

@.BFR_TB_AcctNum1,

@.BFR_TB_AcctNum2,

@.BFR_TB_CurrBal1,

@.BFR_TB_CurrBal2,

@.BFR_TB_CurrBal3,

@.BFR_TB_CurrBal4,

@.BFR_RB_AcctType,

@.TR_TB_ContName1,

@.TR_TB_ContAddr1,

@.TR_TB_ContCity1,

@.TR_DDL_ContSt1,

@.TR_TB_ContZip1,

@.TR_TB_ContPhone1,

@.TR_TB_Cont_Acct1,

@.TR_TB_ContName2,

@.TR_TB_ContAddr2,

@.TR_TB_ContCity2,

@.TR_DDL_ContSt2,

@.TR_TB_ContZip2,

@.TR_TB_ContPhone2,

@.TR_TB_Cont_Acct2,

@.TR_TB_ContName3,

@.TR_TB_ContAddr3,

@.TR_TB_ContCity3,

@.TR_DDL_ContSt3,

@.TR_TB_ContZip3,

@.TR_TB_ContPhone3,

@.TR_TB_Cont_Acct3,

@.PI_TB_Name1,

@.PI_TB_HomeAddr1,

@.PI_TB_BDay1,

@.PI_TB_SSNum1,

@.PI_TB_HomePh1,

@.PI_TB_PrcntOwn1,

@.PI_TB_NetWorth1,

@.PI_TB_AnnInc1,

@.PI_TB_MonHouPay1,

@.PI_TB_Name2,

@.PI_TB_HomeAddr2,

@.PI_TB_BDay2,

@.PI_TB_SSNum2,

@.PI_TB_HomePh2,

@.PI_TB_PrcntOwn2,

@.PI_TB_NetWorth2,

@.PI_TB_AnnInc2,

@.PI_TB_MonHouPay2,

@.PI_TB_Name3,

@.PI_TB_HomeAddr3,

@.PI_TB_BDay3,

@.PI_TB_SSNum3,

@.PI_TB_HomePh3,

@.PI_TB_PrcntOwn3,

@.PI_TB_NetWorth3,

@.PI_TB_AnnInc3,

@.PI_TB_MonHouPay3

); Select Scope_Identity()"

, connection);

try

{

cmd.Parameters.AddWithValue("@.GI_RB_Div", GI_RB_Div.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_RB_ExistCust", GI_RB_ExistCust.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_TB_LegalFirmName", GI_TB_LegalFirmName.Text);

cmd.Parameters.AddWithValue("@.GI_TB_Address", GI_TB_Address.Text);

cmd.Parameters.AddWithValue("@.GI_TB_City", GI_TB_City.Text);

cmd.Parameters.AddWithValue("@.GI_DDL_State", GI_DDL_State.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_TB_Zip", GI_TB_Zip.Text);

cmd.Parameters.AddWithValue("@.GI_TB_TradeName", GI_TB_TradeName.Text);

cmd.Parameters.AddWithValue("@.GI_TB_BillAddress", GI_TB_BillAddress.Text);

cmd.Parameters.AddWithValue("@.GI_TB_BillCity", GI_TB_BillCity.Text);

cmd.Parameters.AddWithValue("@.GI_DDL_BillState", GI_DDL_BillState.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_TB_BillZip", GI_TB_BillZip.Text);

cmd.Parameters.AddWithValue("@.GI_TB_ContName", GI_TB_ContName.Text);

cmd.Parameters.AddWithValue("@.GI_TB_Title", GI_TB_Title.Text);

cmd.Parameters.AddWithValue("@.GI_TB_MobilePager", GI_TB_MobilePager.Text);

cmd.Parameters.AddWithValue("@.GI_TB_Email", GI_TB_Email.Text);

cmd.Parameters.AddWithValue("@.GI_TB_Fax", GI_TB_Fax.Text);

cmd.Parameters.AddWithValue("@.GI_TB_BusDesc", GI_TB_BusDesc.Text);

cmd.Parameters.AddWithValue("@.GI_TB_BusStartDate", GI_TB_BusStartDate.Text);

cmd.Parameters.AddWithValue("@.GI_TB_BusPhone", GI_TB_BusPhone.Text);

cmd.Parameters.AddWithValue("@.GI_RB_PuchOrdsReq", GI_RB_PuchOrdsReq.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_RB_BusType", GI_RB_BusType.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_RB_LiensJudges", GI_RB_LiensJudges.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_RB_TaxExempt", GI_RB_TaxExempt.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_TB_FedIDNO", GI_TB_FedIDNO.Text);

cmd.Parameters.AddWithValue("@.GI_RB_PriorBankrupt", GI_RB_PriorBankrupt.SelectedValue);

cmd.Parameters.AddWithValue("@.GI_TB_PriorBankr", GI_TB_PriorBankr.Text);

cmd.Parameters.AddWithValue("@.BFR_TB_ContPhone", BFR_TB_ContPhone.Text);

cmd.Parameters.AddWithValue("@.BFR_TB_AcctNum1", BFR_TB_AcctNum1.Text);

cmd.Parameters.AddWithValue("@.BFR_TB_AcctNum2", BFR_TB_AcctNum2.Text);

cmd.Parameters.AddWithValue("@.BFR_TB_CurrBal1", BFR_TB_CurrBal1.Text);

cmd.Parameters.AddWithValue("@.BFR_TB_CurrBal2", BFR_TB_CurrBal2.Text);

cmd.Parameters.AddWithValue("@.BFR_TB_CurrBal3", BFR_TB_CurrBal3.Text);

cmd.Parameters.AddWithValue("@.BFR_TB_CurrBal4", BFR_TB_CurrBal4.Text);

cmd.Parameters.AddWithValue("@.BFR_RB_AcctType", BFR_RB_AcctType.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContName1", TR_TB_ContName1.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContAddr1", TR_TB_ContAddr1.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContCity1", TR_TB_ContCity1.Text);

cmd.Parameters.AddWithValue("@.TR_DDL_ContSt1", TR_DDL_ContSt1.SelectedValue);

cmd.Parameters.AddWithValue("@.TR_TB_ContZip1", TR_TB_ContZip1.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContPhone1", TR_TB_ContPhone1.Text);

cmd.Parameters.AddWithValue("@.TR_TB_Cont_Acct1", TR_TB_Cont_Acct1.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContName2", TR_TB_ContName2.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContAddr2", TR_TB_ContAddr2.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContCity2", TR_TB_ContCity2.Text);

cmd.Parameters.AddWithValue("@.TR_DDL_ContSt2", TR_DDL_ContSt2.SelectedValue);

cmd.Parameters.AddWithValue("@.TR_TB_ContZip2", TR_TB_ContZip2.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContPhone2", TR_TB_ContPhone2.Text);

cmd.Parameters.AddWithValue("@.TR_TB_Cont_Acct2", TR_TB_Cont_Acct2.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContName3", TR_TB_ContName3.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContAddr3", TR_TB_ContAddr3.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContCity3", TR_TB_ContCity3.Text);

cmd.Parameters.AddWithValue("@.TR_DDL_ContSt3", TR_DDL_ContSt3.SelectedValue);

cmd.Parameters.AddWithValue("@.TR_TB_ContZip3", TR_TB_ContZip3.Text);

cmd.Parameters.AddWithValue("@.TR_TB_ContPhone3", TR_TB_ContPhone3.Text);

cmd.Parameters.AddWithValue("@.TR_TB_Cont_Acct3", TR_TB_Cont_Acct3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_Name1", PI_TB_Name1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_HomeAddr1", PI_TB_HomeAddr1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_BDay1", PI_TB_BDay1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_SSNum1", PI_TB_SSNum1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_HomePh1", PI_TB_HomePh1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_PrcntOwn1", PI_TB_PrcntOwn1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_NetWorth1", PI_TB_NetWorth1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_AnnInc1", PI_TB_AnnInc1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_MonHouPay1", PI_TB_MonHouPay1.Text);

cmd.Parameters.AddWithValue("@.PI_TB_Name2", PI_TB_Name2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_HomeAddr2", PI_TB_HomeAddr2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_BDay2", PI_TB_BDay2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_SSNum2", PI_TB_SSNum2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_HomePh2", PI_TB_HomePh2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_PrcntOwn2", PI_TB_PrcntOwn2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_NetWorth2", PI_TB_NetWorth2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_AnnInc2", PI_TB_AnnInc2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_MonHouPay2", PI_TB_MonHouPay2.Text);

cmd.Parameters.AddWithValue("@.PI_TB_Name3", PI_TB_Name3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_HomeAddr3", PI_TB_HomeAddr3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_BDay3", PI_TB_BDay3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_SSNum3", PI_TB_SSNum3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_HomePh3", PI_TB_HomePh3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_PrcntOwn3", PI_TB_PrcntOwn3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_NetWorth3", PI_TB_NetWorth3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_AnnInc3", PI_TB_AnnInc3.Text);

cmd.Parameters.AddWithValue("@.PI_TB_MonHouPay3", PI_TB_MonHouPay3.Text);

cmd.Connection = connection;

cmd.Connection.Open();

string NewID = cmd.ExecuteScalar().ToString();

cmd.Connection.Close();

Response.Redirect("DisplayCredApp.aspx?ID=" + Request["NewID"]);

}

catch (Exception ex)

{

lblMessage.Text ="ERROR: " + ex.Message;lblMessage.Visible =true;

}

}

}

this seems to be ok... but i get a load of errors on my "read/display" page which looks like the folowing... (this is the code behind, again, written in C#)

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

publicpartialclassVinny_Credit_Application_DisplayCredApp : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

if (!IsPostBack && Response["NewID"] !=null)

{

string ConnectionString =ConfigurationManager.ConnectionStrings["ws2dbConnectionString"].ConnectionString;

SqlConnection connection =newSqlConnection(ConnectionString);

SqlCommand cmd =newSqlCommand(@."SELECT * FROM CreditAppFile WHERE ID = @.ID", connection);cmd.Parameters.Add("@.ID", Response["NewID"].ToString());

connection.Open();

SqlDataReader reader = cmd.ExecuteReader();

reader.Read();

GI_RB_Div.Text = reader["GI_RB_Div"].ToString();

GI_RB_ExistCust.Text = reader["GI_RB_ExistCust"].ToString();

GI_TB_LegalFirmName.Text = reader["GI_TB_LegalFirmName"].ToString();

GI_TB_Address.Text = reader["GI_TB_Address"].ToString();

GI_TB_City.Text = reader["GI_TB_City"].ToString();

GI_DDL_State.Text = reader["GI_DDL_State"].ToString();

GI_TB_Zip.Text = reader["GI_TB_Zip"].ToString();

GI_TB_TradeName.Text = reader["GI_TB_TradeName"].ToString();

GI_TB_BillAddress.Text = reader["GI_TB_BillAddress"].ToString();

GI_TB_BillCity.Text = reader["GI_TB_BillCity"].ToString();

GI_DDL_BillState.Text = reader["GI_DDL_BillState"].ToString();

GI_TB_BillZip.Text = reader["GI_TB_BillZip"].ToString();

GI_TB_ContName.Text = reader["GI_TB_ContName"].ToString();

GI_TB_Title.Text = reader["GI_TB_Title"].ToString();

GI_TB_MobilePager.Text = reader["GI_TB_MobilePager"].ToString();

GI_TB_Email.Text = reader["GI_TB_Email"].ToString();

GI_TB_Fax.Text = reader["GI_TB_Fax"].ToString();

GI_TB_BusDesc.Text = reader["GI_TB_BusDesc"].ToString();

GI_TB_BusStartDate.Text = reader["GI_TB_BusStartDate"].ToString();

GI_TB_BusPhone.Text = reader["GI_TB_BusPhone"].ToString();

GI_RB_PuchOrdsReq.Text = reader["GI_RB_PuchOrdsReq"].ToString();

GI_RB_BusType.Text = reader["GI_RB_BusType"].ToString();

GI_RB_LiensJudges.Text = reader["GI_RB_LiensJudges"].ToString();

GI_RB_TaxExempt.Text = reader["GI_RB_TaxExempt"].ToString();

GI_TB_FedIDNO.Text = reader["GI_TB_FedIDNO"].ToString();

GI_RB_PriorBankrupt.Text = reader["GI_RB_PriorBankrupt"].ToString();

GI_TB_PriorBankr.Text = reader["GI_TB_PriorBankr"].ToString();

BFR_TB_ContPhone.Text = reader["BFR_TB_ContPhone"].ToString();

BFR_TB_AcctNum1.Text = reader["BFR_TB_AcctNum1"].ToString();

BFR_TB_AcctNum2.Text = reader["BFR_TB_AcctNum2"].ToString();

BFR_TB_CurrBal1.Text = reader["BFR_TB_CurrBal1"].ToString();

BFR_TB_CurrBal2.Text = reader["BFR_TB_CurrBal2"].ToString();

BFR_TB_CurrBal3.Text = reader["BFR_TB_CurrBal3"].ToString();

BFR_TB_CurrBal4.Text = reader["BFR_TB_CurrBal4"].ToString();

BFR_RB_AcctType.Text = reader["BFR_RB_AcctType"].ToString();

TR_TB_ContName1.Text = reader["TR_TB_ContName1"].ToString();

TR_TB_ContAddr1.Text = reader["TR_TB_ContAddr1"].ToString();

TR_TB_ContCity1.Text = reader["TR_TB_ContCity1"].ToString();

TR_DDL_ContSt1.Text = reader["TR_DDL_ContSt1"].ToString();

TR_TB_ContZip1.Text = reader["TR_TB_ContZip1"].ToString();

TR_TB_ContPhone1.Text = reader["TR_TB_ContPhone1"].ToString();

TR_TB_Cont_Acct1.Text = reader["TR_TB_Cont_Acct1"].ToString();

TR_TB_ContName2.Text = reader["TR_TB_ContName2"].ToString();

TR_TB_ContAddr2.Text = reader["TR_TB_ContAddr2"].ToString();

TR_TB_ContCity2.Text = reader["TR_TB_ContCity2"].ToString();

TR_DDL_ContSt2.Text = reader["TR_DDL_ContSt2"].ToString();

TR_TB_ContZip2.Text = reader["TR_TB_ContZip2"].ToString();

TR_TB_ContPhone2.Text = reader["TR_TB_ContPhone2"].ToString();

TR_TB_Cont_Acct2.Text = reader["TR_TB_Cont_Acct2"].ToString();

TR_TB_ContName3.Text = reader["TR_TB_ContName3"].ToString();

TR_TB_ContAddr3.Text = reader["TR_TB_ContAddr3"].ToString();

TR_TB_ContCity3.Text = reader["TR_TB_ContCity3"].ToString();

TR_DDL_ContSt3.Text = reader["TR_DDL_ContSt3"].ToString();

TR_TB_ContZip3.Text = reader["TR_TB_ContZip3"].ToString();

TR_TB_ContPhone3.Text = reader["TR_TB_ContPhone3"].ToString();

TR_TB_Cont_Acct3.Text = reader["TR_TB_Cont_Acct3"].ToString();

PI_TB_Name1.Text = reader["PI_TB_Name1"].ToString();

PI_TB_HomeAddr1.Text = reader["PI_TB_HomeAddr1"].ToString();

PI_TB_BDay1.Text = reader["PI_TB_BDay1"].ToString();

PI_TB_SSNum1.Text = reader["PI_TB_SSNum1"].ToString();

PI_TB_HomePh1.Text = reader["PI_TB_HomePh1"].ToString();

PI_TB_PrcntOwn1.Text = reader["PI_TB_PrcntOwn1"].ToString();

PI_TB_NetWorth1.Text = reader["PI_TB_NetWorth1"].ToString();

PI_TB_AnnInc1.Text = reader["PI_TB_AnnInc1"].ToString();

PI_TB_MonHouPay1.Text = reader["PI_TB_MonHouPay1"].ToString();

PI_TB_Name2.Text = reader["PI_TB_Name2"].ToString();

PI_TB_HomeAddr2.Text = reader["PI_TB_HomeAddr2"].ToString();

PI_TB_BDay2.Text = reader["PI_TB_BDay2"].ToString();

PI_TB_SSNum2.Text = reader["PI_TB_SSNum2"].ToString();

PI_TB_HomePh2.Text = reader["PI_TB_HomePh2"].ToString();

PI_TB_PrcntOwn2.Text = reader["PI_TB_PrcntOwn2"].ToString();

PI_TB_NetWorth2.Text = reader["PI_TB_NetWorth2"].ToString();

PI_TB_AnnInc2.Text = reader["PI_TB_AnnInc2"].ToString();

PI_TB_Name2.Text = reader["PI_TB_MonHouPay2"].ToString();

PI_TB_Name3.Text = reader["PI_TB_Name3"].ToString();

PI_TB_HomeAddr3.Text = reader["PI_TB_HomeAddr3"].ToString();

PI_TB_BDay3.Text = reader["PI_TB_BDay3"].ToString();

PI_TB_SSNum3.Text = reader["PI_TB_SSNum3"].ToString();

PI_TB_HomePh3.Text = reader["PI_TB_HomePh3"].ToString();

PI_TB_PrcntOwn3.Text = reader["PI_TB_PrcntOwn3"].ToString();

PI_TB_NetWorth3.Text = reader["PI_TB_NetWorth3"].ToString();

PI_TB_AnnInc3.Text = reader["PI_TB_AnnInc3"].ToString();PI_TB_MonHouPay3.Text = reader["PI_TB_MonHouPay3"].ToString();

connection.Close();

}

}

}

been working on this for days... everytime i get a little closer i seem to get that much farther away... if you can help by all means give me what you got...

thanks for all the help in advance!!!!

- Vinny

Hi Vinny,

Thats alot of columns in that table. Anyway what is the error message you are getting. It would help to know where it stops.

|||

VinnyPip:

string NewID = cmd.ExecuteScalar().ToString();

cmd.Connection.Close();

Response.Redirect("DisplayCredApp.aspx?ID=" + Request["NewID"]);

.

.

.

SqlCommand cmd =newSqlCommand(@."SELECT * FROM CreditAppFile WHERE ID = @.ID", connection);cmd.Parameters.Add("@.ID", Response["NewID"].ToString());

Change

Response.Redirect("DisplayCredApp.aspx?ID=" + Request["NewID"]);

to

Response.Redirect("DisplayCredApp.aspx?ID=" +NewID);

Change

SqlCommand cmd =newSqlCommand(@."SELECT * FROM CreditAppFile WHERE ID = @.ID", connection);cmd.Parameters.Add("@.ID", Response["NewID"].ToString());

to

SqlCommand cmd =newSqlCommand(@."SELECT * FROM CreditAppFile WHERE ID = @.ID", connection);cmd.Parameters.Add("@.ID", Request.QueryString["NewID"]);

|||

made the above changes Mike suggested and the main error is still the same...

Description:An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message:CS0021: Cannot apply indexing with [] to an expression of type 'System.Web.HttpResponse'

Source Error:

Line 15: protected void Page_Load(object sender, EventArgs e)Line 16: {Line 17: if (!IsPostBack && Response["NewID"] != null)Line 18: {Line 19: string ConnectionString = ConfigurationManager.ConnectionStrings["ws2dbConnectionString"].ConnectionString;

|||

Damn. Missed that one. Change it to

if (!IsPostBack && Request.QueryString["ID"] != null)

And - where I changed something to Request,QueryString["NewID"] before (in the parameters), change that to

Request.QueryString["ID"]

|||

that seemed to compile ok...

and for my next trick... uhh... i mean error:

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.

Exception Details:System.InvalidOperationException: Invalid attempt to read when no data is present.

Source Error:

Line 29: reader.Read();Line 30:Line 31: GI_RB_Div.Text = reader["GI_RB_Div"].ToString();Line 32: GI_RB_ExistCust.Text = reader["GI_RB_ExistCust"].ToString();Line 33: GI_TB_LegalFirmName.Text = reader["GI_TB_LegalFirmName"].ToString();

keep em comin' mike... really appreciate this!!!

|||

NEVERMIND MIKE!!!!!!!!

IT WORKS!!!!!!

next time you're in NYC the drinks are on me... thanks a ton!!!

|||That suggests there is no data. Check the value of ID in the querystring of the page to make sure it was passed correctly (no spaces or odd urlencoded chars), then try running the query in SSMS to see that it works.