data adapter problem

Hot Topic

File IO Behaviour
problem with the online xml/xsd validator
How to pass objects across applications
HELP! On Looping Records for Insert
Programatically checking whether a type implements a interface?
How do i enable AllowDBNull property with TypedDataSets?
How to encrypt a SOAP response
calling a COM object from c#
File Size via FTP
char* from c code via dllimport loses last character

.NET Development

regEx (relate to what you've found)
Questions on EncoderParameters / PNG TIFF Image types
Data insertion in MS Access database Using vb.net
Can't Add Reference to DLL
ADO.NET/Access DB Insertion Exception
how to modify HTTP Post behavior when calling a webservice
HTTP status 407: Proxy Authentication Required +ISA Server
How do I reference a string in a XML file that's part of my C# project?
Unexplained CryptographicException : Padding is invalid...
XMLDocument.Load doubt!

data adapter problem

Hi everyone,

I have a problem that I have tried correct some days, but I still cannot found why it is not correct.

string sqlCommand = ToString(adapter.SelectCommand);
adapter.SelectCommand.CommandText = sqlCommand;
int rowCount = adapter.Fill(dataset);

protected virtual string GetSQL(string name)
{
return resourceHandler.GetStringFromTextResource("sql." + name + ".sql");
}

/// <summary>
/// Output the sql command with the parameter provided.
/// </summary>
/// <param name="command">the command to output to string</param>
/// <returns>The sql command with parameter filled.</returns>
protected virtual string ToString(IDbCommand command)
{
try
{
string sql = command.CommandText;

Regex regex = new Regex(@"(\ |((:|@)p[0-9]+))");
MatchCollection matches = regex.Matches(sql);
string textValue;

for (int i = matches.Count-1; i >= 0; i--)
{
IDataParameter param = ((IDataParameter)command.ParametersIdea);
object value = param.Value;
if (value == DBNull.Value || value == null)
textValue = "null";
else
{
if (param.DbType == DbType.String || param.DbType == DbType.String)
textValue = "'" + System.Convert.ToString(value) + "'";
else
textValue = System.Convert.ToString(value);
}

StringBuilder newSql = new StringBuilder(sql.Substring(0, matchesIdea.Index));
newSql.Append(textValue).Append(sql.Substring(matchesIdea.Index+1));
sql = newSql.ToString();
}
return sql;
}
catch (Exception e)
{
return e.Message;
}
}

for sql, I fetched and run it in query analyser, it is correct, but in adapter, it is always System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'name'.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at KBEWorks.Data.Sql.AbstractBroker.Fill(IDbDataAdapter adapter, DataSet dataset) in c:\projects\kbeworks\kbeworks-2.3\kbeworks.data.sql\src\brokers\abstractbroker.cs:line 135



Sometime I fix the sql stqtements, il always occurs the same error, is there some specially option set in adapter, in the same statement, if no parameters sometime it is run correct!

Thanks any help!

Answer this question

data adapter problem

  • Bob Schaefer

    In your last example, I don't see 'name' anywhere, which implies that the commandtext you've written out isn't the command that is actually executing against the server. Check SqlProfiler for the text the server is seeing. Also, can you post a concise, but complete repro for the scenario Include the code using System.Data and the target table schema.


  • Jin17

    please help me !

    string sqlCommand = ToString(adapter.SelectCommand);
    adapter.SelectCommand.CommandText = sqlCommand;
    Trace.WriteLine("*******SelectCommand********");
    Trace.WriteLine(adapter.SelectCommand.CommandText);
    Trace.WriteLine("*******end********");

    int rowCount = adapter.Fill(dataset);

    output:

    *******SelectCommand********
    SELECT frameworkID,
    name,
    frameworkType,
    creator,
    creationDate,
    version,
    nextGeneratedItem,
    nextGeneratedConnection,
    units
    FROM Framework
    where name='hh'

    System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'name'.
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
    at KBEWorks.Data.Sql.AbstractBroker.Fill(IDbDataAdapter adapter, DataSet dataset) in c:\projects\kbeworks\kbeworks-2.3\kbeworks.data.sql\src\brokers\abstractbroker.cs:line 142


    Thanks!!!

  • CPB

    When I changed the SQL like that:

    Getting embedded resource stream : KBEWorks.Data.Sql.resources.sql.framework.selectbyname.sql
    *******SelectCommand********
    SELECT frameworkID,
    name,
    frameworkType,
    creator,
    creationDate,
    version,
    nextGeneratedItem,
    nextGeneratedConnection,
    units
    FROM Framework

    *******end********

    or as follow:

    DEBUG VisionKBE.Util.ResourceHandler [] - Getting embedded resource stream : KBEWorks.Data.Sql.resources.sql.framework.selectbyname.sql
    *******SelectCommand********
    SELECT creator,
    creationDate,
    version,
    nextGeneratedItem,
    nextGeneratedConnection,
    units
    FROM Framework

    *******end********

    Why still have the same error: ex.messageLine 1: Incorrect syntax near 'name'.
  • data adapter problem
Answer this question