Solved

Search criteria problem.

Posted on 2004-04-30
10
234 Views
Last Modified: 2010-04-24
Hi

I am hoping someone can help me, i am fairly new to VB.NET and have the following problem:

I have a form that has a number of textbox's and combobox's, the user enters the details they want on the form and then press's search, i want to have a search class that i can pass in the criteria to and read the database and pull back the information based on the criteria the user entered on the form. I wanted to use a stored procedure. But how would the storedprocedure know what is getting passed from the form as the user could select different criteria to search from on the screen.

If you could help i would really appreciate it !

Thanks in advance
 
0
Comment
Question by:Puglet
  • 6
  • 2
10 Comments
 
LVL 2

Expert Comment

by:Jeffr0
ID: 10960591
Here is an example Stored Procedure call from my app:


    Public Sub PostEmailMessage(ByVal AddressTo As String, ByVal MessageBody As String, ByVal TrackingNumber As String, ByVal DeveloperID As Integer, ByVal Success As Boolean, ByVal ContentTypeID As Integer)

        Dim cmd As New SqlCommand("sp_PostSupportEmail_CT", _conn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("@AddressTo", SqlDbType.NText)
        cmd.Parameters("@AddressTo").Value = AddressTo

        cmd.Parameters.Add("@MessageBody", SqlDbType.NText)
        cmd.Parameters("@MessageBody").Value = MessageBody

        cmd.Parameters.Add("@TrackingNumber", SqlDbType.VarChar, 50)
        cmd.Parameters("@TrackingNumber").Value = TrackingNumber

        cmd.Parameters.Add("@DeveloperID", SqlDbType.Int)
        cmd.Parameters("@DeveloperID").Value = DeveloperID

        cmd.Parameters.Add("@Success", SqlDbType.Bit)
        cmd.Parameters("@Success").Value = BitBool(Success)

        cmd.Parameters.Add("@ContentTypeID", SqlDbType.Int)
        cmd.Parameters("@ContentTypeID").Value = ContentTypeID

        _conn.Open()
        cmd.ExecuteNonQuery()
        _conn.Close()

    End Sub
0
 
LVL 2

Expert Comment

by:Jeffr0
ID: 10960637
And... here's an example stored procedure from SQL Server:  (Although you would probably want just a "SELECT" query in yours it sounds like...)


CREATE PROCEDURE dbo.sp_PostSupportEmail (@AddressTo AS ntext, @MessageBody AS ntext, @TrackingNumber AS varchar(50), @DeveloperID AS int, @Success as bit)  
  AS

INSERT INTO tblSupportEmails ( AddressTo, MessageBody, TrackingNumber, DeveloperID, Success, DateSent ) VALUES (@AddressTo, @MessageBody, @TrackingNumber, @DeveloperID, @Success, GetDate())
GO
0
 
LVL 2

Expert Comment

by:Jeffr0
ID: 10960661
One more thing-- my vb.net code above doesn't show the connection object being instantiated at the top of the class:

Private _conn As SqlConnection = New SqlConnection("(Your connect string here!")
0
 

Author Comment

by:Puglet
ID: 11087903
Hi

Sorry i didnt get back sooner, i was sick.

The thing i am trying to find out is if say you have a form that a user selects criteria from and based on the criteria the user has selected you do a search on a database table. My problem is that say for example on the form you had Name, Type, Color, if you have your stored procedure set up to take in these values and say the user only selects name and type how does the stored procedure know only to search our database table by name and type ? depending on what has been entered we want to search the table, but my problem is that not all criteria on the form will be used to search for each time so the stored procedure would need to be able to handle this.

Thanks  
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:Jeffr0
ID: 11088695
It sounds like everything you need to do can be done with a SELECT query.

You have several options:

1) Don't use a stored proc; just put your SQL string together with VB.  Use a DataAdapter to .Fill a DataTable in order to get your data.  If the field on the form is left blank, do not include it in the where clause.

example:

if cboName.SelectedText.Length > 0 then strWhere = strWhere & " AND Name = '" & cboName.SelectedText & "'"

2) You can write the same logic in the Stored procedure code.  (SQL Server allows "IF" blocks, for example.)  

Which database are you using?



0
 

Author Comment

by:Puglet
ID: 11088743

Hi

The database i am using is SQL Server.

0
 
LVL 2

Expert Comment

by:Jeffr0
ID: 11089451
Here's one way to do it:

CREATE PROCEDURE [dbo].[AAAsp_Jeffro](@Name varchar(20), @Address varchar(50)) AS

SELECT * FROM tblCustomers WHERE CustomerName LIKE @Name AND MailingAddress  LIKE @Address

GO


When you call the procedure, pass a % sign if they didn't fill out anything for the criteria:

EXEC AAAsp_Jeffro '%', 200 Jetson Ave S. E.'

EXEC AAAsp_Jeffro 'R & W Products', '%'
0
 
LVL 2

Accepted Solution

by:
Jeffr0 earned 100 total points
ID: 11089457
Concacting the WHERE string in VB.Net is the easiest and most flexible way to do it, though.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now