|
|
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.
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.
|
|
|
|
|
Use parameters instead to prevent SQL Injection
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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  
0 will always beats the 1.
|
|
|
|
|
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.
|
|
|
|
|
Thanks for your follow man, but seems that we r not on the same channel.. 
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 
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
0 will always beats the 1.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
ok Sir, i understand that but i want a solution.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Thank you, Sir.
100 ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Ali Al Omairi wrote: 100
What it mean it's a virtual points to Mycroft or bless of 100 year to live.
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.
|
|
|
|
|
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.
|
|
|
|
|
Interesting cultural tidbit, Ali. I never know what I'm going to learn when I stop by CodeProject. 
Will Rogers never met me.
|
|
|
|
|
That's good to know, Ali.
Thanks 100 to you too for that.
Regards,
Hiren.
-"I don't know, I don't care, and it doesn't make any difference".
|
|
|
|
|
Sir, Since you told me so i was trying to reinstall the software
but i had some problem with some components
1- MSXML6
2- SQL Server Database Services
what can be the reson
Call me "Abu AlHassan", Please.
|
|
|
|
|
I am currently writing a stored proc for a database that will help create a new user. The logic I am using is something I had used before, but then I had used three separate stored procs and thought it would be more effecient to just call one stored proc to handle the whole job. Here are the tables I am using:
users:
userID (primary key, IsIdentity turned on)
firstName
lastName
sex
authenticationID (foreign key that points back to the authentication table)
contactID (foreign key that points back to the contact table)
authentication:
authenticationID (primary key, IsIdentity turned on)
userName
password
contact:
contactID (primary key, IsIdentity turned on)
street
city
state
emailAddress
In my stored proc, I pass in all of the parameters needed for each table (listed above, except, of course, the identity fields) and start the inserts. Since I need the contact and authentication inserts done first, I naturally would want to start with those tables, but how would I get the ID fields for each tables so I can insert them into the users table?
|
|
|
|
|
The sproc could return it using a output[^]-parameter. Or you could write a function instead of an sproc, and have that return the identity. Or you check the latest identity values for your transaction
I are Troll 
|
|
|
|
|
Use the @@IDENTITY function within the SP
|
|
|
|
|
Nevermind. Did a little bit of experimentation and got it to work. Thanks for all the input though.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
