Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 sequence

Author  Topic 

aylin_sk
Starting Member

25 Posts
Posted - 2005-01-05 : 09:20:51
PATIENT_NUMBER is coming from a sequence called PATIENT_ID_SEQ shown in ADD_PATIENT procedure...How can i use PATIENT_NUMBER in update patient procedure???Should i use it again like a sequence??





procedure ADD_PATIENT (p_patient_name in varchar2, p_patient_last_name in varchar2, p_patient_sex in varchar2,
p_patient_treat_start_date in varchar2, p_patient_birthdate in varchar2, p_patient_parent in varchar2,
p_patient_phone in varchar2, p_patient_address1 in varchar2, p_patient_address2 in varchar2,
p_patient_county_id in number, p_patient_city_id in number, p_patient_zipcode in varchar2,
p_patient_group in varchar2, p_patient_explanation in varchar2, p_patient_week in number, p_patient_potential in varchar2,
p_patient_record_status in varchar2)


is

v_patient_period varchar2(1) := 'M';
begin

if ((sysdate - to_date(p_patient_treat_start_date, 'dd-mm-yyyy')) / 7) < 18 then
v_patient_period := 'W';
end if;

insert into LPMS.PATIENTS
values(PATIENT_ID_SEQ.nextval, p_patient_name, p_patient_last_name, p_patient_sex, to_date(p_patient_treat_start_date, 'dd-mm-yyyy'), to_date(p_patient_birthdate, 'dd-mm-yyyy'), p_patient_parent,
p_patient_phone, p_patient_address1, p_patient_address2, p_patient_county_id, p_patient_city_id, p_patient_zipcode, v_patient_period,
p_patient_group, p_patient_explanation, p_patient_week, p_patient_potential, p_patient_record_status);
end;



procedure UPDATE_PATIENT(p_patient_number in number, p_patient_name in varchar2, p_patient_last_name in varchar2, p_patient_sex in varchar2,
p_patient_treat_start_date in varchar2, p_patient_birthdate in varchar2, p_patient_parent in varchar2,
p_patient_phone in varchar2, p_patient_address1 in varchar2, p_patient_address2 in varchar2,
p_patient_county_id in number, p_patient_city_id in number, p_patient_zipcode in varchar2,
p_patient_group in varchar2, p_patient_explanation in varchar2, p_patient_week in number, p_patient_potential in varchar2,
p_patient_record_status in varchar2)


is
begin

update LPMS.PATIENTS
set PATIENT_NAME = p_patient_name,
PATIENT_LASTNAME = p_patient_last_name,
PATIENT_SEX = p_patient_sex,
PATIENT_TREATSTARTDATE = to_date(p_patient_treat_start_date, 'dd-mm-yyyy'),
PATIENT_BIRTHDATE = to_date(p_patient_birthdate, 'dd-mm-yyyy'),
PATIENT_PARENT = p_patient_parent,
PATIENT_PHONE = p_patient_phone,
PATIENT_ADDRESS1 = p_patient_address1,
PATIENT_ADDRESS2 = p_patient_address2,
PATIENT_COUNTY_ID = p_patient_county_id,
PATIENT_CITY_ID = p_patient_city_id,
PATIENT_ZIPCODE = p_patient_zipcode,
PATIENT_GROUP = p_patient_group,
PATIENT_EXPLANATION = p_patient_explanation,
PATIENT_WEEK = p_patient_week,
PATIENT_POTENTIAL = p_patient_potential

where PATIENT_NUMBER = p_patient_number;
end;

aylin_sk
Starting Member

25 Posts
Posted - 2005-01-05 : 10:02:17
Please help me...
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2005-01-05 : 10:16:08
Unfortunately for you, this is a Microsoft SQL Server site.

- Jeff
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts
Posted - 2005-01-05 : 12:45:26
Well, let's give it a whack anyways (I've been in remission from Oracle for a few years now but I'm sensing a relapse.)

Aylin_Sk,

