CODEWITHSUNDEEP
c#sqldatetimesql-insertdatetimepicker
user11703890
user11703890

Reputation: 25

Can we insert some data along with insert value select query in c# winform

I want to insert the date and month (which is in two datetimepicker) along with insert value select query.

I have five columns in my invoice table

Student_id, Amount, Fee_description, issue_date, month

I can insert the values for the first three columns but the remaining two are null for which I don't know where to put the datetimepicker value??

I take a datatimepicker for date and month in the design view of the form

insert into invoice (Student_id, Amount, Fee_description, issue_date, month)
    select 
        student.Student_id, 
        Fee.Fee_Amount, Fee.fee_type
    from 
        student
    join 
        parent on student.father_nic = parent.father_nic
    join 
        allocate_class on student.Student_id = allocate_class.Student_id
    join 
        class on class.class_id = allocate_class.class_id
    join 
        session on allocate_class.session_id = session.session_id
    join 
        Fee on class.class_id = Fee.class_id
    where 
        session.session_id = 1
        and Fee.fee_type = 'Tution Fee'
        and student.status = 'active'

Where to add that two that datetimpicker value in the above query?

Upvotes: 0

Views: 332

Answers (1)

Caius Jard
Caius Jard

Reputation: 74730

Sure. It would look something like this:

var cmd = new SqlCommand("insert into invoice (
    Student_id,
Amount,
Fee_description,
issue_date,
month
)
select student.Student_id
 , Fee.Fee_Amount
 , Fee.fee_type

 , @issDat
 , @mon

  from ...", "conn str here");


cmd.Parameters.AddWithValue("@issDat", issueDateDateTimePicker.Value);
cmd.Parameters.AddWithValue("@mon", monthDateTimePicker.Value);

I've used AddWithValue to quickly explain the concept- google for a blog called "can we stop using AddWithValue already" for a long discussion on how to move away from it(it's reasonable in this context as it's not being used for filtering) but for now the concept I'm trying to relate is:

An sql statement can take a fixed value from you:

SELECT 'hello' FROM table

It can also take a fixed parameter you supply:

SELECT @pHello FROM table

Hence adding parameters and filing them with fixed values from your day time pickers (or datetime.Now or whatever) is fine, and what you should be doing to insert fixed values using an INSERT ... SELECT style statement

Side note, it isn't clear if month and issue date are related but if they're the same date then you don't need to store it twice- you can extract the month from a date at any point.

Upvotes: 1

Related Questions