Sql Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sql Query

Author

Topic

saidev
Posting Yak Master

101 Posts

Posted - 2006-07-05 : 17:42:53

Hi Guys,
My Query is
SELECT * FROM tblbook WHERE InActive = 0 OR Inactive IS NULL

I am getting the error incorrect Syntax at 'WHERE'

also the whole query works fine in the Query Analyzer. But in the application it doesn't work.

If i just use SELECT * FROM tblbook it works fine in the application. any ideas..?
Thanks

My application query is

Dim sql As String
Dim sqlWhere As String
Dim objconn As New SqlConnection(CONNECTIONSTRING)
Dim objcmd As SqlCommand = New SqlCommand
objcmd.Connection = objconn

Try
sql = "SELECT * FROM tblbook WHERE InActive = 0 OR Inactive IS NULL "

If Me.ddContractType.SelectedValue <> "" Then
If sqlWhere = "" Then
sqlWhere = "WHERE fkcontracttype = '" & ddContractType.SelectedValue & "'"
Else
sqlWhere = sqlWhere & " AND fkcontracttype = '" & ddContractType.SelectedValue & "'"
End If
End If

If Me.txtContractNo.Text <> "" Then
If sqlWhere = "" Then
sqlWhere = "WHERE pkid = '" & Me.txtContractNo.Text & "'"
Else
sqlWhere = sqlWhere & " AND pkid = '" & Me.txtContractNo.Text & "'"
End If
End If

If Me.ddGroup.SelectedValue <> "" Then
If sqlWhere = "" Then
sqlWhere = "WHERE fkgroup = '" & ddGroup.SelectedValue & "'"
Else
sqlWhere = sqlWhere & " AND fkgroup = '" & ddGroup.SelectedValue "'"
End If
End If

objcmd.CommandText = sql & sqlWhere
Dim objda As SqlDataAdapter = New SqlDataAdapter(objcmd)
Dim objds As DataSet = New DataSet
objda.Fill(objds, "tblbook")
Session("DataSet") = objds
Dim objDataRow As DataRow

objDataRow = objds.Tables("tblbook").Rows(0)
If objds.Tables("tblbook").Rows.Count > 0 Then
populateGUI(objDataRow)

I am using this Query in the Search Functionality. Any ideas why it is not working. Appreciate your help.

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 18:05:30

All the WHERE's following

WHERE InActive = 0 OR Inactive IS NULL

should be changed to AND's, because you already have stated WHERE. You are just adding more cases to the first WHERE.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 18:08:02

Or smarter way is

sql = "SELECT * FROM tblbook WHERE (InActive = 0 OR Inactive IS NULL)"

If Me.ddContractType.SelectedValue <> "" Then
   sql = sql & " AND fkcontracttype = '" & ddContractType.SelectedValue & "'"
End If

If Me.txtContractNo.Text <> "" Then
   sql = sql & " AND pkid = '" & Me.txtContractNo.Text & "'"
End If

If Me.ddGroup.SelectedValue <> "" Then
    sql = sql & " AND fkgroup = '" & ddGroup.SelectedValue & "'"
End If

In both cases you are exposed to the possibility of an SQL injection. Íf you have the knowledge, change code to use Command Object instead, and let SQL server stored procedure handle values/parameters that are empty.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-06 : 02:38:37

Use Stored Procedures with parameters. Avoid concatenated SQL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources