C# Data Access Issue

[Guest]

Hi All,
I was wondering if any one knew how to create INSERT statement using the OleDbDataCommand, that includes single quotes in the data that is entered in the table fields.
e.g.

string fieldval = "Some Data with single's quotations";
OledbCommand cmd = new OledbCommand("INSERT INTO Table (SomeStringField) VALUES ('"+ fieldval +"')", OleDbConnection);

Now as indicated in the above statement the fieldval single quotation causes the OleDbCommand to throw an error.
Whats the way around to INSERT data into a string Field, when the data itself has single quotes embedded in it.

With Regards
Thanks

 

[Nicholas Paldino [.NET/C# MVP]]

Sunny,

You should not do this. Instead, use a parameterized command. You
basically set the text to this:

"insert into table (somestringfield) values (?)"

Then, use the CreateParameter method to create a parameter. For the
Ole-Db provider, the order you create the parameters in is the order that
you will have to make the calls to CreateParameter. Once you have the
parameter, and add it to the Parameters collection, just set the value on
the parameter, and execute. The provider will take care of everything for
you.

This also adds some security, preventing injection attacks as well.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi All,
I was wondering if any one knew how to create INSERT statement using the

OleDbDataCommand, that includes single quotes in the data that is entered in
the table fields.

e.g.

string fieldval = "Some Data with single's quotations";
OledbCommand cmd = new OledbCommand("INSERT INTO Table (SomeStringField)

VALUES ('"+ fieldval +"')", OleDbConnection);

Now as indicated in the above statement the fieldval single quotation

causes the OleDbCommand to throw an error.

Whats the way around to INSERT data into a string Field, when the data

itself has single quotes embedded in it.

 

[Bill Styles]

Hi All,
I was wondering if any one knew how to create INSERT statement using
the OleDbDataCommand, that includes single quotes in the data that is
entered in the table fields. e.g.

string fieldval = "Some Data with single's quotations";
OledbCommand cmd = new OledbCommand("INSERT INTO Table
(SomeStringField) VALUES ('"+ fieldval +"')", OleDbConnection);

Now as indicated in the above statement the fieldval single quotation
causes the OleDbCommand to throw an error. Whats the way around to
INSERT data into a string Field, when the data itself has single
quotes embedded in it.

fieldval.Replace("'","''")

 

[Nicholas Paldino [.NET/C# MVP]]

Sunny,

I am curious, how does the requirement affect the proper use of the
technology? Also, using that piece of code takes maybe an extra 30 seconds
of typing? I don't understand why you wouldn't use it. The whole point of
the provider is to make sure that the statements are passed in the correct
manner, relieving the programmer from having to do it themselves.

Why would you not use it?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thank you very much,
I have worked with the Parameters collection and i understand their

importance and value, however this particular problem is for our office
databases only and the reqs do not justify parameters use and so forth,
since it is a short term requirement ...

 

[Jon Skeet [C# MVP]]

I have worked with the Parameters collection and i understand their
importance and value, however this particular problem is for our
office databases only and the reqs do not justify parameters use and
so forth, since it is a short term requirement ...

Parameters are *absolutely* the way to go here though - you'll end up
spending more time sorting out the various quoting rules than you would
getting the parameters right in the first place, and then you don't
have any of the security worries that you would have otherwise.

 

[Guest]

ok here is the reason for not doing it through parameters, the original problem is this ...

I am working on a Data Project, that involves records (rows) to be inserted from a Table in one database to an exactly similar table in another database. The table names and all field names and values are exactly same.
I am looking for Mass insertions or should i say, the easiest way to insert these records. What are my options? i don't want to use the individual OleDbCommand insert statements since that is a rather slow process.
Also the need to use DataAdapter and the schema details is not the best option since both the tables are exactly same.
However Database 1 is access and the Database 2 is SQL server. (So the connections are different and so on....)
Here is a little desc of the prob

Database1 (TableClientDetials) ----------- > (Insert all Records to) -------> Database2 (TableClientDetails)
From (Access) (On SQL Server)

 

[Guest]

Ok guys I give up, i mean i understand why everyone is stressing upon the need for Parameters
So i am using Parametric command now. Thanks for all the help

I really really appreciate it

With Regard
Syed

 

[Ron Allen]

Sunny,
Any reason not to use DTS on SQL Server to do the import? It would be a
lot faster than reading them into memory and flushing them back out.
Assuming that there were no constraint or data errors this should work very
quickly.

Ron Allen

ok here is the reason for not doing it through parameters, the original problem is this ...

I am working on a Data Project, that involves records (rows) to be

inserted from a Table in one database to an exactly similar table in another
database. The table names and all field names and values are exactly same.

I am looking for Mass insertions or should i say, the easiest way to

insert these records. What are my options? i don't want to use the
individual OleDbCommand insert statements since that is a rather slow
process.

Also the need to use DataAdapter and the schema details is not the best

option since both the tables are exactly same.

However Database 1 is access and the Database 2 is SQL server. (So the

connections are different and so on....)

Here is a little desc of the prob

Database1 (TableClientDetials) ----------- > (Insert all Records

to) -------> Database2 (TableClientDetails)

From (Access)

(On SQL Server)