存储过程可以从.selectedrows.cells [i] .value获取值吗？ [英] Can a stored procedure get a value from a .selectedrows.cells[i].value ?

问题描述

正如标题所示，我想知道存储过程是否可以使用查询从所选行上的datagrid单元格值获取值。



存储程序：

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:

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

编辑：





新程序：

New Procedure:

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# snippet:

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

我尝试过：



在我的存储过程中尝试了这个查询。



SqlQuery：

What I have tried:

Tried this query on my stored procedure.

SqlQuery:

SELECT * FROM `entradas` WHERE id_veiculo= '"+ Grid1.SelectedRows.Cells[i].Value +"';

推荐答案

没有。存储过程在SQL服务器中执行，并且不了解在ASP.NET中执行的datagrid。



你必须在SP中创建一个参数，并使用参数将单元格值从datagrid传递给SP。

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.

你可以不直接绑定网格，但在这种情况下，您不需要。客户端代码（在您的情况下为winforms / WPF应用程序）将在您调用存储过程时使用Grid1.SelectedRows.Cells [i] .Value作为参数。看起来你现在几乎都在那里 - 用SQL参数替换你对网格的引用，你应该好好去。

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.

这篇关于存储过程可以从.selectedrows.cells [i] .value获取值吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！