Can someone please enlighten me on these codes?

Question

1.00/5 (1 vote)

See more:

string sqlCM = @"INSERT INTO Cancelled_Meeting (meeting_id, date, room_id, attendees_pax, start_time, end_time)
                           (SELECT * from meeting WHERE meeting_id = {1})";
           int db = DBUtl.ExecSQL(sqlCM, TxtMeetingID.Text, TxtDate.Text, DrpRoom.SelectedValue, DrpPax.SelectedValue, ti.Value, to.Value);

           if (db == 1)
           {
               LtlMsg.Text = "Added into Cancelled Meeting";
           }
           else
           {
               LtlMsg.Text = "Error " + DBUtl.DB_Message;
           }

What I have tried:

it keeps saying that "operand clash, int not compatible with date" the thing is I did not set a format for the date yet.

Posted 26-Jan-18 10:10am

dibdab

Updated 26-Jan-18 20:57pm

Add a Solution

Comments

j snooze 26-Jan-18 17:16pm

there is not enough info here. clearly some code somewhere that has been unprovided is trying to put a date you sent into an integer field somewhere.
We don't know the layout of the cancelled_meeting table or the meeting table. According to your SQL all you need is a meeting id as parameter {1}, yet your Dbutl.Exec you send every value ? This code makes no sense at all in its current state.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-01-26T20:10:00

Start by never using SELECT * FROM as an SQL query - particularly when you need a specific order to your values. Always list the column names, or the rows returned may not be in the order you expect, and then you get type conflicts.
Try:

SQL

INSERT INTO Cancelled_Meeting (meeting_id, date, room_id, attendees_pax, start_time, end_time)
SELECT meeting_id, date, room_id, attendees_pax, start_time, end_time from meeting 
WHERE ...

But ... I worry about that "{0}" at the end - it implies you are going to concatenate strings to form the final SQL statement in your ExecSQL method, and that's very dangerous - it leaves your code wide open to SQL Injection which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?