Can't seem to find how to take a value out of my table column and put it on a label C#

Question

I can't find how to take the integer value from my primary key (called QuestionID) and display it on a label.

I thought it might be a good idea to have this code in a timer because I want it to update every time a record (row) is added.

I would very much appreciate any help in any way as I'm a bit of a newbie! Thank you!

Here is my very pitiful attempt to doing it which I kind of gave up on as I don't know how to take the value from the database:

private void timer1_Tick(object sender, EventArgs e)
{
        string connectionString = ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString;
        SqlConnection connect = new SqlConnection(connectionString);
        connect.Open();

        SqlCommand command6 = new SqlCommand("SELECT ([QuestionID]) FROM Questions", connect);

        QuestionNum.Text = 
}

The rest of my code is here:

private void button1_Click(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString;

    SqlConnection connect = new SqlConnection(connectionString);
    connect.Open();

    int checkedradiobutton = 0;

    if(radioButton1.Checked)
    {
            checkedradiobutton = 1;
    }
    else if(radioButton2.Checked)
    {
            checkedradiobutton = 2;
    }
    else if(radioButton3.Checked)
    {
            checkedradiobutton = 3;
    }

    string QuestionText = QuestionBox.Text;
    string AnswerText = teacheranswerbox.Text;

    SqlCommand command5 = new SqlCommand(@"INSERT INTO Questions ([Actual answer], [Question Space], [Question Type]) VALUES (@AnswerText, @QuestionText, @checkedradiobutton)", connect);
    command5.Parameters.AddWithValue("@AnswerText", AnswerText);
    command5.Parameters.AddWithValue("@QuestionText", QuestionText);
    command5.Parameters.AddWithValue("@checkedradiobutton", checkedradiobutton);

    command5.ExecuteNonQuery();
}

private void radioButton2_CheckedChanged(object sender, EventArgs e)
{
        if (radioButton2.Checked)
        {
            teacheranswerbox.Text = "You cannot store an answer for a long answer essay question";
            teacheranswerbox.ReadOnly = true;
        }
        else
        {
            teacheranswerbox.ReadOnly = false;
            teacheranswerbox.Text = "Enter the correct answer here";


        }
    }

The table I am trying to take the data from (p.s. the QuestionID automatically increments):

CREATE TABLE [dbo].[Questions] 
(
     [QuestionID]     INT           IDENTITY (1, 1) NOT NULL,
     [Actual answer]  NVARCHAR (50) NULL,
     [Question Space] NVARCHAR (50) NULL,
     [Question Type]  INT           NULL,

     PRIMARY KEY CLUSTERED ([QuestionID] ASC)
);

Are you trying to get back the value of QuestionID for the row inserted by your INSERT INTO query? — Steve, Apr 19 '15 at 15:42
Yeah I want that QuestionID value to be displayed on a label — mot375, Apr 19 '15 at 15:43

Steve · Answer 1 · 2015-04-19T17:04:00.280

Being your QuesitionID an IDENTITY column then you could get back the value assigned to it by your database engine using a batch statement like this

string cmdText =@"INSERT INTO Questions 
                  ([Actual answer], [Question Space], [Question Type]) 
                  VALUES (@AnswerText, @QuestionText, @checkedradiobutton);
                  SELECT SCOPE_IDENTITY();"
SqlCommand command5 = new SqlCommand(cmdText, connect);
command5.Parameters.AddWithValue("@AnswerText", AnswerText);
command5.Parameters.AddWithValue("@QuestionText", QuestionText);
command5.Parameters.AddWithValue("@checkedradiobutton", checkedradiobutton);

int result = Convert.ToInt32(command5.ExecuteScalar());
QuestionNum.Text = result.ToString();

SQL Server supports multiple statement in the same command. Just separe them with a semicolon. In this code I have added, just after the first INSERT INTO, a call to SCOPE_IDENTITY that returns the last value assigned to an IDENTITY field in the same scope of your connection. This value should be retrieved using an ExecuteScalar call instead of an ExecuteNonQuery. The last part is important because ExecuteNonQuery tells you only how many rows have been added/modified by the command, while ExecuteScalar returns the value of the first row first column of the executed query (IE, the value of SCOPE_IDENTITY)

Thank you very much for the help, sorry I took a while to reply as it took me a while to understand all of these different changes. What is an IDENTITY field? Is that just my ID column? Also when I try and run this code it stops at the 'int result = (int)command5.ExecuteScalar();' and says An unhandled exception of type 'System.InvalidCastException' occurred in ComputingA2_Official_Project.exe Additional information: Specified cast is not valid. — mot375, Apr 19 '15 at 16:04
@mot375 fixed. When you assign the IDENTITY property to a column (as you have done for your QuestionID) then that column will be incremented according to an initial value for a determined step value. See IDENTITY(1, 1). Sql Server provides the SCOPE_IDENTITY for the exact purpose to retrieve this automatic value. — Steve, Apr 19 '15 at 17:08

score 0 · Accepted Answer · answered Apr 19 '15 at 15:48

0

Your Problem is that you don't know how to get the Data that is returned isn't it?

    SqlCommand command6 = new SqlCommand("SELECT ([QuestionID]) FROM Questions", connect);

    String s = "";

    using (SqlDataReader reader = command6.ExecuteReader())
    {
        while (reader.Read())
        {
        for (int i = 0; i < reader.FieldCount; i++)
        {
            s += (reader.GetValue(i));
        }
        }
    }

    QuestionNum.Text = s;

I have not tested the code but I think it should work...

answered Apr 19 '15 at 15:48

Luca Schimweg

747
5
18

Thank you very much for the help as well, with the code you provided the label displays every single QuestionID do you know how to just make it so it shows the most recently added ID? – mot375 Apr 19 '15 at 16:06
This approach is wrong. What if there are many rows? How do you find the correct questionID? Looping over the whole table to find the last one added? And what if there are many concurrent users that add new records to this table? The only correct way to find the last IDENTITY value assigned is through the use of SCOPE_IDENTITY. See http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide – Steve Apr 19 '15 at 17:11

Can't seem to find how to take a value out of my table column and put it on a label C#

2 Answers2