Using TableAdapters to insert row if does not already exist

[shalan99]

Hi everyone!

I am creating a Windows Form app in VB.NET. I have a dataset with 1 dataTable. Using the TableAdapter Queries, I have created a SQL Insert query, which I can call successfully when users enter text into a single textbox and then click "Add". However, I want to first ensure that the text being entered in, does not exist anywhere in the second column (non-primary key column) before the insert is called.

In the past, I would have created a stored proc starting with an "IF EXISTS..." and have a return parameter so that I can pop a message to the user to inform them that the text already exists. However, using this same approach to construct a Stored Proc in the TableAdapter Query Configuration Wizard, it tells me: "The EXISTS SQL construct or statement is not supported"

What else can I do to achieve my intention?

Much thanx
Shalan99

 

[cjard]

Hi everyone!

I am creating a Windows Form app in VB.NET. I have a dataset with 1 dataTable. Using the TableAdapter Queries, I have created a SQL Insert query, which I can call successfully when users enter text into a single textbox and then click "Add". However, I want to first ensure that the text being entered in, does not exist anywhere in the second column (non-primary key column) before the insert is called.

Add a unique index on the second column. An attempt to enter a duplicate value will result in an error

 

[shalan99]

thank u as always cjard. fyi - your DW2 data walthroughs 2.0 have helped me alot!

thanks!