Issue with types conversions

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
c#sql
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 submit - Bulvak
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;