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

x
?
Solved

Search criteria problem.

Posted on 2004-04-30
10
Medium Priority
?
246 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
8 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 
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 400 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview
Suggested Courses

564 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