Continuing from my previous program,the code now can update but can't insert.There will be an error on ExecuteQuery.The program is suppose to identify if the id exist or not.You can probably tell from the coding.
Trying to insert but i'm not sure if i set dateID parameter correctly to check if there's a value.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
namespace ExecuteStoredProcedure
{
class Program
{
private int _dateID;
private int _dateTypeID;
private DateTime _date;
private string _name;
private string _notes;
public Program()
{
_dateID = 0;
}
public Program(int dateID)
{
_dateID = dateID;
}
public Program(int datetypeID,DateTime date,string name,string notes){
_dateTypeID = datetypeID;
_date = date;
_name = name;
_notes = notes;
}
public int dateID
{
get
{
return _dateID;
}
set
{
_dateID = value; //IS THIS CORRECT?
}
}
public int dateTypeID
{
get { return _dateTypeID; }
set { _dateTypeID = value; }
}
public DateTime date
{
get {return _date ;}
set { _date = value;}
}
public string name
{
get { return _name;}
set { _name = value; }
}
public string notes
{
get { return _notes;}
set { _notes = value; }
}
public void LoadData(int dateID)
{
SqlConnection conn = new SqlConnection("somedb");
conn.Open();
SqlCommand command = new SqlCommand("p_Date_sel", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@DateID", dateID);
//command.Parameters.Add("@DateID", SqlDbType.Int);
//command.Parameters["@DateID"].Value = dateID;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
_dateTypeID = Convert.ToInt16(reader["DateTypeID"]);
_date = Convert.ToDateTime(reader["Date"]);
_name = reader["Name"].ToString();
_notes = reader["Notes"].ToString();
}
reader.Close();
conn.Close();
}
public void Save(int dateID)
{
//for update
if (_dateID > 0)
{
SqlConnection conn = new SqlConnection("somedb");
SqlCommand command = new SqlCommand("p_Date_update", conn);
command.Parameters.AddWithValue("@DateID", dateID);
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter();
//Do i need to use a reader and while loop here?
{
command.Parameters.AddWithValue("@DateTypeID", dateTypeID);
command.Parameters.AddWithValue("@Date", date);
command.Parameters.AddWithValue("@Name ", name);
command.Parameters.AddWithValue("@Notes ", notes);
}
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
else if (_dateID == 0)
{
SqlConnection conn = new SqlConnection("somedb");
SqlCommand command = new SqlCommand("p_Date_ins", conn);
command.Parameters.AddWithValue("@DateID", dateID);
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter();
//Do i need to use a reader and while loop here?
command.Parameters.Add("@DateTypeID", SqlDbType.VarChar).Value = dateTypeID;
command.Parameters.Add("@Date", SqlDbType.VarChar).Value = date;
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = name;
command.Parameters.Add("@Notes", SqlDbType.VarChar).Value = notes;
conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();
}
}
}
}My Main Program
namespace ExecuteStoredProcedure
{
class TestClass
{
public static void Main(string[] args)
{
p.LoadData(73);
p.date = DateTime.Parse("2020-2-20");
p.dateTypeID = 456;
p.name = "Mr James";
p.Save(73);
Console.WriteLine("DateID = " + "" + p.dateID.ToString());
Console.WriteLine("DateTypeID = " + "" + p.dateTypeID.ToString());
Console.WriteLine("Date = " + "" + p.date.ToString());
Console.WriteLine("Name = " + "" + p.name.ToString());
Console.WriteLine("Notes = " + "" + p.notes.ToString());
Console.ReadLine();
}
}
}