|
Posting more details can help us helping you,
Do you have these records in local or temp table?
Why do you want to access them sequentially?
|
|
|
|
|
Using click once i can update my application on the users machine easily, but when i make database changes, i need the database to also be updated...is there a way to integrate SQL Server 2005 into the click once process without trashing the users existing data?
Thanks in advance for any help!
--
"Keyboard not found. Press < F1 > to RESUME. "
Source unknown (appears in many common BIOSes as a real error message)
|
|
|
|
|
Hi.
For the last 14 days i have been struggling with design pattern (T-SQL / Tables) for cascading product variants in a E-Commerce solution ("value-pairs pattern" / "diamond pattern"). I did find this DB diagram from IBM (http://publib.boulder.ibm.com/infocenter/wchelp/v5r5/topic/com.ibm.commerce.developer.doc/refs/rdmattribute.htm), which seams to be a good table design.
My problem is, that i cant figure out how, to make the SELECT statement for each cascading selection. Attributes should be selected in a specified order, each dropdown below is disabled until the previous selection is made.
Table design is like this:
"Product"
"ProductAttribute" (Relates each combination (T-Shirt, Small & Red) to a ProductID ..... AttributeID > ProductID)
"Attribute" (Relates "AttributeType" and "AttributeValue" together)
"AttributeType" (Type, Size, Color)
"AttributeValue" (T-Shirt, Jeans, Jacket, Small etc)
If i have 3 drop downs, with the following options:
Dropdown #1 (Type): T-Shirt, Jeans, Jacket
Dropdown #2 (Size): Small, Medium, Large, Extra Large etc.
Dropdown #3 (Color): Red, Green, Blue, Purple etc.
When the user selects a Type, Dropdown #2 is enabled and populates with possible values for Size. The problem is, how to make a T-SQL that only selects the available sized with the type "T-Shirt".
There is not limitation on how many Attribute each product can have. So a product could have X amount to attribute selections (dropdowns).
Thanks in advance
mm98 - Denmark
|
|
|
|
|
A SELECT statement that utilizes a number of correlated subqueries should solve your problem.
Something like:
SELECT DISTINCT AttributeType, AttributeValueID
FROM Attribute
INNER JOIN AttributeType
ON Attribute.AttributeTypeID = AttributeType.AttributeTypeID
WHERE AttributeType.AttributeType = 'Size'
AND EXISTS
(
SELECT TOP 1 *
FROM ProductAttribute
WHERE AttributeID = Attribute.AttributeID
AND EXISTS
(
SELECT TOP 1 *
FROM ProductAttribute AS ProductAttribute1
INNER JOIN Attribute
ON ProductAttribute1.AttributeID = Attribute.AttributeID
INNER JOIN AttributeValue
ON Attribute.AttributeValueID = AttributeValue.AttributeValueID
WHERE ProductAttribute1.ProductID = ProductAttribute.ProductID
AND AttributeValue.AttributeValue = 'T-Shirt'
)
)
Hope this helps
Paul Marfleet
|
|
|
|
|
Hi all ,
Is it possible to use Windows Authentication Mode for SQL Server in workgroup environment?
Thanks in advance .
|
|
|
|
|
DotNetWWW wrote: Is it possible to use Windows Authentication Mode for SQL Server in workgroup environment
Yes.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks ,
How can I do that?
|
|
|
|
|
DotNetWWW wrote: How can I do that?
In SQL Server 2005 you can do it as following
1. Right click on Connected server.
2. Select Properties menu.
3. Select "Security" option from left pane.
4. check the "Server authentication" option in right pane.
5. Select "Permissions" option from left pane.
6. Add Login or Role to particular user.
Knock out 't' from can't,
You can if you think you can
|
|
|
|
|
Thanks for your answer , But :
I'm in a workgroup environment , In this situation , How can I add users of other client computers that are going to connect to server?
and another point is that when I finished writing the program , What settings should I change in order to make my program available for client computers?
Thanks in advance .
|
|
|
|
|
Hi
i am trying to create a new user to my sql server database, but i am strugling,i want to enable this new user to be able to remotely connect to my database using his C# application, can anyone pls help show me how to do this. currently i have a database which i use windows authentication to connect to it, but now i want this new user and i to connect to one database.
thanks
gazide
|
|
|
|
|
Use Stored Procedures sp_addlogin, sp_adduser and sp_addrolemember
|
|
|
|
|
In SQL Server 2005, it is preferable to use CREATE LOGIN, CREATE USER instead of sp_addlogin, sp_adduser.
They will be removed in future versions of SQL Server.
|
|
|
|
|
Hi All,
I have wrote stored procedure which calculates points for result for around 1000 users.
This procedure takes no parameters from asp.net code.
asp.net code is used for just for calling this procedure. But when I run this procedure from the code it gives me the timout error.
while running from the sql server itself it works fine.
Kindly tell me how can I overcome this problem?
Below is my code, which is giving me the error.
try
{
Helper connHLP = new Helper(false);
connHLP.Retrieve("prcUpdateUsePoints", null);
}catch (Exception ex)
{
throw ex;
}
----------------------------------------------------------------------------------------------------------------------
Code for connection & retrive:
private SqlConnection conn;
private SqlTransaction tran;
public Helper(bool TransactionRequired)
{
try
{
string strConn = string.Empty;
strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
conn = new SqlConnection(strConn);
conn.Open();
if (TransactionRequired == true)
{
tran = conn.BeginTransaction();
}
else
{
tran = null;
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet Retrieve(string ProcedureName, SqlParameter[] ParamCollection)
{
try
{
DataSet ds = new DataSet();SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
if (ParamCollection != null)
SetParameters(cmd, ParamCollection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public void SetParameters(SqlCommand cmd, SqlParameter[] ParamCollection)
{
try
{
foreach (SqlParameter param in ParamCollection)
cmd.Parameters.Add(param);
}
catch (Exception ex)
{
throw ex;
}
}
----------------------------------------------------------------------------------------------------------------------
Thnking you all in advance.
|
|
|
|
|
The command object has a default timeout of 30 seconds. YOu will need to set this to some sort of timeout that is long enough for the stored procedure to run. If this is a really long running sp you may need to increase some of the web site timeouts as well.
Hope that helps.
Ben
|
|
|
|
|
Query Analyzer/Management Studio does not time out. In contrast, ADO.NET does have a default command timeout of 30 seconds. To change this, change the SqlCommand's CommandTimeout property.
You should consider investigating why this command is taking so long, however. Blocking an ASP.NET worker thread for any significant length of time seriously inhibits the scalability of your website.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks for your reply.
Your answers could have solved my problem. If I limit the no. of users.
But that is the problem. No. of users will increase in coming time.
So if there is any other efficient way to solve this problem.
Plz tell me
|
|
|
|
|
Neither one of us suggested you needed to limit the number of user being returned. Both of us suggested that if you increase the timeout property of your command object then they will all return. If you look at the time in your query tool that it take to execute and it is like 1 minute, then increase your command timeout to like 2 minutes and you should be fine. If the time in it takes to execute is like 5 minutes then fix your query because you are missing indexes or something if it is taking that long to execute.
Ben
|
|
|
|
|
Hi all,
I have written a stored proc for searching in DB.For this purpose i have used ISNULL where in check whether the parameter is null or not.But when i use logical statement such as or in between sql statements it fails miserably.
for eg:
@skill1 varchar(50)=null,
@skill2 varchar(50)=null
select skill from JOBS
where
skill like '%'+ISNULL(@skill1,skill)+'%' or
skill like '%'+ISNULL(@skill2,skill)+'%'
here if i don`t enter skill2, @skill2 becomes null and hence the other part returns 1 and thus the query returns all data from table .
Pls help me for going about this
Thankx in Adv
|
|
|
|
|
I don't think you want to use isnull. I think what you are looking for:
where @skill is null or skill like '%'+@skill
Hope that helps.
Ben
|
|
|
|
|
Try and use Coalesce
Coalesce(@skill1,skill2,"")
|
|
|
|
|
As written, your query will return every row when either @skill1 or @skill2 is NULL because skill always matches skill throughout all rows.
Try something simpler like this:
((skill LIKE '%' + @skill1 + '%') OR
(skill LIKE '%' + @skill2 + '%'))
Concatination with a NULL value results in a NULL value. LIKE NULL will allways return NULL and therefore not qualify the row for inclusion. The OR will allow the other line to qualify or disqualify the row.
The added parenthesis are there for the OR. I have always had trouble with OR when I let the parser/compiler handle what is being OR'd.
|
|
|
|
|
hi,
i am getting an exception while inserting data into a ms access database in "da.Fill(ds);"
"No value given for one or more required parameters."
my code is like this
DataSet ds = new DataSet();
try
{
openconnection();
cmd = new OleDbCommand(query, con);
OleDbDataAdapter oda = new OleDbDataAdapter(query, con);
//fill the DataTable
oda.Fill(ds);
closeconnection();
return ds.Tables[0];
}
catch (Exception)
{
return ds.Tables[0];
}
finally
{
closeconnection();
}
thanks in advance
j
|
|
|
|
|
Dont you need to specify a table name in the oda.Fill(ds,"SomeTable")?
The Fanatical All Black Supporter - Kea Kaha
|
|
|
|
|
Firstly, you're attempting to fill a dataset with the results of a query, not insert data into a database with this code. Is that what you intended?
Secondly, the cmd object is not used. I don't know if you intended this. Passing the query string to the data adapter's constructor causes it to construct its own command object for its SelectCommand property. You can pass your own command object by using the version of the constructor that takes an OleDbCommand object.
I think the reason for the error is that the query string has one or more ? characters in it, indicating replaceable parameters, and you've not specified the values of those parameters. You'll need to use the command's Parameters collection for this.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi,
I Have two tables with same columns (Defferent reccords)
what query do i have to write in order to get the records from both tables?
Thanks.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
