Hello
I have a project running on an Oracle database writted in VB using Visual Studio 2008.
I am using the odbc.odbcdataadpater etc. (not the oracle ones) it works fine with the statement below.
I have migrated the project over to SQL server 2005 (using the migration tool) and 99% of the project works fine (after a few tweaks - still using odbc. components not sql.) except when creating a command using a command builder
I am givving the dataadapter the following string:
SQLSelectString = "SELECT ud_bankpro_stateline.line_id, ud_bankpro_stateline.line_value, ud_bankpro_stateline.allocation, ud_bankpro_stateline.gl_code, ud_bankpro_stateline.narrative_2, ud_bankpro_stateline.output_cmi_date, ud_bankpro_stateline.fund_valid_yn, ud_bankpro_stateline.glcode_valid_yn FROM ud_bankpro_stateline JOIN ud_bankpro_allocation ON ud_bankpro_stateline.allocation = ud_bankpro_allocation.allocation WHERE ud_bankpro_allocation.output_fund='Y' AND ud_bankpro_stateline.state_date=? AND ud_bankpro_stateline.account_no=? ORDER BY ud_bankpro_stateline.allocation"
or
SQLSelectString = "SELECT a.line_id, a.line_value, a.allocation, a.gl_code, a.narrative_2, a.output_cmi_date, a.fund_valid_yn, a.glcode_valid_yn FROM ud_bankpro_stateline a JOIN ud_bankpro_allocation b ON a.allocation = b.allocation WHERE b.output_fund='Y' AND a.state_date='12/12/2012' AND a.account_no='54244870' ORDER BY a.allocation"
ud_bankpro_stateline.line_id
= Primary key and is set as PK on the datbase.
I can either command directly at the datbase and it works.
I don't understand why it works fine in Oracle but fails here.
I am returning a Primary Key and I am only pulling data back from one table.
The dataadapter is returning rows.
Can anyone tell me what I am doing wrong?
(I have tried removing the parameters and hard coding values, cutting down the return values all work directly against the database but not through the command builder)
thanks