Database

Richard MacCutchan19-Jan-11 22:00

Richard MacCutchan

19-Jan-11 22:00

You can do it in the initial string thus:

string myString = "The world\'s largest program";

or in your replace call thus:

myString = myString.Replace("'", "\\'");

In case 1 you need to escape the single quote with a backslash, and in case 2 you need to escape the backslash.

I must get a clever new signature for 2011.

phimix19-Jan-11 22:21

phimix

19-Jan-11 22:21

It's case 2 we need to go for (the myString is just an example that I created for my question, in reality it's a much more complex string)

But case 2 doesn't seem to work:

string myString = "The world's largest program";
myString = myString.Replace("'", "\\'");

string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());

Gives us the SQL string:

UPDATE myTable SET myColumn = 'The world\\'s largest program'

It has to end up like this to work (I have checked it by typing it in the program SQLyog (which give direct SQL access to a MySQL database):

UPDATE myTable SET myColumn = 'The world\'s largest program'

List of common misconceptions

Jörgen Andersson19-Jan-11 22:06

Jörgen Andersson

19-Jan-11 22:06

Use parameters instead.

Luc Pattyn20-Jan-11 0:27

Luc Pattyn

20-Jan-11 0:27

Once you got the single quote in the database, it does not make sense to replace it by some escape sequence; escape sequences are used in a programming language (such as SQL), not in the data itself. So use \' in the queries when you have to. And it is always better to use SqlParameter, you won't need escape sequences then at all.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Overwrite insipte of appending ?? why ??

RyanEK20-Jan-11 17:10

RyanEK

20-Jan-11 17:10

Use parameters instead to prevent SQL Injection

scorp_scorp19-Jan-11 21:04

scorp_scorp

19-Jan-11 21:04

Dear ALl,

I want to append an execel sheet to certain table in my ACCESS 2007 databas (VBA), through this:
Dim exceltable1 As TableDef
Dim strsql As String
Set classdb = CurrentDb()
Set exceltable1 = classdb.CreateTableDef("Temp")
exceltable1.Connect = "Excel 8.0;DATABASE=Path\template.xlsx"
exceltable1.SourceTableName = "template_table" '************ name definition of my ecxel records
classdb.TableDefs.Append exceltable1
Set rst = classdb.OpenRecordset("select * from Temp")
If Not (rst.BOF And rst.EOF) Then
strsql = "insert into tb_contract_details select * from Temp"
classdb.Execute strsql

The problem is: If am running this code twice for same records of the excel sheet say (10 records) then the records will be the same 10 records in the database table not 20 records , ie: it is overwriting records.
but if i change some values for some records in the excel sheet, it only inserts those changed values , while the rest are not inserted.

Any help, i need to insert the records of the excel sheet even if they already exist in the table ??? i need to append them to the existing table records??

Keep in mind that no primary key is set in the table, no restriction on records.

0 will always beats the 1.

jschell20-Jan-11 9:29

jschell

20-Jan-11 9:29

You have two data sources.

You cannot create SQL that references both data sources at the same time.

You must extract the data from one data source and then, in a different step, add it to the other data source.

scorp_scorp22-Jan-11 16:39

scorp_scorp

22-Jan-11 16:39

Thanks Man , but i didnt got it at all....

jschell wrote:
You have two data sources.

What are those two???

jschell wrote:
You must extract the data from one data source and then, in a different step, add it to the other data source.

Can you be more clear, what should i do ?? detailed plz ??

Am sorry i couldnt get it Frown | :(

0 will always beats the 1.

jschell23-Jan-11 10:46

jschell

23-Jan-11 10:46

scorp_scorp wrote:
What are those two???

You are getting the data from one place - that is data source one.
You are putting the data some place - that is data source two.

scorp_scorp wrote:
Can you be more clear, what should i do ?? detailed plz ??

1. Get data - put it in a collection of some sort.
2. Write the data - by using the collection.

Mail sending problem from sql server URGENT

scorp_scorp23-Jan-11 19:21

scorp_scorp

23-Jan-11 19:21

Thanks for your follow man, but seems that we r not on the same channel.. Confused | :confused:

jschell wrote:
You are putting the data some place - that is data source two.

Putting data in some place , could be considered as Data Source ??? i dont know D'Oh! | :doh:

jschell wrote:
1. Get data - put it in a collection of some sort.
2. Write the data - by using the collection.

U i tried to use 2 recordsets also didnt work, u mean that they should be done in two events ??

Any article?? any explanation ?? any examples ?? if you can provide .... most thanksfull Smile | :)

0 will always beats the 1.

Tridip Bhattacharjee19-Jan-11 20:06

Tridip Bhattacharjee

19-Jan-11 20:06

mail is configured in sql server and it sends mail properly but sometime when i send mail from sql server then i saw the message like "Mail queued." but i dont get the mail. so then i check the mail status by a sp like EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'; then i see the mail state is NOTIFIED. then i just stop and start the mail queue using sp called sysmail_stop_sp and sysmail_start_sp. it is often happening in my sql server mail is getting queued but not sending. i am not being able to catch the problem why often my sql server mail state becomes Notified. please help me that how could get come out from this problem.

thanks

tbhattacharjee

Must Declare Scalar variable

Ali Al Omairi(Abu AlHassan)19-Jan-11 11:17

19-Jan-11 11:17

Hey guys, i have wroted some sp's on some server and they used to work well on that server. then i took a backup of the database and restored it on another server so when i tried to run my sp's i got the exception "Must Declare Scalar Variable".

for instance:

Declare @BankName int;
Set @bankname = 1;

Results in:
Must Declare Scalar Variable @bankname.

any help. thank you.

Mycroft Holmes19-Jan-11 11:56

Mycroft Holmes

19-Jan-11 11:56

The SQL Server instance you restored to is set to case sensitive, therefore the you have not declared @bankname

Never underestimate the power of human stupidity
RAH

Ali Al Omairi(Abu AlHassan)19-Jan-11 12:55

19-Jan-11 12:55

ok Sir, i understand that but i want a solution.

Mycroft Holmes19-Jan-11 13:18

Mycroft Holmes

19-Jan-11 13:18

So think about what I said, your sql server settings need to be changed, go find the setting and change it. Note depending on what database you have installed you may need to reinstall the sql server instance to change that setting.

I suggest you prove that I have identified the problem first!

Never underestimate the power of human stupidity
RAH

Ali Al Omairi(Abu AlHassan)19-Jan-11 13:59

19-Jan-11 13:59

yes sir you have identified the problem.
My Database is MS SQL 2005 Developer Edition.
when I installed it in "Collation Sitting" step of the wisard i checed "Case - sensitive" checkbox.

Mycroft Holmes19-Jan-11 14:05

Mycroft Holmes

19-Jan-11 14:05

I think with 2005 you need to reinstall the instance, you may want to look into changing the collation settings but I don't think you can change the case setting.

Never underestimate the power of human stupidity
RAH

Ali Al Omairi(Abu AlHassan)19-Jan-11 14:13

19-Jan-11 14:13

Thank you, Sir.

100 Rose | [Rose]

Hiren solanki19-Jan-11 19:21

Hiren solanki

19-Jan-11 19:21

Ali Al Omairi wrote:
100

What it mean it's a virtual points to Mycroft or bless of 100 year to live. Unsure | :~

Regards,
Hiren.

be good(Help people in CP),do good(Vote if one finds helpful) all will happen good, In case happens bad(You are getting downvote for your best try to help OP) it will be good for later after(Countered with more points by humble member). - Gita sar in context of CP.

-So Guys don't care about downvote believe in you.

Ali Al Omairi(Abu AlHassan)20-Jan-11 7:40

20-Jan-11 7:40

Sir, In Jordan we use the expression "A hundred of flowers" often to say that every thing is ok. we also use it to thank people and to say hi to them.