Solved

Search criteria problem.

Posted on 2004-04-30
10
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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