i have these two tables connected to each other

tblOrder
ID - int (P.K.)
DateTime - datetime
TotalPrice - money
StaffID - int
OrderTypeID - int
ReservationID - int (F.K.) can be null
Paid - bit


tblReservation
ID - int (P.K.)
DateTimeReservation - datetime
NumberOfPeople - int
DateTimeOfEvent - datetime
EventTypeID - int
Done - bit
CustomerID - int
StaffID - int
Cancelled - bit


Now in tblOrder reservationID can be null.. I did the following statement but still it is not working can u help pls?

public bool AddOrder(Order o)
        {
            try
            {
                conn.Open();
                SqlCommand comm;
                if (o.ReservationID == 0)
                {
                    comm = new SqlCommand("INSERT INTO tblOrder VALUES ('" + o.DateTime.ToString("MM/dd/yyyy HH:mm:ss") + "','" + o.TotalPrice + "','" + o.StaffID + "','" + o.OrderTypeID + "','" + null + "','" + o.Paid + "')", conn);
                }
                else
                {
                    comm = new SqlCommand("INSERT INTO tblOrder VALUES ('" + o.DateTime.ToString("MM/dd/yyyy HH:mm:ss") + "','" + o.TotalPrice + "','" + o.StaffID + "','" + o.OrderTypeID + "','" + o.ReservationID + "','" + o.Paid + "')", conn);
                }

                comm.ExecuteNonQuery();
                return true;
            }

            catch (Exception e)
            {
                 MessageBox.Show(e.Message.ToString());
                return false;
            }

            finally
            {
                conn.Close();
            }
        }

Edited 5 Years Ago by xanawa: n/a

Try to create parameters, rather then passing values in the query it self, using sqlCommand:

void InsertMethod(string a, int reservation)
        {
            using (SqlConnection sqlConn = new SqlConnection("connString"))
            {
                string sqlQuery = String.Format(@"INSERT INTO tblOrders VALUES (A = @a, B = @b)");
                SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                cmd.Parameters.Add("@a", SqlDbType.VarChar, 50).Value = a; //always present!
                cmd.Parameters.Add("@b", SqlDbType.Int).Value = reservation != 0 ? (object)reservation : DBNull.Value;
                try
                {
                    sqlConn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

Try to create parameters, rather then passing values in the query it self, using sqlCommand:

void InsertMethod(string a, int reservation)
        {
            using (SqlConnection sqlConn = new SqlConnection("connString"))
            {
                string sqlQuery = String.Format(@"INSERT INTO tblOrders VALUES (A = @a, B = @b)");
                SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                cmd.Parameters.Add("@a", SqlDbType.VarChar, 50).Value = a; //always present!
                cmd.Parameters.Add("@b", SqlDbType.Int).Value = reservation != 0 ? (object)reservation : DBNull.Value;
                try
                {
                    sqlConn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

do u mean i just add the reservationID this way? or all the values?

This article has been dead for over six months. Start a new discussion instead.