How to write an update statement using parameters?

Question

I'm having trouble writing the update statement gathered from a few different classes. So let me explain what I have..

I have a Minutes.cs class which will get and set the attributes of the database

public class Minute{
public Minute()
{
    //
    // TODO: Add constructor logic here
    //
}

public int ID
{
    get;
    set;
}
public string Location
{ 
    get; set; 
}
public string PeriodDate
{
    get;
    set;
}
public string PeriodTime
{
    get;
    set;
}
public string Facilitator
{
    get;
    set;
}
public string Attenders
{
    get;
    set;
}

public string Agenda
{ 
    get; set; 
} 

public string Title
{ 
    get; set; 
}

public DateTime DateTime
{
    get;
    set;
}}

This is my MinuteDB.cs class which interacts with the database layer, and i want to pass the parameters (SAParameters) from this class to the aspx.cs page so I can do a parameterized update(the only reason is because I cant update a datetime format, so i have to use parameterised queries :S), and I don't know if I'm doing this right.

public class MinuteDB{
//string strConnString = ConfigurationManager.ConnectionStrings["emaDB2"].ConnectionString;
public static String DB_STR = ConfigurationManager.ConnectionStrings["emaDB2"].ConnectionString;
//public static String DB_STR = "UserID=dba;Password=sql;DatabaseName=emaDB;ServerName=emaDB";

public MinuteDB()
{

}

public static void DoUpdateQuery(String sql)
{
    //Create a connection, replace the data source name with the name of the SQL Anywhere Demo Database that you installed
    SAConnection myConnection = new SAConnection(DB_STR);
    //open the connection 
    myConnection.Open();

    //Create a command object. 
    SACommand myCommand = myConnection.CreateCommand();

    //Specify a query. 
    myCommand.CommandText = sql;


    SAParameter parameters = new SAParameter();
    parameters.SADbType = SADbType.NVarChar;
    myCommand.Parameters.Add(parameters);

    parameters = new SAParameter();
    parameters.SADbType = SADbType.NVarChar;
    myCommand.Parameters.Add(parameters);

    parameters = new SAParameter();
    parameters.SADbType = SADbType.NVarChar;
    myCommand.Parameters.Add(parameters);

    parameters = new SAParameter();
    parameters.SADbType = SADbType.NVarChar;
    myCommand.Parameters.Add(parameters);

    parameters = new SAParameter();
    parameters.SADbType = SADbType.NVarChar;
    myCommand.Parameters.Add(parameters);

    parameters = new SAParameter();
    parameters.SADbType = SADbType.NVarChar;
    myCommand.Parameters.Add(parameters);

    parameters = new SAParameter();
    parameters.SADbType = SADbType.Integer;
    myCommand.Parameters.Add(parameters);

    parameters = new SAParameter();
    parameters.SADbType = SADbType.DateTime;
    myCommand.Parameters.Add(parameters);

    Minute recentMinutes = new Minute();

    myCommand.Parameters[0].Value = recentMinutes.Title;
    myCommand.Parameters[1].Value = recentMinutes.Location;
    myCommand.Parameters[2].Value = recentMinutes.PeriodDate;
    myCommand.Parameters[3].Value = recentMinutes.PeriodTime;
    myCommand.Parameters[4].Value = recentMinutes.Attenders;
    myCommand.Parameters[5].Value = recentMinutes.Agenda;
    myCommand.Parameters[6].Value = recentMinutes.ID;
    myCommand.Parameters[7].Value = recentMinutes.DateTime;

    try
    {
        myCommand.ExecuteNonQuery();
    }

    catch (Exception excp)
    {
        throw excp;
    }

    finally
    {
        myConnection.Close();
    }

}
public static void UpdateMinutesByMinutesID(int minuteID, string location, string title, string perioddate, string periodtime, string attenders, string agenda,DateTime datetime)
{
    string sql = "UPDATE meetingMinutes SET title=?,location=?,perioddate=?,periodtime=?,attenders=?,agenda=?,datetime=? WHERE minuteID = " + minuteID;
    DoUpdateQuery(sql);
}

}

This is my aspx.cs class, where on click of a button, it will update to database

protected void btnUpdate_Click(object sender, EventArgs e)
{
    int minID = Convert.ToInt32(Session["minID"]);

    string dateAndTime = tbDatepicker.Text + " " + tbTimepicker.Text;
    CultureInfo provider = CultureInfo.InvariantCulture;
    DateTime theDateTime = DateTime.ParseExact(dateAndTime, "d MMMM yyyy hh:mm tt", provider);
    Minute min = new Minute();
    min.DateTime = theDateTime;

    try
    {
        MinuteDB.UpdateMinutesByMinutesID(minID,tbLocation.Text,tbTitle.Text,tbDatepicker.Text,tbTimepicker.Text,tbAttenders.Text,tbAgenda.Text,theDateTime);
    }


    finally 
    {
        btnUpdate.Attributes.Add("onclick", "displaySuccessfulUpdate();");
        Response.Redirect("HomePage.aspx");
    }




}

I don't know why I cannot get this to work. It prompts me an error, saying Column(minID) could not be found. Is it because I'm not passing the actual parameters to the method?? Can someone help me out?

PS: I'm using SQL Anywhere 12

score 0 · Accepted Answer · answered Jul 09 '12 at 03:36

I would suspect the column minID was not found, as reported. (There is a column called ID, but not minID according to the class definition above; a mismatch or just a harmless DAL mapping?)

I would not expect it to have anything to do with the parameters; the error is talking about the query shape and not the bound values (or lack thereof).

Consult the database (directly) to inspect/verify the table schema. Perhaps some code/sproc was not updated ..

Yep pst, you are right, I'm too tired that i overlooked the column name. Thanks for the reminder. — melvg, Jul 12 '12 at 03:03

How to write an update statement using parameters?

1 Answers1