Search text in database

[vks.gautam1]

I am developing a VB project in which i want to search a value .& a text

In database i set
Mobile_no Field Text property .
now i want to search a no.

select * from table_name where mobile_no= ' " & textbox1.text & " '

But im not finding no.

i want to search a string in SQL SERVER 2005 database how to write a query for this.

 

[cjard]

read the PQ link in my signature, then read the DW2 link in my signature, sections: Creating a Simple Data App, and Creating a Form to Search Data

 

[vks.gautam1]

Actually i was wrong at syntex. ex select * from tablename where empname=' " & textbox1.text & " '
i was giving space between ' & ".That is where my text was not matching with database.

 

[cjard]

Read the PQ link in my signature, and hopefully you'll realise that this is completely the wrong way to form an SQL command text. I understand why your query didnt work, but what I'm trying to say is that concatenating strings together to make SQL is a very, very bad way of doing things. If you'd done things the correct way:

SELECT * FROM table WHERE column = @id

...
cmd.Parameters.AddWithValue("@id", myTextBox.Text)


Then your problem would never have occurred

 

[vks.gautam1]

Thanks , I will try , then i will reply u soon

 

[vks.gautam1]

I am getting an Error.

" Procedure or Function 'sp2' expects parameter ' @emp_id ' , which was not supplied.

I want to search .

this is stores procedure.

create procedure[dbo].[sp3]
@emp_no varchar(3),
@emp_name varchar(19),
@dep  varchar(5)
as 
select * from emp_detail where emp_no=@emp_no

This is my form code

Imports System.Data.SqlClient
Public Class Form1
    Dim cn As SqlConnection
    Dim cmd As SqlCommand


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            cmd = New SqlCommand
            cn = New SqlConnection("initial catalog=emp;integrated security=true;data source=.\sqlexpress;")
            cn.Open()
            cmd.Connection = cn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "sp3"
            cmd.Parameters.AddWithValue("@emp_no", TextBox1.Text)

            cmd.Parameters.AddWithValue("@emp_no", TextBox2.Text)
            cmd.Parameters.AddWithValue("@dep", TextBox3.Text)
            cmd.ExecuteNonQuery()
            MsgBox("Search")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

Please get me right as i know my basics are not correct

 

[cjard]

You just need to be more precise, use your eyes more and your fingers less

I am getting an Error. " Procedure or Function 'sp2' expects parameter ' @emp_id ' , which was not supplied.

I want to search .

this is stores procedure.

create procedure[dbo].[sp3]
@emp_no varchar(3),
@emp_name varchar(19),
@dep varchar(5)
as
select * from emp_detail where emp_no=@emp_no





This is my form code



Imports System.Data.SqlClient
Public Class Form1
Dim cn As SqlConnection
Dim cmd As SqlCommand


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
cmd = New SqlCommand
cn = New SqlConnection("initial catalog=emp;integrated security=true;data source=.\sqlexpress;")
cn.Open()
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp3"
cmd.Parameters.AddWithValue("@emp_no", TextBox1.Text)

cmd.Parameters.AddWithValue("@emp_no", TextBox2.Text)
cmd.Parameters.AddWithValue("@dep", TextBox3.Text)
cmd.ExecuteNonQuery()
MsgBox("Search")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class

 

[MattP]

While you're learning you may want to choose a useful naming convention for you stored procedures. Sp3 is going to mean nothing to a fellow programmer or probably even you when you need to look for it next year. proc_EmpDetailByIdSelect however does tell you at a glance what the procedure does.

All internal procedures use the prefix sp_. All stored procedures starting with this prefix check the master table first so you will take a performance hit. It's also easier to find your procedures if you have a different prefix so they're all grouped together.

A good example of a stored procedure naming convention can be found here Link

 

[cjard]

I've never seen that guy go to any effort to name his variables; check out his other posts full of Button1, TextBox47.. I wouldnt be surprised if your very sensible advice goes unheeded

 

[MattP]

Just figured if he couldn't remember what procedure he was using same day he's never going to remember it when something breaks and he needs to research it.

I get the added benefit of knowing that others see the post and may learn something in the process though.

 

[cjard]

Tip about the sp_ check was handy for me.. I prefer to avoid SQLS if possible, but things like that are handy to know

 

[vks.gautam1]

Now tell me how to search No with the help of Stored Procedures

create procedure[dbo].[sp_SearchNo]
@emp_no varchar(3),
@emp_name varchar(19),
@dep varchar(5)
as 
select * from emp_detail where emp_no=@emp_no

This is my form code

Imports System.Data.SqlClient
Public Class Form1
    Dim cn As SqlConnection
    Dim cmd As SqlCommand


    Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Try
            cmd = New SqlCommand
            cn = New SqlConnection("initial catalog=emp;integrated security=true;data source=.\sqlexpress;")
            cn.Open()
            cmd.Connection = cn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "sp_SearchNo"
            cmd.Parameters.AddWithValue("@emp_no", TxtEmpNo.Text)

            cmd.Parameters.AddWithValue("@emp_name", TxtEmpName.Text)
            cmd.Parameters.AddWithValue("@dep", TxtDep.Text)
            cmd.ExecuteNonQuery()
            MsgBox("Search is Finished")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

Tell me about other mistakes also

 

[cjard]

Tell me about other mistakes also

Mistake #1: We're not your personal debugging service

Mistake #2: Visual Basic .NET Forums - BB Code List

 

[MattP]

Mistake #3: Reinventing the wheel.

Do yourself a favor and start here: Link. The time you spend learning how to do this stuff will pay off for you.