Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> Visual Basic >> Visual Basic Language

SQL Search Statement Error

I have this code below to handle my search, but it could not recognize the part & SearchForm.ComboBox1.Text & . I think the the way I make the SQL to read combobox1 is wrong, but I don't know the right code to make sql read the combo1. I really appreciate any help. Thank you.

IfComboBox1.SelectedIndex >= 0Then

Code Snippet

Dim houseSearchAsNew OracleClient.OracleDataAdapter("SELECT NAME, STATUS, ID, TIME AS TODAY_DATE FROM Table1 WHERE (ID = " & SearchForm.ComboBox1.Text &") AND (TIME > TO_DATE('6/1/2007', 'MM/DD/YYYY')) AND (STATUS <> 'VOID')", conn)

Dim house_DatasetAs DataSet =New DataSet("houseSearch")

houseSearch.Fill(house_Dataset,"houseSearch")

SearchTable.DataGrid.DataSource = house_Dataset

SearchTable.DataGrid.DataMember ="houseSearch"

End If

[1743 byte] By [Angel_LL] at [2008-1-10]

«« Team Foundation Automated Builds with a makefile
»» How do I make _CRT_SECURE_CPP_OVERLOAD_STANDARD_NAMES work?

# 1

Try:

& SearchForm.ComboBox1.SelectedText &

& SearchForm.ComboBox1.SelectedValue &

You really should look at switching to using paramaterized SQL if possible.

jgalley at 2007-10-3 >

top of Msdn Tech,Visual Basic,Visual Basic Language...

# 2

& SearchForm.ComboBox1.SelectedText &

& SearchForm.ComboBox1.SelectedValue &

both of them are give same error... ORA-00936

Angel_LL at 2007-10-3 >

# 3

If you look in the debugger,

what is the value of:

"SELECT NAME, STATUS, ID, TIME AS TODAY_DATE FROM Table1 WHERE (ID = " & SearchForm.SelectedText & ") AND (TIME > TO_DATE('6/1/2007', 'MM/DD/YYYY')) AND (STATUS <> 'VOID')"

it looks like you might be passing in an incomplete statment. If SearchForm.SelectedText contained an ' or " then you might have a problem.

jgalley at 2007-10-3 >

# 4

not working, if I put (ID = ' " & SearchForm.SelectedText & " ' ), I don't get any data back

if I put (ID = " " & SearchForm.SelectedText & " " ), different error come up -- ORA-00972-Identifier too long

Angel_LL at 2007-10-3 >

# 5

what about if you look at the text in the debugger?

if there are problems with embedded characters you might need to do something like

SearchForm.SelectedText.Replace("'", "")

this gets a lot easier if you use parameterized sql :-)

jgalley at 2007-10-3 >

# 6

Thank You for all your help jgallery. I also want to use parameterized sql.

Angel_LL at 2007-10-3 >

# 7

Hi Angel,

Based on your post, my understanding of your question is that you need to use SelectCommand with parameter.

Please see this example. It creates an OracleDataAdapter and sets the SelectCommand properties with the parameter. You can modify your query statement similar the following code snippet. Please try this. Hope this helps.

Code Snippet

Dim startDate As DateTime = DateTime.Now.Subtract(TimeSpan)

Dim command As New OracleCommand()

command.Connection = con

command.CommandText = "SELECT EVENTID, TITLE, ABSTRACT," + "EVENTDATE FROM EVENT where EVENTDATE >= :startdate and" + " eventdate <= :enddate"

command.CommandType = System.Data.CommandType.Text

command.Parameters.Add(New OracleParameter("startdate", startDate))

command.Parameters.Add(New OracleParameter("enddate", DateTime.Now))

Dim dataAdapter As New OracleDataAdapter(command)

Dim dataTable As New DataTable()

dataAdapter.Fill(dataTable)

Best regards,

Riquel.

RiquelDong–MSFT at 2007-10-3 >

Visual Basic Hot Topic

Visual Basic New Topic

Visual Basic

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech