How do i update a row based on datagridview selected cell on row index using stored procedures?

Question

0.00/5 (No votes)

See more:

Hello,

I've tried to write some code to do the function mentioned on the title but with no success...

What possibilities i have with stored procedures, and what to know about using a value to search for a selected row on sql table?

Code snippet:

C#

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[0].RowIndex);
                        cmd.ExecuteNonQuery();
                        var res = cmd.Parameters["@retcode"].Value;
                    }

Stored procedure snippet:

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

What I have tried:

Tried this code i got but with no success...

Posted 7-Mar-16 22:55pm

Scribling Doodle

Updated 8-Mar-16 21:48pm

Add a Solution

Comments

Suvendu Shekhar Giri 8-Mar-16 5:01am

Did you get any error on executing this?

Scribling Doodle 8-Mar-16 5:03am

No error shown... It looks like it executes the command but doesn't insert the value into database... I don't really know what's the problem here...

2 solutions

Solution 1

You are passing in a value of "ID_VEICULO" but not using it anywhere.

If entradas.id_veiculo is an integer column then your line

SELECT * FROM 'entradas' WHERE id_veiculo= "@id_veiculo";

will generate an error. I'm not sure of the MySQL error but in SQL Server this would be

Quote:
Conversion failed when converting the varchar value 'asdkfjasldj' to data type int.

Because of the error the exception handler will be fired and _rollback will have been set to 0. Did you debug and check the return code from

C#

var res = cmd.Parameters["@retcode"].Value;

In your stored procedure avoid calling the parameters the same name as the column and actually use them rather than the string "@id_veiculo" ... I think it is unlikely that that string is actually how you are identifying every single vehicle in the table.

Try changing the stored procedure like this

SQL

CREATE DEFINER='root'@'localhost' PROCEDURE 'entradas_sai'(
IN IDTOCHECK VARCHAR(45), OUT retcode INT)
BEGIN
    DECLARE _rollback BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
    START TRANSACTION;
	-- NO point in this at all as you are not using the data
        --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=IDTOCHECK AND SAI=0;
    IF '_rollback' THEN
        SET retcode = 0;
        ROLLBACK;
    ELSE
        SET retcode = 1;
        COMMIT;
    END IF;
END

When problems happen with stored procedures try running parts of the sql directly in MySQL (or at a push use SQL Fiddle[^]). Then you would have spotted the problem in the SELECT.
Also learn to use the Visual Studio debugger - Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

As a general rule of thumb, if something doesn't do what you expected then check the values that are being returned ... in this case I believe res==0

Posted 8-Mar-16 0:01am

CHill60

Updated 8-Mar-16 0:03am

v2

Comments

Scribling Doodle 8-Mar-16 6:17am

The only problem is this seems to not show any errors... it wont just insert the values. Thats all :/

Scribling Doodle 8-Mar-16 6:19am

the res var is just giving null values. Not 0 or 1.

Scribling Doodle 8-Mar-16 6:26am

And this cmd.Parameters that i have were sent by you on another question, that's why i think you were right. And it worked well to insert all values where sai was equal to 0 and change them all to 1... But i want only to set the selected row to sai being equal to 1. And no, id_veiculo is not a int. It's a masked varchar45. It's made to insert the licenseplate. And it acts as an identifier for the vehicle. Not as an integer :)

CHill60 8-Mar-16 6:32am

Have you tried running the query INSERT INTO `entradas`(datasai, horasai) VALUES(date(now()) ,time(now())); directly in MySQL - does it work?

Scribling Doodle 8-Mar-16 6:41am

in mysql wb it retrieves the error 1046. I'm kinda tired of trying all the possible ways and none does the job...

CHill60 8-Mar-16 6:47am

1046 is "no database selected" - you could put a USE statement into your stored procedure, but far better would be to fix your connection string - that's where you would normally put the database name - MySQL connection strings - ConnectionStrings.com[^]

Scribling Doodle 8-Mar-16 6:56am

