CAST Problem Continued

Thanks for previous help, I am however continuing to have problems as I attempt to search a table for a value keyed by the user (e.g. AccessionPresent= 3624-01). The input value that is being searched against is stored in the table as an INT. I have cast the INT to a VARCHAR on the line described as "MSQL=...

Can anyone ascertain why I get the error, described below? The query works in the Query Analyzer?? What am I doing incorrectly... Any assistance will be appreciated...

Thanks !

Dim DSAs DataSet
Dim MyCommandAs SqlDataAdapter
Dim AccessionPresent, strsearchedfor, strsearchresultAsString
Dim RcdCountAsInteger
Dim ResultCountAsInteger
AccessionPresent = Accession.Text
strsearchedfor ="The Keyed Accession Number "
strsearchresult =" Does Not Exist. Please Verify Your Entry. "
ErrorLabel.Text =String.Format("{1}<b><font size=ex-small color=003399>{0}</b></font>{2}", AccessionPresent, strsearchedfor, strsearchresult)
Dim MySQLAsString

MySQL ="SELECT * FROM ClinicalSpecimen WHERE CAST(SpecimenID AS varchar(50))='" & AccessionPresent &"'"

Dim objConnValidateAs SqlConnection
Dim mySettingsValidateAsNew NameValueCollection
mySettingsValidate = AppSettings
Dim strConnAsString
strConn = mySettingsValidate("connString")
objConnValidate =New SqlConnection(strConn)

MyCommand =New SqlDataAdapter(MySQL, strConn)
DS =New DataSet
MyCommand.Fill(DS,"SpecimenID")
RcdCount = DS.Tables("SpecimenID").Rows.Count.ToString()

If DS.Tables(0).Rows.Count > 0Then
Response.Redirect("Accession_Result.aspx?AccessionResult=" & AccessionPresent)

ElseIf DS.Tables(0).Rows.Count = 0Then
Response.Redirect(http://www.aol.com)

IS CAUSING THE FOLLOWING ERROR:

Server Error in '/ClinicTest2' Application.

---

Syntax error converting the nvarchar value '3624-01' to a column of data type int.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value '3624-01' to a column of data type int.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Syntax error converting the nvarchar value '3624-01' to a column of data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlDataReader.HasMoreRows() +194 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +262 System.Data.SqlClient.SqlDataReader.Read() +29 System.Data.ProviderBase.DataReaderContainer.Read() +26 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +240 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +257 System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +383 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +251 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +308 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +152 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2859 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +84 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +153 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99 System.Web.UI.WebControls.GridView.DataBind() +23 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +92 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +100 System.Web.UI.Control.EnsureChildControls() +134 System.Web.UI.Control.PreRenderRecursiveInternal() +109 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4436

You have to remove the - in 3624-01 from AccessionPresent before casting the nvarchar to int.

You may use replace function.

Example:

1replace(MyString,'-','')-- replace any - in MyString with nothing

Good luck.

|||

Thank you... Ive tried that, as :

replace(AccessionPresent,"-","")

However, I don't believe that this resolves the issue since the "int" is in the table as 3624-01 and if I remove the "-" from AccessionPresent then I have "362401" which does not match the int value (3624-01)...

|||

How can a value of 3624-01 fit in an int column? Isn't this just a display value? This value can't simply be put in the integer datatype of .NET. So you have to get rid of the dash, to convert it to an integer.

|||

OK.. so forgive me... please I was mistaken... SpecimenID is a tyoe VarChar... and the query works correctly in the Query Analyzer...

SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other,
OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTime
FROM ClinicalSpecimen
WHERE (SpecimenID = '3131-04')

So, what else can be incorrect to get the Stack Trace message below...

Can it be something in the code listed here? as the error message does not make sense.. to me at least??'

Thanks !!

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

Dim objConnValidateAs SqlConnection

Dim mySettingsValidateAsNew NameValueCollection

mySettingsValidate = AppSettings

Dim strConnAsString

strConn = mySettingsValidate("connString")

objConnValidate =New SqlConnection(strConn)

' Panel1.Visible = "true"

MyCommand =New SqlDataAdapter(MySQL, strConn)

DS =New DataSet

MyCommand.Fill(DS,"ClinicalSpecimen")

RcdCount = DS.Tables("ClinicalSpecimen").Rows.Count.ToString()

If DS.Tables(0).Rows.Count > 0Then

Response.Redirect("Accession_Result.aspx?AccessionResult=" & AccessionPresent)

ElseIf DS.Tables(0).Rows.Count = 0Then

Response.Redirect("http://www.aol.com")

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

STACK TRACE:

Syntax error converting the nvarchar value '3131-04' to a column of data type int.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value '3131-04' to a column of data type int.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Syntax error converting the nvarchar value '3131-04' to a column of data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlDataReader.HasMoreRows() +194 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +262 System.Data.SqlClient.SqlDataReader.Read() +29 System.Data.ProviderBase.DataReaderContainer.Read() +26 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +240 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +257 System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +383 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +251 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +308 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +152 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2859 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +84 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +153 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99 System.Web.UI.WebControls.GridView.DataBind() +23 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +92 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +100 System.Web.UI.Control.EnsureChildControls() +134 System.Web.UI.Control.PreRenderRecursiveInternal() +109 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4435

|||

FORGIVE ME ALL, DUMBO, DUMBO ME!

After wracking what's left of my brain I finally found my answer... again, plainly put in front of my face... The error was not as I was supposing, happening in the query page, but it was in the resulting "Response Redirect" page where the errror WAS that I was trying to Match a VARCHAR and INT... It wasnt clear from the Stack Trace, until I looked in the address bar to see the page it was referencing... when I corrected the offending field that I was searching on the problem went away !!!

Once again... at least I learned from your posts how to CAST... So, Thanks for all your HELP !

|||

HiSmokinJoe,

Please make Answer the post(s) that help you; not your post.

This will help others to get the answer right away. I would like to suggest adding some tags to help seach engines as well.

Thanks SmokinJoe.