Click here to Skip to main content
14,086,361 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I'm trying to update data from DataGridView to my database. While I was looking for the solution of this problem on google, I noticed that all of the solutions are managed by using class variables (for DataTable,SqlDataAdapter,...). I'm trying to do this just by using function variables.

This is how I loaded data to `DataGridView`:

private void LoadDataGridView(int ID)
    {
        try
        {
             SqlConnection connection = new SqlConnection(connString);
             SqlCommand cmd = new SqlCommand("SELECT SENTENCE FROM Sentences WHERE CategoryID = '" + ID + "'",connection);

             DataTable dataTable = new DataTable();

             SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
             dataAdapter.Fill(dataTable);

             DataGridView.DataSource = dataTable;
        }
        catch (Exception)
        {
             MessageBox.Show("ERROR WHILE CONNECTING TO DATABASE!");
        }
    }


DataGridView is showing just those sentences that match particular ID.

What I have tried:

This is what I have tried so far:

private void RefreshBtn_Click(object sender, EventArgs e)
    {
        try
        {
              SqlConnection connection = new SqlConnection(connString);
              SqlCommand cmd = new SqlCommand("SELECT QUESTION FROM Questions WHERE CategoryID IN('" + CategoryID + "')", connection);

              SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
              SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter);
              dataAdapter.Update(dataTable);
        }
        catch (Exception)
        {
              MessageBox.Show("ERROR WHILE CONNECTING WITH DATABASE!");
        }
    }


This is the error that comes up:
Cannot insert value NULL into column CategoryID, table ...;column does not allow nulls.Insert fails. The statement has been terminated.<br />
Posted
Updated 22-Feb-16 5:43am
Comments
Richard Deeming 20-Feb-16 10:47am
   
Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.


Also, you've only shown the code that selects data from the table. The error message is clearly related to the code that inserts data into the table, which you haven't shown. If you don't show us the code, we can't help you fix it.
Sascha Lefèvre 20-Feb-16 11:02am
   
The error message really says it all: CategoryID can not be null. So either provide a value for it when inserting a record, or, if you want to be able to not provide a value for it and you are free to change the database schema, then change the column definition to allow nulls.
PeMaCN 20-Feb-16 11:17am
   
Thanks, I will update my code concerning sql injections.
User is inserting sentences in the datagridview and I need to join each sentence with categoryID that I have. Can you just post how it should look like refreshBtn_Click when your datagridview has less columns than your database table and the rest of columns should be added in code (categoryID in this case)?
PeMaCN 20-Feb-16 11:19am
   
As I said in the upper reply, user inserts sentences in the datagridview and I need to put the categoryID for each sentence. How can I do it?
Maciej Los 21-Feb-16 5:08am
   
I do not see INSERT statement...
PeMaCN 21-Feb-16 7:29am
   
Can you tell me how would you do it? I'm beginner in SQL. Please.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

In your select statement, include the CategoryID - that way the SENTENCE value will be paired with the corresponding CategoryID in the resulting dataset. Use that in your insert/update statements. Or if you have used the dataset designer for the DataGridView handling, it needs that column.
   
Comments
PeMaCN 22-Feb-16 11:55am
   
I found a solution. I have posted it on stack overflow: http://stackoverflow.com/questions/35523153/how-to-update-data-from-datagridview-to-database-error-cannot-insert-value-nu?noredirect=1#comment58739024_35523153

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.190518.1 | Last Updated 22 Feb 2016
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100