TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
magesh
NA
5
0
Stored procedure excuetion error in C#
Mar 3 2009 12:02 PM
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...
Reply
Answers (
4
)
C# Catch a Key and Suppress it?
Use Access Table to Set the DataGrid Constrains