VB help with getting a SQL 'If Not Exists' to work

[Rage187]

'Creates SalesPoint record for computer name

strSQL = "if not exists (select * from SalesPoints where Slpnumber = 999) " + vbCrLf

strSQL = strSQL + "Declare @counter int exec @counter=getnextcounter 999,'TableSalesPoints'" + vbCrLf

strSQL = strSQL + "Insert into SalesPoints Select @counter,1,999,'" + Environment.MachineName + "','',0.00,2,0.00,0,0,0,0.00,1,1,1,1,1,0,1,getdate(),0,'','',0,0,0,0,0,0,1001,'" + Environment.MachineName + "'"




I run it but it just keeps creating a new entry in the table even though there is already a record with a slpNumber=999

 

[KLin]

Try this

strSQL = "if (select COUNT(*) from SalesPoints where Slpnumber = 999) = 0 " + vbCrLf

 

[Rage187]

same thing, keeps creating more entries for the same record.

 

[KLin]

strSQL = "if (select COUNT(*) from SalesPoints where Slpnumber = 999) = 0 " + vbCrLf + "BEGIN" + vbCrLf

strSQL = strSQL + "Declare @counter int exec @counter=getnextcounter 999,'TableSalesPoints'" + vbCrLf

strSQL = strSQL + "Insert into SalesPoints Select @counter,1,999,'" + Environment.MachineName + "','',0.00,2,0.00,0,0,0,0.00,1,1,1,1,1,0,1,getdate(),0,'','',0,0,0,0,0,0,1001,'" + Environment.MachineName + "'" + vbCrLf + "END"

Try that

 

[Rage187]

I had thought about adding the Begin and End, let me try now

 

[Rage187]

That did it. Thanks, been racking my brain for hours.

 

[Rage187]

If your interested in another

'Creates Printers record for a Windows Printer

strSQL = "if(select COUNT(*) from Printers where prname='Windows Printer') = 0 " + vbCrLf + "Begin" + vbCrLf

strSQL = strSQL + "declare @counter int exec @counter=getnextcounter 999,'TablePrinters'" + vbCrLf

strSQL = strSQL + "insert into printers select @counter ,'Windows Printer',ptid,'\\CHS-PRINT1\HP4350-S401D',0,0,1,0,0,'',0,getdate(),'','','',0,'',0,0,'1900-01-01' from printertypes where ptname = 'Generic Windows'" + "END"



Dim AddPrinter As New OleDb.OleDbCommand(strSQL, Conn)

AddPrinter.Connection.Open()

AddPrinter.ExecuteNonQuery()

Conn.Close()



it is throwing up a OleDBException: An expression of non-boolean type specified in a context where a condition is expected, near 'Begin'.

My first VB programming attempt so I'm determined to get this going.

 

[KLin]

Sounds like there's a problem with one of the values you're trying to use in the insert statement to me.

 

[KLin]

Says to use exists