-2

I have a dozen parameters that I'm passing to a SqlCommand. I'm getting a FormatCastException saying that string cannot be converted to integer. I've checked and re-checked, but I'm sure that all the values I'm passing are real integers.

Maybe I am missing something. That's why I'd like to know which parameter is causing the problem. I've looked at the exception message, but there is no indication about the problematic parameter.

Thanks for helping.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richard77
  • 20,343
  • 46
  • 150
  • 252
  • can you log the query that caused the exception? not sure it's possible to find the exact parameter. Can you include the code where you execute the command and the error handling that catches the exception? Though if you are gettijng a format exception when trying to conert an integer, it probably isn't an integer... – user1666620 Mar 16 '16 at 16:20
  • 1
    Perhaps if you post the relevant code someone could spot the problem – Steve Mar 16 '16 at 16:20
  • Possible duplicate of [Obtain the Query/CommandText that caused a SQLException](http://stackoverflow.com/questions/2964838/obtain-the-query-commandtext-that-caused-a-sqlexception) – user1666620 Mar 16 '16 at 16:20
  • @user1666620, why are you so eager to close the question? The link your proposed deals with how to log the error. Please read carefully my question. I'm not interested in the exception but rather the parameter that is causing the exception. If the only way to spot the problem is by looking at the code, just tell me. Otherwise, my question is more to know whether there is a place where I can look that says X, Y, or Z parameter cannot be parsed. – Richard77 Mar 16 '16 at 16:25
  • @Richard77 the answer i linked as a possible duplicate shows how to retrieve the sql command that caused the exception. in my first comment, i made a request, the 2nd is an earlier question that asks the same thing you are asking. – user1666620 Mar 16 '16 at 16:27
  • @user1666620. I see now. Unfortunately, the command Type being StoredProcedure, the only text present is the name of the SPROC. – Richard77 Mar 16 '16 at 16:57
  • @Richard77 Thanks for clearing that up. Can you show the code you use to populate the parameters sent to the SP? – user1666620 Mar 16 '16 at 17:02
  • 1
    @Richard77 can you see if this works? http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object – user1666620 Mar 16 '16 at 17:05
  • @user1666620 thank you so much. I looped through all the parameters then constructed a stringbuilder with the name, type, and value of each parameters. On of them is a string while I defined it as an integer. It must be fatigue as the SPROC is expecting at least 23 parameters. I was able to catch two more errors that way. Going forward, I'll use this technique as it allow me to see what's going to the database. The profiler didn't react because the database didn't receive the query. – Richard77 Mar 16 '16 at 19:44

2 Answers2

1

I'm pretty sure you are going to have to inspect the insert/update SQL and determine which has a string value that is being inserted into a column that is defined as an integer. Make sure that none of the passed values is a string or surrounded by ''.

  • I did what @user1666620 suggested, i.e. I looped through all the parameters and picked, for each of them, the name, the type, and value. 3 of them were not of the right type. I have to create 23 parameters for this SPROC. I overlooked 3 of them while copy/pasting then fixing manually. – Richard77 Mar 16 '16 at 22:20
1

Have you tried using the SQL Server Profiler? Just use that tool to log the query you're making and test it directly with the Management Studio.

Luca Ghersi
  • 3,261
  • 18
  • 32
  • While trying to use the profile on this database, I'm getting the error that the profiler cannot be connected to a Database older that 2005. I guess the SQL management is new while the Database engine itself must be old. – Richard77 Mar 16 '16 at 16:31
  • You need to use the Enterprise Manager profiler. Check this article from Technet: https://technet.microsoft.com/en-us/library/aa173918(v=sql.80).aspx – Luca Ghersi Mar 16 '16 at 16:38