Conversion Error

[Shamas21]

Hi All

Im connecting to my SQL Server Stored Procedure and It requires a '@Parameter' to perform the 'Stored Procedure'. Im inputting the Parameter to 'Me.tbxSearch.Text' but doesnt seem to like it. Any ideas how I can sort this out?

Thanks

CapitalVisasCRMDataSet.EnforceConstraints = False

        Me.SpMainSearchTableAdapter.Fill(Me.CapitalVisasCRMDataSet.spMainSearch, Me.tbxSearch.Text)

        Me.lsvSearchResults.Items.Clear()
        Me.lsvSearchResults.BeginUpdate()

        Dim spMainSearchRow As CapitalVisasCRMDataSet.spMainSearchRow

        For Each spMainSearchRow In CapitalVisasCRMDataSet.spMainSearch.Rows
            Dim reportListItem As New ListViewItem

            reportListItem.Text = ""

            reportListItem.SubItems.Add(spMainSearchRow("CaseID").ToString) ' Case ID

            reportListItem.SubItems.Add(Microsoft.VisualBasic.StrConv(spMainSearchRow("Candidate"), VbStrConv.ProperCase).ToString) 'Candidate Name

            reportListItem.SubItems.Add(spMainSearchRow("Email").ToString) 'Email Address

            reportListItem.SubItems.Add(spMainSearchRow("Country").ToString) 'Country of Lead

            reportListItem.SubItems.Add(spMainSearchRow("Nationality").ToString) 'Country of Nationality

            reportListItem.SubItems.Add(spMainSearchRow.CustomerID) ' Customer ID

            reportListItem.SubItems.Add(spMainSearchRow("CaseWorker").ToString) 'Caseworker

            reportListItem.SubItems.Add(spMainSearchRow("Consultant").ToString) 'Consultant

            lsvSearchResults.Items.Add(reportListItem)
        Next

        lsvSearchResults.EndUpdate()

        spMainSearchRow = Nothing

This is also the Stored Procedure Code

USE [CapitalVisasCRM]
GO
/****** Object:  StoredProcedure [dbo].[spMainSearch]    Script Date: 06/06/2010 13:38:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spMainSearch]


	@FilterText NVARCHAR(255)


AS


SELECT     tblCases.CaseID, tblCases.Candidate, tblCases.Product, tblEmails.Email, tblCustomers.Nationality, tblCustomers.Country, 
                      tblCompany.CompanyName AS [Company Name], tblCustomers.CustomerID, tblCases.Consultant, tblCases.CaseWorker
FROM         tblCustomers LEFT OUTER JOIN
                      tblCases ON tblCustomers.CustomerID = tblCases.CustomerID LEFT OUTER JOIN
                      tblCompany ON tblCustomers.CustomerID = tblCompany.CustomerID LEFT OUTER JOIN
                      tblEmails ON tblCustomers.CustomerID = tblEmails.CustomerID
GROUP BY tblCustomers.Country, tblCustomers.Nationality, tblCustomers.CustomerID, tblCases.CaseID, tblCases.Product, tblCases.Consultant, tblCases.CaseWorker, 
                      tblCases.Candidate, tblEmails.Email, tblCompany.CompanyName
HAVING      (tblCustomers.Country = @FilterText) OR
                      (tblCustomers.Nationality = @FilterText) OR
                      (tblCustomers.CustomerID = @FilterText) OR
                      (tblCases.CaseID = @FilterText) OR
                      (tblCases.Product = @FilterText) OR
                      (tblCases.Consultant = @FilterText) OR
                      (tblCases.CaseWorker = @FilterText) OR
                      (tblCases.Candidate = @FilterText) OR
                      (tblEmails.Email = @FilterText) OR
                      (tblCompany.CompanyName = @FilterText)

[techgnome]

If caseID and CustomerID are numbers then you can't do this:
(tblCustomers.CustomerID = @FilterText) OR
(tblCases.CaseID = @FilterText) OR

Since your FilterText is a string... when SQL encounters 12 (say for the CaseID) ... it tries to convert your FilterText (which is "h" ) to a number... "h" isn't a valid number and so the error is thrown.

-tg

[Shamas21]

If caseID and CustomerID are numbers then you can't do this:
(tblCustomers.CustomerID = @FilterText) OR
(tblCases.CaseID = @FilterText) OR

Since your FilterText is a string... when SQL encounters 12 (say for the CaseID) ... it tries to convert your FilterText (which is "h" ) to a number... "h" isn't a valid number and so the error is thrown.

-tg

Oh yah... your right.. but is there a work around to this?

Thanks

[techgnome]

Sure... don't include those fields in the query...

-tg

[Shamas21]

Sure... don't include those fields in the query...

-tg

I tried something different and it works.

USE [CapitalVisasCRM]
GO
/****** Object:  StoredProcedure [dbo].[spMainSearch]    Script Date: 06/06/2010 15:25:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spMainSearch]


	@FilterText NVARCHAR(255)


AS


SELECT     tblCases.CaseID, tblCases.Candidate, tblCases.Product, tblEmails.Email, tblCustomers.LeadNationality, tblCustomers.LeadCountry, 
                      tblCompany.CompanyName AS [Company Name], tblCustomers.CustomerID, tblCases.Consultant, tblCases.CaseWorker
FROM         tblCustomers LEFT OUTER JOIN
                      tblCases ON tblCustomers.CustomerID = tblCases.CustomerID LEFT OUTER JOIN
                      tblCompany ON tblCustomers.CustomerID = tblCompany.CustomerID LEFT OUTER JOIN
                      tblEmails ON tblCustomers.CustomerID = tblEmails.CustomerID
GROUP BY tblCustomers.LeadCountry, tblCustomers.LeadNationality, tblCustomers.CustomerID, tblCases.CaseID, tblCases.Product, tblCases.Consultant, tblCases.CaseWorker, 
                      tblCases.Candidate, tblEmails.Email, tblCompany.CompanyName
HAVING      (tblCustomers.LeadCountry LIKE '%' + @FilterText + '%') OR
                      (tblCustomers.LeadNationality LIKE '%' + @FilterText + '%') OR
                      (tblCustomers.CustomerID LIKE '%' + @FilterText + '%') OR
                      (tblCases.CaseID LIKE '%' + @FilterText + '%') OR
                      (tblCases.Product LIKE '%' + @FilterText + '%') OR
                      (tblCases.Consultant LIKE '%' + @FilterText + '%') OR
                      (tblCases.CaseWorker LIKE '%' + @FilterText + '%') OR
                      (tblCases.Candidate LIKE '%' + @FilterText + '%') OR
                      (tblEmails.Email LIKE '%' + @FilterText + '%') OR
                      (tblCompany.CompanyName LIKE '%' + @FilterText + '%')