Home > Blockchain >  database insert error: Column name or number of supplied values does not match table definition
database insert error: Column name or number of supplied values does not match table definition

Time:07-22

Hello guys I have little problem cannot figure why but when im inserting data from datagridview rows to datebase got Error occured:

Invalid column name 'string in row'. Column name or number of supplied values does not match table definition.

Here is some lines of my code:

try
        {
            using (SqlConnection conn = new SqlConnection(cs))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;
                    conn.Open();
                    s = 0;
                    for (columnIndex = 0; columnIndex < dgvDataList.Columns.Count; columnIndex  )
                    {
                        columnName = dgvDataList.Columns[columnIndex].Name;
                        if (!regex.IsMatch(columnName))
                        { 
                            if (s == 0)
                            {
                                StrQuery = @"CREATE TABLE FileTable ("   columnName   " varchar(255));";
                                comm.CommandText = StrQuery;
                                comm.ExecuteNonQuery();
                                s  ;
                            }
                            else if (s >= 1)
                            {
                                StrQuery = @"ALTER TABLE FileTable ADD ["   columnName   "] varchar (255);";
                                comm.CommandText = StrQuery;
                                comm.ExecuteNonQuery();
                            }

                        }
                    }
                    columnIndex = 0;
                    for (int i = 0; i < dgvDataList.Rows.Count; i  )
                    {
                        columnName = dgvDataList.Columns[columnIndex].Name;
                        if(!regex.IsMatch(columnName))
                        { 
                            StrQuery = @"INSERT INTO FileTable VALUES (["   dgvDataList.Rows[i].Cells[columnName].Value   "]);";
                            comm.CommandText = StrQuery;
                            comm.ExecuteNonQuery();
                        }
                        columnIndex  ;
                    }
                }
            }
        }

I'll be happy for any tips, thanks.

CodePudding user response:

Does the column name really contain []? It seems you spelled it wrong in ALTER query. Also if you don't want your column value contain [], remove it from INSERT query.

Edit your INSERT query to:

StrQuery = @"INSERT INTO FileTable ("   columnName   ") 
            VALUES ('["   dgvDataList.Rows[i].Cells[columnName].Value   "]');";
  • Related