Returning A Value That Has The Smallest Number Of Instances In A Table
Nov 5, 2007
Hi
Please help! I'm really stuck. I have a table (AssessorAllocations) that stores the data of students who have submitted assessments for marking by an Assessor. Each time an assessment is submitted by a student my program must determine which Assessor has the smallest workload and then assign the current assessment to that Assessor. My problem is how do I determine which Assessor has the smallest number of assessments assigned to him/her.
The table columns are:
AssessorRegistrationNo
LearnerRegistrationNo
QualficationName
SubjectName
AssessmentName
DateAssigned
Marked
DateMarked
Price
So I'm looking for something like this:
Select AssessorRegistrationNo from AssessorAllocations where {Select the Assessor who has the smallest number of AssessmentNames assigned to him/her where Marked='0' and DateAssigned<>''}
Thanks
Neil
View 4 Replies
ADVERTISEMENT
Jun 15, 2005
Hi,
I have a sql query which gives me the result set with lots of columns and rows.
a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395
i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?
Thanks
View 1 Replies
View Related
Dec 30, 2014
i have table in sql and every month i am appending new data in this table, but i want to sort data Culumn Name "account_no' from smallest to largest, and whenever i append new data to this table it auto sort every time,
View 1 Replies
View Related
Nov 5, 2007
SQL Server 2005 SP2Are there any guidelines/rules of thumb as to the max numberof SQL Server instances on a single 64-bit server?thanksaj
View 1 Replies
View Related
Mar 12, 2001
Hi,
I am reading SQL Server 2000 Black book (Coriolis publisher) and Microsoft SQL Server 2000 Optimization guide (Jenney Lynne Fields PH publisher).
In the Black book page 56, it says :"..up to a total of 16 instances.
In the optimization guide page 144, it says :"There is no theoretical limit to the number of instances of SQL server you can run simultaneously..."
Who the hell is right??????
Alain Gagne
Lead DBA
gagnea@msagroup.com
View 1 Replies
View Related
Jan 21, 2005
I am developing an application for a client who already has a liscensed SQL server running. They have a software package that requires an instance using "Windows Only" for the authentication mode. I need an instance to use "SQL Server and Windows" for my application to be able to speak with the server from other computers on their network (or at least I believe I do....).
My question is, "is there any restrictions on the 'number' of instances an institution can run on a computer that is liscensed to run the software"?
Alternately, if there is a limit, I need to figure out if it is possible to set up the application (VB6, btw) to connect to the server from outside the host computer if it is using "Windows Only" Authentication. The application will be installed on several computers in the client's network and I'm not familiar with how to dynamicly poll the 'current Windows Login' information.
It would be much simpler for the application to create a new instance with the desired authentication method, but is there a security issue in doing this? Would the data be any more at risk to outside corruption using the SQL server authentication mode?
View 1 Replies
View Related
Oct 24, 2006
how can i get a row number in a query in ms/sql
View 7 Replies
View Related
Aug 8, 2007
Hi all,Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.Given the client table below my query would return 4 because its the lowest number availeble. Thanks. client tableclnum12367
View 4 Replies
View Related
Jul 8, 2004
I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.
Thanks.
View 2 Replies
View Related
Jan 11, 2004
I'm using a VB6 application to access data from an SQL Server 2000 database. There are a few things I would like to do:
1. Get the number of tables in the database
2. Automatically begin reading data from each table in the database. Is it possible to write some code that will cause the application to start on first table retrieve all the data, then move to next table (similar to moving to next row)?
Thanks,
Joey
View 6 Replies
View Related
Jan 24, 2008
I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records.
sub findcustomers(sender as object,e as eventargs) dim connection1 as sqlconnection=new sqlconnection(...) dim q1 as string="select * from tblcustomers where store='65'" dim command1 as sqlcommand=new sqlcommand(q1,connection1) dim a as integer command1.connection.open() a=command1.executenonquery() label1.text=a.tostring() command1.connection.close() end sub
What am I doing wrong?
View 8 Replies
View Related
Jan 24, 2005
Hi ,,
How to write the Sql Query to return the next generated Identity from the Sql server database.
View 1 Replies
View Related
Jul 7, 2004
Hi
I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.
This is my sproc
create procedure return_data_and_value
@return int output
as
set nocount on
...
...
select * from #Table
select @return = count(*) from #Table
drop table #Table
go
This is asp.net code
Dim nRecords as Int32
Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)
conn.Open()
dr = cmd.ExecuteReader
nRecords = convert.int32(cmd.parameters(@return).value)
conn.close
Thanks
Lbob
View 1 Replies
View Related
May 20, 2007
I have met up a problem. How to i get the second smallest value instead of the smallest value in a table using min(). Any other function rather than min()?
View 11 Replies
View Related
Sep 28, 2007
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
View 4 Replies
View Related
Dec 20, 2007
Hey
Its easy to get the MAX or MIN from a table,
but what if you want to get the 2nd largest ? or 2nd smallest ?
View 7 Replies
View Related
Apr 13, 2007
Is there any way to use a font size smaller than 8pt? Believe it or not... I need to go down to a 6pt font for a report.
Thanks!
Leif
View 3 Replies
View Related
Jun 14, 2007
I have a table with the following variables:
patid cddate nadcd4
Patients (patid) have multiple records. I am trying to find the lowest nadcd4 for each patient along with the associated cddate. I can do a min(nadcd4) and find the lowest nadcd4 by patid but I can't seem to get the associated cddate. Any suggestions? Thanks
wal
View 6 Replies
View Related
May 2, 2007
saw my AS400 has this time stamp: 2007-05-02-11.29.56.811195
is SQL2005 able to have time stamp to this details?
View 1 Replies
View Related
Sep 24, 2007
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
View 1 Replies
View Related
Jul 18, 2001
I had a server with SQL Server 7.0
I installed a named instance of SQL Server 2000 and then i passed all my DB
of the 7.0 instance to the 2000 instance.
Then i removed the 7.0 instance, that was the default instance.
So at the moment there is only the 2000 version, but it isn't the default
instance
Can the 2000 instance become the default instance? (So that clients can
connect to it simply through computer name, and not creating an alias)
thanks
Fede
View 1 Replies
View Related
Nov 19, 2014
I'm using SS 2012.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".
The input table can have several thousand rows.
View 9 Replies
View Related
Jan 11, 2008
I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way.
Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X.
So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time.
I want to keep track of which users used a instance of a wheelchair.
No there's a developer who likes to put this in one table. (the chair and it's user) in a way like this
UID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumns
The UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap.
If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime.
Is this a good or a common practice? Why use it, or stay away from it?
Henri
~~~~
There's no place like 127.0.0.1
View 1 Replies
View Related
Sep 17, 2007
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
View 1 Replies
View Related
Nov 10, 2005
For each blog entry viewed in my web app, a (IPNumber, Date, BlogID) row is inserted into table "BlogViews"
How can I fetch the BlogID which has been most often visited, that is has the most number of rows?
View 1 Replies
View Related
Oct 21, 2015
I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:
Row# Comments
1 I like working on SQL
2 I enjoy sleeping and like watching TV
So the output should be :
Word Count
I 2
like 2
working 1
on 1
SQL 1
.......
Any way to get this count using MS SQL?
View 5 Replies
View Related
Jun 9, 2014
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
View 3 Replies
View Related
Jul 20, 2005
There's an SP that in effect returns a table -- it loops and executesstatements like SELECT @field1, @Field2, @Field2Can I capture its results in a table? I know that if it were a FUNCTIONthat returned a table, that would be simple:INSERT ResultsTable SELECT * FROM dbo.Function (@Param1, @Param2)But how to do that if the code is a procedure rather than a function?
View 1 Replies
View Related
Feb 19, 2008
Hi, I'm new to the forums, so hopefully I'm at the right place with my question...
Suppose I have a user-defined type in C#, which stands for simple point in 2D (let's call it Point). The type consists of two integer fields that represent the X and Y coordinate of the point. To make things a little more complicated, I want to store my values compressed using an algorithm, so I can't directly access X and Y without using a decompression algorithm.
I also have a table (PointTable), it's only column is called Data and is of type Point.
Now, writing and executing a simple query like
SELECT Data.ToString() FROM PointTable
is no problem at all.
What I want to do, is to be able to retrieve a table filled with the X and Y coordinates, so something like this:
SELECT PointX, PointY FROM GetTable()
where GetTable creates a table with columns PointX and PointY.
The function GetTable() should be implemented in C#, and work something like this: iterate through all Point values, decompress them, and create a new row from each Point.
I hope my intentions are clear and thanks for your help in advance!
View 8 Replies
View Related
Jun 10, 2015
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
View 3 Replies
View Related
Jul 31, 2006
I have a multi-level folders table, named folders with attributes of folder_id, parent_id and user_id, and have another table, users, contains all the user_id. I need to list all the users for each folder which has parent_id = 0 and its all sub-folders' users into one table. I have created a function to return a table with folder_id and user_id for one single folder. However, I don't know how to use this function to get the sub-folder's users and merge them together as one single table.
Here is my function:
CREATE FUNCTION [dbo].[FolderUsers] (@fid int) RETURNS Table AS Return (select folder_id, f.user_id from folders f, users u where f.user_id = u.user_id and folder_id = @fid)Go
where @fid is the top folder with parent_id = 0 at here, the next level sub-folder's parent_id would be = @fid.
I am thinking to have recursive call from the parent_id = @fid that returns another table and have to concatenate to the called table. I have been thinking of store procedure, "insert into" and so on, but don't know how to implement it.
Do you have any good inspiration for me? Thank you in advance!
View 9 Replies
View Related
Nov 16, 2005
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create FUNCTION [dbo].[search](@art varchar,@cd varchar,@tra varchar,@gen varchar,@cdate datetime,@label varchar)
RETURNS @result TABLE(Artist varchar(100),CDTitle varchar(100),Track varchar(100),CDtype
varchar(100),CDDate datetime, Label varchar(100))
AS
BEGIN
IF @art <>'/'
INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as
'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label,
dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and
track.trackid=cdtrack.trackid and label.labelid=cd.labelid and
shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid
and artist.artist=@art
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate,
dbo.Label.Label, dbo.Shelf.Shelf
if @cd <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @tra <> '/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @gen <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @cdate<>'01/01/1900'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @label<>'/'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
return
end
---------------------------------------------------------------------
upon running executing this function with valid values i am not getting any results.
anything is wrong?
thank you,
View 13 Replies
View Related
May 7, 2008
Hi guys,
I have to work with some configuration data that is stored in rows as a comma separated values. Something like this:
Key1
A,1,Z,0;B,2,Y,9;C,,8,X;
Key2
Alpha,101;Beta,102;
Each group of data is separated by a semicolon and each value by a comma. The quantity of values may vary from one key to the other. Over this values sometimes I need to run some selects, so I went with the idea to get it as a table using CLR.
There I find the first problem: I didn't find a way to return a "variable" column with a CLR function, I had to create a SP. Ok, now I execute spGetConfigurationAsTable 'Key1' and I can obtain something like this:
A
1
Z
0
B
2
Y
9
C
3
X
8
But I'm faced with a second problem: How can I run a query over this? I didn't find a way to run a query over an output of a SP. And I can't INSERT the result into a temporary table because I cannot CREATE the table static (remember the columns may differ from one configuration to the other).
So, it seemed a pretty simple task and a neat solution, but I'm kinda stuck. Is there a way to run a query over the SP output? Or is there a way to have a variable table output from a CLR UDF?
Here is the code of the CLR SP I use to obtain the data, and also the (non-working) CLR user defined function.
THANKS!
Code Snippet
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void spGetConfigurationAsTable(string Key)
{
SqlConnection conn = new SqlConnection("Context Connection=true");
string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key);
SqlCommand cmd = new SqlCommand(SqlCmd, conn);
conn.Open();
string Value = Convert.ToString(cmd.ExecuteScalar());
if (Value.Length > 0)
{
char SeparatorRow = ';';
char SeparatorColumn = ',';
if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35)
return;
StringBuilder SqlCreate = new StringBuilder("DECLARE @Output TABLE (");
for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++)
{
SqlCreate.AppendFormat("[{0}] varchar(50),", Convert.ToChar(65 + i));
}
SqlCreate.Remove(SqlCreate.Length - 1, 1);
SqlCreate.AppendLine(");");
StringBuilder SqlInsert = new StringBuilder();
foreach (string row in Value.Split(SeparatorRow))
{
if (row.Length > 0)
{
SqlInsert.Append("INSERT INTO @Output VALUES (");
// busca las diferentes "columns" ~ Charly
foreach (string column in row.Split(SeparatorColumn))
{
SqlInsert.AppendFormat("'{0}',", column);
}
SqlInsert.Remove(SqlInsert.Length - 1, 1);
SqlInsert.AppendLine(");");
}
}
string SqlSelect = "SELECT * FROM @Output;";
cmd.CommandText = SqlCreate.ToString() + SqlInsert.ToString() + SqlSelect;
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
reader.Dispose();
}
conn.Close();
conn.Dispose();
cmd.Dispose();
}
};
Code Snippet
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static IEnumerable fGetConfigurationAsTable(string Key)
{
SqlConnection conn = new SqlConnection("Context Connection=true");
string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key);
SqlCommand cmd = new SqlCommand(SqlCmd, conn);
conn.Open();
string Value = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
conn.Dispose();
cmd.Dispose();
DataTable dt = new DataTable();
if (Value.Length > 0)
{
char SeparatorRow = ';';
char SeparatorColumn = ',';
if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35)
{
// throw exception
}
string ColumnName;
for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++)
{
ColumnName = string.Format("[{0}] varchar(50),", Convert.ToChar(65 + i));
dt.Columns.Add(ColumnName, Type.GetType("System.String"));
}
foreach (string row in Value.Split(SeparatorRow))
{
if (row.Length > 0)
{
dt.Rows.Add(row.Split(SeparatorColumn));
}
}
}
return dt.Rows;
}
};
View 5 Replies
View Related
