14 Replies - 7743 Views - Last Post: 16 November 2011 - 01:02 PM

#1 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 377
  • Joined: 22-September 10

Convert from Access to SqlServ

Posted 02 November 2011 - 10:00 AM

Hello all!

I got access querie which name is: "kw_Attributes_x3"
It looks like:
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;



the function in VB where I use this access querie is looks like:
Private Function GetGrdSpecSql() As String

        Dim SpecId As Long
        Dim AneksId() As Long
        Dim i As Integer = 0
        ReDim AneksId(i)

        SpecId = DsSpecHeader.Tables(0).Rows(0)("Spec_ID")

        Dim dr As DataRow
        For Each dr In DsAneksHeader.Tables(0).Rows
            ReDim Preserve AneksId(i)
            AneksId(i) = dr("Aneksy_ID")
            i = i + 1
        Next

        Dim Aneks As Long
Dim AneksSql As String = "(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"
        Dim AneksWhere As String = ""
        i = 1
        For Each Aneks In AneksId
If RetLng(Aneks) > 0 Then AneksSql = AneksSql & ", (select SpecValue from tbAneksy_data " & Chr(65 + i) & " where kw_Attributes_x3.Attribute_ColName = " & Chr(65 + i) & ".Attribute_ColName and UCASE(kw_Attributes_x3.Range) = UCASE(" & Chr(65 + i) & ".Range) and " & Chr(65 + i) & ".Aneksy_ID = " & Aneks & ") AS Aneks" & i
            If RetLng(Aneks) > 0 Then AneksWhere = AneksWhere & " or Attrib.Aneks" & i & " > 0 "
            i = i + 1
        Next
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

    End Function



The problem is i have to convert it all to C# and SQL Server. I think I can use CTE but if someone Could help me with do it will be really good.

