c# – Assign a database value to variable

c# – Assign a database value to variable

Try something along this line – supply your own methods to create the connection.

static void Main(string[] args)
{
    string someText;

    using (var connection = new SqlConnection(...))
    { 
        // autodisposing if no longer needed
        using (var command = new SqlCommand(SELECT * FROM checkout))
        { 
            var msg = new List<string>(); // collects msg to avoid several updates to label
            // autodisposing ifnolonger needed
            using (var reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                { 
                    // if rows present, read one by one until done
                    while(reader.NextResult())
                    { 
                        // process one row 
                        var movieId = Convert.ToInt32(reader[MovieId]);
                        var subId = Convert.ToInt32(reader[SubscriberID]);
                        var dueDate = Convert.ToDateTime(reader[DueDate]);

                        // check due
                        if (DateTime.Now > dueDate)
                            msg.Add(SendLateMail(subId)); // send mail and store message
                    }

                    // make label text by joining all msg with newlines
                    someText = string.Join(n, msg); 
                }
                else
                {
                    // no rows found, set label text
                    someText = No movies due.;
                }
                // TODO: set your labels .Text
            }                 
        }
    } 
}

static string SendLateMail(int subId)
{
    // Todo: send mail 
    // create text to accumulate for label
    return $Due Email send to {subId};
}

Your code sample is not complete, but I was trying to see where in the code you are using Int to String conversion. Visual Studio should tell you the exact line the error is on.
I suspect the error is on line:

int movieId = one_data2.Rows[0][MovieID].ToString();

You cant reference the rows in the data reader – you have to open the data reader and start iterating thru them and access each rows columns.

I would recommend using Linq to SQL or Entity Framework to get the list of records into a anonymous class with a computed column:

var movies = (from m in ctx.checkouts
                      select new
                      {
                          MovieID = m.MovieID,
                          SubscriberID = m.SubscriberID,
                          DueDate = m.DueDate,
                          // Computed Column is line below
                          IsOverDue = (m.DueDate > DateTime.Now) ? NO : YES
                      }).ToList();

Then you can add an extra column to your grid and bind the movies list to your grid. Heres the new column to display the computed column:

        <asp:BoundField DataField=IsOverDue HeaderText=IsOverDue />

c# – Assign a database value to variable

I understand, after taking a look to your HTML and code that youre using ASP.Net Web Forms, right? If this is the case then you should try to parse the content of the cells in your gridview to the data types of your variables.
The GridViewRow has a collection of TableCells that can be acceded through the Cells property. The way to reference to the one row is your GridView is one_data2.Rows[0] and a way to assing the values to your variables could be:

 int movieId; 
 int.tryParse(one_data2.Rows[0].Cells[MovieID].Text, out movieId);
 string subId = one_data2.Rows[0][SubscriberID].Text;
 string dueDate = one_data2.Rows[0][DueDate].Text;
 string yourStringFormat =yyyy-MM-dd // or whatever format your GridView has
 DateTime lateDate;
 DateTime.TryParseExact(yourStringFormat, pattern, null, System.Globalization.DateTimeStyles.None, out lateDate);

Another way may be by accessing the rows dataItem but as you did not specify if your grid is databound.

Leave a Reply

Your email address will not be published. Required fields are marked *