Ecommerce database design?

I am setting up a simple store to sell a few products and need some input on
how to design the database for the best efficiency.
Many but not all of the products I sell will have variants. For example, I
will be selling photo prints of different sizes - 5x7, 11x14, etc. , and
packs of cards in varying quantities. They are just different sizes of the
same product, so I don't really want to set each one up as a separate
product record.
However, each size will be a different price so that has to be designed for
too.
Can you direct me to an example of the best way to set up tables to allow
for such a project?
Thanks,
PaulPaul
I think the 'proper' design would call for two tables. One
for the base products, this would have a product-type ie
photo prints, product-id and any other data that was
common to all versions of the product (maybe a foreign key
to a supplier table).
The second table would be the product version table, where
you would have a row for every different version of the
product.
I say 'proper' design because depending on the amount of
data items in each table and the volumes you are dealing
with you may find your system runs better if you de-
normalise the table and make it just one table anyway.
Hope this helps. (And does not confuse.)
Regards
John

Echo for SQL scripts

Hi all,

I want to see input SQL statements in the log file when I run the script in SQLPlus. I have used this set command "SET ECHO ON" for this. However, the log file looks like this -

drop table table_A
*
ERROR at line 1:
ORA-00942: table or view does not exist

7444 rows deleted.

Commit complete.

Thus, the SQL statement is not visible if it is error free. Is there a way to get around this?

ThanksDid you try to SPOOL the results?
This will do it:
SET ECHO ON
SPOOL logfile.log
@.MyScript
SPOOL OFF
;)|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.

This error means what it means: your script executes a script that executes a script ...etc upto more that 20 levels deep.
:rolleyes:

Dynamicly create report

Hi All,
I would like to create report like below:
For example: I have a stored procedure spDeptEmp, which has a Dept ID
as input parameter, Once the Dept ID has been passed in, I will get
all the employees on that dept. The question is: I would like to
generate the report, which will display each employee's detail
information, one person per page.
Could you let me know how can I do that using reporting services?
Thanks in advance!
--BillWhat do you mean by dynamic? I don't see the report being dynamic (i.e. that
you show different columns at different times or some such thing). It seems
to me that you are just returning different data depending on the parameter
but the format/layout etc of the report is unchanged. Everything you
describe here is very vanilla report generation for RS. You can easily add
page breaks, you can easily have a query based on a parameter.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408250950.723ae518@.posting.google.com...
> Hi All,
> I would like to create report like below:
> For example: I have a stored procedure spDeptEmp, which has a Dept ID
> as input parameter, Once the Dept ID has been passed in, I will get
> all the employees on that dept. The question is: I would like to
> generate the report, which will display each employee's detail
> information, one person per page.
> Could you let me know how can I do that using reporting services?
> Thanks in advance!
> --Bill|||"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> What do you mean by dynamic? I don't see the report being dynamic (i.e. that
> you show different columns at different times or some such thing). It seems
> to me that you are just returning different data depending on the parameter
> but the format/layout etc of the report is unchanged. Everything you
> describe here is very vanilla report generation for RS. You can easily add
> page breaks, you can easily have a query based on a parameter.
> Bruce L-C
> "bill" <bli2001@.hotmail.com> wrote in message
> news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > Hi All,
> >
> > I would like to create report like below:
> > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > as input parameter, Once the Dept ID has been passed in, I will get
> > all the employees on that dept. The question is: I would like to
> > generate the report, which will display each employee's detail
> > information, one person per page.
> >
> > Could you let me know how can I do that using reporting services?
> >
> > Thanks in advance!
> >
> > --Bill
The dynamic means you don't know how many employees inside one dept.
until you get the input parameter(dept ID). Different dept. will have
different number of employees. i.e. the report will be different.
Also, for one employee's information, it will come from different
dataset.
Thanks,
--Bill|||What you are wanting to do is exactly what RS is designed to do quite
easily. If a simple matter of here is a dept, list all employee's
information with page breaks between them. That would be a single
parameterized query with appropriate grouping and page breaks. If it is more
a master detail type report then subreports will do what you want.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408251442.232899ab@.posting.google.com...
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> > What do you mean by dynamic? I don't see the report being dynamic (i.e.
that
> > you show different columns at different times or some such thing). It
seems
> > to me that you are just returning different data depending on the
parameter
> > but the format/layout etc of the report is unchanged. Everything you
> > describe here is very vanilla report generation for RS. You can easily
add
> > page breaks, you can easily have a query based on a parameter.
> >
> > Bruce L-C
> >
> > "bill" <bli2001@.hotmail.com> wrote in message
> > news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > > Hi All,
> > >
> > > I would like to create report like below:
> > > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > > as input parameter, Once the Dept ID has been passed in, I will get
> > > all the employees on that dept. The question is: I would like to
> > > generate the report, which will display each employee's detail
> > > information, one person per page.
> > >
> > > Could you let me know how can I do that using reporting services?
> > >
> > > Thanks in advance!
> > >
> > > --Bill
> The dynamic means you don't know how many employees inside one dept.
> until you get the input parameter(dept ID). Different dept. will have
> different number of employees. i.e. the report will be different.
> Also, for one employee's information, it will come from different
> dataset.
> Thanks,
> --Bill