I start to do CTE of return query, but it's only start:
;with cte as (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 need to modify all of queries from function and the retun query.

This post has been edited by nighttrain: 02 November 2011 - 10:04 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Convert from Access to SqlServ

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Convert from Access to SqlServ

Posted 02 November 2011 - 11:19 AM

Can't really tell what you're up to here, I'm afraid. Looks like time for a new design...

However, I simplify that wonky query:
SELECT a.*, b.Range, (a.Attribute_position * 10 + b.Pos) AS Attribute_Sorting
	FROM tbAttributes a
		CROSS JOIN (
			SELECT 'MIN' AS Range, 1 as Pos
			UNION SELECT 'MAX' AS Range, 2 as Pos
		) b



Also, there is no IIf in MS SQL.
Was This Post Helpful? 0
  • +
  • -

#3 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 377
  • Joined: 22-September 10

Re: Convert from Access to SqlServ

Posted 03 November 2011 - 12:47 AM

Hello Baavgai,

like always You are helping a lot of me. A thing is I need to modify sql in that function. Like u see in the return query is using this AneksSql and AneksWhere. I think we can prepare this variables (sql's) and after return query Where's the AneksSql and AneksWhere are included (concat). All of it are using query which is in Access ("kw_Attributes_x3"). So that's why think about temprary sql query CTE. Will be great if You could help me to correct this all.

Your SQl give a diffrent (not good)result I do some
;with cte as (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)

SELECT Attribute_Position * 10 + 
(CASE Range When  'TAR' 
                                 THEN 1
                     WHEN 'MAX'
                                  THEN 2
                      ELSE 0 END) AS Attribute_Position,
Attribute_ColName as ID, Attribute_Name as Nazwa,
Range as Zakres
FROM cte


But still I have got to modify AneksSql, AneksWhere and then concat it to this query. Please help

This post has been edited by nighttrain: 03 November 2011 - 02:14 AM

Was This Post Helpful? 0
  • +
  • -

#4 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 377
  • Joined: 22-September 10

Re: Convert from Access to SqlServ

Posted 06 November 2011 - 11:48 AM

It's the function which is in C# - but it's use Access querie like i write in first post:
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 convert from VB to C# the function where's a few queries which uses this Access querie:
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;			
		       
        }


But for now I still have to change all of queries in function cause I can;t use Access querie for now So I think we need CTE to include in queries in function.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Convert from Access to SqlServ

Posted 08 November 2011 - 02:07 PM

To be perfectly frank, it's almost implossible to see what you're after here:
private string GetGrdSpecSql() {
	// magic globals
	// DsSpecHeader, presumed DataSet, presumed comtainting one table
	// DsAneksHeader, presumed DataSet, presumed comtainting one table
	// Procs impossible to extrapolate
	
	int i = 0;
	long[] AneksId; = new long[i + 1]; // so, your array is length of 1
	long SpecId = (Int32)DsSpecHeader.Tables[0].Rows[0]["Spec_ID"];
					
	foreach (DataRow dr in DsAneksHeader.Tables[0].Rows) {
		// this is bad
		// why not just allocate the thing based on DsAneksHeader.Tables[0].Rows.Count?
		Array.Resize(ref AneksId, i + 1);
		AneksId[i++] = (Int32)dr["Aneksy_ID"];
	}

	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; // why?
	// now it gets ugly
	foreach (long Aneks in AneksId) {
		if ((Int32)Procs.RetLng(Aneks) > 0) {
			string alias1 = Strings.Chr(System.Convert.ToInt32(65 + i)) ;
			string alias2 = "Aneks" + i;
			AneksSql += ", ("
				+ " select SpecValue"
				+ " from tbAneksy_data " + alias1 
				+ " where kw_Attributes_x3.Attribute_ColName = " + alias1 + ".Attribute_ColName"
				+ " and UCASE(kw_Attributes_x3.Range) = UCASE(" + alias1 + ".Range)"
				+ " and " + alias1 + ".Aneksy_ID = " + Aneks 
				+ ") AS " + alias2;
			AneksWhere += " or Attrib." + alias2 + " > 0 ";
		}
		i++;
	}

	return "SELECT * FROM "
		+ "[" // what follows is an absurdly long comment name
		+ "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 ]." // end comment name, with period
		+ " As Attrib WHERE (NOT IsNull(Attrib.Specyfikacja)) " + AneksWhere;
}



If you have SQL that works, anywhere, someone might be able to help. Some database designs are too broken to fix and you have to start over. This looks like one of those.

If you gave example tables, fields, data, desired result set, and your logic for getting there, maybe. At the moment, I am at a loss.
Was This Post Helpful? 0
  • +
  • -

#6 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 377
  • Joined: 22-September 10

Re: Convert from Access to SqlServ

Posted 08 November 2011 - 03:10 PM

You right that's impossible to imagine what it have to be. I will prepare some working code that You could compile it and I include needed access file with tables and U will see how it works. I do it tommorow. Thanks BaV for all help. I send a message to You when I finish.

This post has been edited by nighttrain: 08 November 2011 - 03:39 PM

Was This Post Helpful? 0
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Convert from Access to SqlServ

Posted 10 November 2011 - 11:36 AM

Ok, you sent me some info. It's not quite fair to do this behind closed door, so here it is.

First, I needed a sample query. I took your query builder code and fed it some numbers:
private static string GetGrdSpecSql(int specId, long[] aneksId) {
	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 = "";

	int i = 1;
	foreach (long Aneks in aneksId) {
		if (Aneks > 0) {
			string alias1 = "" + Convert.ToChar(65 + i);
			string alias2 = "Aneks" + i;
			AneksSql += ", ("
				+ " select SpecValue"
				+ " from tbAneksy_data " + alias1
				+ " where kw_Attributes_x3.Attribute_ColName = " + alias1 + ".Attribute_ColName"
				+ " and UCASE(kw_Attributes_x3.Range) = UCASE(" + alias1 + ".Range)"
				+ " and " + alias1 + ".Aneksy_ID = " + Aneks
				+ ") AS " + alias2;
			AneksWhere += " or Attrib." + alias2 + " > 0 ";
		}
		i++;
	}

	return "SELECT * FROM "
		+ "[" // what follows is an absurdly long comment name
		+ "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 ]." // end comment name, with period
		+ " As Attrib WHERE (NOT IsNull(Attrib.Specyfikacja)) " + AneksWhere;
}

static void Main(string[] args) {
	Debug.WriteLine(GetGrdSpecSql(1, new long[] {2,3}));
}



This got me a query that looked like:
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,
				(
				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 = 1
				) AS Specyfikacja, 
				( 
				select SpecValue 
					from tbAneksy_data B 
					where kw_Attributes_x3.Attribute_ColName = B.Attribute_ColName 
						and UCASE(kw_Attributes_x3.Range) = UCASE(B.Range) 
						and B.Aneksy_ID = 2) AS Aneks1, 
				(
				select SpecValue 
					from tbAneksy_data C 
					where kw_Attributes_x3.Attribute_ColName = C.Attribute_ColName 
						and UCASE(kw_Attributes_x3.Range) = UCASE(C.Range)
						and C.Aneksy_ID = 3
				) AS Aneks2 
			FROM kw_Attributes_x3 
		)  AS Attrib
	WHERE [Attrib].[Specyfikacja] IS NOT NULL
		OR Attrib.Aneks1>0
		OR Attrib.Aneks2>0



