Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

sql server vb .net how to search for records

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
kyriakos70
Asked:
kyriakos70
  • 9
  • 7
  • 2
1 Solution
 
jpaulinoCommented:
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
 
Tapan PattanaikSenior EngineerCommented:

SQL Syntax:

"SELECT * FROM TableName WHERE city LIKE ' %S'
0
 
jpaulinoCommented:
@tapanpattanaik,
That is for something that ends with "S", not starts!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Tapan PattanaikSenior EngineerCommented:
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
 
kyriakos70Author Commented:
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
 
kyriakos70Author Commented:
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
 
kyriakos70Author Commented:
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
 
jpaulinoCommented:
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
 
kyriakos70Author Commented:
Hello,
Found it Thank you all.

Kyriakos
0
 
kyriakos70Author Commented:
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
 
jpaulinoCommented:
You use:
Form2.SqlDataAdapter2.SelectCommand.Parameters.Add("@parametername", parameterType).Value = TextBox1.Text

 
0
 
jpaulinoCommented:
Any update ?
0
 
kyriakos70Author Commented:
And how do I add the '%' operator?
0
 
kyriakos70Author Commented:
Works.

Thank you
0
 
jpaulinoCommented:
You add the operator in the SQL command and you pass only the parameter.
0
 
kyriakos70Author Commented:
No,
it is not working now tells me it needs a value even with one parameter.

0
 
jpaulinoCommented:
You have to show the code ?
0
 
kyriakos70Author Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now