<div>
you can set a unique constraint on WHCode and ItemCode columns. Which will prevent the duplicates...
</div>


<div>
No, I don't want to.<br />
<br />
I need to do it using query.<br />
<br />
Thank you.<br />
<br />

</div>


<div>
declare a variable<br />
set it to a deafult value i.e 0<br />
select number of rows from &nbsp;TMSTOCK where the column value are equal to the one that you are trying to insert and assign that number to your variable.<br />
<br />
if the variable value not equal to the default then the values are exists do not insert <br />
else the values are not present insert them.
</div>


<div>
Please see below code:<br />
<br />
INSERT INTO TMSTOKBAHAN(KodeStok &nbsp;, GdgCode &nbsp;, BahanCode &nbsp;, QtyStok &nbsp;, Satuan &nbsp;, AutoYN &nbsp;, CrtId, CrtDate, UpdId, UpdDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) WHERE (SELECT COUNT(GdgCode) FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1 &nbsp;' AND BahanCode = '5A130') = 0<br />
<br />
Line 1: Incorrect syntax near '='. Incorrect syntax near the keyword 'WHERE'.<br />
<br />
What's wrong with it?<br />
<br />
Thank you.
</div>


<div>
Check this one<br />

<pre><code class="code-10 nolinks" id="code-221476"><span>-- Assume that this is your table</span>
<span>CREATE TABLE TMSTOCK(WHCode int,ItemCode int,Qty int)</span>
<span>INSERT INTO TMSTOCK VALUES(1,2,3)</span>
<span>INSERT INTO TMSTOCK VALUES(1,3,3)</span>
<span>INSERT INTO TMSTOCK VALUES(1,4,5)</span>
<span>INSERT INTO TMSTOCK VALUES(2,3,10)</span>
<span>-- The data in your table</span>
<span>SELECT * FROM TMSTOCK</span>
<span>-- This is the data which you want to insert into your table based on your condition</span>
<span>DECLARE @WHCode int,@ItemCode int,@Qty int</span>
<span>   SET @WHCode = 1</span>
<span>   SET @ItemCode = 4</span>
<span>   SET @Qty = 20</span>
<span>-- This is the condition to check whether the record already present in your table. </span>
<span>-- If not insert into your table</span>
<span>IF NOT EXISTS(SELECT 1 FROM TMSTOCK WHERE WHCode = @WHCode AND ItemCode = @ItemCode) </span>
<span>INSERT INTO TMSTOCK VALUES(@WHCode,@ItemCode,@Qty)</span>
<span>-- check data in your table again whether duplicate record is inserted or not</span>
<span>SELECT * FROM TMSTOCK</span>
</code></pre>
</div>


<div>
you can not use WHERE cluase in an insert statement<br />
<br />
you can use such a statement<br />
<br />
INSERT INTO TMSTOKBAHAN(KodeStok &nbsp;, GdgCode &nbsp;, BahanCode &nbsp;, QtyStok &nbsp;, Satuan &nbsp;, AutoYN &nbsp;, CrtId, CrtDate, UpdId, UpdDate) <br />
SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ? <br />
FROM TMSTOKBAHAN <br />
WHERE GdgCode &lt;&gt;&nbsp;'GDGUBS1 &nbsp;' <br />
&nbsp; &nbsp; AND BahanCode &lt;&gt;&nbsp;'5A130'<br />
<br />
<br />
<br />

</div>


<div>
<br />

<pre><code class="code-1 nolinks" id="code-221478"><span>IF NOT EXISTS (SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1  ' AND BahanCode = '5A130')</span>
<span>INSERT INTO TMSTOKBAHAN(KodeStok  , GdgCode  , BahanCode  , QtyStok  , Satuan  , AutoYN  , CrtId, CrtDate, UpdId, UpdDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) </span>
</code></pre>
</div>


<div>
Hi Sharath,<br />
<br />
Unfortunately I don't use SP.<br />
<br />
INSERT INTO TMSTOKBAHAN(KodeStok &nbsp;, GdgCode &nbsp;, BahanCode &nbsp;, QtyStok &nbsp;, Satuan &nbsp;, AutoYN &nbsp;, CrtId, CrtDate, UpdId, UpdDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) WHERE NOT EXIST (SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1 &nbsp;' AND BahanCode = '5A130 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ') <br />
<br />
Line 1: Incorrect syntax near '='. Incorrect syntax near the keyword 'WHERE'.<br />
<br />
What's wrong with it?<br />
<br />
Thank you.
</div>


<div>
Hi Sharath,<br />
<br />
&nbsp;IF NOT EXIST (SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1 &nbsp;' AND BahanCode = '5A130') &nbsp;INSERT INTO TMSTOKBAHAN(KodeStok &nbsp;, GdgCode &nbsp;, BahanCode &nbsp;, QtyStok &nbsp;, Satuan &nbsp;, AutoYN &nbsp;, CrtId, CrtDate, UpdId, UpdDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? )<br />
<br />
Line 1: Incorrect syntax near ')'. Incorrect syntax near the keyword 'SELECT'.<br />
<br />
Thank you.
</div>


<div>
I forgot, SQL Server 7.<br />
<br />
Thank you.
</div>


<div>
can you run these queries and post the solution?<br />

<pre><code class="code-1 nolinks" id="code-221491"><span>--Query 1</span>
<span>SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1  ' AND BahanCode = '5A130'</span>
<span>--Query 2</span>
<span>IF NOT EXIST (SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1  ' AND BahanCode = '5A130')  </span>
<span>--Query 3</span>
<span>IF NOT EXIST (SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1  ' AND BahanCode = '5A130')  </span>
<span>INSERT INTO TMSTOKBAHAN(KodeStok  , GdgCode  , BahanCode  , QtyStok  , Satuan  , AutoYN  , CrtId, CrtDate, UpdId, UpdDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? )</span>
</code></pre>
</div>


<div>
I try :<br />
<br />
SELECT *<br />
FROM TMSTOKBAHAN<br />
WHERE (SELECT COUNT(BAHANCODE)<br />
&nbsp; &nbsp; &nbsp; &nbsp;FROM TMSTOKBAHAN<br />
&nbsp; &nbsp; &nbsp; &nbsp;WHERE GDGCODE = 'A') = 0<br />
<br />
This is working in SQL PANE.<br />
<br />
Thank you.
</div>


<div>
can you post the result/errors of my 3 queries?
</div>


<div>
IF NOT EXIST (SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1 &nbsp;' AND BahanCode = '5A130')<br />
<br />
Incorrect syntax near the keyword 'SELECT'.<br />
<br />
Thank you.
</div>


<div>
Yes, but it return -1<br />
<br />
What does it mean?<br />
<br />
Thank you.
</div>


<div>
 Dim intCount As Integer = cmdSQLCommand.ExecuteNonQu<wbr />ery()<br />
<br />
Thank you.
</div>


<div>
did you run the entire statement or only the IF part? run the code<br />

<pre><code class="code-1 nolinks" id="code-221500"><span>IF NOT EXISTS (SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1  ' AND BahanCode = '5A130')  </span>
<span>INSERT INTO TMSTOKBAHAN(KodeStok  , GdgCode  , BahanCode  , QtyStok  , Satuan  , AutoYN  , CrtId, CrtDate, UpdId, UpdDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? )</span>
</code></pre>
</div>


<div>
 IF NOT EXISTS (SELECT 1 FROM TMSTOKBAHAN WHERE GdgCode = 'GDGUBS1 &nbsp;' AND BahanCode = '5A130 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ') &nbsp;INSERT INTO TMSTOKBAHAN(KodeStok &nbsp;, GdgCode &nbsp;, BahanCode &nbsp;, QtyStok &nbsp;, Satuan &nbsp;, AutoYN &nbsp;, CrtId, CrtDate, UpdId, UpdDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? )<br />
<br />
Dim intCount As Integer = cmdSQLCommand.ExecuteNonQu<wbr />ery()<br />
<br />
Thank you.
</div>


<div>
Get it now.<br />
<br />
If return intCount &nbsp;= -1 that means no insertion.<br />
<br />
Thank you very much for your help.
</div>


<div>
yes, i am about to tell that only regarding the -1. Glad to see that you got the solution :)
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
Let's say I have a table TMSTOCK structure like:<br />
<br />
1. WHCode<br />
2. ItemCode.<br />
3. Qty<br />
<br />
I want to insert to the table if (WHCode and ItemCode) not exist in the table.<br />
<br />
How to do it?<br />
<br />
Thank you.
</div>
</div>


Hi,

Let's say I have a table TMSTOCK structure like:

1. WHCode
2. ItemCode.
3. Qty

I want to insert to the table if (WHCode and ItemCode) not exist in the table.

How to do it?

Thank you.

INSERT WITH CONDITION OF TWO FIELD

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.