Replace Statement Causing Pain

[pozitron969]

Hello,

I am trying to use the Replace Statement to clean up a portion of a SQL statement that I use.

Snippet:

sSQL = "SELECT dbo.FTMILog.Time,dbo.FTMILog.Message, dbo.FTMILog.LogLevel FROM dbo.FTMILog WHERE dbo.FTMILog.Message LIKE '[sMess]' AND dbo.FTMILog.Time > '[sFromDate]' AND dbo.FTMILog.Time < '[sToDate]' AND ([sErrLev])"

'// No input then set to Default Behaviour
if Len(sErrLev) < 1 then 
	'// Default Behaviour is to return all message flag types
	sErrLev = "dbo.FTMILog.LogLevel LIKE 'I' OR dbo.FTMILog.LogLevel LIKE 'N' OR dbo.FTMILog.LogLevel LIKE 'W' OR dbo.FTMILog.LogLevel LIKE 'E'"
'//Temporarily only return Errors
	sErrLev = "dbo.FTMILog.LogLevel LIKE 'E'"
end if

sSQL = Replace(sSQL,"[sMess]",sMsgSearch) 'Works fine
sSQL = Replace(sSQL,"[sFromDate]",sFromDate) 'Works fine
sSQL = Replace(sSQL,"[sToDate]",sToDate) 'Works fine
sSQL = Replace(sSQL,"[sErrLev]",sErrLev) 'Works fine

sSQL = Replace(sSQL,"dbo.FTMILog.Message LIKE ''","") 'Works fine
sSQL = Replace(sSQL,"AND dbo.FTMILog.Time > ''","") 'Works fine
sSQL = Replace(sSQL,"AND dbo.FTMILog.Time < ''","") 'Works fine
sSQL = Replace(sSQL,"WHERE AND","WHERE")  'Doesn't Work

response.write sSQL
Set rs =Server.CreateObject("ADODB.Recordset")
rs.open sSQL, conn

The top 3 Replace statements in the second group work just fine, but the last one won't replace the WHERE AND text with WHERE

Resulting SQL Statement:

SELECT dbo.FTMILog.Time,dbo.FTMILog.Message, dbo.FTMILog.LogLevel FROM dbo.FTMILog WHERE AND (dbo.FTMILog.LogLevel LIKE 'E')

Error Message:
Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'AND'.

/datacenter/ftmidb_search.asp, line 125

From my testing & fiddling it seems like the replace won't work with a space in the first parameter. (which seems crazy to me) But if that is true then why are the other statements working just fine? I have tried rewriting the statement thinking that i have an escape code in there somewhere and it still behaves that way.

Many Thanks in advanced.

Andy

 

[johnwm]

Replace works fine with a space for me. I suspect you may have 2 'AND' in a row. Try looking at the string before and after the replace

...
sSQL = Replace(sSQL,"AND dbo.FTMILog.Time < ''","") 'Works fine
[red]response.write "Before: " & sSQL[/red]
sSQL = Replace(sSQL,"WHERE AND","WHERE")  'Doesn't Work

response.write "After: " & sSQL
...

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[gmmastros]

Try something like this...

sSQL = "SELECT dbo.FTMILog.Time,dbo.FTMILog.Message, dbo.FTMILog.LogLevel FROM dbo.FTMILog WHERE [!]1=1 and [/!] dbo.FTMILog.Message LIKE '[sMess]' AND dbo.FTMILog.Time > '[sFromDate]' AND dbo.FTMILog.Time < '[sToDate]' AND ([sErrLev])"

'// No input then set to Default Behaviour
if Len(sErrLev) < 1 then
    '// Default Behaviour is to return all message flag types
    sErrLev = "dbo.FTMILog.LogLevel LIKE 'I' OR dbo.FTMILog.LogLevel LIKE 'N' OR dbo.FTMILog.LogLevel LIKE 'W' OR dbo.FTMILog.LogLevel LIKE 'E'"
'//Temporarily only return Errors
    sErrLev = "dbo.FTMILog.LogLevel LIKE 'E'"
end if

sSQL = Replace(sSQL,"[sMess]",sMsgSearch) 'Works fine
sSQL = Replace(sSQL,"[sFromDate]",sFromDate) 'Works fine
sSQL = Replace(sSQL,"[sToDate]",sToDate) 'Works fine
sSQL = Replace(sSQL,"[sErrLev]",sErrLev) 'Works fine

sSQL = Replace(sSQL,"[!]AND[/!] dbo.FTMILog.Message LIKE ''","") 'Works fine
sSQL = Replace(sSQL,"AND dbo.FTMILog.Time > ''","") 'Works fine
sSQL = Replace(sSQL,"AND dbo.FTMILog.Time < ''","") 'Works fine
[green]' sSQL = Replace(sSQL,"WHERE AND","WHERE")  'Doesn't Work [!]Don't do this[/!][/green]

response.write sSQL
Set rs =Server.CreateObject("ADODB.Recordset")
rs.open sSQL, conn

Essentially, you should always end up with a Where clause that looks like...


[tt][blue]Where 1=1 And Blah blah blah[/blue][/tt]

I suspect that the reason you were originally having a problem is because there multiple spaces between the WHERE and the AND.

Of course, since you are using SQL Server, you should be made aware that there are MUCH better ways to do this within a stored procedure. Let me know if you'd like some help with the stored procedure approach.

-George

"the screen with the little boxes in the window." - Moron

 

[pozitron969]

Thank you all for your help. I decided to use the Where 1=1 method. Since this is just a simple webpage that only I am going to use I probably won't invest much more effort into the development than this.

Thank you again for your help.