textbox and SQL Statement

[xswzaq]

I have a problem with SQL, I am trying to make the SQL statment to read the textbox, but couldn't. In TextBox if users enter 100, it the SQL in Where Clause it will know ID = 100, and if if users enter 150, it will know ID = 150. The the SQL below, it did not give me any error, but as the same time it did not give my back any data. Blank DatagridView. I don't have any idea how to fix this. Please guide me. Thank you.

[NOTE] I don't have dataset in my form. I use code to open connection and get the data I want from the database table.

Dim IDSearch As New OracleClient.OracleDataAdapter("SELECT Name, ID, Test, Last, First, Status
FROM Test
WHERE (Status is not Null) and (ID = ' " & Form1.IDTextBox.Text & " ' )" , conn)

 

[Adrenaline Junkie]

Have you called the .Fill method to get the data? So far you have only made something that can get it but you don't show if you actually did go get it.

Dim DA As New OracleClient.OracleDataAdapter("Select * FROM MyTable", Conn)
Dim DS as new DataSet

DA.Fill(DS)

MyDatagridView.Datasource = DS.Tables(0)

 

[cjard]

see: signature/dw2 link/creating a form to search data

 

[xswzaq]

Yes, I have sucess fill it in the in the table, right now I want to do a search. Like if I type some number in IDtextbox, it will go out and search throught ID column and give me what value I type in IDtextbox, but I could not make the sql above the read the IDtextBox. It give me blank datagrid. The problem I have is this part (ID = ' " & Form1.IDTextBox.Text & " ' )"

 

[Adrenaline Junkie]

Dim IDSearch As New OracleClient.OracleDataAdapter("SELECT Name, ID, Test, Last, " & _
"First, Status FROM Test WHERE (Status is not Null) and (ID = ' " & IDTextBox.Text & " ' )" , conn)

That should work. Otherwise I would delete the textbox and drag a new one onto the page from the toolbox and make sure it is an asp one, not a regular html input control.

 

[cjard]

Yes, I have sucess fill it in the in the table, right now I want to do a search. Like if I type some number in IDtextbox, it will go out and search throught ID column and give me what value I type in IDtextbox, but I could not make the sql above the read the IDtextBox. It give me blank datagrid. The problem I have is this part (ID = ' " & Form1.IDTextBox.Text & " ' )"

see: signature/dw2 link/creating a form to search data

 

[cjard]

Dim IDSearch As New OracleClient.OracleDataAdapter("SELECT Name, ID, Test, Last, " & _
"First, Status FROM Test WHERE (Status is not Null) and (ID = ' " & IDTextBox.Text & " ' )" , conn)

That should work. Otherwise I would delete the textbox and drag a new one onto the page from the toolbox and make sure it is an asp one, not a regular html input control.

1) Please try to avoid advising struggling programmers to use SQLs built through string concatenation. For more information, see the PQ link in my signature
2) When did this become an ASP question?

 

[xswzaq]

there is no way go around it except connect it to the database and use Add Query in Data Menu?
Thanks for all the reply.

 

[cjard]

Um. What's the logic in ignoring the database search capabilities (i.e. what a database is built for), and downloading hundreds of rows you might not want to look at, just so you can use something that wasnt designed solely for fast searching of data (the client side) to search data?

 

[xswzaq]

Because i am going to add ini file in the sql. And the query did not except ini file, beside that I am also having problem with "case when" statement (sql does not except case), that why I try to do search through code instead of using wizzard.

 

[cjard]

Sorry..this post doesnt make much sense to me. Can you phrase it a different way?

 

[xswzaq]

I got it to work by using

Dim IDSearch As New OracleClient.OracleDataAdapter("SELECT Name, ID, Test, Last, First, Status
FROM Test
WHERE (Status is not Null) and (ID = ' " & Form.ComboBox1.SelectedItem.ToString & " ' )" , conn)

Thank you for all your help and support

 

[cjard]

@AdrenalinJunkie - see what I mean?