OleDbDataReader throws error: Missing Parameters

Question

Hi all!

New on his forum. I'm learning C# for about a month now. Met a lot of problems of course, but so far managed to crack 'm all by trying and searching the internet (that's just the way I learn best and how I mastered vba before).

But (hate to admit) now I've stumbled upon an issue I can't seem te solve nor have I been able to find a solution on the internet.

So need some help here.

I have a method retrieving 0 or more records from an Access db which works fine. Retrieved data is moved to datagridview cells.

private void Calendar_PopulatePeriod(DateTime Period)
{
OleDbCommand cmd = new OleDbCommand("SELECT [PropertyId], [Period], [CalendarText], [StayId] " +
"FROM EVBookings " +
"WHERE [Period] = @PeriodCol " +
"ORDER BY [Period]", conn);
cmd.Parameters.AddWithValue("@PeriodCol", Period);
OleDbDataReader rdr = cmd.ExecuteReader();
int b = 0;
while (rdr.Read())
{
ome code....
}
}

Then I have a second method retrieving 1 record which I based on this working method, but which just won't work.

Retrieved data should go to text boxes on a form.

I keep getting this error message:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Values for one or more required parameters are missing.

This is the code of the method which is syntactically equal to the first mentioned (working) method:

private void GetStayDetails(double StayId)
{
OleDbCommand cmd = new OleDbCommand("SELECT [GuestName] , [Date In], [Date Out], [OpenEnded], " +
`[First Month Rent], [Security Deposit], [Monthly Rent], " +
"[First Month Rent To Owner], [Monthly Rent To Owner], " +
"[ES Mgt Fee] " +
"FROM Stays " +
"WHERE StayId = @StayId", conn);
cmd.Parameters.AddWithValue("@StayId", StayId);
OleDbDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Debug.WriteLine("==> " + rdr.GetValue(0));
}
}

When searching for what's causing the error I tried the following alternative sql commands:

OleDbCommand cmd = new OleDbCommand("SELECT [StayId], [GuestName] FROM Stays", conn);
OleDbCommand cmd = new OleDbCommand("SELECT [StayId], [GuestName] FROM Stays", conn);OleDbCommand cmd = new OleDbCommand("SELECT * FROM Stays WHERE [StayId] = @StayId", conn);
OleDbCommand cmd = new OleDbCommand("SELECT [StayId], [GuestName] FROM Stays WHERE [StayId] = 6500010", conn);

All three work fine.

But this one gives the error again:

OleDbCommand cmd = new OleDbCommand("SELECT [StayId], [GuestName] FROM Stays WHERE [StayId] = @Stayid", conn);

I have not a clue why the sql in one method works fine but not in the other.

Let me know if I need to supply more detail.

Cheers!

Tsjallie

Jin Necesario · Answer

OleDbCommand cmd = new OleDbCommand( "SELECT [StayId], [GuestName] FROM Stays WHERE [StayId] = @Stayid" , conn); var parameter = new OleDbParameter( "@Stayid" , SqlDbType.Integer, 4) { Value = StayId }; cmd.parameters.Add(parameter); I hope you get the idea. Thanks.

Mageshwaran R · Answer

Hi, try this code: OleDbCommand cmd = new OleDbCommand( "SELECT [PropertyId], [Period], [CalendarText], [StayId] " + "FROM EVBookings " + "WHERE [Period] = @PeriodCol " + "ORDER BY [Period]" , conn); cmd.Parameters.AddWithValue( "@PeriodCol" , Period); cmnd.Executenonquery(); oledbdataadapter da= new oledbdataadapter(cmnd); datatable dt= new datatable(); da.fill(dt); datagrid.itemsource=da.Asdataview();

Amit Mohanty · Answer

Hey change your OleDbCommand to below code. OleDbCommand cmd = new OleDbCommand( "SELECT [PropertyId], [Period], [CalendarText], [StayId] " + "FROM EVBookings " + "WHERE [Period] = #@PeriodCol# " + "ORDER BY [Period]" , conn);

Tsjallie · Answer

Ok, there it is. Was looking at a version of the database which hadn't the column GuestName in the Stays table. Thx for taking the time looking into this and responding. I'll get my coat Cheers! Tsjallie

Tsjallie · Answer

Additional info. Tried closing and disposing previously used readers (rdr). No go. Same error. Tried using different names for the readers (rd1, rd2, etc). No go. Same error. The more stumped I get the more I get the idea that the problem is just to obvious to see it

Tsjallie · Answer

@ Amit Mohanty , thx for yr reply, but this gives a syntax error.

Tsjallie · Answer

Thx for yr quick response. Unfortunately I still get an error: System.Data.SqlDbType' does not contain a definition for 'integer' My using are: using System; using System.Diagnostics; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Data.OleDb; using System.Drawing; using System.Globalization; using System.Windows.Forms; There's no System.Data.SqlDbType available. Is there some reference I need? Did try to change the type to Decimal with no size specified, but that gives the original error again.