ASP.NET

Re: Randomize Data of Groupboxes

7-Oct-12 20:10

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.

//////////////////////////////////////////////////////////////////////////////////////

SQL

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()

--Select * from #Options

Declare
@RandomCols nvarchar(100)

Set
@RandomCols=''

Select
@RandomCols=@RandomCols+Fields+' ,' from #Options

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O1,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O2,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O3,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O4,')

+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))

--Set @RandomCols=SUBSTRING(@RandomCols,0,len(@RandomCols))

--Select @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

//////////////////////////////////////////////////////////////////////

Rockstar_7-Oct-12 20:46

weird problems: nothing is happening!!

7-Oct-12 20:46

If it has not solved your problem , please let me know ...

Jassim Rahma5-Oct-12 6:50

5-Oct-12 6:50

I have the following code and SQL:

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_connection.Open();
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;

sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_volow_member_id", Convert.ToInt32(Session["volow_member_id"])).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.Add("param_is_channel_created", MySqlDbType.Bit).Direction = ParameterDirection.Output;

// execute the query;
int result_rows = sql_command.ExecuteNonQuery();

if (Convert.ToBoolean(sql_command.Parameters["param_is_channel_created"].Value) != true)
{ Session["is_channel_created"] = false; }

and this is MySQL stored procedure:

SQL

BEGIN
        SET param_is_channel_created = TRUE;

        DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
        DELETE FROM member_channels WHERE member_channel_id = param_channel_id;

        IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
        BEGIN
                UPDATE volow_members SET is_channel_created = FALSE;

                SET param_is_channel_created = FALSE;
        END;
        END IF;
END

but nothing is happending and I am sure all are ok because I change something in the storedprocedure and I got and error but with the above I don''t get any error and page is just reloading with the same data when I click the delete button?

Kindly help...

jkirkerx5-Oct-12 8:43

jkirkerx

5-Oct-12 8:43

I don't write in c-sharp, but vb is my thing, but I did my best to rewrite the function in how I think it should be written. The catch will tell you what's wrong with your function or TSQL.

Also it's best practice to package everything first, then open the database, execute, and close the database all in 1 shot.

I can't test your TSQL, in my tester program, that would take another 20 minutes for me.

exitCode int = 2;


sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;

param_Channel_ID SqlParameter = new SqlParameter("@param_channel_id", SqlDbType.Int);
param_Channel_ID.Value = channel_id;
sql_command.Add(param_Channel_ID);

param_Member_ID SqlParameter = new SqlParameter("@param_volow_member_id", SqlDbType.Int);
param_Member_ID.Value = Convert.ToInt32(Session["volow_member_id"]);
sql_command.Add(param_Memeber_ID);

param_Created SqlParameter = new SqlParameter("@param_is_channel_created", SqlDbType.Bit); 
//[edit] I think it's boolean in mySQL, and bit in SQL Server
param_Created.Value = Convert.ToBoolean(ParameterDirection.Output);
sql_command.Add(param_Created_ID);

try {
   // execute the query;
   sql_connection.Open();
   sql_reader SqlDataReader = sql_command.ExecuteReader();
   sql_command.ExecuteNonQuery();
   sql_reader.Close();
   sql_connection.Close();
   exitCode = 0
}
catch ex as SQLException {
 exitCode = 1
 //Exception for the function
 //SQLException for the TSQL
}

return exitCode;

BEGIN
        SET param_is_channel_created = TRUE;
        DELETE FROM member_posts WHERE member_channel_id=@param_channel_id;
        DELETE FROM member_channels WHERE member_channel_id=@param_channel_id;
 
        IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id=@param_volow_member_id) THEN
        BEGIN
           SET param_is_channel_created = FALSE;     
           UPDATE volow_members SET is_channel_created=param_is_channel_created;                
        END;
        END IF;
END

Jassim Rahma5-Oct-12 9:36

5-Oct-12 9:36

thanks for the reply

but the problem is my code is not throwing any exception error as if everything is normal and it's not doing what is should do (deleting the record)!!

Rockstar_7-Oct-12 20:15

7-Oct-12 20:15

May be your gridview data is not refreshing because of the dataset , please check, and Is this deleted from the database?

Sunil_Nikam8-Oct-12 5:27

Sunil_Nikam

8-Oct-12 5:27

Please check the value of "int result_rows" also at first glance it seems that "IF NOT EXISTS" part wont be executed atall returning always "param_is_channel_created = TRUE"

Jassim Rahma8-Oct-12 6:52

8-Oct-12 6:52

there is datagrid VijayChauhan123

Sunil: the DELETE is before the IF NOT EXIST so it should have been invoked.

Jassim Rahma8-Oct-12 7:09

8-Oct-12 7:09

I even tried this simple delete but still not working:

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();
                                        
sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
int result_row = sql_command.ExecuteNonQuery();

and SQL:

SQL

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
    DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
    DELETE FROM member_channels WHERE member_channel_id = param_channel_id;

    IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
    BEGIN
        UPDATE volow_members SET is_channel_created = FALSE;

        SET param_is_channel_created = FALSE;
    END;
    ELSE SET param_is_channel_created = TRUE;
    END IF;
END

Jassim Rahma8-Oct-12 7:10

8-Oct-12 7:10

I even tried this simple delete but still not working. No error and records not getting deleted!!!

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();
                                        
sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
int result_row = sql_command.ExecuteNonQuery();

and SQL:

SQL

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
    DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
    DELETE FROM member_channels WHERE member_channel_id = param_channel_id;

    IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
    BEGIN
        UPDATE volow_members SET is_channel_created = FALSE;

        SET param_is_channel_created = FALSE;
    END;
    ELSE SET param_is_channel_created = TRUE;
    END IF;
END

How to Develop asp.net application to control Time and authenticationfingerprint Device such as get user ID

Sunil_Nikam8-Oct-12 21:06

Sunil_Nikam

8-Oct-12 21:06

Can you check if the SP itself works as expected by passing parameters (from ssms itself)

osama_hagrass5-Oct-12 3:54

osama_hagrass

5-Oct-12 3:54

How to Develop asp.net application to control Time and authentication fingerprint Device such as get user ID from Device,enroll new user and act..

Thanks All...

Device

David Mujica5-Oct-12 6:20

David Mujica

5-Oct-12 6:20

You need to start with the manufacturer of the device. The vendor will have a programming guide or API that will allow you to communicate with the device. There is no industry standard for communicating to these devices.

Re: Device

osama_hagrass5-Oct-12 7:25

osama_hagrass

5-Oct-12 7:25

After getting API of Device,what's the next step in programming and How can i deal with API in asp.net,because it is first time to deal with api in c# programming

THanks David...

Re: Device

Rockstar_7-Oct-12 20:19