Click here to Skip to main content
15,886,006 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
As the title says, i want to know if a stored procedure can use the query to get values from a datagrid cell value on the selected row.

Stored procedure:

SQL
CREATE DEFINER=`root`@`localhost` PROCEDURE `entradas_sai`(
IN ID_VEICULO VARCHAR(45), OUT retcode INT)
BEGIN
    DECLARE _rollback BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
    START TRANSACTION;
	UPDATE entradas SET SAI=1 WHERE id_veiculo='"+ Grid1.SelectedRows.Cells[i].Value +"' AND SAI=0;
        //More 2 queries here but those are fine.
    IF '_rollback' THEN
        SET retcode = 0;
        ROLLBACK;
    ELSE
        SET retcode = 1;
        COMMIT;
    END IF;
END



EDIT:


New Procedure:

SQL
CREATE DEFINER=`root`@`localhost` PROCEDURE `entradas_sai`(
IN ID_VEICULO VARCHAR(45), OUT retcode INT)
BEGIN
    DECLARE _rollback BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
    START TRANSACTION;
	SELECT * FROM entradas WHERE id_veiculo= "@id_veiculo";
	INSERT INTO entradas(datasai, horasai) VALUES(date(now()) ,time(now()));
	UPDATE entradas SET SAI=1 WHERE id_veiculo="@idveiculo" AND SAI=0;
    IF '_rollback' THEN
        SET retcode = 0;
        ROLLBACK;
    ELSE
        SET retcode = 1;
        COMMIT;
    END IF;
END





c# snippet:

C#
private void cmdSaida_Click(object sender, EventArgs e)
{

    using (var cn = new MySqlConnection("server=localhost;user id=root;password=12345;persistsecurityinfo=True;database=portaria;allowuservariables=True"))
        {
            cn.Open();
            if (Grid1.SelectedRows.Count > 0)
            {
                MySqlCommand cmd = new MySqlCommand("entradas_sai", cn);
                cmd.CommandText = "entradas_sai";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@retcode", MySqlDbType.Int32);
                cmd.Parameters["@retcode"].Direction = ParameterDirection.Output;
                cmd.Parameters.AddWithValue("@id_veiculo", Grid1.SelectedCells[1].Value);
                cmd.ExecuteNonQuery();
                var res = cmd.Parameters["@retcode"].Value;
            }
            cn.Close();
        }
    Grid1.Refresh();
}


What I have tried:

Tried this query on my stored procedure.

SqlQuery:

C#
SELECT * FROM `entradas` WHERE id_veiculo= '"+ Grid1.SelectedRows.Cells[i].Value +"';
Posted
Updated 7-Mar-16 23:48pm
v5
Comments
Pete O'Hanlon 8-Mar-16 4:22am    
So, you've tried nothing then. The whole point of the What I have tried part is to tell us what you have tried and ruled out; what errors you encountered, etc. Without any basic effort, this question will be deleted.
Scribling Doodle 8-Mar-16 4:26am    
i tried this sqlquery that i got... Here you got your answer.
Scribling Doodle 8-Mar-16 4:30am    
it doesn't make sense writing the query 2 times. I think anyone could see by the topic that this was what i've tried. And didn't work. Thanks anyway.
Pete O'Hanlon 8-Mar-16 4:50am    
No, they can't see what you've tried. They can only see a question. For instance, you haven't shown if you have written a stored procedure already and got stuck on the parameters. That's why you add the second part - it shows what you've done.
Scribling Doodle 8-Mar-16 4:59am    
Re-organized the question.

No. Stored procedures are executed within SQL server and have no knowledge of datagrid which is executed in ASP.NET.

You have to create a parameter in SP and pass the cell value from datagrid to SP using the parameter.
 
Share this answer
 
v2
Comments
Scribling Doodle 8-Mar-16 4:27am    
I'm using c# 6.0 not asp.net. Not much into it, im working on winform and WPF.
Richard Deeming 8-Mar-16 5:44am    
That doesn't make any difference. The code executing in MySQL still has no knowledge of the controls in your UI.

You need to pass the cell value to the stored procedure using the ID_VEICULO parameter that you've already defined.
Scribling Doodle 8-Mar-16 5:47am    
I'll edit the question and show up my actual code for it. I did that and still not working...
You can't bind the grid in directly but, in this case, you don't need to. The client side code (the winforms/WPF app in your case) will simply use Grid1.SelectedRows.Cells[i].Value as a parameter when you call your Stored Procedure. It looks like you are nearly all there right now - replace your reference to the grid with the SQL parameter and you should be good to go.
 
Share this answer
 
Comments
Scribling Doodle 8-Mar-16 5:18am    
didn't understood that "replace your reference to the grid with the sql parameter". Can you be more clear? Thanks!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900