Hi..
My requirement is to search a patient record with around 12 inputs(every i/p is optional)and i have written stored procedure for that and it works prefect in query analyzer and when i tried to execute that stored procedure in the C#..it throws an exception that "no mapping between the textbox and known provider native type"..i'm posting the c# code and stored procedure below..
CREATE PROCEDURE SP_SEARCHPATIENT
@pvPName VARCHAR(50) =NULL ,
@pvMRDNo VARCHAR(50) =NULL,
@pstDOB SMALLDATETIME = NULL ,
@piStateID INT = NULL,
@piCityID INT = NULL ,
@piMobNo BIGINT = NULL ,
@piHomeNo BIGINT = NULL,
@piOffice BIGINT = NULL,
@pvEmail VARCHAR(50) =NULL,
@pvAddr VARCHAR(100) =NULL,
@pvRelatedto VARCHAR(50) =NULL,
@pvReferedby VARCHAR(50) =NULL,
@pvMtongue VARCHAR(50) =NULL
--@pvArea VARCHAR(50) =NULL
AS
DECLARE @lsSql NVARCHAR(1000)
DECLARE @lvPName VARCHAR(50)
DECLARE @lvMRDNo VARCHAR(50)
DECLARE @lstDOB SMALLDATETIME
DECLARE @liStateID INT
DECLARE @liCityID INT
DECLARE @liMobNo BIGINT
DECLARE @liHomeNo BIGINT
DECLARE @liOffice BIGINT
DECLARE @lvEmail VARCHAR(50)
DECLARE @lvAddr VARCHAR(100)
DECLARE @liflag INT
DECLARE @lvRelatedto VARCHAR(50)
DECLARE @lvReferedby VARCHAR(50)
DECLARE @lvMtongue VARCHAR(50)
--DECLARE @lvArea VARCHAR(50)
SET @lsSql=N'SELECT pat_pat_id,pat_MRD_num,pat_pat_name,pat_pat_DOB,pat_pat_mobnum,pat_pat_mod_date FROM PatientDetails WHERE pat_pat_name LIKE @lvPName'
SET @lvPName=@pvPName
SET @lvMRDNo=@pvMRDNo
SET @lstDOB=@pstDOB
SET @liStateID=@piStateID
SET @liCityID=@piCityID
SET @liMobNo=@piMobNo
SET @liHomeNo=@piHomeNo
SET @liOffice=@piOffice
SET @lvEmail=@pvEmail
SET @lvAddr=@pvAddr
SET @lvRelatedto=@pvRelatedto
SET @lvReferedby=@pvReferedby
SET @lvMtongue=@pvMtongue
-- SET @lvArea=@pvArea
SET @liflag=0
IF @pvPName IS NULL
BEGIN
SET @liflag=@liflag+1
END
IF @pvMRDNo IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_MRD_num=@lvMRDNo '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @pstDOB IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_DOB=@lstDOB '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @piStateID IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_state=@liStateID '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @piCityID IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_city=@liCityID '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @piMobNo IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_mobnum=@liMobNo '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @piHomeNo IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_homenum=@liHomeNo '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @piOffice IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_worknum=@liOffice '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @pvEmail IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_emailid=@lvEmail '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @pvAddr IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_addr=@lvAddr '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @pvRelatedto IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_rel_to=@lvRelatedto '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @pvReferedby IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_ref_by=@lvReferedby '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
IF @pvMtongue IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_mtongue=@lvMtongue '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END
/*IF @pvArea IS NOT NULL
BEGIN
SET @lsSql = @lsSql + N' OR PatientDetails.pat_pat_area=@lvArea '
END
ELSE
BEGIN
SET @liflag=@liflag+1
END*/
IF NOT @liflag=13
BEGIN
EXEC sp_executesql @lsSql , N'@lvPName VARCHAR(50), @lvMRDNo VARCHAR(50), @lstDOB SMALLDATETIME, @liStateID INT, @liCityID INT, @liMobNo BIGINT, @liHomeNo BIGINT, @liOffice BIGINT, @lvEmail VARCHAR(50), @lvAddr VARCHAR(100), @lvRelatedto VARCHAR(50), @lvReferedby VARCHAR(50), @lvMtongue VARCHAR(50) ',
@pvPName , @pvMRDNo, @pstDOB , @piStateID, @piCityID , @piMobNo, @piHomeNo, @piOffice, @pvEmail, @pvAddr,@lvRelatedto,@lvReferedby,@lvMtongue
END
ELSE
BEGIN
SELECT pat_pat_id,pat_MRD_num,pat_pat_name,pat_pat_DOB,pat_pat_mobnum,pat_pat_mod_date FROM PatientDetails
END
GO
and the c# code now..
Conn = new SqlConnection();
Conn.ConnectionString = "Database=SATHISEYEHOSPITAL;Server=MAGZCOMP;Integrated Security=SSPI;";
Conn.Open();
SqlCommand lcmdconn = new SqlCommand("SP_SEARCHPATIENT", Conn);
lcmdconn.Parameters.Add("@pvPName", txtname.Text);
lcmdconn.Parameters.Add("@pvMRDNo", txtMRDno);
lcmdconn.Parameters.Add("@pstDOB", DTPDOB.Value);
lcmdconn.Parameters.Add("@piMobNo", txtmobile.Text);
lcmdconn.Parameters.Add("@piHomeNo", txthome.Text);
lcmdconn.Parameters.Add("@piOffice", txtoffice.Text);
lcmdconn.Parameters.Add("@pvEmail", txtemail.Text);
lcmdconn.Parameters.Add("@pvAddr", txtAddress.Text);
lcmdconn.Parameters.Add("@pvRelatedto", txtrelatedto.Text);
lcmdconn.Parameters.Add("@pvReferedby", txtreferedby.Text);
lcmdconn.Parameters.Add("@pvMtongue", txtMtongue.Text);
lcmdconn.Parameters.Add("@pvArea", txtarea.Text);
lcmdconn.Parameters.Add("@piStateID", cbostate.SelectedItem);
lcmdconn.Parameters.Add("@piCityID", cbocity.SelectedItem);
lcmdconn.CommandType = CommandType.StoredProcedure;
SqlDataAdapter ldaconn = new SqlDataAdapter();
ldaconn.SelectCommand = lcmdconn;
DataSet dsconn = new DataSet("PatientDetails");
ldaconn.Fill(dsconn, "PatientDetails");
DgdSearchResult.DataSource = dsconn.DefaultViewManager;
Can anyone help me to solve this....
OR Is there other solution available for this...