C# insert statement

[Graduate]

Hi Guys,

I need your help as in yesterday this deadline was last week already but I'm still struggling.

I want to ask if its possible to have an insert statement with select statements?exam ple I want to insert dataExtracted, dateFrom, dateTo,image but the dateFrom and dateTo is in another query how do I pass them to the insert statement :-(
this is what I have:


string query1 = @"select DATEADD(d,+1,DA TEADD(mm, DATEDIFF(m,0,GE TDATE())-6,-1)) as DateFrom
from dbo.tbl_sale_da te";

string query2 = @"select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GE TDATE())+1,0)) as DateTo
from dbo.tbl_sale_da te";

ConnectionStrin g = Properties.Sett ings.Default.Co nnectionString;

SqlConnection con = new SqlConnection(C onnectionString );
{

string path = @"C:\\DownLoade dFile.zip";
byte[] bytearr = File.ReadAllByt es(path);

SqlCommand cmd = new SqlCommand("pro cInsertToBlobDa ta", con);
cmd.CommandType = CommandType.Sto redProcedure;


cmd.Parameters. Add(new SqlParameter("@ DateExtracted", DateTime.Now));
cmd.Parameters. Add(new SqlParameter("@ DateFrom", SqlDbType.DateT ime));
cmd.Parameters. Add(new SqlParameter("@ DateTo", SqlDbType.DateT ime));
cmd.Parameters. Add(new SqlParameter("@ Data", bytearr));
cmd.Parameters. Add(new SqlParameter("@ DBParentCategor yKey", SqlDbType.DateT ime));
}

[Christian Binder]

You have to execute your first two statements to get the values.

Before you do SqlCommand cmd = new SqlCommand("pro cInsertToBlobDa ta", con);
, you can do the following:

SqlCommand cmdDateFrom = new SqlCommand(query1, con);
SqlCommand cmdDateTo = new SqlCommand(query2, con);

DateTime dtFrom = (DateTime)cmdDateFrom.ExecuteScalar();
DateTime dtTo = (DateTime)cmdDateTo.ExecuteScalar();

ExecuteScalar returns the value of the first row of the first column of the result-set. (see msdn)

After doing that, you can use dtFrom and dtTo as parameters for your insert-query.

[Graduate]

ExecuteScalar Error

Hi,

Many thanks for your reply but when I run the application I keep getting this error : ExecuteScalar requires an open and available Connection. The connection's current state is closed.

What am I missing here :-(


ConnectionStrin g = Properties.Sett ings.Default.Co nnectionString;

SqlConnection con = new SqlConnection(C onnectionString );
{

string query1 = @"select DATEADD(d,+1,DA TEADD(mm, DATEDIFF(m,0,GE TDATE())-6,-1)) as DateFrom
from dbo.tbl_sale_da te";

string query2 = @"select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GE TDATE())+1,0)) as DateTo
from dbo.tbl_sale_da te";

string path = @"C:\\DownLoade dFile.zip";
byte[] byteArray = File.ReadAllByt es(path);

SqlCommand cmdDateFrom = new SqlCommand(quer y1, con);
cmdDateFrom.Com mandType = CommandType.Sto redProcedure;

SqlCommand cmdDateTo = new SqlCommand(quer y2, con);
cmdDateTo.Comma ndType = CommandType.Sto redProcedure;

DateTime dtFrom = (DateTime)cmdDa teFrom.ExecuteS calar();
DateTime dtTo = (DateTime)cmdDa teTo.ExecuteSca lar();


SqlCommand cmd = new SqlCommand("pro cInsertToBlobDa ta", con);
cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add(new SqlParameter("@ DateExtracted", DateTime.Now));
cmd.Parameters. Add(new SqlParameter("@ DateFrom", SqlDbType.DateT ime));
cmd.Parameters["@DateFrom"].Value = dtFrom;

cmd.Parameters. Add(new SqlParameter("@ DateTo", SqlDbType.DateT ime));
cmd.Parameters["@DateTo"].Value = dtTo;
cmd.Parameters. Add(new SqlParameter("@ Data", SqlDbType.Image ));
cmd.Parameters["@Image"].Value = byteArray;
cmd.Parameters. Add(new SqlParameter("@ DBParentCategor yKey", SqlDbType.Int)) ;
//cmd.Parameters["@DBParentCateg oryKey"].Value = cagory.Category ID;
try
{

con.Open();
cmd.ExecuteNonQ uery();
cmdDateTo.Execu teNonQuery();
cmdDateFrom.Exe cuteNonQuery();
}

catch (Exception ex)
{
throw ex;
}
}

[Bassem]

Hi,

Aren't cmdDateFrom and cmdDateTo sql commands? You should open the connection then execute them, just like try catch you did at the end of your code.

[b]con.Open();[/b]

DateTime dtFrom = (DateTime)cmdDateFrom.ExecuteScalar();
DateTime dtTo = (DateTime)cmdDateTo.ExecuteScalar();

[Graduate]

Thank you so much, I saw what was missing.