Dynamically number of parameters

I have 3 paramemters. How i can dynamically show/hide other two parameter based on the value as 1 or 2 input from the user from the first parameter? (for example: if the user enter 1, i will show the second parameter, if tyhe user enter 2, i will sho the thirs parameter on the report so the user can enter other value in these dynamical parameters?)Thats not possible. What is possible to still display them , but to clean them to show no value. That can be done using a query in the parameter definition.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Dynamically generate where condition

If we have a pre-defined SQL statement without "where conditions", how do I dynamically insert the where conditions into it (by user input or whatever)? Is there any free tool available for carrying out this kind of work?

Regards,

Ricky.

Ricky,

If the Where condition must be added to a Sql statement say within a Stored Procedure,

then you could use dynamic SQL statements.

declare @.sql varchar(255),

Select @.sql ="Predefined SQL statement" + "The where condition which can be input to the SP"

Exec @.sql

Sandoty

|||

Be cautious when you use the dynamic sql queries. It may cause two preaches,

1. SQL Injection

2. Permission issues

SQL Injection,

Your code is allowing the end user to inject the SQL command on your own code. So they can vulnerable your database be cautious here. Try to eliminate the comments (-- or /**/) as your input. You can control it on the UI itself. There are lots more practices are there. Google it for SQL Injection. You will get better idea.

Permission Issues,

Dynamic sql will expect the required permission on the source tables, if current user doesn’t have the permission on the table level (but on the SP), the dynamic SQL won’t work.

You can achieve the filter conditions from the SPs or prepared query itself. If the intention is reducing the code & time to achieve this task Pls take care the above issues.

|||

One more point about dynamic queries is the performance issues.

Each time you want to run them the sql command has to be recompiled in order to get an execution plan.

This may be positive in some situations. But most of the time the best way is to set your application to run with specific queries.|||

You can find a lot of information about this theme in the following articles.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

AMB

Dynamically display/hide the parameter input

I have a handful of reports that are currently used by sales reps, and I'm trying to make them available to their regional VP's, and coporate users (executives and administrative staff that support Sales nationwide).

Currently, the reports take the UserID and resolve it to show the information that is only appropriate for that specific rep.

What I would like to do is have the parameter section at the top of the report be displayed for higher level users, so they could select an individual sales rep from a drop-down. (Ideally, the RVP's would only be able to select from reps in their region, but the corporate users would be able to select any rep.) The problem is, I don't want any of the sales reps to be able to select a rep other than themselves, for obvious reasons.

Is there a way to have the parameter section hidden/displayed dynamically, based on the UserID, so that users other than reps would have the ability to enter the desired rep name, but reps would not?

If you are using the .NET ReportView, it is just:

ReportViewer1.ShowParameterPrompts = False

|||I am using Report Manager.|||If you want any level of control, you are going to have to host the ReportViewer control in an ASP.NET page and customize the web page/report based on the user. There is not much you can do in ReportManager as far as fine control.|||

1. One way is to control this by writting a html page instead of report manager home page within which you will have a link to each of the report. Depending on who the user is you will change the path of the link (with or without parameters), meaning you will default all the parameters and pass them within the hidden link, but this wont work if you have more that 1 parameter and would want to hide only the user id parameter

2. Another option is to control this within the report. As your userid parameter list is being populated by a stored procedure, you will make the stored procedure accept a input parameter which is the userId of the person that is trying to run the report. Depending on who the user is the SP will return only the required users list.

Meaning

1. if XXX is passed and if XXX is RVP then it will return only the sales persons of XXX's region.

2. If YYY is passed and if YYY is sales person then it will return back only YYY

3. If CORP is passed and if CORP is a corporate user then the procedure will return all sales person's names

Hope this is clear and helps!!!

|||I think another way of achieving this is writting specific RDL code.|||

Thanks.

In the short term, this was the best solution. I wrote a sp that populates the drop down based on the NetLogin value.

So, the sales reps can see the drop down, but it only has their name in it.

|||You can also set the parameters visible in the URL using the &rsarameters=True/False.

i.e.

Code Snippet

http://SERVER/ReportServer/ReportPath&rs:Command=Render&rs:Parameters=False

HTH,
Jimmy

Dynamically assigning a value using the "Top N" Clause

I've developed a proc that takes an input parameter @.TopN Int. I want to
use it to dynamically pull the top n records from my DB as
such
Select TOP @.TopN UserID, Metric1, Metrics2...
Order By Metric1
I can only get this to work if I use an integer constant.
i.e. Select TOP 10 UserID
I Can't get it to work with the variable I'm passing in
Anyone know how to do this easily?
Thanks in advance
Message posted via http://www.webservertalk.comYou can use SET ROWCOUNT instead, but make sure that your ORDER BY
clause includes a key that is unique in the result set because SET
ROWCOUNT has no equivalent of the TOP WITH TIES option. Unless the
ORDER BY criteria is unique you may get unpredictable results.
David Portas
SQL Server MVP
--|||See:
http://groups.google.ca/groups?selm...FTNGP10.phx.gbl
Anith|||T Harris via webservertalk.com wrote:
> I've developed a proc that takes an input parameter @.TopN Int. I want
> to use it to dynamically pull the top n records from my DB as
> such
> Select TOP @.TopN UserID, Metric1, Metrics2...
> Order By Metric1
> I can only get this to work if I use an integer constant.
> i.e. Select TOP 10 UserID
> I Can't get it to work with the variable I'm passing in
> Anyone know how to do this easily?
> Thanks in advance
You'll need to wait for SQL 2005 for that.
David Gugick
Imceda Software
www.imceda.com|||Thanks, everyone for the responses.
Since the subset of data that I'm querying for is dynamic itself (i.e. I
don't know what the topN data set will be until after the order by is
applied) I can't directly apply the set rowcount. So the only way I can get
it to work it to query the dataset and then use the set rowcount against
the already ordered data set. This adds some overhead but it does work.
Tharrris
Message posted via http://www.webservertalk.com

