Read a value from the database and save it into a variable

Question

I want to access my SQL Server database and retrieve the value of some columns with specific id. I get the id as a print the database in a dataGridView table, so the first cell of the row that the user has selected is the id. Here is the code

con3.Open();

if (typeBOX.SelectedIndex == 0)
{
    pictureBox1.Image = Organizer.Properties.Resources.b_height_years;
    ChartForm.ImageSet = 1;
    pictureBox1.Invalidate();

    SqlCommand cmd1 = new SqlCommand("select Height,Age from data where Id = '" + dataGridView1.SelectedCells[0].Value.ToString() + "'", con3);
    // SqlCommand cmd2 = new SqlCommand("select Age from data where Id = '" + dataGridView1.SelectedCells[0].Value.ToString() + "'", con3);

    SqlDataReader reader1 = cmd1.ExecuteReader();
    bool flag = false;

    while (reader1.Read() && flag == false)
    {
        string tempHeight = reader1["Height"].ToString();
        ChartForm.Height = int.Parse(tempHeight);
        string tempAge = reader1["Age"].ToString();
        ChartForm.Age = int.Parse(tempAge);
        flag = true;
    }
}

But when I am trying to run the code I get the error:

System.Data.SqlClient.SqlException: 'Conversion failed when converting the varchar value 'zxxv' to data type int.

'zxxv' is a saved FirstName in the database but I do not for it in my command cmd1. I am only accessing the height and age which are both integers. I do not know why this is happening.

Always use parameters to avoid sql injection and formatting errors. Id is probably not a string. — LarsTech, Sep 27 '19 at 18:05

haldo · Answer 1 · 2019-09-30T11:38:59.180

The problem is you're passing the Id as a string, not an int. So, the error is telling you that the SQL server failed to convert the string into an int. The reason this is happening is because the Id is wrapped in single quotes which SQL will interpret as a string (varchar/nvarchar).

I'd recommend to always parameterise your SQL query to avoid potential attacks through SQL injection and problems like this one. Please see Why do we always prefer using parameters in SQL statements?.

You also need to ensure you're selecting the correct value from the dataGridView as @RussW mentions in their answer. You're selecting the FirstName field, so perhaps you could use:

int selectedRowIndex = datagridview1.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = datagridview1.Rows[selectedRowIndex ];
int id = int.Parse(selectedRow.Cells["Id"].Value.To);

Or

int rowIndex = dataGridView1.CurrentCell.RowIndex;
int columnIndex = dataGridView1.CurrentCell.ColumnIndex; 
int id = int.Parse(dataGridView1.Rows[rowIndex].Cells[columnIndex].Value.ToString());

Below is a sample which should get you started:

string query = "select Height, Age from data where Id = @id";  // parameter @id in string 
SqlCommand cmd1 = new SqlCommand(query, con3);              // create command with string

// get the correct row and cell
int selectedRowIndex = dataGridView1.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView1.Rows[selectedRowIndex];
int id = int.Parse(selectedRow.Cells["Id"].Value.ToString());        // parse id to int

// create sql parameter and add to SqlCommand
SqlParameter param1 = new SqlParameter("@id", id);
cmd1.Parameters.Add(param1);

// continue your code...
SqlDataReader reader1 = cmd1.ExecuteReader();
....

Note, when using parameterised queries there's no need to wrap the parameter in quotes, it is handled for you. Please refer to the SqlCommand.Parameters docs.

There is an error in line where it says `int id = int.Parse(selectedRow["Id"].Value);` that says that "cannot apply indexing with [] to an expression of type 'DataGridViewRow'... — Stratos Eftychiadis, Sep 30 '19 at 11:07
Sorry, I think I was wrong. You need to access the `.Cells` property. Maybe try `int id = int.Parse(selectedRow.Cells["Id"].Value);` or `int id = int.Parse(selectedRow.Cells[0].Value);` @StratosEftychiadis — haldo, Sep 30 '19 at 11:20
What does your dataGridView look like when you're running this? Which rows or cells are selected? Only you can answer this because you've not shown us what the dataGridView looks like or what rows/cells are selected @StratosEftychiadis — haldo, Sep 30 '19 at 11:31
Updated answer. I've tested this works and gets the correct ID: `int selectedRowIndex = dataGridView1.SelectedCells[0].RowIndex; DataGridViewRow selectedRow = dataGridView1.Rows[selectedRowIndex]; int id = int.Parse(selectedRow.Cells["Id"].Value.ToString());` — haldo, Sep 30 '19 at 11:40

score 1 · Answer 2 · answered Sep 28 '19 at 02:40

I bet you received that error because you tried to pull the Id out of the wrong cell in the data. The error message states that an error occurred while trying to convert value 'zxxv' to int. That would mean that the value stored in:

dataGridView1.SelectedCells[0]

is 'zxxv'. An error is occurring when the SQL Server attempts to compare that value to an int column. It looks like maybe you clicked on the cell in the dataGridView1 that contained the first name, but the id is in a different column, or is hidden. This is just a wild guess, but it's the best I can do without having the rest of your code. Would something like this fix it?

dataGridView1.SelectedRows[0].Cells[0].Value

or:

dataGridView1.SelectedRows[0].Cells["Id"].Value

I totally agree with the others who said you should pass the id in as a parameter. You should do that too. But I don't think the lack of a parameter is what is causing the error.

@LarsTech, you're forgetting about implicit conversion, though. The engine will convert the varchar value '123' to the int value 123 before executing the where clause. The error message is: Conversion failed when converting the varchar value 'zxxv' to data type int. Which means the root cause of the error is the fact that a non-numeric value was passed in. — dangeruss, Sep 29 '19 at 02:59

Read a value from the database and save it into a variable

2 Answers2