scheidel21
Member
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:
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.
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
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