Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
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:
C#
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
C#
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
Posted
Updated 4-Jan-13 5:29am
v2

You said that you have defined the primary key for ud_bankpro_stateline and the select returns it, but what about ud_bankpro_allocation?

In your query you join two tables so if you update the results, it would lead to a situation where two tables should be updated. Because of this, key information from both tables is needed.

I'm not sure about multi table updates with SQL Server ODBC driver (I understood that this is what you use). It could also be that even if you define the keys for both tables, the driver isn't capable of updating both tables.
 
Share this answer
 
I am afraid I had to give up on this. I cannot see why it shouldn't work. -but it doesn't
In the end I pumped out the oracle commandbuilder output and used that as a basis to write my own command commands (it is much easier than I imagined) and now it works fine.
Mr Graus is right; it seems like you will get problems with command builders sooner or later so best to get used to writing your own commands.

Thanks for the input everyone.
 
Share this answer
 
It sounds like the issue is that a column was a key in Oracle and not SQL Server. Either way, using this automagic stuff instead of writing your own data layer will always end in grief.
 
Share this answer
 
Comments
markpearson 7-Jan-13 4:11am    
Thank you very much for the responces all help is very appreciated.

I will try returning a PK for ud_allocations but I didn't think I needed to as it will not be updated. I am only using the join to cut down my selection return (i.e only return records where the b.output_fund='Y') and I don't want to update that table.

Yes Christian initally I was suprised this object worked at all. 8-)
But now I have a bee in my bonnet as it worked (and still works) in Oracle with the same sql string and an equivelant table structure.

Logically it must be a problem with my tables (unless there is a Visual Studio issue) I will see if I can get hold of a DBA to check my tables.

Thank you very much for all help recieved so far and if anyone has any ideas I would love to hear them
thanks again.
Christian Graus 7-Jan-13 4:26am    
If you go to the SQL Server tools, right click on a table and click script as... create, you can see a script with all the properties of the table. If Oracle has the same, you should compare them. I doubt they are the same
markpearson 7-Jan-13 4:56am    
I have included the ud_allocation PK in the return and now I get this error.
'Dynamic SQL generation is not supported against multiple base tables.'
LOL I appear to be inbetween a rock and a hard place.
Just as a note when I run that exact SQL against the Oracle tables it works fine. I guess Oracle is just more forgiving than MS.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900