Ok, it didn't look exactly like that; I'd already cleaned it up some.

I'd previously stated to use something like:
select 'MIN' AS Range, 0 as Pos
union
select 'TAR' AS Range, 1 as Pos



Which you categorically stated didn't work. It works just fine, just not in MS Access. Many things don't work in Access. It's a toy and you should not be working in it. If you're moving to MS SQL, migrate the database and don't look back.

First full cleanup on MS SQL, for my test data, looks like:
SELECT kw_Attr.Attribute_Sorting As Attribute_position, 
		kw_Attr.Attribute_ColName As Id,
		kw_Attr.Attribute_Name As Nazwa,
		kw_Attr.[Range] As Zakres,
		S.SpecValue AS Specyfikacja,
		B.SpecValue AS Aneks1, 
		C.SpecValue AS Aneks2 
	FROM (
		SELECT b.[Range], 
				(a.Attribute_position * 10 + b.Pos) AS Attribute_Sorting,
				a.Attribute_ColName, a.Attribute_Name
			FROM tbAttributes a
				CROSS JOIN ( 
					select 'MIN' AS [Range], 0 as Pos union select 'TAR', 1 union select 'MAX', 2
				) b
	) kw_Attr
		LEFT OUTER JOIN tbSpec_data S 
			on kw_Attr.Attribute_ColName = S.Attribute_ColName 
				and kw_Attr.[Range] = upper(S.[Range]) 
				and S.Spec_ID = 1
		LEFT OUTER JOIN tbAneksy_data B
			on kw_Attr.Attribute_ColName = B.Attribute_ColName 
				and kw_Attr.[Range] = upper(B.[Range]) 
				and B.Aneksy_ID = 2
		LEFT OUTER JOIN tbAneksy_data C
			on kw_Attr.Attribute_ColName = C.Attribute_ColName 
				and kw_Attr.[Range] = upper(C.[Range]) 
				and C.Aneksy_ID = 3
	WHERE S.SpecValue IS NOT NULL
		OR B.SpecValue IS NOT NULL
		OR C.SpecValue IS NOT NULL




Next step, get rid of an extra table:
SELECT kw_Attr.Attribute_Sorting As Attribute_position, 
		kw_Attr.Attribute_ColName As Id,
		kw_Attr.Attribute_Name As Nazwa,
		kw_Attr.[Range] As Zakres,
		S.SpecValue AS Specyfikacja,
		A.Aneks1, 
		A.Aneks2 
	FROM (
		SELECT b.[Range], 
				(a.Attribute_position * 10 + b.Pos) AS Attribute_Sorting,
				a.Attribute_ColName, a.Attribute_Name
			FROM tbAttributes a
				CROSS JOIN ( 
					select 'MIN' AS [Range], 0 as Pos union select 'TAR', 1 union select 'MAX', 2
				) b
	) kw_Attr
		LEFT OUTER JOIN tbSpec_data S 
			on kw_Attr.Attribute_ColName = S.Attribute_ColName 
				and kw_Attr.[Range] = upper(S.[Range]) 
				and S.Spec_ID = 1
		LEFT OUTER JOIN (
			select Attribute_ColName, upper([Range]) as [Range], 
					min(case when Aneksy_ID=2 then SpecValue else null end) as Aneks1, 
					min(case when Aneksy_ID=3 then SpecValue else null end) as Aneks2
				from tbAneksy_data
				group by Attribute_ColName, upper([Range])
		) A
			on kw_Attr.Attribute_ColName = A.Attribute_ColName 
				and kw_Attr.[Range] = A.[Range]
	WHERE S.SpecValue IS NOT NULL
		OR A.Aneks1 IS NOT NULL
		OR A.Aneks2 IS NOT NULL