Dynamic WHERE operator based on user input

Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements:

DropDownList1: This drop down list contains 3 choices- "=", ">", and "<".

Age: This text box is where someone would enter a number.

Button1: This is the form's submit button.

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error:

"The variable name'@.Age' has already been declared. Variable names must be unique within a query batch or stored procedure."

Any help would be appreciated.

Here is what I'm using in my code behind:

protected void Button1_Click(object sender, EventArgs e)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT * FROM People WHERE Age ");
switch (DropDownList1.SelectedValue)
{
case "=":
sb.Append("= ");
break;
case ">":
sb.Append("> ");
break;
case "<":
sb.Append("< ");
break;
}
sb.Append("@.Age");
SqlDataSource1.SelectCommand = sb.ToString();
SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text);
}

i would create 3 stored procedures first with "<" second with "=" and third with ">"

then, in code behind

if (DropDownList1.SelectedValue.ToString() == "<") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("LessThan_toate", conn);//cnn = your conn string command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader;// your dataReader DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() =="=") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("EqualsWith", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() ==">") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("GreatherThen", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }

sure, my stored procedures doesn't have input parameters but you can change easly that

|||

adammckee:

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

So basically you want everything in the table? what are you excluding?

|||

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||Either I don't understand what you're doing or you're making it more complicated than it should be. Wouldn't this work?

Private Sub cmd_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cmd.ClickDim sQueryAs String sQuery ="SELECT * FROM People WHERE Age " &Me.DropDownList1.SelectedValue &" " &Me.txtAge.Text SqlDataSource1.SelectCommand = sQueryEnd Sub

|||

This worked nicely, but doesn't this leave me open to SQL injection? I was attempting to do this with the SelectParameters method to avoid that.

To clarify what I'm trying to do for the other person that asked, I want to give the user the ability to search for people in the database based on their age. If the user wants to search for anyone that's over 50, they pull down the drop down list, select the ">" operator then type "50" in the text field and perform the search.

ecbruck:

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||

If you're worried about it, then go ahead and use a Parameter instead. However, I would at least recommend declaratively placing the Parameter within your SqlDataSource on your ASPX page and then simply setting the DeefaultValue of the property within your Click event handler or the SqlDataSource.Selecting event handler.

|||

For anyone interested, this is what works for me. Thanks again for the advice.

1protected void Button1_Click(object sender, EventArgs e)2 {3 System.Text.StringBuilder sb =new System.Text.StringBuilder();4 sb.Append("SELECT * FROM People WHERE Age ");5switch (DropDownList1.SelectedValue)6 {7case"=":8 sb.Append("= ");9break;10case">":11 sb.Append("> ");12break;13case"<":14 sb.Append("< ");15break;16 }17 sb.Append("@.Age");18 SqlDataSource1.SelectCommand = sb.ToString();19if (SqlDataSource1.SelectParameters["Age"] ==null)20 SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, TextBox1.Text);21else22 SqlDataSource1.SelectParameters["Age"].DefaultValue = TextBox1.Text;23 }

Dynamic Tool Configuration

Newbie here wanting to get information, links, opinions on how to configure a simple data flow from code. Here's my example. I have a CSV input tool, a SORT tool, and a CSV output tool. I want to call this package from an application running C#. Can I access this package like DataPack("filename","sort","output filename") from the object model? Or does XML have to get involved? Can I dynamically swap out the input or output tools? Any links to the UML mockup of the object model? Just getting started here.

Thanks in advance,
Mitch

Start here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/60fe148b-a7c4-4289-ae3e-2e949fc1886c.htm