"parameter is missing at the statement" Exception occurred...

SeongRyong · Post by **SeongRyong** » Fri 14 Jul 2006 12:21

Hi.

I just test your MySQLDirect.NET 3.50.11.0 version with Enterprise Library 2.0.

While test your data provider, some exception occurred.......

-----------------------------------------------------------------------
System.InvalidOperationException was unhandled
Message="'userID' parameter is missing at the statement"
Source="CoreLab.MySql"
StackTrace:
위치: CoreLab.MySql.s.a()
위치: CoreLab.MySql.s.a(Int32& A_0, ArrayList& A_1, Boolean& A_2)
위치: CoreLab.MySql.v.d()
위치: CoreLab.MySql.MySqlDataReader.NextResult()
위치: System.Data.ProviderBase.DataReaderContainer.NextResult()
위치: System.Data.Common.DataAdapter.FillNextResult(DataReaderContainer dataReader)
위치: System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
위치: System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
위치: CoreLab.MySql.MySqlDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
위치: System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
위치: System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
위치: System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
위치: Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
위치: Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
위치: Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName)
위치: Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command)
위치: MySQLConnectionTest.Form1.button1_Click(Object sender, EventArgs e) 파일 C:\Documents and Settings\305014458\My Documents\Visual Studio 2005\Projects\MySQLConnectionTest\MySQLConnectionTest\Form1.cs:줄 46
위치: System.Windows.Forms.Control.OnClick(EventArgs e)
위치: System.Windows.Forms.Button.OnClick(EventArgs e)
위치: System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
위치: System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
위치: System.Windows.Forms.Control.WndProc(Message& m)
위치: System.Windows.Forms.ButtonBase.WndProc(Message& m)
위치: System.Windows.Forms.Button.WndProc(Message& m)
위치: System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
위치: System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
위치: System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
위치: System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
위치: System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
위치: System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
위치: System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
위치: System.Windows.Forms.Application.Run(Form mainForm)
위치: MySQLConnectionTest.Program.Main() 파일 C:\Documents and Settings\305014458\My Documents\Visual Studio 2005\Projects\MySQLConnectionTest\MySQLConnectionTest\Program.cs:줄 17
위치: System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
위치: System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
위치: Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
위치: System.Threading.ThreadHelper.ThreadStart_Context(Object state)
위치: System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
위치: System.Threading.ThreadHelper.ThreadStart()

-----------------------------------------------------------------------

-----------------------------------------------------------------------
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "select * from users where userId = @userID";

DbCommand dbcommand = db.GetSqlStringCommand(sqlCommand);
db.AddInParameter(dbcommand, "userID", DbType.String, "SeongRyong");

DataSet dataset = db.ExecuteDataSet(dbcommand);
DataTable dataTable = dataset.Tables[0];

listBox1.DataSource = dataTable;
listBox1.DisplayMember = "password";
listBox1.ValueMember = "userid";

-----------------------------------------------------------------------

I don't know why such exception occurred....

Surely, I add 'userID' parameter by using AddInParameter method.
(Above, Bold code.)

and, I just test another way to add userID value to command string just like this.

string sqlCommand = "select * from users where userId = 'SeongRyong' ";

In this case, No Exception occurred.

Why this case occurred??? Could you explain to me??

---------------------------------------------------------------------

--------------------------------------------------------------------------------

Alexey · Post by **Alexey** » Fri 14 Jul 2006 12:37

Try to add the parameter like this:

Code: Select all

db.AddInParameter(dbcommand, "@userID", DbType.String, "SeongRyong");

SeongRyong · Post by **SeongRyong** » Wed 19 Jul 2006 04:00

Thank you...

As you mention, after your code applied, then works well.

but, I wonder that '@serID' is standard code in Enterprise Library 2.0.

In Enterprise Library 2.0 Documentation, I saw that using 'serID' in Data Acess Applicationo Block.

So I think that 'serID' is also work well in MySQLDirect.NET. Isn't right??

Alexey · Post by **Alexey** » Wed 19 Jul 2006 06:34

Do you mean 'userID'? Was it a typo?

SeongRyong · Post by **SeongRyong** » Wed 19 Jul 2006 07:32

Yes, I mean 'userID'. It was misspelling.
I mean that 'userID' as a parameter name is also work as well as '@userID'.

db.AddInParameter(dbcommand, "@userID" , DbType.String, "SeongRyong");

and

db.AddInParameter(dbcommand, "userID" , DbType.String, "SeongRyong");

I think the both of above two example is right. Isn't it?

Alexey · Post by **Alexey** » Wed 19 Jul 2006 07:52

OK. Let's have a little recollection. You reported the error:

System.InvalidOperationException was unhandled
Message="'userID' parameter is missing at the statement"
Source="CoreLab.MySql"

I recommended you to use "@userID" instead of "userID in your code:

Code: Select all

db.AddInParameter(dbcommand, "@userID", DbType.String, "SeongRyong");

You announced success. And now you are asking whether two different strings:

Code: Select all

db.AddInParameter(dbcommand, "@userID" , DbType.String, "SeongRyong");

and

Code: Select all

db.AddInParameter(dbcommand, "userID" , DbType.String, "SeongRyong");

are identical. How they both can be correct if first string doesn't raise any error while second one does? Maybe i didn't understand what you mean?

SeongRyong · Post by **SeongRyong** » Wed 19 Jul 2006 08:35

1. db.AddInParameter(dbcommand, "@userID" , DbType.String, "SeongRyong");

- with SQLServer Provider ==> Success
- with MySQLDirect.NET Provider ==> Success

2. db.AddInParameter(dbcommand, "userID" , DbType.String, "SeongRyong");

- with SQLServer Provider ==> Success
- with MySQLDirect.NET Provider ==> Failure

So, I think that MySQLDirect.NET should support case 2.

Alexey · Post by **Alexey** » Tue 29 Aug 2006 12:33

MySQL server variables are also denoted using '@' symbol. When encountered in query text, such fragments are treated as parameters only if they are found in MySqlCommand.Parameters collection; otherwise they are considered server variables. That's why your second case is not possible.
For more information about parameters please read "Using Parameters" article of MySQLDirect .NET help documentation.