Actually, we could just include all those attribute columns, maybe in a view? There are only three and it would keep the names consistent.

create view VwAneksyDataRange
as
select Attribute_ColName, upper([Range]) as [Range], 
		min(case when Aneksy_ID=1 then SpecValue else null end) as Aneks1, 
		min(case when Aneksy_ID=2 then SpecValue else null end) as Aneks2, 
		min(case when Aneksy_ID=3 then SpecValue else null end) as Aneks3
	from tbAneksy_data
	group by Attribute_ColName, upper([Range])
go

create view VwAttributeRange
as
SELECT a.Attribute_ColName, a.Attribute_Name, a.Attribute_position, b.[Range], 
		(a.Attribute_position * 10 + b.Pos) AS Attribute_Sorting
	FROM tbAttributes a
		CROSS JOIN ( 
			select 'MIN' AS [Range], 0 as Pos union select 'TAR', 1 union select 'MAX', 2
		) b



Now, the view for everything:
create view VwSpec
as
SELECT kw_Attr.Attribute_Sorting As Attribute_position, 
		kw_Attr.Attribute_ColName As Id,
		kw_Attr.Attribute_Name As Nazwa,
		kw_Attr.[Range] As Zakres,
		S.Spec_ID,
		S.SpecValue AS Specyfikacja,
		A.Aneks1, A.Aneks2, A.Aneks3
	FROM VwAttributeRange kw_Attr
		LEFT OUTER JOIN tbSpec_data S 
			on kw_Attr.Attribute_ColName = S.Attribute_ColName 
				and kw_Attr.[Range] = upper(S.[Range]) 
		LEFT OUTER JOIN VwAneksyDataRange A
			on kw_Attr.Attribute_ColName = A.Attribute_ColName 
				and kw_Attr.[Range] = A.[Range]



Back to the original C# call:
private static string GetGrdSpecSql(int specId, long[] aneksId) {
	string AneksSql = "select Spec_ID, Attribute_position, Id, Nazwa, Zakres, Specyfikacja"
	foreach (long Aneks in aneksId) { AneksSql += ",Aneks" + Aneks; }
	AneksSql += " from VwSpec"
		+ " where IsNull(Spec_ID," + specId + ")=" + specId
		+ " and ( Specyfikacja is not null";
	foreach (long Aneks in aneksId) { AneksSql += " or Aneks" + Aneks + "is not null"; }
	return AneksSql + ")";
}



So, GetGrdSpecSql(1, new long[] {2,3}) should yield something like:
select Spec_ID, Attribute_position, Id, Nazwa, Zakres, Specyfikacja, Aneks2, Aneks3
	from VwSpec				
	where IsNull(Spec_ID,1)=1
		and (
			Specyfikacja is not null
			or Aneks2 is not null
			or Aneks3 is not null
			)



I really hope that helps, because I'm not going through that again. Good luck.
Was This Post Helpful? 0
  • +
  • -

#8 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 377
  • Joined: 22-September 10

Re: Convert from Access to SqlServ

Posted 10 November 2011 - 03:54 PM

ok, but what if specification can have more Anekses so in datagrid can be for example Aneks1, Aneks2,Aneks3,Aneks4 columns with values etc..? It will be works?

P.S By the way the sql server views are something like Access queries, so I could just convert to correct syntaxes and some functions the old access aueries to sql server view and use it in code i am right?

This post has been edited by nighttrain: 10 November 2011 - 04:10 PM

Was This Post Helpful? 0
  • +
  • -

#9 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Convert from Access to SqlServ

Posted 10 November 2011 - 06:32 PM

View Postnighttrain, on 10 November 2011 - 06:54 PM, said:

