kyriakos70
asked on
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)
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)
SQL Syntax:
"SELECT * FROM TableName WHERE city LIKE ' %S'
@tapanpattanaik,
That is for something that ends with "S", not starts!
That is for something that ends with "S", not starts!
sorry to kyriakos70 and jpaulino,
I have mention the "%" mark in wrong location
correct location:
SQL Syntax:
"SELECT * FROM TableName WHERE city LIKE ' S %'
I have mention the "%" mark in wrong location
correct location:
SQL Syntax:
"SELECT * FROM TableName WHERE city LIKE ' S %'
ASKER
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
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
ASKER
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Form2.SqlDataAdapter2.Fill (Form2.Dat aSet21)
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'
Form2.SqlDataAdapter2.Fill
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'
ASKER
I have inserted this :
Try
TextBox1.Text = Form2.SqlDataAdapter2.Sele ctCommand. Parameters .Item(0).V alue
Form2.SqlDataAdapter2.Fill (Form2.Dat aSet21)
...
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
Try
TextBox1.Text = Form2.SqlDataAdapter2.Sele
Form2.SqlDataAdapter2.Fill
...
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
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 & "%')
WHERE (aa LIKE '" & me.textbox1.text & "%')
ASKER
Hello,
Found it Thank you all.
Kyriakos
Found it Thank you all.
Kyriakos
ASKER
Hello,
Not found all, it worked after I have changed to :
Try
Form2.SqlDataAdapter2.Sele ctCommand. Parameters .Item(0).V alue = TextBox1.Text
Form2.SqlDataAdapter2.Fill (Form2.Dat aSet21)
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.Sele ctCommand. Parameters .Item(2).V alue = TextBox1.Text
Form2.SqlDataAdapter2.Fill (Form2.Dat aSet21)
Kyriakos
Not found all, it worked after I have changed to :
Try
Form2.SqlDataAdapter2.Sele
Form2.SqlDataAdapter2.Fill
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.Sele
Form2.SqlDataAdapter2.Fill
Kyriakos
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Any update ?
ASKER
And how do I add the '%' operator?
ASKER
Works.
Thank you
Thank you
You add the operator in the SQL command and you pass only the parameter.
ASKER
No,
it is not working now tells me it needs a value even with one parameter.
it is not working now tells me it needs a value even with one parameter.
You have to show the code ?
ASKER
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
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
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx
Somehting like:
"SELECT * FROM myTable WHERE [Field1] LIKE '" & Me.TextBox1.TexT & "%'"