I'm hooking into DB2 and it returns a DB2 error when I start view the ASP page:







In this case, I believe the SQL is being generated incorrectly because of the square brackets around each field name - DB2 doesn't accept these. Are there any options I need to set on the QueryInformation object to change how it generates SQL and for what database?



Thanks in advance!

Peter

Some additional information. I was looking at the source code for the SqlDataSourceItem object and it looks like it will have this kind of trouble on the tables as well.



The following segment adds the fields to the query:







It's adding brackets regardless of the database type. Something similar happens when specifying table names:







If the database is DB2 v8 (we've not upgraded to v9 yet, but the DB2 Express v9 on my development PC provides the same error), it can't use brackets around the table names either and the query will fail.



Thanks!



Peter

Brackets around SQL Server items is OK. This allows for non-compliant names to be used (such as names with spaces or that are key words).



Might be nice if there was a way to configure the data source to not use the brackets...

Yes, it's OK to use square brackets around fields and tables on SQL Server. I imagine it's OK on Oracle, too (I don't have enough experience to confirm that), but DB2 v9 and DB2 v8 don't allow it. It causes a SQL error to be thrown. I also know of at least one other DB platform (it's not supported by this architecture) that would throw an error.

You are correct both correct.  The "identifier delimiter" used by the different databases for special names is like this:

SQL Server: [name]

Oracle: "name"

DB2: "name"

Access: [name]

So, knowing this, we took out all of the [] from the Db2DataSourceItem and replaced them with "".  Could you post the stack trace for the error you're getting?  It's odd that the Db2DataSourceItem is using [], so I need to track that one down.

Here's the stack trace from my error page:



IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1, DB2CursorType A_2, Boolean A_3, Boolean A_4, Boolean A_5, Int32 A_6) +3973

IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1) +47

IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior) +93

IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior behavior) +33

System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +7

System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2464

System.Web.UI.WebControls.Repeater.GetData() +50

System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +232

System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +53

System.Web.UI.WebControls.Repeater.DataBind() +72

System.Web.UI.WebControls.Repeater.EnsureDataBound() +55

System.Web.UI.WebControls.Repeater.OnPreRender(EventArgs e) +12

System.Web.UI.Control.PreRenderRecursiveInternal() +77

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Control.PreRenderRecursiveInternal() +161

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360



Thanks!

Aha, you're not going through any SF stuff to get there.  You're using the System.Web.UI.WebControls.SqlDataSourceView class which is designed to work with SQL Server, so it's creating the query for SQL.  DB2 might have a corresponding ASP.NET control.  If it doesn't then you might need to fill a DataTable in code and use it to populate the grid (or whatever you're bound to).

Well, I've got it sort of working. I created a static SqlHandler object that is initialized when the request is started and retrieves a connection whenever necessary. Originally, I had it returning a SqlDataSourceItem object, but changed that to Db2DataSourceItem. I had though the SqlDataSourceItem was a generic object rather than one specific to SQL Server.



I've now got it connected in and pulling data like it should.



Thanks for your help!

Excellent.  Glad it's working for you  

As for the objects, we tried to keep the names the same as the corresponding ADO.NET provider objects (except for a few cases where the data source uses OLE DB.

SqlDataSourceItem -> System.Data.SqlClient.SqlCommand

OracleDataSourceItem -> System.Data.OracleClient.OracleCommand

Db2DataSourceItem -> IBM.Data.DB2.DB2Command (technically this one accepts a factory, so you can use whatever provider is optimized for your database type since DB2 has several different versions of their ADO.NET provider and we didn't want to limit you)

VfpDataSourceItem -> System.Data.OleDb.OleDbCommand

AccessDataSourceItem -> System.Data.OleDb.OleDbCommand

That's good to know and I'll have to keep it in mind. I'd probably have realized that if I'd worked in .NET technologies prior to now (I know more about PHP/MySQL applications than anything else at the moment).



Thanks!