Friday, March 23, 2012

problem with If Exist select

Have patience, I'm just a script kiddie. I'm trying to write a
vbsscript that queries a sql 2005 database to see if a record exists
and if so update some values and if it doesn't then insert an entry. On
the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
getting the following error:
Char: 11
Error: Syntax error
Code: 800a03ea
Can anyone tell me what I'm doing wrong.
Thanks.
Set objCon = CreateObject("adodb.connection")
objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
uid=something; pwd=something")
on error resume Next
If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
& ")"
Else
strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
& _
"values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
strProc & ", " & strMem & ")"
End If
if err.number <> 0 then
msgbox err.description
end if
on error resume next
objCon.Execute(strSQL)
if err.number <> 0 then
msgbox err.description
end if
objCon.Closemcgrew.michael@.gmail.com wrote:
> Have patience, I'm just a script kiddie. I'm trying to write a
> vbsscript that queries a sql 2005 database to see if a record exists
> and if so update some values and if it doesn't then insert an entry. On
> the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
> getting the following error:
> Char: 11
> Error: Syntax error
> Code: 800a03ea
> Can anyone tell me what I'm doing wrong.
> Thanks.
>
> Set objCon = CreateObject("adodb.connection")
> objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
> uid=something; pwd=something")
> on error resume Next
> If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
> & ")"
> Else
> strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
> & _
> "values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
> strProc & ", " & strMem & ")"
>
instead of using if exits (select...........)
u should open a recordset of this SQL query and check condition....
let say if Rst as recorset..
then ur statement should be like this
if Rst.eof then
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
'" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
strMem
& ")"
else
....|||Better yet, put stored procedures in your database and call them from VB.
Pass criteria using the parameters objects. Dynamic SQL can get you into
all kinds of problems.
Also, you can include all of this logic in a single stored procedure and
make one call to the database, rather than having the VB app checking all of
this logic on the client. You need to look at the type of logic you need to
enforce and determine whether to do this on the client app or the database,
but it is worth considering.
SQL Injection and Parameters:
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.sqlservercentral.com/col...qlinjection.asp
Dynamic SQL in stored procedures:
http://www.sommarskog.se/dynamic_sql.html
"SQL-Star (Rajeev Shukla)" <dreams.alot@.gmail.com> wrote in message
news:1145465365.635163.158830@.e56g2000cwe.googlegroups.com...
> mcgrew.michael@.gmail.com wrote:
> instead of using if exits (select...........)
> u should open a recordset of this SQL query and check condition....
> let say if Rst as recorset..
> then ur statement should be like this
> if Rst.eof then
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
> '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
> strMem
> & ")"
> else
> ....
>

No comments:

Post a Comment