Hi Sameer,
Try changing parameter name "@Key" to "?".
? represents parameter in Access notation.
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
Hi Miha,
Here is a snippet of my code that fills a DataSet.
// For Reading data into a typed DataSet
public ReplicaConfigDataSet GetReplicaConfig()
{
// DBProvider is a reference to an Abstract Factory that returns the
required Databases objects based on a config file
using(IDbConnection dbConn = DBProvider.CreateConnection())
{
String cmdText = String.Empty;
IDbDataAdapter da = null;
try
{
IDbCommand cmd = DBProvider.CreateCommand();
cmd.Connection = dbConn;
int repConfigId = 1;
cmd.CommandType = CommandType.Text;
// Using the Parameter @key in my SQL statement
cmdText = "SELECT [KEY],[MASTERDBPATH],[SYNTIMEINTERVAL],[REPLICABLE]
FROM REPLICACONFIG WHERE [KEY]=@Key";
cmd.CommandText = cmdText;
IDbDataParameter param = DBProvider.CreateParameter("@Key",repConfigId);
cmd.Parameters.Add(param);
da = DBProvider.CreateDataAdapter();
da.SelectCommand = cmd;
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
ReplicaConfigDataSet ds = new ReplicaConfigDataSet();
ds.ReplicaConfig.TableName = "Table";
da.Fill(ds);
ds.ReplicaConfig.TableName = "ReplicaConfig";
return ds;
}
catch(Exception e)
{
if (dbConn.State.Equals(ConnectionState.Open) ||
dbConn.State.Equals(ConnectionState.Broken))
{
dbConn.Close();
}
throw e;
}
}
}
// Code for Database Abstract Factory that returns the required Database
objects
public enum DBProviderType : int { SqlClient = 0, OleDB = 1, Odbc = 2 }
public class DBProviderFactory
{
private static readonly int NoDBProvider = 3;
private DBProviderType pType = DBProviderType.OleDB;
private Type[] connType,cmdType,paramType,daType;
public DBProviderFactory(DBProviderType provider)
{
pType = provider;
Init();
}
public DBProviderType ProviderType
{
get
{
return pType;
}
}
private void Init()
{
connType = new Type[NoDBProvider];
cmdType = new Type[NoDBProvider];
paramType = new Type[NoDBProvider];
daType = new Type[NoDBProvider];
// Initialize the types for the providers
connType[(int)DBProviderType.SqlClient] = typeof(SqlConnection);
connType[(int)DBProviderType.OleDB] = typeof(OleDbConnection);
connType[(int)DBProviderType.Odbc] = typeof(OdbcConnection);
cmdType[(int)DBProviderType.SqlClient] = typeof(SqlCommand);
cmdType[(int)DBProviderType.OleDB] = typeof(OleDbCommand);
cmdType[(int)DBProviderType.Odbc] = typeof(OdbcCommand);
paramType[(int)DBProviderType.SqlClient] = typeof(SqlParameter);
paramType[(int)DBProviderType.OleDB] = typeof(OleDbParameter);
paramType[(int)DBProviderType.Odbc] = typeof(OdbcParameter);
daType[(int)DBProviderType.SqlClient] = typeof(SqlDataAdapter);
daType[(int)DBProviderType.OleDB] = typeof(OleDbDataAdapter);
daType[(int)DBProviderType.Odbc] = typeof(OdbcDataAdapter);
}
public IDbDataAdapter CreateDataAdapter()
{
IDbDataAdapter da;
da = (IDbDataAdapter)Activator.CreateInstance(daType[(int)pType],false);
return da;
}
public IDbDataAdapter CreateDataAdapter(string commandText,IDbConnection
connection)
{
IDbDataAdapter da;
da = (IDbDataAdapter)Activator.CreateInstance(daType[(int)pType],false);
da.SelectCommand = this.CreateCommand(commandText, connection);
return da;
}
public IDbDataAdapter CreateDataAdapter(IDbCommand cmd)
{
IDbDataAdapter da;
da = (IDbDataAdapter)Activator.CreateInstance(daType[(int)pType],false);
da.SelectCommand = cmd;
return da;
}
public IDataParameter CreateParameter(string paramName, DbType dbType)
{
IDbDataParameter param;
param =
(IDbDataParameter)Activator.CreateInstance(paramType[(int)pType], false);
param.ParameterName = paramName;
param.DbType = dbType;
return param;
}
public IDbDataParameter CreateParameter(string paramName, Object value)
{
IDbDataParameter param;
param =
(IDbDataParameter)Activator.CreateInstance(paramType[(int)pType], false);
param.ParameterName = paramName;
param.Value = value;
return param;
}
public IDbDataParameter CreateParameter(string paramName, DbType dbType, int
size)
{
IDbDataParameter param;
param =
(IDbDataParameter)Activator.CreateInstance(paramType[(int)pType], false);
param.ParameterName = paramName;
param.DbType = dbType;
param.Size = size;
return param;
}
public IDbDataParameter CreateParameter(string paramName, DbType dbType, int
size, string srcColumn)
{
IDbDataParameter param;
param =
(IDbDataParameter)Activator.CreateInstance(paramType[(int)pType], false);
param.ParameterName = paramName;
param.DbType = dbType;
param.Size = size;
param.SourceColumn = srcColumn;
return param;
}
public IDbDataParameter CreateParameter(string paramName, DbType dbType,
string srcColumn)
{
IDbDataParameter param;
param =
(IDbDataParameter)Activator.CreateInstance(paramType[(int)pType], false);
param.ParameterName = paramName;
param.DbType = dbType;
param.SourceColumn = srcColumn;
return param;
}
public IDbConnection CreateConnection()
{
IDbConnection dbConn =
(IDbConnection)Activator.CreateInstance(connType[(int)pType],false);
// Gets the Connection String from a App.Config file
dbConn.ConnectionString =
AppConfig.GetAppSetting("DBConnString",String.Empty);
return dbConn ;
}
public IDbCommand CreateCommand(string cmdText, IDbConnection connection)
{
IDbCommand cmd;
cmd = (IDbCommand)Activator.CreateInstance(cmdType[(int)pType],false);
cmd.CommandText = cmdText;
cmd.Connection = connection;
return cmd;
}
public IDbCommand CreateCommand()
{
IDbCommand cmd;
cmd = (IDbCommand)Activator.CreateInstance(cmdType[(int)pType],false);
return cmd;
}
public IDbCommand CreateCommand(string cmdText)
{
IDbCommand cmd;
cmd = (IDbCommand)Activator.CreateInstance(cmdType[(int)pType],false);
cmd.CommandText = cmdText;
return cmd;
}
}
Miha Markic said:
I
am
using parameters in my SQL statement.
So, whenever I have parameters in my SQL statement the the ODBC DataAdapter
throws an exception containg the following message
ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 1
when I try to fill the Dataset from the Database and
ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 7
when I try to update the Database using the Dataset.
But if I use the OLE DB Data Providers then everything works fine
with
no int
the
Can you show us the code?
