Problem with two queries

Question

0.00/5 (No votes)

See more:

I have got this query:

C#

SELECT tbAttributes.*, 'MIN' AS Range, (Attribute_position * 10) AS Attribute_Sorting 
FROM tbAttributes 

UNION
 
SELECT tbAttributes.*, 'TAR' AS Range, (Attribute_position * 10 + 1) AS Attribute_Sorting 
FROM tbAttributes 

UNION 

SELECT tbAttributes.*, 'MAX' AS Range, (Attribute_position * 10 + 2) AS Attribute_Sorting 
FROM tbAttributes

I would want to use results of this query in other query.

I did try to send result of this query to DataSet:

DataSet Ds_kw_Attributes_x3 = new DataSet();

Ds_kw_Attributes_x3 = Procs.FillDataSet(
"SELECT tbAttributes.*, 'MIN' AS Range, (Attribute_position * 10) AS Attribute_Sorting FROM tbAttributes 
UNION 
SELECT tbAttributes.*, 'TAR' AS Range, (Attribute_position * 10 + 1) AS Attribute_Sorting FROM tbAttributes 
UNION 
SELECT tbAttributes.*, 'MAX' AS Range, (Attribute_position * 10 + 2) AS Attribute_Sorting FROM tbAttributes");

And then this:

string Attrib_posi = Ds_kw_Attributes_x3.Tables[0].Columns["Attribute_position"].ToString();
string Range = Ds_kw_Attributes_x3.Tables[0].Columns["Range"].ToString();
string Attri_ColName = Ds_kw_Attributes_x3.Tables[0].Columns["Attribute_ColName"].ToString();

string Attrib_Name = Ds_kw_Attributes_x3.Tables[0].Columns["Attribute_Name"].ToString();

And finnaly I did try to use it in second query:

return "SELECT * FROM [SELECT " + Attrib_posi + " * 10 + (CASE UPPER(" + Range + ") WHEN 'TAR' THEN 1 WHEN 'MAX' THEN 2 ELSE 0 END) As Attribute_position, " + Attri_ColName + " As Id, " + Attrib_Name + " As Nazwa, " + Range + " As Zakres, " + AneksSql + " FROM " + Ds_kw_Attributes_x3 + " ]. As Attrib WHERE (NOT IsNull(Attrib.Specyfikacja)) " + AneksWhere;

The problem is I got Huge error, the first I don't know is that good way of all I wrote, please help.

Posted 31-Oct-11 2:44am

carlito_brigante

Updated 7-Nov-11 23:31pm

AditSheth

v5

Add a Solution

Comments

Om Prakash Pant 31-Oct-11 8:59am

why not use a stored procedure & store in temp table or use with statement.

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Zubair Alie · Answer 1 · 2011-10-31T03:13:00

first of all..why you are making two calls to database. what i preffer is, you add first query result in WITH and then pass it to second query just as a table.
this way we would answer it, if you inform about the error you receive.
but i suggest you to use WITH clause for reducing a database call and complexity as well...while writing query..

i.e.

SQL

;WITH testIT
(
 select id,name,fatherName from student
)
select * from studentDetails details std
where  std.FK_studentID = testIT.id

this way you put your sub query inside and make your final query within one call.

nit_singh · Answer 2 · 2011-10-31T03:18:00

This is bad practice to use Query in the code, better you covert them into Sp and then use it

Which row you want to read?

C#

string Attrib_posi = Ds_kw_Attributes_x3.Tables[0].Columns["Attribute_position"].ToString();

in this code, you are accessing Table 0, but not giving any particular row.

It should be something like this

C#

string Attrib_posi = Ds_kw_Attributes_x3.Tables[0].Rows[0].Columns["Attribute_position"].ToString();

carlito_brigante · Answer 3 · 2011-11-07T23:27:00

In C# + Access I got it working well, but I can't use Access querie result in others queries in code.

I will show now how it looksm like working.

So I got Access querie in AccessI got 1x query name: "kw_Attributes_x3"

C#

SELECT tbAttributes.*, "MIN" AS Range, (Attribute_position * 10) AS Attribute_Sorting FROM tbAttributes 
UNION
SELECT tbAttributes.*, "TAR" AS Range, (Attribute_position * 10 + 1) AS Attribute_Sorting FROM tbAttributes
UNION SELECT tbAttributes.*, "MAX" AS Range, (Attribute_position * 10 + 2) AS Attribute_Sorting FROM tbAttributes;

And a function where are queries which are using this Access querie:

C#

private string GetGrdSpecSql()
		{		
			long SpecId;
			long[] AneksId;
			int i = 0;
			AneksId = new long[i + 1];

            SpecId = (Int32)DsSpecHeader.Tables[0].Rows[0]["Spec_ID"];
					
			foreach (DataRow dr in DsAneksHeader.Tables[0].Rows)
			{
				Array.Resize(ref AneksId, i + 1);
                AneksId[i] = (Int32)dr["Aneksy_ID"];
				i++;
			}

			string AneksSql = "(select SpecValue from tbSpec_data S where kw_Attributes_x3.Attribute_ColName = S.Attribute_ColName and UCASE(kw_Attributes_x3.Range) = UCASE(S.Range) and S.Spec_ID = " + SpecId + ") AS Specyfikacja";
			string AneksWhere = "";
			i = 1;
			foreach (long Aneks in AneksId)
			{
                if ((Int32)Procs.RetLng(Aneks) > 0)
				{
					AneksSql = AneksSql + ", (select SpecValue from tbAneksy_data " + Strings.Chr(System.Convert.ToInt32(65 + i)) + " where kw_Attributes_x3.Attribute_ColName = " + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Attribute_ColName and UCASE(kw_Attributes_x3.Range) = UCASE(" + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Range) and " + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Aneksy_ID = " + Aneks + ") AS Aneks" + i;
				}
                if ((Int32)Procs.RetLng(Aneks) > 0)
				{
					AneksWhere = AneksWhere + " or Attrib.Aneks" + i + " > 0 ";
				}
				i++;
			}
			
			return "SELECT * FROM [SELECT kw_Attributes_x3.Attribute_position * 10 + IIf(UCase(kw_Attributes_x3.Range)=\'TAR\',1,IIf(UCase(kw_Attributes_x3.Range)=\'MAX\',2,0)) As Attribute_position, kw_Attributes_x3.Attribute_ColName As Id, kw_Attributes_x3.Attribute_Name As Nazwa, kw_Attributes_x3.Range As Zakres, " + AneksSql + " FROM kw_Attributes_x3 ]. As Attrib WHERE (NOT IsNull(Attrib.Specyfikacja)) " + AneksWhere;			
		       
        }

The poroblem is now I have all in SQL Server. I know there exist something like CTE. Could someone help do it?

carlito_brigante · Answer 4 · 2011-11-08T00:17:00

Solution 8

There's no problem in above code it's working great well. But I have all database in SqlServer now and i can;t use "kw_Attributes_x3" Access query in GetGrdSpecSQL() queries. I wonder how to convert it. I mean AneksSql,AneksWhere and last return query. If anybody could help..

Posted 8-Nov-11 0:17am

carlito_brigante

d2niraj · Answer 5 · 2011-10-31T02:56:00

Solution 1

Please post the error you are getting here. This help in identifying the problem.

Posted 31-Oct-11 2:56am

d2niraj

Problem with two queries

5 solutions

Solution 2

Solution 3

Solution 6

Solution 8

Solution 1

Add your solution here

Preview 0