using (var cn = new MySqlConnection("server=localhost;user id=root;password=12345;persistsecurityinfo=True;database=portaria;allowuservariables=True"))
{ My connection string is allright... It works on the other querys... Just on this procedure it's giving me headache...

Scribling Doodle 8-Mar-16 6:57am

It just shows me this error... Additional information: Parameter 'ID_VEICULO' not found in the collection. What can i do about it?

CHill60 8-Mar-16 7:33am

You said earlier you weren't getting any errors - did you start getting this error after trying my solution or before?

CHill60 8-Mar-16 7:35am

I think I've got it - you're adding the parameter retcode (in the C#) before id_veiculo ... they need to be in the same order (bug in MySQL)

Scribling Doodle 8-Mar-16 8:39am

So, what is the fix for this procedure? Will i need to setup in the correct order? or what?

CHill60 8-Mar-16 8:43am

Yes ... they need to be set up in the same order they are used by the SP i.e.
cmd.Parameters.AddWithValue("@id_veiculo", Grid1.SelectedCells[0].RowIndex);
cmd.Parameters.AddWithValue("@retcode", MySqlDbType.Int32);
cmd.Parameters["@retcode"].Direction = ParameterDirection.Output;

Scribling Doodle 8-Mar-16 9:05am

now it doesn't have any error but doesn't do anything xD It's confusing...

CHill60 8-Mar-16 9:21am

If you use MySQL wb and type
USE portaria
INSERT INTO `entradas`(datasai, horasai) VALUES(date(now()) ,time(now()));
do you get any error?

Scribling Doodle 8-Mar-16 10:32am

LOG:
Operation failed: There was an error while applying the SQL script to the database.
Executing:
USE portaria
INSERT INTO `entradas`(datasai, horasai) VALUES(date(now()) ,time(now()));

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO `entradas`(datasai, horasai) VALUES(date(now()) ,time(now()))' at line 2
SQL Statement:
USE portaria
INSERT INTO `entradas`(datasai, horasai) VALUES(date(now()) ,time(now()))

CHill60 8-Mar-16 10:55am

Try removing the single quotes from the table name

Scribling Doodle 8-Mar-16 11:14am

still nothing xD Just answer this... I got a grid called Grid1, how do it get the value from the selected cell on row index and use it as a value to update a database?

CHill60 8-Mar-16 11:45am

I'm not sure why you want the row index.
Grid1.SelectedCells[0].Value will get the value of the first selected cell, and
Grid1.Rows[anumber].Cells[anothernumber].Selected will tell you if a specific cell is selected.
You can force only a single cell to be selected at a time by Grid1.MultiSelect = false;
If you are having a problem with this then I suggest starting a new question, but perhaps some reading up on using datagridviews first

Scribling Doodle 9-Mar-16 3:40am

I'll post a answer that resolved my problems. No need for stored procedures or anything like that. The problem atm is that the grid wont force the refresh with the Grid1.Refresh(); neither Grid1.Update();

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Scribling Doodle · Accepted Answer · 2016-03-08T21:47:00

I've tryied to use a store procedure to execute 2 querys, but with no success, so why not try to use the same query with 2 syntaxes? Yep, that's what i did and work out pretty well.

C#

using (var cn2 = new MySqlConnection("server=localhost;user id=root;password=12345;persistsecurityinfo=True;database=portaria;allowuservariables=True"))
            {
                
                string SQL2 = "INSERT INTO entradas(datasai, horasai) VALUES(@datasai, @horasai); UPDATE entradas SET sai = 1 WHERE id_entrada =@entrada";

                using (var cmd = new MySqlCommand(SQL2, cn2))
                {
                    cmd.Parameters.AddWithValue("@datasai", DateTime.Now.ToString("yyyy-MM-dd"));
                    cmd.Parameters.AddWithValue("@horasai", DateTime.Now.ToString("hh:mm:ss"));
                    cmd.Parameters.AddWithValue("@entrada", Grid1.CurrentRow.Cells["#"].Value);
                    MySqlDataAdapter sda = new MySqlDataAdapter();
                    DataTable dt = new DataTable();
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                    Grid1.Update();
                    Grid1.Refresh();
                    cn2.Open();
                }
                cn2.Close();
            }

With that in mind i'll explain what i did, i used 2 vars(one for the connection, and one to execute the query on the connection string). And then i used some parameters to insert data and one field to get the fields from the selected row with the index of column "#". After that i simply created the datatable and forced a fill command and a grid update/refresh to simply force the grid to show this new data inserted. Then i simply opened the connection and closed after the var was used.

There were no need in using a stored procedure. That's why i did this way. Simplier and easier. Thanks for all the help here @CHILL60! You were a great help ;)