Dynamic SQL Question

Hey all,I am trying to write a query against Teradata using a dynamic SQL Statement. Here's what I got:Sub Process(ByVal senderAs Object,ByVal eAs EventArgs)
Dim testAs New TextBox

Dim strSQLAs System.Text.StringBuilder =New StringBuilder("SELECT TELEPHONE, BUSINESS_NAME, TRADESTYLE, SECOND_TRADESTYLE, PHYSICAL_STREET_ADDRES, SECOND_STREET_ADDRESS, PHYSICAL_CITY, PHYSICAL_STATE, MAIL_ADDRESS, MAIL_ADDRESS_2, MAIL_CITY, MAIL_STATE, DUNS_NUMBER , PARENT_DUNS_NUMBER, HEADQUARTERS_DUNS_NUMB, GLOBAL_ULT_DUNS_NUMBER, DOMESTIC_ULT_DUNS_NUMB, PARENT_HQ_NAME, GLOBAL_ULT_BUSINESS_NA, DOMESTIC_ULT_BUSINESS_, FAMILY_UPDATE_DATE FROM VLBS_ECV.DNB_CORE")

If Len(NAME.Text) > 0Then
strSQL.AppendFormat(" WHERE BUSINESS_NAME LIKE'{0}%'", NAME.Text)
End If

If Len(Address.Text) > 0 Then
strSQL.AppendFormat(" {1} PHYSICAL_STREET_ADDRES LIKE'{0}%'", Address.Text, (strSQL.ToString().Contains("WHERE")) & " AND " & "WHERE")

End If


Dim strSQL1As String = strSQL.ToString()
test.Text = strSQL1
Dim connStrAs String ="Dsn=Connection Data"
Dim DBConnectionAs OdbcConnection =New OdbcConnection(connStr)
Dim dsAs New DataSet

Dim tblAdapterAs New OdbcDataAdapter(strSQL1, connStr)
tblAdapter.Fill(ds)
'Assign the datagrid's datasource to the datatable
SearchResults.DataSource = ds
SearchResults.DataBind()
End Sub If I put the name in, I get the query to work. But if I add another line, this is what is getting passed over:"SELECT TELEPHONE, BUSINESS_NAME, TRADESTYLE, SECOND_TRADESTYLE, PHYSICAL_STREET_ADDRES, SECOND_STREET_ADDRESS, PHYSICAL_CITY, PHYSICAL_STATE, MAIL_ADDRESS, MAIL_ADDRESS_2, MAIL_CITY, MAIL_STATE, DUNS_NUMBER , PARENT_DUNS_NUMBER, HEADQUARTERS_DUNS_NUMB, GLOBAL_ULT_DUNS_NUMBER, DOMESTIC_ULT_DUNS_NUMB, PARENT_HQ_NAME, GLOBAL_ULT_BUSINESS_NA, DOMESTIC_ULT_BUSINESS_, FAMILY_UPDATE_DATE FROM VLBS_ECV.DNB_CORE WHERE BUSINESS_NAME LIKE 'california%'True AND WHERE PHYSICAL_STREET_ADDRES LIKE '12345%'"ThatTRUE clause is causing my query to blow up. Anybody have ideas why that is doing this?

Why are you using dynamic SQL anyway? A stored procedure would be much more appropriate, even if it's just to protect your application from SQL Injection.

|||

Because I don't have access in the Warehouse to write stored procs. I have select only. Also, I'm putting some validators on the page to prevent malicious code.

|||

In that case you may be stuck but I'd try to get access if you can otherwise you could leave yourself open to attack, and it may also affect the performance of your query.

As for why you are getting the error, theContainsmethod returns a boolean value based on whether the string exists or not.

|||

Why are you checking the length of the input fields? If the length is 0, LIKE '%' will return true...

cm1jm1:

Because I don't have access in the Warehouse to write stored procs. I have select only. Also, I'm putting some validators on the page to prevent malicious code.

This doesn't mean that u should make an SQL string like this. U should use aparameterized query instead, something like:

"SELECT ... FROM VLBS_ECV.DNB_CORE WHERE BUSINESS_NAME LIKE @.name AND PHYSICAL_STREET_ADDRES LIKE @.address"

Create a command object, set this string as the commandText property, add 2 parameter objects (Values name.Text + "%" AND Address.Text + "%") and thats it

|||

Are some of the address columns really spelled ADDRES and other spelled ADDRESS?

|||

I think you are trying achieve something like below, I've modified your code:

Sub Process(ByVal senderAs Object,ByVal eAs EventArgs)Dim testAs New TextBoxDim strSQLAs System.Text.StringBuilder =New StringBuilder("SELECT TELEPHONE, BUSINESS_NAME, TRADESTYLE, SECOND_TRADESTYLE, PHYSICAL_STREET_ADDRES, SECOND_STREET_ADDRESS, PHYSICAL_CITY, PHYSICAL_STATE, MAIL_ADDRESS, MAIL_ADDRESS_2, MAIL_CITY, MAIL_STATE, DUNS_NUMBER , PARENT_DUNS_NUMBER, HEADQUARTERS_DUNS_NUMB, GLOBAL_ULT_DUNS_NUMBER, DOMESTIC_ULT_DUNS_NUMB, PARENT_HQ_NAME, GLOBAL_ULT_BUSINESS_NA, DOMESTIC_ULT_BUSINESS_, FAMILY_UPDATE_DATE FROM VLBS_ECV.DNB_CORE where 1=1")If Len(NAME.Text) > 0Then strSQL.AppendFormat(" and BUSINESS_NAME LIKE'{0}%'", NAME.Text) End If If Len(Address.Text) > 0 Then strSQL.AppendFormat(" and PHYSICAL_STREET_ADDRES LIKE'{0}%'", Address.Text)End If Dim strSQL1As String = strSQL.ToString() test.Text = strSQL1Dim connStrAs String ="Dsn=Connection Data"Dim DBConnectionAs OdbcConnection =New OdbcConnection(connStr)Dim dsAs New DataSetDim tblAdapterAs New OdbcDataAdapter(strSQL1, connStr) tblAdapter.Fill(ds)'Assign the datagrid's datasource to the datatable SearchResults.DataSource = ds SearchResults.DataBind()End Sub

I've changed the way your query is being generated. See the Where 1=1 in your basic query. Then you can just go on to add the clauses with " and ".

Hope this will help.