PAGE HEADER: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

Problem with SqlDataSource using sub-query and date as parameters

I am creating a search page for master detail tables. The search criteria is mainly on the header table. However, there is also one criteria which is in detail table, let said product number.

In my SqlDataSource, I setup the SQL like this.

select fieldA, fieldB, ..., fieldZ from masterTable where (1 = 1)

Then, the additional search criteria is appended to the SqlDataSource select command once the user click the search button. If user wants to search product number, the following will be appended

and exists (select 1 from detailTable where pid = masterTable.id and productNo = @.productNo)

The problem is when I provides both the sub-query criteria and 2 date fields criteria. The page will raise an timeout exception. I don't have any clue on this as I can copy the SQL and run it inside the SQL Server Management Studio. The result come up in a second.

Any suggestion on tackling this problem? Thanks!

hi,

U can avoid this by diffarent options...By above information I can explain like this

1 use UNION

2 Use Primary Key in your every Subquery Query followed by the Search.

3 Use Joins with valid Key Elements.etc

or send the required result columnes and the Table Design

bye

murthy

|||

Hi Murthy,

1. use UNION

I don't know how should I use UNION in master-detail structure. Please give more detail.

2. The primary key is already used in the where clause of the subquery. The column pid means the primary key ID in master table.

3. Use Join

The use of join is not desirable. I have to group the records back together afterward. I just want to search the master table but use detail record as criteria. If I join them without group, the master record will repeat themselves in the result.

Actually, I'm strange about the performance difference by using ADO.NET and SQL Server management studio.

|||

Hi,

Do this Use "#' table with one primary key ID as the Column column in all the condictions

and finally join all the Table with the key Elements..

Ex:

select <Key>,<Search column 1 > into #TableA from <Master table> where <Condition>

select <Key>,<Search column 2 > into #TableB from <Master table> where <Condition>

.....

....

and Finally

select <Search Column1>,<Search Column2>.<Search Column3>,... from #TableA, #TableB, #TableC...where #TableA.Key=#TableB.Key,#TableA.Key=#TableC.key etc

drop all temp table

your result is ready now...

|||

This approach seems making the simple request into a complex one.

problem with SQLConnection

I am working on a set of webforms that insert user data into a set of db tables.

I set up a test of an approach using northwind and I'm having trouble getting the insert to work. When I open the form, input the name and phone, and submit there is no error, but no record inserted into the Shippers table.

You can see one of my approaches in the ASPX code. I don't like having to do the select in order to do the insert -- so that's commented off.

I'm stuck. Thoughts about what I'm missing appreciated...

Ray

ASPX code.

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Default66a.aspx.cs"Inherits="pages_audit_Default66a" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headid="Head1"runat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

CompanyName:

<asp:textboxid="txtCompanyName"runat="server"/>

Phone:

<asp:textboxid="txtPhone"runat="server"/>

<asp:buttonid="btnSubmit"runat="server"text="Submit"onclick="btnSubmit_Click"/>

<asp:LabelID="awesomelbl"runat="server"Text="Label"></asp:Label>

<!--

<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"

insertcommand="INSERT INTO Shippers(CompanyName, Phone) VALUES (@.CompanyName, @.Phone)" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Shippers]">

<asp:controlparameter controlid="txtCompanyName" name="CompanyName" />

<asp:controlparameter controlid="txtPhone" name="Phone" />

</insertparameters>

</asp:sqldatasource>

-->

</form>

</body>

</html>

c Sharp code

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.Sql;

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;

publicpartialclasspages_audit_Default66a : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

}

protectedvoid btnSubmit_Click(object sender,EventArgs e)

{

SqlConnection con =newSqlConnection("Data Source=Chilibowl;Trusted_Connection=yes;DataBase=Northwind");

SqlCommand cmd =newSqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@.CompanyName, @.Phone)");

SqlParameter cnameparam =newSqlParameter("@.CompanyName", txtCompanyName.Text);SqlParameter phnparam =newSqlParameter("@.Phone", txtPhone.Text);

cmd.Parameters.Add(cnameparam);

cmd.Parameters.Add(phnparam);

try

{

con.Open();

if (cmd.ExecuteNonQuery() > 0)awesomelbl.Text ="successful insert";

}

catch

{

//handel

}

finally

{

con.Close();

}

rkobs:
SqlCommand cmd =newSqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@.CompanyName, @.Phone)");

Try this:

SqlCommand cmd =newSqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@.CompanyName, @.Phone)",con);

|||

Worked. Thanks.

Ray

sql

Monday, March 26, 2012

problem with sql query.

Hi ya,

I have two tables. One is the temporary table called tbl_temp and the other is called tbl_empDetails.
I want to run a query that would bring me the records of tbl_temp where there is no corresponding ID present in tbl_empDetails, meaning it shud bring only records where a corresponding records is not available in tbl_empDetails. I tried it but this query doesn't work, it is bringing me the records which are there in tbl_empdetails. How can i do it?

SELECTDISTINCT tbl_temp.SID, tbl_temp.fname, tbl_temp.lname, tbl_temp.mail, tbl_temp.tel, tbl_temp.mobile, tbl_temp.office

FROM tbl_temp, tbl_empDetails where tbl_empDetails.adSID != tbl_temp.SID

ORDERBY tbl_temp.lname

Try this

SELECTDISTINCT *

FROMtbl_empDetails

