0
votes

I'm having trouble dealing with type conversions...

CODE:

public static string isLocalIncidentSubmitted()
{

    string query = "SELECT Submit From [MVCOmar].[dbo].PrideMVCSubmitLog WHERE ReportID=@existingNum";

    DataTable dt = new DataTable();
    SqlConnection connection = new SqlConnection(connectionStr4);

    SqlCommand command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@existingNum", MyGlobals1.secondversionDisplayTesting);

    connection.Open();
    SqlDataAdapter adp = new SqlDataAdapter(command);
    adp.Fill(dt);
    connection.Close();
    command.Dispose();
    connection.Dispose();


        return dt.Rows[0]["Submit"].ToString();   

}

the table submit is of type varchar

I get a large error but here are first few lines of it:

System.Data.SqlClient.SqlException: Conversion failed when converting from a character string to uniqueidentifier.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
3
If you're going to ask a question about type conversions, it would really help if you'd give more details about the types involved...Jon Skeet
the ReportID can only be 36 string letters long. existingNum is also 36 letters long so the problem is somewhere with submitBulvak
secondversionDisplayTesting is of type string, the Submit column is of type varchar with max string length being 5.Bulvak
It sounds like ReportID is a GUID, not a string...Jon Skeet
What line causes the error? What is the type of ReportID. What is the type of @existingNum? What is the type of MyGlobals1.secondversionDisplayTesting?cadrell0

3 Answers

3
votes

It looks like your ReportID is a Guid or uniqueidentifier in SQL, but you are trying to give it a string value. Are you sure you're using the correct field in that query?

If your MyGlobals1.secondversionDisplayTesting is a string then do it like:

Guid g = Guid.Parse(MyGlobals1.secondversionDisplayTesting);
command.Parameters.AddWithValue("@existingNum", g);
0
votes

Looks like the problem is not with what you are returning, but the SQL you are trying to execute. Wrap everything but the return statement in a try/catch, and I'm sure you will find confirmation of this. Your SQL is where your problem lies.

from MSDN (http://msdn.microsoft.com/en-us/library/ms187942.aspx): The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and therefore is subject to the truncation rules for converting to a character type. That is, when character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated. See the Examples section.

Examples The following example converts a uniqueidentifier value to a char data type.

 DECLARE @myid uniqueidentifier = NEWID();
 SELECT CONVERT(char(255), @myid) AS 'char';
0
votes

Try using .Add instead of .AddWithValue so that you can specify the type and size of your parameter, like so:

command.Parameters.Add("@existingNum", SqlDbType.UniqueIdentifier).Value = MyGlobals1.secondversionDisplayTesting;