What are you trying to get accomplished? What problems are you running into? Any error numbers or messages? Any results that you would like to see but aren't seeing? Any results that you are seeing but don't want to see? Can you be a little more forthcoming with some details on what the table looks like (e.g., DDL statement)?

HTH

=================================================================

Where it is a duty to worship the sun, it is pretty sure to be a crime to examine the laws of heat. -John Morley, statesman and writer (1838-1923)
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2005-01-05 : 12:47:51
www.dbforums.com is probably a better site to post Oracle specific questions.


-ec
Go to Top of Page

aylin_sk
Starting Member

25 Posts
Posted - 2005-01-06 : 02:10:51
I want to update my values but instead of updating, my code again is inserting the data by another patient_number coming from the sequence...How can i update my values???In my ADD_PATIENT procedure ,PATIENT_NUMBER is a sequence called PATIENT_ID_SEQ....

-------------
procedure UPDATE_PATIENT(p_patient_number in number, p_patient_name in varchar2, p_patient_last_name in varchar2, p_patient_sex in varchar2,

p_patient_treat_start_date in varchar2, p_patient_birthdate in varchar2, p_patient_parent in varchar2,

p_patient_phone in varchar2, p_patient_address1 in varchar2, p_patient_address2 in varchar2,

p_patient_county_id in number, p_patient_city_id in number, p_patient_zipcode in varchar2,

p_patient_group in varchar2, p_patient_explanation in varchar2, p_patient_week in number, p_patient_potential in varchar2,

p_patient_record_status in varchar2)



is

begin


update LPMS.PATIENTS

set PATIENT_NAME = p_patient_name,

PATIENT_LASTNAME = p_patient_last_name,

PATIENT_SEX = p_patient_sex,

PATIENT_TREATSTARTDATE = to_date(p_patient_treat_start_date, 'dd-mm-yyyy'),

PATIENT_BIRTHDATE = to_date(p_patient_birthdate, 'dd-mm-yyyy'),

PATIENT_PARENT = p_patient_parent,

PATIENT_PHONE = p_patient_phone,

PATIENT_ADDRESS1 = p_patient_address1,

PATIENT_ADDRESS2 = p_patient_address2,

PATIENT_COUNTY_ID = p_patient_county_id,

PATIENT_CITY_ID = p_patient_city_id,

PATIENT_ZIPCODE = p_patient_zipcode,

PATIENT_GROUP = p_patient_group,

PATIENT_EXPLANATION = p_patient_explanation,

PATIENT_WEEK = p_patient_week,

PATIENT_POTENTIAL = p_patient_potential


where PATIENT_NUMBER = p_patient_number;

end;



---------
if (myPatient.updatePatient(Int32.Parse(textPatientNumber.Text), textPatientName.Text, textPatientLastName.Text, comboPatientSex.SelectedItem.Value, textPatientTreatStartDate.Text,

textPatientBirthdate.Text, textPatientParent.Text, textPatientPhone.Text, textPatientAddress1.Text,

textPatientAddress2.Text, comboPatientCountyId.SelectedItem.Value, comboPatientCityId.SelectedItem.Value, textPatientZipCode.Text,

comboPatientGroup.SelectedItem.Value, textPatientExplanation.Text, textPatientWeek.Text, patientPotential))

{

myError.Visible = false;

}

else

{

myError.Visible = true;

((Label)myError.FindControl("labelError")).Text = myPatient.myError.getLastError();

}

}

---------------

public bool updatePatient (int patientNumber, string patientName, string patientLastName, string patientSex, string patientTreatStartDate,

string patientBirthdate, string patientParent, string patientPhone, string patientAddress1,

string patientAddress2, string patientCountyId, string patientCityId, string patientZipCode,

string patientGroup, string patientExplanation, string patientWeek, string patientPotential)

