Click here to Skip to main content
15,303,151 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a table structured below called time
name	1	2	3	4

and a datagridview structured below

name	value
sani	1
sani	2
sani	3
sani	4

expected outcome
name	1	2	3	4
sani	1	2	3	4

i want an SQL command that save the data into MySQl table using DataGridview as the specifying column

when ever am saving it should loop through the DatGridView and save base on specifying column. i can save the data from DataGridview to Database. Let say what am saving is SANI with 3, then it should check for column 3 and save the data. 

Thank you in ADVANCE

What I have tried:

Still trying to figure out the outcome will be, am new to MySql and C#
Updated 20-Jan-22 4:18am

I can't test this as I don't have VS but one approach could be to create a new dataGridView with 5 columns ('name','1','2','3','4). Add an empty row

Loop through dataGridView1, capture the value
update the second dataGridView by referencing the column by name
var row = dataGridView2.Rows[0];
row.Cells("name").Value = name from datagridview1
row.Cells[value.ToString()].Value = value;
You can then load that data to MySQL using one of the techniques in Solution 1

Alternatively, load your existing data in the same format into another table in MySQL and "pivot" or "Transpose" the data using SQL. MySQL doesn't have a PIVOT function as far as I know but there are plenty of examples on how to do that e.g. How to rotate rows into columns in MySQL - Taryn Pratt - Pivots and other SQL fun[^]

What is more intriguing is why you would want to do this, but to each their own
You can use the SQL bulk query to insert all data at once or through loop on datagrid. Use following reference links:

c# - Insert all data of a datagridview to database at once - Stack Overflow[^]

Bulk Insert:
Bulk Insert In SQL Server From C#[^]
CHill60 20-Jan-22 10:01am
This doesn't help the OP insert the right data into the right column

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