Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Search criteria problem.

Posted on 2004-04-30
10
Medium Priority
?
243 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
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.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

963 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