Update statement OleDb from queried results SQL Server 05 field cannot be bound

scheidel21

Member
Joined
May 17, 2007
Messages
8
Location
Connecticut, USA
Programming Experience
5-10
Hi all I know the title is a little messed up, but trying to convey a lot in it. I am writing an application to change the value in a sequnce field on a table from an SQL server 05 backend. In order to calculate and write the appropriate value I need to pull information from the table I need to modify and another table. So I write a query to pull the information this is below:

VB.NET:
SELECT item.itm_id, item.itm_num, item.itm_prodname, catalogitem.cat_id, catalogitem.ctg_id, catalogitem.itm_seq 
FROM catalogitem INNER JOIN item ON catalogitem.itm_id = item.itm_id 
WHERE catalogitem.cat_id = 4 
ORDER BY item.itm_num

This works fine, I then populate a datagrid with this sorting it by a category ID. So now I have everything alphabetized and grouped by their category. I can now go through a loop of each record and write the new value for the sequence starting at 0 for each category and going up until the end of items in the category. This works flawlessly up to this point. When I am done doing this to all the categories I want to update the database table. that holds the sequence information. This first issue I run into is an error that Dynamic SQL generation is not possible accross multiple tables. Fair enough I will just write the update statement myself. I write it and then I run into an error that says the object is set to a null reference. Oh that sucks,, but OK some research and I modify the Update query, and I am finally at a point where the error says that the column I want to update cannot be bound. Research leads me to think this error is coming from the SQL server which means there is probably an issue with my Update statement. I also tried this entire thing by creating a view in the SQL Server and then using that instead of writing the actual SQL SELECT statement in VB, then tried to use that view to update the table but I received the same error regarding not being able to bind the column. Here is a sample of my update statement when I was using the view I have in the SQL database.

VB.NET:
UPDATE catalogitem SET catlogitem.itm_seq = simplecatitems.itm_seq From simplecatitems as simplecatitems 
WHERE catalogitem.cat_id = simplecatitems.cat_id AND  catalogitem.ctg_id = simplecatitems.ctg_id AND catalogitem.itm_id = simplecatitems.itm_id

Some notes: I am using OleDb connectors, I have the data I am modifying in a local datatable in a dataset. And I haven't written complex SQL since College over 9 years ago.

Any assistance in this would be appreciated. A sample Update statement would be killer. I know I could import the tables I need and use temp tables and modify the table i need to update by itself in a seperate dataadapter but that would greatly impact the performance of this app. Additionally I could brute force it by writting out my modified information then importing it into a table and creating an update query through SQL management studio or ACCESS, but that also is not optimal.

Thank you all,

Alex
 
Back
Top