How do I correct my stored Procedure code to display data on Win. Form

David Godistei

In a project I have created a Stored Procedure by collecting information from the Employees and Salaries tables respectively. I set @Year with int datatype as my first Parameter column. I also set @Month with varchar datatype as my second Parameter column. On the Windows Form when I enter the year and the month in the parameter textboxes I want the required data to display, but it could not. If I had used int datatype throughout it would have displayed the data.

Probably I would have to correct something in the stored procedure code which I do not know. Please I will appreciate if someone can help me succeed. I therefore, want to attach here with images of the code and the form. Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul1y ago
Convert @Month to INT in the Query

If Salaries.Month is stored as an INT, you should convert the @Month parameter to an INT in the query. This ensures consistency between the data types.
```
WHERE Salaries.Year = @Year

AND Salaries.Month = CAST(@Month AS INT)

ORDER BY Salaries.Month DESC;
```
Why This Works:
- The CAST function converts the @Month parameter from VARCHAR to INT, aligning it with the Salaries.Month column's data type.
- This approach avoids unnecessary conversions on the database column, which can degrade performance.
2. Change the Data Type of @Month to INT

If Salaries.Month is consistently stored as an INT, the most efficient solution is to update the stored procedure to accept @Month as an INT. This eliminates the need for runtime conversions.

Steps to Implement:
1. Modify the stored procedure definition:
2. the application passes @Month as an integer value. By aligning the parameter type (@Month) with the column type (Salaries.Month), you eliminate the need for runtime conversions, improving query performance and reducing the risk of errors.
3. Convert Salaries.Month to VARCHAR (Not Recommended)

If Salaries.Month is stored as an INT but you want to compare it with a VARCHAR parameter, you can cast the column to VARCHAR. However, this approach is not recommended because:
- Casting the column prevents the database from using indexes effectively, leading to slower queries.
- It introduces unnecessary overhead.
```
WHERE Salaries.Year = @Year

AND CAST(Salaries.Month AS VARCHAR) = @Month

ORDER BY Salaries.Month DESC;
```
When to Use This:

Only use this approach if Salaries.Month is stored as a VARCHAR and you cannot change the database schema. Otherwise, avoid casting the column.

4. Validate Input from the Application

Check that the application passes the correct data type for @Month. For example:
- If @Month is expected to be an integer, ensure the application sends numeric values (e.g., 1 instead of '01').
- If @Month is expected to be a string, ensure the application sends properly formatted strings (e.g., '01').
Example Validation in C#:
```
int month = int.Parse(txtMonth.Text); // Ensure input is parsed as INT

cmd.Parameters.AddWithValue("@Month", month);
```
0
Sreenath Kappoor1y ago
Hello David,

The issue seems to be related to the data type mismatch between @Month (which is VARCHAR) and Salaries.Month in your database. You mentioned that when both parameters are INT, the stored procedure works correctly. That means Salaries.Month might be stored as an INT instead of VARCHAR.

possible solutions

1: Convert @Month to INT in the WHERE clause

WHERE Salaries.Year = @Year
AND Salaries.Month = CAST(@Month AS INT)
ORDER BY Salaries.Month DESC;

2: Convert Salaries.Month to VARCHAR in the WHERE clause

WHERE Salaries.Year = @Year
AND CAST(Salaries.Month AS VARCHAR) = @Month
ORDER BY Salaries.Month DESC;

If Salaries.Month is stored as INT, change @Month to INT in the stored procedure. If it's stored as VARCHAR, ensure you're passing string values correctly in the Windows Form.

If this does not work, please provide the table structure and what value is passed from the application.
0