I want to have visitors to my website get price calculations from my database based on input variables which the visitor enters. I think I need the combination of Access, ASP.NET, and Sql Sever. Is this correct?
View 5 Replieshello,
i have a Products table, and i want to make an image data type to one of his rows (Picture1 for example), i want to know, what is best, to store the picture in the database or store only the direction to the picture?
if i store only the direction, i should take it from some output parameters of the Upload function of ASP and the add it to the Database? can i add it to a special folder for example MySiteUserName + file name?
and another thing: let's say i have Promotion - tinyint, to store the promotion value of this product..If i show products using DataList, i could order my products first after Promotion and then after date added? could i use a special CssClass(font weight or other background) for the products witch has the Promotion more than 10 for example?
how can i know the exact date time (yy/mm/dd/hh/mm) ? - it is taked from the Server date?
thank you
For the last 2 years we have been using ArcServe for Windows NT (version 6.0 and 6.5) with the Backup Agent for SQL Server. We are now re-evaluating our backup software. Our primary issues with ArcServe are that it has been unstable, unreliable and it does not support the full SQL Server recovery functionality. ArcServe does not include the ability to do point-in-time database recovery. Nor does it support single table restores (when a full database backup was performed). These last 2 items are critical and necessary features at my installation.
What products do people recommend for performs Windows NT files and MS-SQL Server backups?
Consider the below tables
Table: Products
----------------------------------------------------
| pid | pname | ranking | cat1 | cat2 | cat3 | mid |
----------------------------------------------------
| 1 | x1 | 20 | 2 | 3 | -1 | 1 |
| 2 | x2 | 40 | 3 | 2 | -1 | 1 |
| 3 | x3 | 80 | 2 | 4 | -1 | 1 |
| 4 | y1 | 40 | 2 | -1 | -1 | 2 |
| 5 | y2 | 60 | 1 | 3 | -1 | 2 |
| 6 | z1 | 50 | 3 | -1 | -1 | 3 |
| 7 | z2 | 70 | 1 | 3 | -1 | 4 |
----------------------------------------------------
Table: Manufacturers
---------------
| mid | mname |
---------------
| 1 | A1 |
| 2 | B2 |
| 3 | C3 |
---------------
Table: Categories
---------------
| cid | cname |
---------------
| 1 | I1 |
| 2 | J2 |
| 3 | K3 |
---------------
Say if I want to get the top 3 products that fall under category "J2" and have them ordered by ranking then I would use the
below query
ELECT DISTINCT TOP 3 mid, pid, pname, prank, mname
FROM Products, Manufacturers, Categories
WHERE Products.mid = Manufacturers.mid AND (cat1=2 OR cat2=2 OR cat3=2)
ORDER BY ranking
Now what if I need the top 3 ranked products that fall under category "J2" and have them ordered by ranking selecting only
one product from each manufacturer. So in order words I want the top ranked product from each manufacturer.
What would that query be?
I am having a really tough time and have already spent quite sometime on it unsuccessfully. I would appreciate any help.
Hi guys, I am an absolute beginner to SQL Server, so please excuse my
simple questions. What is the difference between:
1) Microsoft SQL Server 2005
and
2) Microsoft SQL Server 2005 Express Edition
and
3) MSDE?
Do both the SQL Server and Express Edition have the same functionality?
Thanks,
Kon
I am trying to write a bit of code that I can pass a brand name to. If the brand name exists I want to return the brandid to the calling middle tier. If the brand id does not exist I want to insert and then return the new brand id. The code below works unless the brand does not exist. Then it inserts, and I get an application exception. Next time I run the code it continues on until the next time it has to do an insert. So the inserts are working, but getting the value back is resulting in an application excetio.
Middle Tier Function (
private static int GetBrandForProduct(clsProduct o)
{
int brandid = -1;
// If the brand name comes in blank use the first word of the overstock product
o.BrandName = o.BrandName.Trim();
// if we do not have a brand for this product
if (o.BrandName.Length == 0) return -1;
Database db = CommonManager.GetDatabase();
;
try
{
// Get the brand id for this brand name
// If it does not exist we will add it and STILL return a brand id
object obj = db.ExecuteScalar("BrandIDGetOrInsert", o.BrandName);
string catid = obj.ToString(); *** FAILING LINE ***
return Convert.ToInt32(obj.ToString());
}
catch (Exception ex)
{
throw ex;
return -1;
}
return brandid;
}
Stored Procedure: -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[BrandIDGetOrInsert]
-- Add the parameters for the stored procedure here
@brandnameparm varchar(50)
AS
BEGIN
-- SET NOCOUNT ON
SELECT brandid from brands
where Lower(brandname) = Lower(@brandnameparm)
-- If we found a record, exit
if @@rowcount > 0 return
-- We did not find a record, so add a new one.
begin
insert into brands (Brandname) values(@brandnameparm)
end
SELECT brandid from brands
where Lower(brandname) = Lower(@brandnameparm)
END
Hi everyone,
This is probably a very easy question:
I have a shopping cart table and two products tables (legacy reasons...).
I need to create a relationship between the ShoppingCart table and the two products tables such that no products are entered in the Cart which don't exist in either product table 1 or product table 2.
Any ideas?
Thanks heaps,
LJ.
I have successfully downloaded and installed the VS C# 2005 Express, Web Developer 2005 Express, and SQL Server 2005 Express.
View 1 Replies View RelatedHi
I'm trying to select the TOP 20 Products per Catagory out of my Star Topology Database
if someone would PLEASE be able to help me out.
Here is my Query: -
SELECT Distinct (SELECT TOP 20 DP.[Description]), DP.Mims_Sub_Cat, SUM(FD.Cost) AS 'Cost'
FROM DIM_Product DP, FACT_Dispensary FD, DIM_Time DT, DIM_Client DC
WHERE DP.Product_KEY = FD.Product_Key
AND FD.Time_KEY = DT.Time_KEY
AND FD.Client_Key = DC.Client_KEY
AND DT.[Year] = 2007
AND DT.[Month] IN (2)
AND Client_name LIKE '%Medicare%'
AND DP.Manufacture_Name LIKE '%Cipla%'
--AND DP.[Description] IN (SELECT TOP 20 [Description])
AND DP.Mims_Sub_Cat IN (SELECT Mims_Sub_Cat)
GROUP BY DP.Mims_Sub_Cat, DP.[Description], FD.Cost
ORDER by DP.Mims_Sub_Cat, SUM(FD.Cost) DESC
My other problem is that it keeps on selecting the same products although i have a distinct in my query
The query will select a product with the amount and then select the same product again with a different amount.
Example :-
Product Descripion Catagory Cost
ULTAK TABS 150MG 60 Acid Reducers 384.94
ULTAK TABS 150MG 60 Acid Reducers 85.54
ULTAK TABS 150MG 60 Acid Reducers 56.98
How can I store more than one category in a products table?
For exampe: I have a dvd website where the admin can add new dvd's. On the website all the categories are listed with a checkbox. If the dvd is a action comedy the admin have to check these two checkboxes. But how do I store that in the sql database an retrieve it?
Thanks
David
Are there any other products out there to monitor database availability
in SQL server 2000 beside Tivoli by IBM?
Thanks;
Lava
Hi i've to table where i have a product code and a area code.
I want to insert the values from table 1 into table 2 if the product code and area code do not already exists in the table in table 2.
Am using this code below but it does not seem to be working.
select distinct p_code1, area_code
from table1
where not exists
(select p_code, area_code
from table 2 )
[crossposted]Hi, I wonder if anyone might lend me a brain.I have a stock database to build that covers over 1000 products, whichmight be said to exist in around 50 product families.Obviously, just to be awkward all the types of stock will havedifferent attributes. So one product might be a tube withinside/outside diameter and length and another a T shaped cable joint.All I can come up with is a separate table for each stock type familyand store the table name and product code in the main stock table, so:Tables:ProdAProdBProdCStockStock attributes:ProdIdProdTableAmountDateetc..ProdA attribute:ProdIdAttributeXAttributeYAttributeZetc..Then use code to parse the table and product ID to select the correctquery to get the product details. BUT This seems awefuly inelegant andpotentially wrong so I'm loathe to continue down this route.Can anyone tell me the "right" way to do this, I feel sure it must bea classic db design exercise, but unfortunatly one they didn't teachus at University -- or maybe I was asleep...Thanks!
View 4 Replies View RelatedI got a stored proc which returns the result dataset.
My requirement is to sort the final output depending on a parameter value (which is dependent on 2 different amount column from the result dataset) , display only top 10 products and equate the % mix for those 10 products only. Basically the % mix column should sum up to 100% for those top 10 products.
How do I achieve it in SSRS ?
If I do
=Fields!ProductCurrentBalance.Value/SUM(Fields!ProductCurrentBalance.Value, "ds_Top10ProductMix")
Then it divides by the SUM for all products and not the TOP 10 products only.
The query I have written below works fine. However, I now want to uncomment the WHERE clause below to find entries where the PatientAge does not fall between the PAGBeginningAge and PAGEndingAge. However, when I uncomment the WHERE clause line I receive the following error message:
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'FROM'.
SELECT ampfm.rpt_AdtVisit.PatientFullName, ampfm.rpt_AdtVisit.AdmitPriorityCode, ampfm.dct_AdmitPriorityType.AdmitPriorityTypeName,
ampfm.rpt_AdtVisit.AccountNumber, ampfm.rpt_PatientDemographics.PatientAge, ampfm.rpt_PatientDemographics.PatientAgeGroup,
ampfm.rpt_PatientDemographics.PatientSex,
CASE WHEN PatientAgeGroup = '0 - 14' THEN '0' ELSE
CASE WHEN PatientAgeGroup = '15 - 24' THEN '15' ELSE
CASE WHEN PatientAgeGroup = '25 - 34' THEN '25' ELSE
CASE WHEN PatientAgeGroup = '35 - 44' THEN '35' ELSE
CASE WHEN PatientAgeGroup = '45 - 54' THEN '45' ELSE
CASE WHEN PatientAgeGroup = '55 - 64' THEN '55' ELSE
CASE WHEN PatientAgeGroup = '65 - 74' THEN '65' ELSE
CASE WHEN PatientAgeGroup = '75 - 79' THEN '75' ELSE
CASE WHEN PatientAgeGroup = '80 - OVER' THEN '80'
END END END END END END END END END AS PAGBeginningAge,
CASE WHEN PatientAgeGroup = '0 - 14' THEN '14' ELSE
CASE WHEN PatientAgeGroup = '15 - 24' THEN '24' ELSE
CASE WHEN PatientAgeGroup = '25 - 34' THEN '34' ELSE
CASE WHEN PatientAgeGroup = '35 - 44' THEN '44' ELSE
CASE WHEN PatientAgeGroup = '45 - 54' THEN '54' ELSE
CASE WHEN PatientAgeGroup = '55 - 64' THEN '64' ELSE
CASE WHEN PatientAgeGroup = '65 - 74' THEN '74' ELSE
CASE WHEN PatientAgeGroup = '75 - 79' THEN '79' ELSE
CASE WHEN PatientAgeGroup = '80 - OVER' THEN '200'
END END END END END END END END END AS PAGEndingAge
--WHERE PatientAge NOT BETWEEN PAGBeginningAge AND PAGEndingAge
FROM ampfm.dct_AdmitPriorityType INNER JOIN
ampfm.rpt_AdtVisit ON ampfm.dct_AdmitPriorityType.AdmitPriorityTypeCode = ampfm.rpt_AdtVisit.AdmitPriorityCode INNER JOIN
ampfm.rpt_PatientDemographics ON ampfm.rpt_AdtVisit.RegNum = ampfm.rpt_PatientDemographics.RegNum
i designed a database for eshop.
i have two table one of them is products and it has id,code,name columns and the other one is option with id,proid,option,optiondes column. and there is a relationship with id from products to proid in option table.
i want to make query that results is compare two or more products with the same option column.
Hello,
I'm wondering what would be the best approach to designing a database that will have different products one of them being T-Shirts of different sizes... for example 1 t-shirt design might only have 2 available sizes while another may have 4. I'm kinda stumped on how to approach this cuz there is multiple products like CD's, DVD's, Magazines etc which is pretty straight forward, but the T-shirts have this "variable" to it.
What i'm really wondering is should i have 1 main "Products" Table or should i have a separate table for the t-shirts? Should there be a column for each available size?
Currently my database has a "products table" that has foreign keys to "Product Type", "Artists", "Genre"
The database is basically for a record company
If anyone has designed a database similar to this i'd love any insight or even possibly to see a database diagram
Thanks
selecting number of products that comes under a parent categorysqlserver2005CREATE TABLE [ProductCategoryAssociation]([CategoryID] [bigint] , --this is primary key of table Category[ProductID] [bigint] ,--this is primary key of table product) the above table binds a product to its category CEATE TABLE [Category]([CategoryID] [bigint] IDENTITY(1,1) -- this is primary key of table category[Name] [nvarchar](255)[ParentCategory] [bigint] NULL,) I have a Category"A" as a supper parentcategoryI have 1product in Category"A"Category"A" have 2 child category A11,A12. A11,A12 both have 2 products in the category (totally 4)Now I can get the count of all product belong Category"A" (ie is 5= 1+2*2) fivelet 1 be the id of category "A" Now the following querry give me the expected resultselect count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1) BUT if i have child for A11 named A111 and if there is ONE product belong to A111 this querry wont take that in to cosiderationso the following querry will do itselect count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1 or Parentcategory in (select categoryid from Category where Parentcategory=1))ie i nested one more selectbuthis has limitationsSo kindly give me a general solution to check up to the las level of category
View 8 Replies View RelatedFind out the models and prices for all the products (of any type) produced by maker B.
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
select product.type,
pc.price as pcprice,
laptop.price lapprice,
pc.model as pcmod,
laptop.model as lapmod
from product
join
pc on product.model=pc.model
join
laptop on laptop.model=product.model
where maker = 'B'
the syntex runs but its not displaying any results + I know that I have some extra columns there but its for some thing else I was trying
i want to make a web page and when somebody come in. i want show for him which products that everyone often buy at that time ( month or summer ).
how i do in data mining to predict that products ?
more: i want know how much percent of product is like by buyer
or i want show products with desc % of the like of people
Greetings
I'm completely stumped by this one.
Yesterday I installed Microsoft Visio Professional 2003 and Microsoft Project 2003.
Today when I open any of my SSIS packages I get this:
There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.
The error list says: Unable to instantiate XML DOM document please verify that the MSXML binaries are properly installed and registered.
When I try to create a new integration services project I get the following modal message box:
Failed to save Package File C:Documents and SettingsMyNameLocalSettingsTemp mp172.tmp with error 0x8002802B - Element Not Found
Yet I can create and run a small C# program no problem, and SQL Server Management Studio seems to be OK.
What do I need to de-install and buy a seperate computer for here? Is it Visio or Project?
Thanks in advance
Hi
I have developed a product
basket mining model as follows
DSV
SELECT C.CustomerId,C.CustomerIdName,P.ProductId,P.ProductIdName
FROM Customer INNER
JOIN CustomerProduct
ON C.OpportunityId
= P.OpportunityId
Mining Structure
CREATE MINING MODEL ProductBasket
(
CustomerId
TEXT KEY,
CustomerIdName
PREDICT,
ProductId
PREDICT,
ProductRecommend
TABLE PREDICT
(
ProductId TEXT KEY
ProductIdName PREDICTONLY
)
)
USING Microsoft_Association_Rules
Prediction Query
Since I want the output in the following format
Product
ID
Items (nested Table)
Product
A
product B
Product C
Product
B
Product A
Product C
I have written the prediction query as follows
SELECT
t.[ProductId],
PredictAssociation([Association].[ ProductId],3)
From
[Association]
PREDICTION JOIN
OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT
[ProductId]
FROM
(SELECT ProductId FROM ProductBase)
as [Product]
') AS t
ON
[Association].[Product Id] = t.[ProductId]
The model is predicting the
same set of products for every case. Even changes in the algorithm parameter
value do not have any impact on the result.
What is the reason for this and
how can u rectify it?
Query that show me a Products Tables and its models, 1 product could have 2 or more models
Example Table Products
id Name
1 Product 1
2 Product 2
3 Product 3
Example Table Models
idmodel idproduct model
1 1 Model 1
2 1 Model 2
3 1 Model 3
4 2 Model 1
5 2 Model 2
6 3 Model 1
And I want to show:
Product Model
Product 1 Model 1
Model 2
Model 3
Product 2 Model 1
Model 2
Product 3 Model 1
How can I query to show that?
We have a 3 month old quad processor/dual core server running SQLServer 2005 and already it is getting close to hitting the CPU wall.An 8 way CPU box is prohibitively expensive and out of the question. Iam looking desperately for a way to TRULY scale out SQL server...in thesame way that IIS can be scaled out via App Center.The "in the box" solution for SQL Server 2005 scaling out is the DMV.Unfortunately this solution makes the system less available rather thanmore (one server outage takes them all out for that table) and requiresserious rearchitecting of the software to use. Contrast this to IISand AppCenter where each added server makes the system more available,and requires no rearchitecting to work.Before someone says "what you want can't be done in adatabase"...Oracle has an application server middleware product thatlets you do both of the above. Just plug a new server with Oracle onit, and you've doubled your capacity. But SQL Server 2005 doesn't yethave a similar capability.So I read with great interest the following article that talks aboutwhy this is the case with SQL Server. There are two issues that makeit very difficult to do:http://www.sql-server-performance.c...ability_availab...You can create a crude pool using replication, but the performancetimes look horrendous.However, the article also talks about the latest developments in thisfield...specifically MIDDLEWARE that can create a scale out solutionthat is more available and that requires simply adding new servers toscale up.I found two companies which seem to offer this new capability:http://www.metaverse.cc/newsevents.asp?cid=17999andhttp://www.pcticorp.com/product.aspxBoth companies appear to have patents or a patent pending on theprocess. I tried to contact metaverse but got no reply, despite theirrecent press release. I just emailed Pcticorp today to see if I couldlearn more about their product.My question for this group is:Does anyone have experience with either of the two products (or anyothers that provide this capability)?Many thanks in advance for your help.Ian Ippolitohttp://www.rentacoder.com
View 17 Replies View RelatedHello,
I would like to get some advice on Visual Studio and Business Intelligence products. I currently work with SQL Server 2005 and I created an export package which I turned into an SSIS package. I would like to be able to change the SSIS package when I need to and I read that I needed Business Intelligence to modify the package. I installed the Business Intelligence software through our SQL Server install disks and still didn't see the Business Intelligence app in my program files. We have another server running SQL 2005 and I can modify SSIS packages there with BI but it has Visual Studio loaded on it. Do I need Visual Studio to use BI?
Thanks,
Smith3rd
I am writing a query to display the count of products when a month is the primary month (Month where large sales happened)..Please consider the following query..
With Member ProductRank as
Rank([Date Due].[Calendar].currentmember,TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount]))
Member PrimaryProd as
Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1))
Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,bdesc) on 1 from [Sales]
Output:
It runs for 13 seconds on my laptop. I have modified the query to get the results fast like With
Set MySet as TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount])
Member ProductRank as
Rank([Date Due].[Calendar].currentmember,MySet)
Member PrimaryProd as
Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1))
Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,Bdesc) on 1 from [Sales]
Output:
This query runs instantly and result is not the same. Am I missing something here?
We have a sales fact table that has sales by customers, products, date etc. and dimension tables such as customers, products etc.
We built a multi-dimensional cube on top of these tables. how do we support queries like "show me top 10 products by sales amount and then show customers that didn't buy these products"?
Hi,
I tried to install sharepoint server 2007 as a standalone in my computer. When i run the sharepoint products and technologies configuration wizard, i get the following error,
Failed to create sample data.
An exception of type System.Data.Sqlclient.SqlException was thrown. Additional Exception information:
Cannot open database "SharedServices2_DB_28aa01d8************************" requested by the login. The login failed.
Login failed for user "domainAdministrator"
Please advice me to solve this issue.
Thanks,
Magesh S
I am trying to pivot my data results so that instead of showing multiple rows for each product a client has it will show one line for each client and concatenate all of their products together.
For example, if I run the following query:
SELECT
[Year],
[Client_Name],
[Product_Name]
FROM My.dbo.Table
GROUP BY
[Year],
[Client_Name],
[Product_Name]
I get the following result set:
YearClient_NameProduct_Name
2014BobHosting
2014BobDevelopment
2014BobTesting
2014FredDevelopment
2014FredConsulting
2014MaxConsulting
2015BobHosting
2015BobTesting
What I want to get back as the result set is the following:
YearClient NameProduct-List
2014BobHosting-Development-Testing
2014FredDevelopment-Consulting
2014MaxConsulting
2015BobHosting-Testing
So, I would only get one record back for each Client for each Year with a list of all of their products concatenated together.
My site uses a text box to allow visitors to search products. I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
E.g.
megger
meg
mft megger (proper order in the database is megger mft)
mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code:
<%
Dim RSResults__param5
RSResults__param5 = "xxx"
If (Request("searchme") <> "") Then
RSResults__param5 = Request("searchme")
End If
[Code] ....