CODEWITHSUNDEEP
c#mysql
PAVITRA
PAVITRA

Reputation: 861

mysql insert a query into a table column

I'm creating a system log functionality.
What I basically need to do is add the relevant insert,update query to a separate table with date time and user. My question is when i'm trying to insert an insert query as a row data I get an error. I understand this is because of the " ' " in the query.But I still need to re run those queries again.So removing the " ' " is not an option for me.

Following is the query I'm trying to insert:

insert into querylog (query,systemTime,user) 
values 
('INSERT INTO invoice(invoiceno,invoicenote,invoicetotal,nettotal,invoicedate,customer,receivedby,vehicleno) VALUES ('I 501','3223',15000,15000,'2013-12-06','C 116','','-')',
'12/6/2013 10:35:56 PM',
'Lakmal')

Upvotes: 0

Views: 425

Answers (3)

Guntram Blohm
Guntram Blohm

Reputation: 9819

I agree with Grant Winney, change the apostrophes to double apostrophes.

Or, first prepare your statement, then execute it with the values as parameter. See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare%28v=vs.110%29.aspx for an example. The big advantage is that you can put your strings into Command.Parameters unmodified, and there's just no way of anything in your Sql statement string breaking the syntax of the insert command.

Upvotes: 0

user2999920
user2999920

Reputation: 263

You're starting your single quotes at the beginning of the values.

You then end them halfway through it.

Replace:

values ('INSERT INTO invoice(invoiceno,invoicenote,invoicetotal,nettotal,invoicedate,customer,receivedby,vehicleno) VALUES ('I 501','3223',15000,15000,'2013-12-06','C 116','','-')','12/6/2013 10:35:56 PM','Lakmal')

with:

values ("INSERT INTO invoice(invoiceno,invoicenote,invoicetotal,nettotal,invoicedate,customer,receivedby,vehicleno) VALUES ('I 501','3223',15000,15000,'2013-12-06','C 116','','-')','12/6/2013 10:35:56 PM','Lakmal")

Upvotes: 1

Grant Winney
Grant Winney

Reputation: 66449

Try doubling up on the apostrophes (two single apostrophes, not a double-quote) inside that inner "insert" statement:

insert into querylog (query,systemTime,user) 
values 
('INSERT INTO invoice(invoiceno,invoicenote,invoicetotal,nettotal,invoicedate,customer,receivedby,vehicleno) VALUES (''I 501'',''3223'',15000,15000,''2013-12-06'',''C 116'','''',''-'')',
'12/6/2013 10:35:56 PM',
'Lakmal')

Upvotes: 2

Related Questions