I have a page that askes for a users zip code. When the user clicks submit I want to pass this value to a sql function and return a sales reps name, address, phone, email.

I can return a single value but how do I go about retrieving multiple values.

I'm using asp.net with vb

Thanks

Hi,

What muliple values are you talking about? Like the name, address, phone, email? Or is it more than one salesman? To where are you returning the single value?

Richard

Hi

You can return these values in the form of table, there are broadly 2 types of function, Scalar functions that return single value, & Tabular functions that return data in the form of table, so use Tabular functions for your purpose.

Regards
Mike

Fortune favours the brave, so don't regret on missed oppurtunities.

This is what I have

Function fn_salesrep_lookup_custzip(strZipcode) as String

    Dim strConString as String
    Dim conZip as SqlConnection
    Dim cmdFindRep as SqlCommand



    strConString = ConfigurationSettings.AppSettings("constring")
    conZip = New SqlConnection(strConString)
    cmdFindRep = New SqlCommand("fn_salesrep_lookup_custzip", conZip)
    cmdFindRep.CommandType = CommandType.StoredProcedure

    cmdFindRep.Parameters.Add("@CustomerZip", strZipcode)

    conZip.Open()
    lblName.Text = cmdFindRep.ExecuteScalar()
    conZip.Close()

End Function

and I'm getting this error

The request for procedure 'fn_salesrep_lookup_custzip' failed because 'fn_salesrep_lookup_custzip' is a table valued function object.

I'm not sure on how to get this in a table format.
I what to return Name, Address, City, State, Zip, Phone and email

Sorry I'm just not grasping the concept

Hi

Instead of creating an SQL function, create a stored procedure that will return the rows for the selected customer zip, i.e. pass the customerzip as input parameter to the stored procedure, fetch the returned rows into a dataset, after which you can perform actions on the dataset rows. You can retrieve the values from dataset as below:

ds.Tables(0).Rows(0)("City").ToString()

Regards
Mike

Fortune favours the brave, so don't regret on missed oppurtunities.

Well I changed a lot of code but I'm still getting an error.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:strConString %>"

Could not load type System.Web.UI.WebControls.SqlDataSource from assembly System.Web

Any ideas?

Do I need to be using Asp.net 2.0

I'm using 1.1 at the moment

Yes that class is new in the .NET 2.0 framework you will need to upgrade IIS to 2.0 of the framework.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

Here is the error I'm getting. Any suggestions?

Server Error in '/' Application.
--------------------------------------------------------------------------------

Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: Could not load type System.Web.UI.WebControls.SqlDataSource from assembly System.Web, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a.

Source Error:


Line 361: &nbsp; &nbsp;&nbsp; &nbsp;
Line 362:
Line 363: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings: constring %>"
Line 364: SelectCommand="SELECT * FROM fn_salesrep_lookup_custzip(@CustomerZip)" />
Line 365: <SelectParameters>




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

Did you upgrade to .NET 2 as dparsons suggested? The error message says you're using .NET 1.....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.