locked
MYSQL RRS feed

  • Question

  • User-1596888384 posted

    Can someone help me with my code? I tried to solve it for hours but just couldnt get it right!

    protected void BtnEdit_Click(object sender, EventArgs e)
            {
                string sql = @"UPDATE Meeting SET date = {1},
                                                room_id = {2}
                                                attendees_pax = '{3}',
                                                start_time = '{4}',
                                                end_time = '{5}',
                                                where meeting_id = {0}";
    
                int rowsAffected = DBUtl.ExecSQL(sql,
                                                        TxtDate.Text,
                                                        DrpRoom.SelectedValue,
                                                        DrpPax.SelectedValue,
                                                        GetSelectedTimeIn,
                                                        GetSelectedTimeOut);
    
    
                if (rowsAffected == 1)
                {
                    Response.Redirect("CurrentBooking.aspx");
                }
                else
                {
                    LtlMsg.Text = "Error";
                }
            }

    I kept getting 2 errors;

    1. 

    An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code. Additional information: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

    2. An exception of type 'System.NullReferenceException' occurred in  System.dll but was not handled in user code Additional information: Object reference not set to an instance of an object.

    Friday, January 19, 2018 2:05 PM

All replies

  • User475983607 posted

    Can someone help me with my code?

    It's tough as you did not post the code causing the exception. 

    An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code. Additional information: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

    Most likely an error in the DBUtl.ExecSQL() method.  Set a breakpoint and debug the code.  This link explains how to use the visual Studio debugger https://msdn.microsoft.com/en-us/library/y740d9d3.aspx

    2. An exception of type 'System.NullReferenceException' occurred in  System.dll but was not handled in user code Additional information: Object reference not set to an instance of an object.

    Generally, this error shows the line of code that caused the error.  

    Friday, January 19, 2018 2:28 PM
  • User-1596888384 posted

    post the code causing the exception ? 

    it was the SQL code that was getting the error.. I cannot attach a picture.. so .. 

    Friday, January 19, 2018 3:25 PM
  • User475983607 posted

    Leynnn

    post the code causing the exception ? 

    it was the SQL code that was getting the error.. I cannot attach a picture.. so .. 

    There is no reason to post a picture just post the source code.

    As stated above the error must be coming from this line of code. 

    int rowsAffected = DBUtl.ExecSQL(sql,
                                                        TxtDate.Text,
                                                        DrpRoom.SelectedValue,
                                                        DrpPax.SelectedValue,
                                                        GetSelectedTimeIn,
                                                        GetSelectedTimeOut);

    Which means the error probably starts within the custom data access layer DBUtl.ExecSQL().  And the error indicates that there is probably an string interpolation bug.

    Visual Studio has a very powerful debugger which allows you to set a breakpoint and step through each line of code.  The previous link explains how to use the debugger.  Please take a moment to read the doc and give it a try.

    If you need help finding the bug, then post the DBUtl.ExecSQL() source code but it is best if you learn how to use the debugger.

    Friday, January 19, 2018 3:46 PM
  • User-1596888384 posted
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    
    public class DBUtl
    {
    
       ////
       //// "dbstr" must exist in web.config to determine which database to connect
       ////
       private static string DB_CONNECTION =
          ConfigurationManager.ConnectionStrings["dbstr"].ConnectionString;
    
       public static string DB_Message;
    
       public static DataTable GetTable(string sql, params object[] list)
       {
          List<String> escParams = new List<String>();
          foreach (object o in list)
          {
             escParams.Add(EscQuote(o.ToString()));
          }
          string escSQL = String.Format(sql, escParams.ToArray());
    
          DataTable dt = new DataTable();
          using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))
          using (SqlDataAdapter dAdptr = new SqlDataAdapter(escSQL, dbConn))
          {
             try
             {
                dAdptr.Fill(dt);
                return dt;
             }
    
             catch (System.Exception ex)
             {
                dt.Columns.Add("EXCEPTION", typeof(string));
                dt.Columns.Add("SQL", typeof(string));
                DataRow row = dt.NewRow();
                row[0] = ex.Message;
                row[1] = sql;
                dt.Rows.Add(row);
                return dt;
             }
          }
       }
    
       public static int ExecSQL(string sql, params object[] list)
       {
          List<String> escParams = new List<String>();
          foreach (object o in list)
          {
             escParams.Add(EscQuote(o.ToString()));
          }
          string escSQL = String.Format(sql, escParams.ToArray());
    
          int rowsAffected = 0;
          using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))
          using (SqlCommand dbCmd = dbConn.CreateCommand())
          {
             try
             {
                dbConn.Open();
                dbCmd.CommandText = escSQL;
                rowsAffected = dbCmd.ExecuteNonQuery();
             }
    
             catch (System.Exception ex)
             {
                DB_Message = ex.Message;
                rowsAffected = -1;
             }
          }
          return rowsAffected;
       }
    
       public static string EscQuote(string line)
       {
          return line.Replace("'", "''");
       }
    }
    

    I think you meant this? so sorry I am not very familiar with these, but i need to solve this ASAP. This is the code that was given to me in my classes and we were not taught to change these code.

    Saturday, January 20, 2018 6:52 AM
  • User475983607 posted

    You're passing 5 parameters but the string interpolation expects 6 and DBUtl.ExecSQL() just takes the param array as is.  Consider refactoring the code to use the following syntax which will simply things.  This syntax requires .NET 4.5+

    string sql = $"UPDATE Meeting SET date = {TxtDate.Text}, room_id = {DrpRoom.SelectedValue}, attendees_pax = '{DrpPax.SelectedValue}', start_time = '{GetSelectedTimeIn}', end_time = '{GetSelectedTimeOut}', where meeting_id = {theId}"

    Null reference errors are very common.  The code does not guard against a null references.  For example, the code takes the user input as is .  Properties or fields are being passed but there is no indication in the code if there fields are set.

    These types of error are extremely easy to find using the Visual Studio debugger.  I strongly suggest that you learn how to use the Visual Studio tools chain.  Otherwise, you'll keep asking the same questions over and over.  Also learn how to unit test code.  Unit testing will force you to write better code.

    https://docs.microsoft.com/en-us/visualstudio/test/getting-started-with-unit-testing

    Saturday, January 20, 2018 1:51 PM