Solved

Search criteria problem.

Posted on 2004-04-30
10
238 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Close form "before" open 3 49
Help with converting xml file to excel using VB.NET 3 25
No Data for DropDown List 2 29
VB.Net - String Manipulation Scenario 2 26
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
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.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 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