{

OracleConnection myConnection = new OracleConnection(myRegistry.getRegistryValue("connectionString"));

OracleCommand myCommand = myConnection.CreateCommand();

myCommand.CommandText = "LPMS.PATIENTS_PKG.UPDATE_PATIENT";

myCommand.CommandType = CommandType.StoredProcedure;

myCommand.Parameters.Add("p_patient_number", OracleType.Number).Value = patientNumber;

myCommand.Parameters.Add("p_patient_name", OracleType.VarChar, 20).Value = patientName;

myCommand.Parameters.Add("p_patient_last_name", OracleType.VarChar, 20).Value = patientLastName;

myCommand.Parameters.Add("p_patient_sex", OracleType.VarChar, 1).Value = patientSex;

myCommand.Parameters.Add("p_patient_treat_start_date", OracleType.VarChar, 10).Value = patientTreatStartDate;

myCommand.Parameters.Add("p_patient_birthdate", OracleType.VarChar, 10).Value = patientBirthdate;

myCommand.Parameters.Add("p_patient_parent", OracleType.VarChar, 50).Value = patientParent;

myCommand.Parameters.Add("p_patient_phone", OracleType.VarChar, 40).Value = patientPhone;

myCommand.Parameters.Add("p_patient_address1", OracleType.VarChar, 50).Value = patientAddress1;

myCommand.Parameters.Add("p_patient_address2", OracleType.VarChar, 50).Value = patientAddress2;

if (patientCountyId.Equals(""))

myCommand.Parameters.Add("p_patient_county_id", OracleType.Number).Value = DBNull.Value;

else

myCommand.Parameters.Add("p_patient_county_id", OracleType.Number).Value = Int32.Parse(patientCountyId);

if (patientCityId.Equals(""))

myCommand.Parameters.Add("p_patient_city_id", OracleType.Number).Value = DBNull.Value;

else

myCommand.Parameters.Add("p_patient_city_id", OracleType.Number).Value = Int32.Parse(patientCityId);


myCommand.Parameters.Add("p_patient_zipcode", OracleType.VarChar, 5).Value = patientZipCode;

myCommand.Parameters.Add("p_patient_group", OracleType.VarChar, 1).Value = patientGroup;

myCommand.Parameters.Add("p_patient_explanation", OracleType.VarChar, 75).Value = patientExplanation;


if (patientWeek.Equals(""))

myCommand.Parameters.Add("p_patient_week", OracleType.Number).Value = DBNull.Value;

else

myCommand.Parameters.Add("p_patient_week", OracleType.Number).Value = Int32.Parse(patientWeek);



myCommand.Parameters.Add("p_patient_potential", OracleType.VarChar, 1).Value = patientPotential;

myCommand.Parameters.Add("p_patient_record_status", OracleType.VarChar, 1).Value = DBNull.Value;



OracleDataAdapter adapter = new OracleDataAdapter(myCommand);

try

{

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

}

catch (OracleException e)

{

string errorMessages = "";

errorMessages += "Message: " + e.Message + "\n" + "Source: " + e.Source + "\n";

System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();

log.Source = "discoveryZone";

log.WriteEntry(errorMessages, System.Diagnostics.EventLogEntryType.Error, e.Code);


myError.lastError = errorMessages;

return(false);

}

return(true);

}

------------
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts
Posted - 2005-01-06 : 16:03:10
Your procedure UPDATE_PATIENT only performs an UPDATE statement so it cannot be inserting rows into your table. It could be that you want to use the PRIMARY KEY column(s) in your WHERE clause.

You still haven't told us what the table structure is (DDL statement) so it's hard to get specific with you.

HTH

=================================================================

A faith that cannot survive collision with the truth is not worth many regrets. -Arthur C Clarke, science fiction writer (1917- )
Go to Top of Page

aylin_sk
Starting Member

25 Posts
Posted - 2005-01-07 : 08:07:42
I solved it thanks..
=================================================================

A faith that cannot survive collision with the truth is not worth many regrets. -Arthur C Clarke, science fiction writer (1917- )
[/quote]
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site