stored procedure problem T.T

[bettybarnes]

Hi guys pls help me with my problem

this is for my update code

Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
        Dim StrSQL As String = "updatePatient"
        classLibrary = New ConnectionLibrary
        classLibrary.openConnection(strConnection)
        'label1.text = databasestatus
        If Not classLibrary.isConnectionOpen() Then
            Exit Sub
        End If
        classLibrary.initializeCommand(StrSQL)
        classLibrary.addParameter("@IPD_ID", SqlDbType.VarChar, TxtIPD_ID.TextLength, TxtIPD_ID.Text)
        classLibrary.addParameter("@LName", SqlDbType.VarChar, TxtLName.TextLength, TxtLName.Text)
        classLibrary.addParameter("@FName", SqlDbType.VarChar, TxtFName.TextLength, TxtLName.Text)
        classLibrary.addParameter("@MName", SqlDbType.VarChar, TxtMName.TextLength, TxtLName.Text)
        classLibrary.addParameter("@Address", SqlDbType.VarChar, TxtAddress.TextLength, TxtAddress.Text)
        classLibrary.addParameter("@Birthplace", SqlDbType.VarChar, TxtBirthplace.TextLength, TxtBirthplace.Text)
        classLibrary.addParameter("@Birthdate", SqlDbType.VarChar, TxtBirthdate.TextLength, TxtBirthdate.Text)
        classLibrary.addParameter("@Age", SqlDbType.VarChar, TxtAge.TextLength, TxtAge.Text)
        classLibrary.addParameter("@Sex", SqlDbType.VarChar, TxtSex.TextLength, TxtSex.Text)
        classLibrary.addParameter("@Room_ID", SqlDbType.VarChar, TxtRmID.TextLength, TxtRmID.Text)
        classLibrary.addParameter("@CivilStatus", SqlDbType.VarChar, TxtCStatus.TextLength, TxtCStatus.Text)
        classLibrary.addParameter("@Nationality", SqlDbType.VarChar, TxtNationality.TextLength, TxtNationality.Text)
        classLibrary.addParameter("@Religion", SqlDbType.VarChar, TxtReligion.TextLength, TxtReligion.Text)
        classLibrary.addParameter("@Date_Admitted", SqlDbType.VarChar, TxtDate_Admitted.TextLength, TxtDate_Admitted.Text)
        classLibrary.addParameter("@Date_Discharged", SqlDbType.VarChar, TxtDate_Discharged.TextLength, TxtDate_Discharged.Text)
        classLibrary.addParameter("@Time_Admitted", SqlDbType.VarChar, TxtTime_Admitted.TextLength, TxtTime_Admitted.Text)
        classLibrary.addParameter("@Time_Discharged", SqlDbType.VarChar, TxtTime_Discharged.TextLength, TxtTime_Discharged.Text)
        classLibrary.addParameter("@Chart_ID", SqlDbType.VarChar, TxtChart_ID.TextLength, TxtChart_ID.Text)
        classLibrary.addParameter("@Occupation", SqlDbType.VarChar, txtOccupation.TextLength, txtOccupation.Text)
        objCommand.ExecuteNonQuery()
        refreshlist()
    End Sub

and this is my stored procedure for update

ALTER PROCEDURE dbo.updatePatient
(
@IPD_ID varchar(50),@LName varchar(50),@FName varchar(50),@MName varchar(50),@Address varchar(50),@Birthplace varchar(50),@Birthdate varchar(50),@CivilStatus varchar(50),@Nationality varchar(50),@Occupation varchar(50),@Age varchar(50),@Sex varchar(50),@Religion varchar(50),@Date_Admitted varchar(50),@Date_Discharged varchar(50),@Time_Admitted varchar(50),@Time_Discharged varchar(50),@Room_ID varchar(50)
)
AS
	update Admission_Record
	set LName=@LName,FName=@FName,MName=@MName,Address=@Address,Birthplace=@Birthplace,Birthdate=@Birthdate,CivilStatus=@CivilStatus,Nationality=@Nationality,Occupation=@Occupation,Age=@Age,Sex=@Sex,Religion=@Religion,Date_Admitted=@Date_Admitted,
Date_Discharged=@Date_Discharged,Time_Admitted=@Time_Admitted,Time_Discharged=@Time_Discharged,Room_ID=@Room_ID
WHERE IPD_ID=@IPD_ID;

[lolafuertes]

And the problem is ...

[bettybarnes]

the problem is procedures or functions has too many arguments specified

[izyrider]

are you sure?

you are declaring your parameters (!! all your parameters):
in the sproc as varchar(50)
in vb as varchar(actual-length-of-the-text)

and it could be worthwhile to revisit your db design (or is it your sproc design):
"@Birthdate varchar(50),@CivilStatus varchar(50),@Nationality varchar(50),@Occupation varchar(50),@Age varchar(50),@Sex varchar(50),@Religion varchar(50),@Date_Admitted varchar(50),@Date_Discharged varchar(50),@Time_Admitted varchar(50),@Time_Discharged varchar(50),@Room_ID varchar(50)"
is really ...unfortunate.

why all the varchar(50) ?

does @Occupation always fit in 50 ?
how can you spread @Time_Admitted to fill 50 even if you are sending string data.

@sex varchar(50) is not a big enough to write a book about the patient's love-life so i guess you are expecting gender input in the form of M or F (or even the more elaborate U:Unknown, M:Male, F:Female, L:LegalEntity) ...char(1) would seem to be enough.

@age is a calculated field that should not ordinarily be stored in your db (it is always available using (depending on what you are looking for) some combination of GETDATE(), Birthdate, DateAdmitted. any @age you store is guaranteed to be wrong next birthday.

please consider using more appropriate datatypes (sproc (& table))
please get your user input into an appropriate datatype in your vb

separately (and i'm only guessing from your sproc design so correct me if i'm wrong) your data is nowhere near normalised to even 1NF!
@sex is a property of the patient
@DateAdmitted is a property of an admission
they absolutely DO NOT belong in the same table.

izy

[lolafuertes]

and your question is how to reduce the number of parameters of your stored procedure?

A possible approach may be splitting the update in several steps all under the same transaction.
Another approach is not to use a stored procedure, intead use and adhoc query from your application.

Hope this helps

[lolafuertes]

@izyrider: agreed with you.

[bettybarnes]

thanks i resolved my problem! God bless u guys

[lolafuertes]

So, please be so kind to put your solution, and mark this thread as solved.

Thanks in advance