Databases
StackOverflow2013
Postsdbo
POHow to check whether value already exists or not in database when inserting new record in sql server 2008
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to check whether value already exists or not in database when inserting new record in sql server 2008
    primarykey
    data
    text
    <p>I have created a stored procedure for selecting value from the table if that value already exists then it will update that value but if not exists then it will insert new value. </p> <p>I used stored procedure for that as follows :</p> <pre><code>Create PROCEDURE [dbo].[sp_Insert] ( @Tid int = NULL, @Quid int = NULL, @Selected int = NULL, @Score int = NULL ) AS DECLARE @AlreadyExisted BIT SET @AlreadyExisted = 0 IF (EXISTS (SELECT Quid FROM Sel_Ans) BEGIN SET @AlreadyExisted = 1 UPDATE Sel_Ans SET Selected=@Selected WHERE Quid = @Quid END ELSE BEGIN INSERT INTO Sel_Ans (Quid, Selected) VALUES (@Quid, @Selected) END RETURN </code></pre> <p>And in C# (Asp.net) I used this code but its giving error "Error connecting to the database" as its going in try block. Suggest me any changes to my code to work correctly.</p> <pre><code>//session["Counter"] is my counter that will give next record in next click event if (Convert.ToInt32(Session["Counter"]) &lt;= 7) { int t = Convert.ToInt32(Session["Counter"]); SqlCommand MyCmd = new SqlCommand("sp_Insert", sqlconn); MyCmd.CommandType = CommandType.StoredProcedure; SqlParameter objParam1 = null; SqlParameter objParam2 = null; SqlParameter objParam3 = null; SqlParameter objParam4 = null; objParam1 = MyCmd.Parameters.Add("@Tid", SqlDbType.Int); objParam1.Value = '1'; objParam2 = MyCmd.Parameters.Add("@Quid", SqlDbType.Int); objParam2.Value = a[t - 2]; objParam3 = MyCmd.Parameters.Add("@Selected", SqlDbType.Int); objParam3.Value = rb; objParam4 = MyCmd.Parameters.Add("@Score", SqlDbType.Int); objParam4.Value = '1'; try { if (sqlconn.State == ConnectionState.Closed) { sqlconn.Open(); MyCmd.ExecuteNonQuery(); } } catch (Exception ex) { Response.Write("Error Connecting to Database!"); } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USuser707294
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to check whether value already exists or not in database when inserting new record in sql server 2008
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou shouldn't hide the error in the catch block by outputting your own error message. For instance, in this case, you don't know if the error occurred during Open or ExecuteNonQuery. Instead you should Response.Write(ex.ToString()) - at least until all your issues are resolved. The error message in this case will give you enough context to figure out what is going on. Second point: you didn't show how sqlconn was instantiated. Please clarify that in the code. Third point: you can use MERGE to do INSERT/UDPATE together in a single statement.
      singulars
      1. POHow to check whether value already exists or not in database when inserting new record in sql server 2008
      1. USAzeem
    2. COhow i can do merge statement here
      singulars
      1. POHow to check whether value already exists or not in database when inserting new record in sql server 2008
      1. USuser707294
    3. CO1. What does the error message in `ex` tell you? 2. The SP has a missing where clause in `if exists` query and one syntax error. 3. What version of SQL Server do you use? Merge can be used in SQL Server 2008.
      singulars
      1. POHow to check whether value already exists or not in database when inserting new record in sql server 2008
      1. USMikael Eriksson
 

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