Database

create table #tmp (ColA varchar(2),ColB varchar(100)) insert into #tmp select '1', '12.324.145' union all select '2', '425.152.643' union all select '3', '12' select a.ColA,t.c.value('.','varchar(100)') as col2 from (select cola,cast(''+replace(colB,'.','')+'' as xml) as c2 from #tmp) a cross apply c2.nodes('/t') t(c)

Did you have a question?

the error is found at dt = ds.Tables("subject1")

Oh wow, didn't hear about that. Thanks for sharing!

Please read http://www.codeproject.com/Messages/1278600/How-to-get-an-answer-to-your-question.aspx[^], and see https://www.google.com/search?q=Service+Broker+in+SQL+SERVER[^].

By altering the table when a new unit is added

The BETWEEN condition needs two single values; the lower and the upper bound. For example: WHERE NumColumn BETWEEN 5 AND 10 In that example you can't replace neither 5 or 10 with table variables since then there would be more than one value in the comparison. You can think the BETWEEN condition as two separate conditions, like: WHERE NumColumn >= 5 AND NumColumn <= 10 I hope this clarifies your question.

I would use the execution plan to identify bottlenecks in the existing indexes and any missing indexes for the query, I would then defrag those indexes Never underestimate the power of human stupidity RAH

I had to add few extra condition suitable to my table, but it DID WORK !! :) I have learnt about STUFF and FOR XML PATH. Thanks a ton for all your time Richard.

Another option is to maintian a heirarchical structure /rootid/child1/child2/, this is what MS heirarchiy structure does, I use a varchar instead of the binary data type but the concept is excellent. You can always trace the changes and also find the root and leaf nodes. Never underestimate the power of human stupidity RAH

You could start by studying http://www.codeproject.com/Messages/1278600/How-to-get-an-answer-to-your-question.aspx[^] again, and posting a proper detailed question.

public void GetDatainArr() { string strSQl = ""; string[] Arr = new string[50]; strSQl = "select [Id] from [tblname] "; DataTable dt = GetDataTable(strSQl); //GetDataTable function if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { Arr[i] = Convert.ToString(dt.Rows[i][0]); } } } public DataTable GetDataTable(string Sql) { SqlConnection conn = new SqlConnection("ConnectionString "); conn.Open(); DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand(Sql, conn); SqlDataAdapter ad = new SqlDataAdapter(); ad.Fill(dt); conn.Close(); return dt; } This code in C# so you can Convert this code in vb using this link http://www.developerfusion.com/tools/convert/csharp-to-vb/ Sanket Gandhi

Thanks buddies I got it. Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA

What David has described is a many to many relationship, an allowance can be used by many employees and an employee can have many allowances. This is the correct data structure for the requirements. Never underestimate the power of human stupidity RAH

finally the ranking is on ascending order and need it to be in such a way that the highest marks/value takes position 1: tried this: create view View_Positioning as SELECT t1.`SAdmNo`, t1.`YearAdmitted`,t1.`TermAdmitted`,t1.`CLASSAdmitted` ,t1.`StreamAdmitted`,t1.`OutOfMarks`,t1.`ENGLISH`,t1.`KISWAHILI`,t1.`MATHEMATICS`,t1.`SCIENCE`,t1.`SSR`,t1.`Average`,(SELECT COUNT(*) FROM total_termaverage_view WHERE `Average` IS NOT NULL AND`Average` < t1.`Average`) +1 AS Position FROM total_termaverage_view t1 order by Average ASC ; which gives: Average position 324 1 345 2

Have a look at our code project article Overview of SQL Server database Triggers[^]