Hi Dear,
I have worked on this type of project...
The following is the stored procedure you can use for randomly getting the questions as well as the random options (answers) from the database.
This stored procedure covers almost your 80% of the work, only the thing is you have to bind it to the gridview . If you have any doubts in this you can comment.
//////////////////////////////////////////////////////////////////////////////////////
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER
Procedure [dbo].[GetRandomOptionsold]
(@class varchar(max),
@Subject varchar(max),
@ExamType varchar(max),
@School varchar(max)
)
as
Begin
Create
Table #Options(ID int identity(1,1),Fields nvarchar(10))
Insert
into #Options
Select
COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME='Questions' And COLUMN_NAME like 'O%' Order By NEWID()
Declare
@RandomCols nvarchar(100)
Set
@RandomCols=''
Select
@RandomCols=@RandomCols+Fields+' ,' from #Options
Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O1,')
+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))
Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O2,')
+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))
Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O3,')
+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))
Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O4,')
+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))
Declare
@strQry nvarchar(MAX)
Set
@strQry=''
Set
@strQry='Select qno,que,'+@RandomCols+'ans from Questions where Class='''+@Class+''' and Subject='''+@Subject+''' and ExamType='''+@ExamType+''' and School='''+@School+''''
exec
(@strQry)
End
//////////////////////////////////////////////////////////////////////
|