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

Question

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?

Caius Jard · Accepted Answer · 2019-10-05 06:15:35Z

1

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.

edited Oct 5, 2019 at 6:15

answered Oct 5, 2019 at 6:10

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user11703890 Over a year ago

hurrah u nailed it......... Thanks you much but i have one issue ...........on form load i changed the month datetimepicker value to monthDateTimePicker.Format = DateTimePickerFormat.Custom; monthDateTimePicker.CustomFormat = "MMMM-yyyy"; ............. your one cmd.Parameters.AddWithValue("@mon", monthDateTimePicker.Value); it store the date and time also

Caius Jard Over a year ago

Not quite sure how that's an issue but you should know that there isn't any such thing as "a date that is only a month, and not a day/time too" - dates always represent a specific moment in time, down to fractions of a second. If you want to represent just a month you either ignore the day part when you process it or you store some common value for the day and time, like 01 for the day and 00:00 for the time, so your March 2001 date in the db is 2001-03-01 00:00 and your April is 2001-04-01 00:00 etc that you always cut off

Caius Jard Over a year ago

This is why I said if issue date and month are th same, just store the issue date as 2001-04-01 12:34:56.789 and when you want the month for display on a report or whatever, format it as yyyy-MMMM so it becomes just "2001-April"

user11703890 Over a year ago

Thank you so much.. you solve my problem.. really salute to you

Caius Jard Over a year ago

You can fix your @mon with something like var d = monthDateTimePicker.Value; cmd.Parameters.AddWithValue("@mon", new DateTime(d.Year, d.Month, 1)) - it pulls just the year and the month and fixes the day at 1 (time will be midnight)

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related