Databases
StackOverflow2013
Postsdbo
POINSERT VALUES WHERE NOT EXISTS
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POINSERT VALUES WHERE NOT EXISTS
    primarykey
    data
    text
    <p>OK so I'm trying to improve my asp data entry page to ensure that the entry going into my data table is unique.</p> <p>So in this table I have SoftwareName and SoftwareType. I'm trying to get it so if the entry page sends an insert query with parameters that match whats in the table (so same title and type) then an error is thrown up and the Data isn't entered.</p> <p>Something like this:</p> <pre><code>INSERT INTO tblSoftwareTitles( SoftwareName, SoftwareSystemType) VALUES(@SoftwareName,@SoftwareType) WHERE NOT EXISTS (SELECT SoftwareName FROM tblSoftwareTitles WHERE Softwarename = @SoftwareName AND SoftwareType = @Softwaretype) </code></pre> <p>So This syntax works great for Selecting columns from one table into another without duplicates being entered but doesn't seem to want to work with a parametrized insert query. can anyone help me out with this?</p> <p>Cheers Dan</p> <p>Edit:</p> <p>Here's the code I'm using in my asp insert method</p> <pre><code> private void ExecuteInsert(string name, string type) { //Creates a new connection using the HWM string using (SqlConnection HWM = new SqlConnection(GetConnectionStringHWM())) { //Creates a sql string with parameters string sql = " INSERT INTO tblSoftwareTitles( " + " SoftwareName, " + " SoftwareSystemType) " + " SELECT " + " @SoftwareName, " + " @SoftwareType " + " WHERE NOT EXISTS " + " ( SELECT 1 " + " FROM tblSoftwareTitles " + " WHERE Softwarename = @SoftwareName " + " AND SoftwareSystemType = @Softwaretype); "; //Opens the connection HWM.Open(); try { //Creates a Sql command using (SqlCommand addSoftware = new SqlCommand{ CommandType = CommandType.Text, Connection = HWM, CommandTimeout = 300, CommandText = sql}) { //adds parameters to the Sql command addSoftware.Parameters.Add("@SoftwareName", SqlDbType.NVarChar, 200).Value = name; addSoftware.Parameters.Add("@SoftwareType", SqlDbType.Int).Value = type; //Executes the Sql addSoftware.ExecuteNonQuery(); } Alert.Show("Software title saved!"); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Insert Error:"; msg += ex.Message; throw new Exception(msg); } } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTinydan
    1. USTinydan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POINSERT VALUES WHERE NOT EXISTS
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou probably want to use something like `INSERT INTO ... SELECT @P1, @P2 WHERE NOT EXISTS ...`
      singulars
      1. POINSERT VALUES WHERE NOT EXISTS
      1. USta.speot.is
    2. COSQL2008+ : You could use `MERGE` statement [link](http://technet.microsoft.com/en-us/library/bb510625.aspx)
      singulars
      1. POINSERT VALUES WHERE NOT EXISTS
      1. USBogdan Sahlean
    3. COAnd how about using some constraints? Unique key?
      singulars
      1. POINSERT VALUES WHERE NOT EXISTS
      1. USOzrenTkalcecKrznaric
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload