Click here to Skip to main content
15,907,281 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In SQL server, I created this sproc:
SQL
CREATE PROCEDURE [dbo].[GetOutletInfo] 
@PermitID int
AS SELECT * FROM Outlets WHERE PermitID=@PermitID
GO


then in my C# code, I run this:
C#
var cn = new SqlConnection(connectionString);
SqlDataAdapter da;
DataSet ds = new DataSet();
da = new SqlDataAdapter("GetOutletInfo", cn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@PermitID", SqlDbType.Int).Value = PermitID;
da.Fill(ds, "GetOutletInfo");


Upon execution, the da.Fill throws this exception:
System.FormatException
HResult=0x80131537
Message=Failed to convert parameter value from a String to a Int32.
Source=System.Data.SqlClient

I have googled and scratched my head for hours trying different things, with no success. ANy pointers or insight will be appreciated. Thank you

What I have tried:

This query works (since it does not need a parameter)
SqlDataAdapter da;
DataSet ds = new DataSet();
da = new SqlDataAdapter("GetInspectionLettersQueue", cn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds, "GetInspectionLettersQueue");


I tried casting the parameter to int32 before adding it to the Parameters.Add command which did not work.
Posted
Comments
PIEBALDconsult 20-Feb-24 12:24pm    
What type is PermitID?
I recommend against setting the datatypes of parameters.
Pete O'Hanlon 20-Feb-24 12:26pm    
I have to ask. What type is PermitID in your C# code?
Richard MacCutchan 20-Feb-24 13:01pm    
You cannot cast a string to an integer, you need to convert it. See Int32.TryParse Method (System) | Microsoft Learn[^].

If I remember my SQL, the parameter types must all be string, not integer. Try that.
 
Share this answer
 
Comments
Richard Deeming 21-Feb-24 4:13am    
You clearly don't remember your SQL then. :)

Even SQLite and MS Access support integer parameters.
Well, I guess that I need more coffee. It turned out that PermitID was a string, but was set to "foo" - duh - no wonder - thanks for looking once I set it to "42" it worked.

I don't feel too
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900