where not tbl_empDetails.adSID in (select SID from tbl_temp)

|||

thank you very very much, you saved me lot of work.

Can you care to explain what you have done with the query and what I was doing wrong?

Just in case if i got any similar problem again.

Cheers

|||

Table 1

KeyValue

Table 2

KeyValue

If you use "where tbl_empDetails.adSID = tbl_temp.SID" you will get 2 record. (1,1) (2,2)

if you use "where tbl_empDetails.adSID != tbl_temp.SID" you will get 4 records. (1,2) (2,1) (3,1) (3,2)

So. I use sub-query to solve it !

|||

SELECTDISTINCT a.*

FROMtbl_temp a

LEFT JOIN tbl_empDetails b

ON a.SID = b.adSID

Where b.asSID IS NULL

This will run much faster than a sub-query if your tables are large...

Brad

Monday, March 12, 2012

Problem with rollback statement

Hi,

I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code.

Does anyone see my error?

Thanks,

poc1010

Create proc AddProducts

@.dcint=null,
@.pcint=null,
@.imagepathvarchar(50)=null,
@.typevarchar(2)=null,
@.descriptionvarchar(1000)=null,
@.gendervarchar(8)=null,
@.productidint=null,
@.pccodevarchar(2)=null,
@.weightvarchar(80)=null,
@.pricemoney=null,
@.activevarchar(1)=null

declare @.errorsave int
set @.errorsave=0
declare @.dg int

Begin transaction

insert productdescription(
designercategory,
productcategory,
imagepath,
type,
[description],
gender)
values(@.dc,
@.pc,
@.imagepath,
@.type,
@.description,
@.gender)

if @.@.error <> 0
set @.errorsave=@.@.error

set @.dg = @.@.identity

begin
insert Products(
productid,
designergroup,
designercategory,
productcategory,
pccode,
weight,
price,
active)
values(@.productid,
@.dg,
@.dc,
@.pc,
@.pccode,
@.weight,
@.price,
@.active)

if @.@.error <> 0
set @.errorsave=@.@.error
end

if @.errorsave <> 0
begin
print 'Insert into Products tables failed'
rollback transaction
return -5--Insert into Products tables failed
end

commit transaction
print 'Success'
return 0 --SuccessYou have begins and ends in useless spots. Whats the acual error message?|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||None of you guys used ELSE. Your BEGIN/END's are a little whacked out. Honestly, I'm against returning in mid procedure if it's not necessary. You can easily follow through the entire procedure using an ELSE, then returning a specified value.|||Pierre,

Thanks for your example. I saw what I was doing wrong. Works great.

Thank you for your help.

poc1010|||That's just personal taste Lee. No real argument either way. Not in this case.|||You're right. That's why I said that I prefer the other way. Didn't say you were wrong, because it works fine.

Wednesday, March 7, 2012

Problem with reference dimension & snowflaked tables

OK, this is a bit complicated, so I hope I can explain it clearly. I'll try not to get bogged down in the details of the problem domain, but bear with me.

We have a cube with a regular dimension and a reference dimension. The reference dimension is connected through the fact table through the regular dimension (there are many other dimensions as well, but I'll simplify the situation here). Both the regular dimension and the reference dimension have hierarchies that that include as their top level a level called "System". For both of the dimensions, the "System" level is snowflaked in from the same table. So the regular dimension table contains a column called System_key, and a corresponding attribute. Similarly, the reference dimension table also contains a column called System_key, and a corresponding attribute. Both keys point in to the same table. Naturally, the regular dimension table also has a column that points to the reference dimension table, as you would expect with a reference dimension.

OK, I hope that's clear (if a little pathological). Essentially, the "System" attribute is present on both dimensions, and both dimensions get that value by snowflaking in to the same table.

So the problem comes when looking at measures broken down by System. The regular dimension has a "System" user hierarchy which contains a single level, the System level. When I drag that hierarchy on to a pivot chart, I get the wrong results.

From what I can tell, when processing the dimension it is populating the system attribute for the regular dimension based on the key value stored in the reference dimension table, not the regular dimension table. I have no idea why it would do that. But I look at the SQL statement generated during processing of the key attribute for the regular dimension, and it includes in the where statement a join between the reference dimension System_key column and the System table, rather than the regular dimension System_key column as I would expect. The result is totally wrong values.

Does this make any kind of sense? Any idea what could be causing this, and what might fix it (note that totally redoing all the incestuous table relationships at this point is probably not an option).What if, in the Data Source View, you create another instance of the "System" attribute table, using a simple Named Query like: "select * from System"? Then you snowflake one instance with the regular dimension table, and the other instance with the reference dimension table. My guess (maybe someone can confirm this?) is that, unless you explicitly use role-playing dimensions (like Date in Adventure Works), a single snowflaked table in the DSV is interpreted as a single instance, even if it's incorporated into the schema of multiple dimensions. And in your case, I'm assuming that breaking the "System" attribute out into its own role-playing dimension is not an option?|||Deekpak,
Thanks for the suggestion. That does seem to solve the problem.

However, it does seem like a bit of a hack. It would be nice to know if this is a bug in SSAS that may be fixed in the future, or if there's something fundamental that I don't understand here. We snowflake in this table in multiple other dimensions as well (though none others with reference dimensions currently), so it would be good to know if we're setting ourselves for more trouble later.

Any 'Softies out there care to comment?

Saturday, February 25, 2012

Problem with query