Database

Re: Accommodating an apostrophe in SQL...

3-Dec-08 6:12

Ah, try changing the function to :

Function MakeSqlsafe(strData)
     MakeSqlSafe = Replace(strData, "'", "''")
end Function

new_phoenix3-Dec-08 6:30

new_phoenix

3-Dec-08 6:30

It worked!!! Woo Hoo!!!

Thank you liqz, for your assistance in resolving this item!!!

Re: Accommodating an apostrophe in SQL...

Wendelius3-Dec-08 6:51

Wendelius

3-Dec-08 6:51

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.

The need to optimize rises from a bad design.My articles[^]

MSSQL : getting data from few tables

benjamin yap3-Dec-08 1:41

benjamin yap

3-Dec-08 1:41

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

Re: MSSQL : getting data from few tables

Ben Fair3-Dec-08 3:49

Ben Fair

3-Dec-08 3:49

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)

Re: MSSQL : getting data from few tables [modified]

benjamin yap3-Dec-08 4:13

benjamin yap

3-Dec-08 4:13

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

Re: MSSQL : getting data from few tables

Ben Fair3-Dec-08 5:04

Ben Fair

3-Dec-08 5:04

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)

How can I store PDB files?

harcaype3-Dec-08 1:00

harcaype

3-Dec-08 1:00

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? Confused | :confused:

Re: How can I store PDB files?

Jay Royall3-Dec-08 1:19

Re: How can I store PDB files?

3-Dec-08 1:19

Take a look at the Binary and VarBinary datatypes.

Giorgi Dalakishvili3-Dec-08 4:35

Giorgi Dalakishvili

3-Dec-08 4:35

Here you are: How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET[^]

Giorgi Dalakishvili

#region signature
My Articles

Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^]

My blog
#endregion

Insert before update -the first data- in a historical table

foryou3-Dec-08 0:11

Re: Insert before update -the first data- in a historical table

3-Dec-08 0:11

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.

Blue_Boy3-Dec-08 0:21

Re: Insert before update -the first data- in a historical table

3-Dec-08 0:21

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.

foryou3-Dec-08 0:50

Re: Insert before update -the first data- in a historical table

3-Dec-08 0:50

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 .

Blue_Boy3-Dec-08 0:57

Re: Insert before update -the first data- in a historical table

3-Dec-08 0:57

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.

foryou3-Dec-08 1:22

Re: Insert before update -the first data- in a historical table

3-Dec-08 1:22

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. Smile | :)

Blue_Boy3-Dec-08 1:41

Re: Insert before update -the first data- in a historical table

3-Dec-08 1:41

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.

foryou3-Dec-08 2:50

Re: Insert before update -the first data- in a historical table

3-Dec-08 2:50

Merci beaucoup Blue_Boy.

Blue_Boy3-Dec-08 5:06

Re: Create A USER in Databse

3-Dec-08 5:06

You are welcome. Smile | :)

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.

Create A USER in Databse

kibromg3-Dec-08 0:01

kibromg

3-Dec-08 0:01

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

Ashfield3-Dec-08 7:13

Ashfield

3-Dec-08 7:13

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

Replace function

Jay Royall2-Dec-08 23:47

2-Dec-08 23:47

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.

RGTuffin3-Dec-08 1:19

RGTuffin

3-Dec-08 1:19

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.

Jay Royall3-Dec-08 1:35

3-Dec-08 1:35

I like the idea of a SQL Server function to remove any non alpha characters but, my query is being dynamically built within the Windows application rather than being a stored procedure. Am I able to call a SQL Server function in this way? If not then the nested replace will have to suffice!

Ben Fair3-Dec-08 3:54

Ben Fair

3-Dec-08 3:54

Yes, you can call it that way. If you make a SQL Server scalar function for this purpose, let's say it's named 'RemoveNonAlphaChars'. Your SELECT would use it like so:

SELECT tblContacts.* 
FROM tblContacts
WHERE dbo.RemoveNonAlphaChars(tblContacts.[Surname] + tblContacts.[Firstname]) LIKE '%SmithHonMemberJohn%'

Keep It Simple Stupid! (KISS)

Jay Royall3-Dec-08 4:17