EDN Admin
Well-known member
We are writing a program to work alongside our SQL/VB.NET-based POS system.
We can both co-exist in the same SQL instance, but we have to keep the two databases separate. Any extra columns that dont belong in their tables causes their DB maintenance to fail, so I have a second database with all of their table names, with column
1 being the GUID from their table, and the remaining columns are the ones that I needed to add, and Im going insane having to split between the two. Ive looked into a lot of different ways to handle my issue below, and the only thing that I can seem
to get working are datasets created at runtime through code. I can get SELECTs into my program just fine, but my problems are when I want to run loops on the datasets, and then insert, update, or delete back to SQL.
My first problem is that every time I run the select command to fill the dataset, it duplicates the entries in the dataset. The only way I know how to stop it is to clear the dataset before each poll, but I dont want to erase whats in the dataset. I want
to insert the records that dont exist, and update the ones that have changed since the last poll. How can I say "only insert new records into the dataset that are not already in there"?
I have a Windows service set up with a timer that runs a "Check for New Tickets" function stored in the SQL Server every 5 seconds to check for new tickets. It returns 1 if there are New Tickets, or 0 for No New Tickets. When SQL returns 1 to the service,
then the service will temporarily stop the timer to process stored procedure 2, which gets all the new ticket GUIDs and inserts them into a dataset to process. The part that Im having trouble with is using if-then logic to run different procedures based
on different variables returned from the database (i.e. what items are on the ticket, who created the ticket, if its from workstation B, run this procedure, mark the ticket as synced, etc.) So my goal is to get data from the SQL server, run logic to
update the dataset, and then update it back to the SQL server, or insert/delete/etc.). I know its possible because this book I have steps you through it, but the books examples dont fit what Im trying to do.
For example, there is a bit column called TicketHasCarWash, 1 is yes, and 0 is no. If the ticket has a car wash, I need to determine what kind of car wash they have. There is a table called TicketItems where the car wash type is stored. I need to
say "For each ticketGUID in the dataset where "HasCarWash = 1", UPDATE LocalDataSet SET CarWashType = (SELECT CarWashType FROM SQL.dbo.TicketItems WHERE TicketGUID = @TheTicketGUIDOfTheCurrentRowInTheDataset) Next.
As I typed all this out, I got an idea, but if you have any suggestions please help.
Thanks
<br/>
<br/>
View the full article
We can both co-exist in the same SQL instance, but we have to keep the two databases separate. Any extra columns that dont belong in their tables causes their DB maintenance to fail, so I have a second database with all of their table names, with column
1 being the GUID from their table, and the remaining columns are the ones that I needed to add, and Im going insane having to split between the two. Ive looked into a lot of different ways to handle my issue below, and the only thing that I can seem
to get working are datasets created at runtime through code. I can get SELECTs into my program just fine, but my problems are when I want to run loops on the datasets, and then insert, update, or delete back to SQL.
My first problem is that every time I run the select command to fill the dataset, it duplicates the entries in the dataset. The only way I know how to stop it is to clear the dataset before each poll, but I dont want to erase whats in the dataset. I want
to insert the records that dont exist, and update the ones that have changed since the last poll. How can I say "only insert new records into the dataset that are not already in there"?
I have a Windows service set up with a timer that runs a "Check for New Tickets" function stored in the SQL Server every 5 seconds to check for new tickets. It returns 1 if there are New Tickets, or 0 for No New Tickets. When SQL returns 1 to the service,
then the service will temporarily stop the timer to process stored procedure 2, which gets all the new ticket GUIDs and inserts them into a dataset to process. The part that Im having trouble with is using if-then logic to run different procedures based
on different variables returned from the database (i.e. what items are on the ticket, who created the ticket, if its from workstation B, run this procedure, mark the ticket as synced, etc.) So my goal is to get data from the SQL server, run logic to
update the dataset, and then update it back to the SQL server, or insert/delete/etc.). I know its possible because this book I have steps you through it, but the books examples dont fit what Im trying to do.
For example, there is a bit column called TicketHasCarWash, 1 is yes, and 0 is no. If the ticket has a car wash, I need to determine what kind of car wash they have. There is a table called TicketItems where the car wash type is stored. I need to
say "For each ticketGUID in the dataset where "HasCarWash = 1", UPDATE LocalDataSet SET CarWashType = (SELECT CarWashType FROM SQL.dbo.TicketItems WHERE TicketGUID = @TheTicketGUIDOfTheCurrentRowInTheDataset) Next.
As I typed all this out, I got an idea, but if you have any suggestions please help.
Thanks
<br/>
<br/>
View the full article