Solved

sql server vb .net how to search for records

Posted on 2009-05-05
18
182 Views
Last Modified: 2012-05-06
hello,
I have a database application, I used sqlconnection, sqldataadapter and dataset. I have all the functions (update,delete,insert) but I want to have a textbox to search for records based on the textbox string eg. if the textbox has  an "s" to display the records of the cities which start with "s" (cities is a record field)
0
Comment
Question by:kyriakos70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
18 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 24303239
You can use a SqlDataReader and use a correct T-SQL command.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx 
Somehting like:
 "SELECT * FROM myTable WHERE [Field1] LIKE '" & Me.TextBox1.TexT & "%'"
0
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 24303290

SQL Syntax:

"SELECT * FROM TableName WHERE city LIKE ' %S'
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24303315
@tapanpattanaik,
That is for something that ends with "S", not starts!
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 24303386
sorry to kyriakos70 and jpaulino,
     
                             I have mention the "%" mark in wrong location

correct location:

SQL Syntax:

"SELECT * FROM TableName WHERE city LIKE ' S %'
0
 

Author Comment

by:kyriakos70
ID: 24305794
Yes but I have created a dataadapter with this parameter ' SELECT     id, aa, DIAKRITIKO, date_fortosis, num_autokinitou, topos_fortosis, topos_proorismou, apostoleas, receiver, entoleas, parastatiko_apostolea, product,
                      litres, kilos, net_value, base_flag, center_pump, various_expenses, FPA, tolls, General_sum, comments, payer, payer_id
FROM         Taxinomisi_fortotikon
WHERE     (aa LIKE @aaa + '%') '
How can I add the text box to this statment?

Kyriakos
0
 

Author Comment

by:kyriakos70
ID: 24305994
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Form2.SqlDataAdapter2.Fill(Form2.DataSet21)
End Sub

' SELECT     id, aa, DIAKRITIKO, date_fortosis, num_autokinitou, topos_fortosis, topos_proorismou, apostoleas, receiver, entoleas, parastatiko_apostolea, product,
                      litres, kilos, net_value, base_flag, center_pump, various_expenses, FPA, tolls, General_sum, comments, payer, payer_id
FROM         Taxinomisi_fortotikon
WHERE     (aa LIKE 'form2.textbox1.text') <changed to this but I get nothing I want the parameter to have the value of the textbox1 and display the results by pressing a button (button1_click procedure'
0
 

Author Comment

by:kyriakos70
ID: 24306153
I have inserted this :
Try
TextBox1.Text = Form2.SqlDataAdapter2.SelectCommand.Parameters.Item(0).Value
Form2.SqlDataAdapter2.Fill(Form2.DataSet21)
...
and get this error
parametrized query (@aaa int) needs value ... expects parameter which was not supplied

and the sql string : ..... FROM  Taxinomisi_fortotikon
WHERE  (aa LIKE @aaa) <changed to this
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24306560
If you will use parameters you define the parameter later, if you do that in the string you can do:
WHERE    (aa LIKE '" &  me.textbox1.text & "%')
 
0
 

Author Comment

by:kyriakos70
ID: 24306707
Hello,
Found it Thank you all.

Kyriakos
0
 

Author Comment

by:kyriakos70
ID: 24307814
Hello,
Not found all, it worked after I have changed to :
Try
Form2.SqlDataAdapter2.SelectCommand.Parameters.Item(0).Value = TextBox1.Text
Form2.SqlDataAdapter2.Fill(Form2.DataSet21)

But I want to have multiple parameters in the sql stament and choose in what parameter to give a value

SELECT     id, aa, DIAKRITIKO, date_fortosis, num_autokinitou, topos_fortosis, topos_proorismou, apostoleas, receiver, entoleas, parastatiko_apostolea, product,
                      litres, kilos, net_value, base_flag, center_pump, various_expenses, FPA, tolls, General_sum, comments, payer, payer_id
FROM         Taxinomisi_fortotikon
WHERE     (aa LIKE @aad) OR
                      (DIAKRITIKO LIKE @diak) OR
                      (date_fortosis LIKE @date) OR
                      (num_autokinitou LIKE @num + '%') OR
                      (topos_fortosis LIKE @toposf + '%') OR
                      (topos_proorismou LIKE @toposp + '%') OR
                      (apostoleas LIKE @apost + '%') OR
                      (receiver LIKE @reiceiv + '%') OR
                      (entoleas LIKE @entolea + '%') OR
                      (parastatiko_apostolea LIKE @parast_ap + '%') OR
                      (product LIKE @produc + '%') OR
                      (litres LIKE @litr) OR
                      (kilos LIKE @kil) OR
                      (net_value LIKE @netv) OR
                      (base_flag LIKE @basef) OR
                      (center_pump LIKE @centerp) OR
                      (various_expenses LIKE @varexp) OR
                      (FPA LIKE @fpa) OR
                      (tolls LIKE @toll) OR
                      (General_sum LIKE @gens) OR
                      (comments LIKE @comm + '%') OR
                      (payer LIKE @paye + '%')
when I give a value to parameter num(2)(@date) it keeps telling me that needs a value for parameter num(0) (@aad)
how can I have multiple parameters and give only to one value to return me the record(s) :
eg. Form2.SqlDataAdapter2.SelectCommand.Parameters.Item(2).Value = TextBox1.Text
Form2.SqlDataAdapter2.Fill(Form2.DataSet21)

Kyriakos

0
 
LVL 48

Accepted Solution

by:
jpaulino earned 500 total points
ID: 24308731
You use:
Form2.SqlDataAdapter2.SelectCommand.Parameters.Add("@parametername", parameterType).Value = TextBox1.Text

 
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24312581
Any update ?
0
 

Author Comment

by:kyriakos70
ID: 24312711
And how do I add the '%' operator?
0
 

Author Comment

by:kyriakos70
ID: 24312778
Works.

Thank you
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24312866
You add the operator in the SQL command and you pass only the parameter.
0
 

Author Comment

by:kyriakos70
ID: 24316860
No,
it is not working now tells me it needs a value even with one parameter.

0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24316881
You have to show the code ?
0
 

Author Comment

by:kyriakos70
ID: 24327665
here is the code of the commandtext of the select command but it is always returns the original commandtext, how can I change it at runtime or how can I use the sqldatareader and show the results to a dbgrid?
code below doesn' work
Select Case ComboBox1.Items.Count
            Case 0              Form2.SqlDataAdapter2.SelectCommand.CommandText = "SELECT * FROM Taxinomisi_fortotikon WHERE aa LIKE @aad"
                Form2.SqlDataAdapter2.SelectCommand.Parameters.Item(0).Value = TextBox1.Text
            Case 1
                Form2.SqlDataAdapter2.SelectCommand.CommandText = "SELECT * FROM Taxinomisi_fortotikon WHERE DIAKRITIKO LIKE @diak"
                Form2.SqlDataAdapter2.SelectCommand.Parameters.Item(0).Value = TextBox1.Text

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question