|
I need some assistance in accommodating an apostrophe in an SQL statement using VBA in MS Access. Please help.
<br />
strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br />
"[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br />
"[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br />
"WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br />
"AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br />
"AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br />
"AND [ALT_GROUPING]='" & UCase(rstInputFile![ALT_GROUPING]) & "' " & _<br />
"AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br />
"AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br />
"AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br />
"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'"<br />
What do I need to do to prevent it from hanging up on the apostrophe? The apostrophe needs to be accepted in:
<br />
"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" <br />
modified on Wednesday, December 3, 2008 11:31 AM
|
|
|
|
|
You need to replace the apostrophy with 2 apostrophies. Best way is probably through a function (although I'm not sure of the exact syntax for VBA) - something like :
Public Function MakeSqlSafe(strData) as string
Return strData.Replace("'", "''")
end function
and then pass each one of your inputs through the function, e.g.
'" & MakeSqlSafe(UCase(rstInputFile![REGION])) & "'
Hope this helps.
|
|
|
|
|
Thank you, liqz for your assistance.
The item that I have encountered in the implementation in the temporary copy of the application is: "Return without GoSub".
The code is as follows in VBA:
<br />
Public Function MakeSqlSafe(strData) As String<br />
strNewStrData = Replace(strData, "'", "''")<br />
Return<br />
End Function<br />
<br />
strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br />
"[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br />
"[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br />
"WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br />
"AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br />
"AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br />
"AND IsNull([ALT_GROUPING]) " & _<br />
"AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br />
"AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br />
"AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br />
"AND [JOB_FUNCTION]='" & MakeSqlSafe(UCase(rstInputFile![JOB_FUNCTION])) & "'"<br />
Any assistance in resolving this item would be greatly appreciated!!!
|
|
|
|
|
Ah, try changing the function to :
Function MakeSqlsafe(strData)
MakeSqlSafe = Replace(strData, "'", "''")
end Function
|
|
|
|
|
It worked!!! Woo Hoo!!!
Thank you liqz, for your assistance in resolving this item!!!
|
|
|
|
|
Although replacing single apostrophe with double apostrophes will correct your problem, I think you should use parameters instead. Concatenating literal strings leaves you open to sql injections. Also using parameters gives a performance advantage.
|
|
|
|
|
Hi, below is my database schema and some of my sample data
patient(patientID,name,email, address)
visitentry(visitentryID,medicalnotes, patientID)
symptom(symptomID,name,description)
visitentrysymptom(symptomentryID, patientID, symptomID,date)
Patient Table
1 || myname || email@email.com || blk 123 tampines
2 || myname2 || another@email.com || blk 543 pasir ris
Visit Entry Table
1 || high fever 40degree || 1
Symptom Table
1 || fever || feeling warm in body
2 || bleeding || red substance
3 || flu || sneezing with mucas
Visit Entry Symptom Table
1 || 1 || 2 || 02/12/2008
2 || 1 || 3 || 02/12/2008
Okay so what i wan to do now is to Count the number of fever, bleeding and flu base on the patient address (using the LIKE to extract TAMPINES) and the current month
I came up with this statement
SELECT count(visitentrysymptom.symptomID)
FROM symptom inner join visitentrysymptom
on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in ('fever')
But my statement does not check the patient address(TAMPINES) and the current month
|
|
|
|
|
SELECT count(visitentrysymptom.symptomID)
FROM symptom
inner join visitentrysymptom on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in ('fever')
and visitentrysymptom.patientID in (select patientID from patient where address like '%TAMPINES%')
Keep It Simple Stupid! (KISS)
|
|
|
|
|
THANKS ALOT!!!
I have modified ur query to check the date as well
ALTER PROCEDURE CountCases
(
@location varchar(100),
@symptomname varchar(100)
)
AS
SELECT count(visitentrysymptom.symptomID) "number"
FROM symptom inner join visitentrysymptom on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in (select name from symptom where name= @symptomname)
and visitentrysymptom.date in (select date from visitentrysymptom where date= '03/12/2008 22:39:09')
and visitentrysymptom.patientID in (select patientID from patient where homeaddress like '%'+@location+'%')
How do i make the date to check only the month(current month)
modified on Wednesday, December 3, 2008 10:50 AM
|
|
|
|
|
Very simple, like this:
ALTER PROCEDURE CountCases
(
@location varchar(100),
@symptomname varchar(100)
)
AS
SELECT count(visitentrysymptom.symptomID) "number"
FROM symptom inner join visitentrysymptom on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in (select name from symptom where name= @symptomname)
and visitentrysymptom.date in (select date from visitentrysymptom where month(date) = 3 and year(date) = 2008)
and visitentrysymptom.patientID in (select patientID from patient where homeaddress like '%'+@location+'%')
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hi. How can I store PDB files in my MS SQL Database? I want to have a compilation of those files for quick retrieval. There's only an image and xml file in the datatype list. What about other files?
|
|
|
|
|
Take a look at the Binary and VarBinary datatypes.
|
|
|
|
|
|
Hi!
I work with Sqlserver2005 and I have a table which I update, I need to insert the data before update in a table History.
is a way to do that.
Thank you in advance.
|
|
|
|
|
Insert into TableName Values('val1','val2'.....)
Update TableName set Col1='val1', Col2='val2'..... where ColName=ValCondition
For better answer,post more detailed questin.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks Blue_Boy;
sorry my english is bad !
I have two tables Table1 and historical_table1. With the same properties and the data are displayed in a gridview in editable mode . when the user modify a line of table1 I want that the first data (before update) will be inserted in historical_table1 automatically.
Thanks .
|
|
|
|
|
Use triggers[^].
While updating data in table Table1 (I sugggest you to rename this table 'Table1' and give it any meaningful name) execute trigger which do insert in historical_table1.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks a lot Blue_Boy;
I must document on triggers because its the first time I heard trigger in database, thank you very much.
if you know a course for beginners on the triggers thank you pass me the link.
|
|
|
|
|
No problem,I am glad to help you.
Check this link.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi All,
I would like to create a user when he logs in , i can hide all the tables that has been Created Before.All the database Table and Stored Procedures will be Hidden.
Can you please advice?
Many thanks
|
|
|
|
|
He cannot be logging directly into the database to do this, so you need to create a login page and use a known user name to see if he already exists. If not then you the need to create the login and user with the appropriate permissions.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I am using SQL Server 2005 and have a question regarding the Replace function. I have a 'select' query looking something like this:
SELECT tblContacts.*
FROM tblContacts
WHERE tblContacts.[Surname] + tblContacts.[Firstname] LIKE '%Smith (Hon. Member)John%'
('Smith (Hon. Member)John' will come from the user's input)
Now, the end users do not want to have to type in 'Smith (Hon. Member)John', they just want to type SmithHonMemberJohn, that is, the full name and membership type without any spaces, brackets or full stops (periods).
I have looked at the replace function to replace characters but is it possible to replace more than 1 type of character? That is, the replcae function will replace all instances of a certain character, for instance, commas. But is it possible to specify more than 1 chacter type, for instance commas, brackets and spaces?
Hope I have explained this OK. Thanks in advance for any help.
|
|
|
|
|
Not ideal, but you can nest the replace statments for a quick fix:
replace(replace(replace(replace('Smith (Hon. Member)John','(',''),')',''),'.',''),' ','')
produces: SmithHonMemberJohn
otherwise you could always write a function to remove any non alphanumeric characters.
I don't know of any pattern matching within the replace statement though.
|
|
|
|