ok, but what if specification can have more Anekses so in datagrid can be for example Aneks1, Aneks2,Aneks3,Aneks4 columns with values etc..? It will be works?


Nope, you'd have to change the view. In which case, you're better going with the prior example that doesn't use views.

Look, the design you have of flattening out a list of attributes horizontally to wedge them into ad hoc queries is pretty awful. It is not the way to design a database and the problems you are encountering, and will continue to encounter, are a direct result of that design choice.

If you insist on such a design, I'd recommend reading up on EAV databases. They offer flexibility, to the detriment of everything else. They are the worst possible design to query against; much like yours.


View Postnighttrain, on 10 November 2011 - 06:54 PM, said:

By the way the sql server views are something like Access queries


Correct. Pretty much every RDBMS calls SQL statements stored in the database like that a view. Access, calls same object a query.

You would also do well to look at stored procedures.
Was This Post Helpful? 0
  • +
  • -

#10 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 377
  • Joined: 22-September 10

Re: Convert from Access to SqlServ

Posted 11 November 2011 - 02:35 PM

Hmm, so could I just make a view in SQL Server name "kw_Attributes_x3":

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 correct all syntaxes in function GetGrdSpecSql? Is that possible? It will be not so hard so Yours to understand then..
Was This Post Helpful? 0
  • +
  • -

#11 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Convert from Access to SqlServ

Posted 11 November 2011 - 03:56 PM

Sure. That would be the one I offered before, VwAttributeRange, with a *:
create view VwAttributeRange
as
SELECT a.*, b.[Range], (a.Attribute_position * 10 + b.Pos) AS Attribute_Sorting
	FROM tbAttributes a
		CROSS JOIN ( 
			select 'MIN' AS [Range], 0 as Pos union select 'TAR', 1 union select 'MAX', 2
		) b


Was This Post Helpful? 0
  • +
  • -

#12 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 377
  • Joined: 22-September 10

Re: Convert from Access to SqlServ

Posted 14 November 2011 - 01:24 AM

Ok, so I make a View I call it the same as was in Access query: "kw_Attributes_x3":
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;



It's simple diffrent of Your cause in Yours there sorting first all MAX, then all MIN and then all TAR. In above view I got:
..
MAX
MIN
TAR
MAX
MIN
TAR
..


Of course attribute got all of this three.
Yours first:
MAX
MAX
...
MIN
MIN
MIN
..
TAR
...



Ok so Now we got this code to correct syntaxes in all queries, Could You help me with that, it's so hard. I already switch from UCase to UPPER in all function.
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 UPPER(kw_Attributes_x3.Range) = UPPER(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 UPPER(kw_Attributes_x3.Range) = UPPER(" + 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(UPPER(kw_Attributes_x3.Range)=\'TAR\',1,IIf(UPPER(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;

        }



best regards and thanks for since help to now.
Was This Post Helpful? 0
  • +
  • -

#13 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Convert from Access to SqlServ

Posted 16 November 2011 - 12:00 PM

I've rewritten this twice already. Maybe more.

Take your code, dump out the SQL, and run it against the database. Where does it fail? I'm sorry, I've given you enough information to reasonably do the work. More than normal, honestly. I've offered several solutions over the course of this thread, most of which have been ignored. I can offer no more than that. I'm not going to do it for you.
Was This Post Helpful? 0
  • +
  • -

#14 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 377
  • Joined: 22-September 10

Re: Convert from Access to SqlServ

Posted 16 November 2011 - 12:15 PM

I know but just told me that now if i got this view maked, so now in my function I have to only correct the AneksSql, AneksWhere and return queries in string will be enough? I am try to do this but I got mega errors ;/

P.S I know u did all for me, but If You could help last time It will be great, or just write what I got to correct which syntaxes I will try and mayby I will finish it. Will be great.

For now when I choose a product I got this error:
Posted Image

This post has been edited by nighttrain: 16 November 2011 - 12:53 PM

Was This Post Helpful? 0
  • +
  • -

#15 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Convert from Access to SqlServ

Posted 16 November 2011 - 01:02 PM

You need to take the SQL generated an run it in the database.

The syntax " = [.A" is clearly invalid. What did you want to be there? This is called debugging. It is the task of a programmer. It's nothing others do for you.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1