Data Extension Parameters

Hi,
I've implemented a data extension to use a custom business class to return a
dataset. This all works fine but I cannot seem to get parameters working. Has
anyone got any code snippets of creating parameters and the parameters being
available at run-time & design time.
All the examples I've seen don't use parameters so any help would be much
appreciated as I've got to produce this ASAP.
RegardsHi,
OK - I've worked out how to get the parameters in designer mode via the
GetParameters method. However, how do I get the parameters that I add via the
Report Dialog in VS.NET?
I've tried passing the parameter collection object but this comes back with
no items although there is one displayed in the preview.
Regards
"MikeD" wrote:
> Hi,
> I've implemented a data extension to use a custom business class to return a
> dataset. This all works fine but I cannot seem to get parameters working. Has
> anyone got any code snippets of creating parameters and the parameters being
> available at run-time & design time.
> All the examples I've seen don't use parameters so any help would be much
> appreciated as I've got to produce this ASAP.
> Regardssql

Data Driven

Hi,
I understand I can create a stored procedure to return all the parameters
for data driven. The last statement of the Stored Procedure is Select *
DataDrivenTableName. However, after I put Exec SPName, and when i go to the
next page to fill out all the To, CC, Subject..., there is nothing in each
drop down box and I can't continue to finish the setup.
Any idea what's wrong.
Thanks
EdAFAIK RS uses first resultset to get data, not last.
Stjepan
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2F0D6FE4-2FA8-49EB-A965-D0A57D47362C@.microsoft.com...
> Hi,
> I understand I can create a stored procedure to return all the parameters
> for data driven. The last statement of the Stored Procedure is Select *
> DataDrivenTableName. However, after I put Exec SPName, and when i go to
> the
> next page to fill out all the To, CC, Subject..., there is nothing in each
> drop down box and I can't continue to finish the setup.
> Any idea what's wrong.
> Thanks
> Ed

Data Convertion Error

I am using SQL server 2005, Visual Web Developer 2005 express (for right now). Can get the stored procedure to run fine if I do not return the CityID.

Stored Procedure

ALTER Procedure [dbo].[WDR_CityAdd1]

(

@.CountryID int,

@.CityName nvarchar(50),

@.InternetAvail bit,

@.FlowersAvail bit,

@.CityID int OUTPUT

)

AS

IF EXISTS(SELECT 'True' FROM city WHERE CityName = @.CityName AND CountryID = @.CountryID)

BEGIN

SELECT

@.CityID = 0

END

ELSE

BEGIN

INSERT INTO City

(

CountryID,

CityName,

InternetAvail,

FlowersAvail

)

VALUES

(

@.CountryID,

@.CityName,

@.InternetAvail,

@.FlowersAvail

)

SELECT

@.CityID = 'CityID' ( I have also tried = @.@.Identity but that never returned anything it is an identity column 1,1)

END

Here is the code on the other end. I have not included all the parameters, but should get a sense of what I am doing wrong.

Dim myCommand As New SqlCommand("WDR_CityAdd1", dbConn)

myCommand.CommandType = CommandType.StoredProcedure

Dim parameterCityName As New SqlParameter("@.CityName", SqlDbType.NVarChar, 50)

parameterCityName.Value = CityName

myCommand.Parameters.Add(parameterCityName)

Dim parameterCityID As New SqlParameter("@.CityID", SqlDbType.Int, 4)

parameterCityID.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterCityID)

Try

dbConn.Open()

myCommand.ExecuteNonQuery()

dbConn.Close()

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

'AlertMessage = "City Added"

Catch ex As Exception

AlertMessage = ex.ToString

End Try

Here is the error I get. So what am I doing wrong? I figured maybe in the stored procedure. CityID is difined in the table as an int So why is it telling me that it is a varchar when it is defined in the stored procedure, table and code as an int? Am I missing something?

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'CityID' to data type int. at System.Data.SqlClient.SqlConnection.OnError

Thanks

Jerry

There are a couple of issues:

grbourque wrote:

...

SELECT @.CityID = 'CityID'

...

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

...

--> Conversion failed when converting the varchar value 'CityID' to data type int.

In this location,

SELECT @.CityID = 'CityID'

You are attempting to assign the @.CityID (an integer) the string value 'CityID'. That is what caused the error you reported.

I suggest using SCOPE_IDENTITY to capture the IDENTITY value of the last row entered by the current user/session.

SET @.CityID = SCOPE_IDENTITY()

Also, the RETURN value 'should' be an integer, and you are attempting to return a string value.

('Normally', one would use the RETURN value for success/failure reporting.)

Somewhere prior to the RETURN statement, you 'should' assign the parameterCityID value to a previously declared variable, then use that variable in your application.

|||

Arnie

I had tried playing around with the 'cityID' fieldname and was getting different error messages. I have re-written the code with your help and it works. Sometimes this just amazes me. I do appreciate the help. Maybe with time I will be able to help others as I get a handle on this.

Jerry

Data by ID and Getdate function

I am trying to return data by date but I also need to return data by date "and" by ID. I've queried various select statements but always get only one days worth of data and the date is repeated many times. I only want to return data for today only but with ID as the other variable.

For example I want only the shows for today by date and by ID. ID of course being the key in the DB. Below I will show you a code block followed by a text version of what it looks like in the browser when tested.

Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Kuow\Kuow.UDL"
set recProgram = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101))"

'strSQL = "SELECT *, Air_Date AS Expr1, Unit AS Expr2 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) AND (Unit = 'TB')"
recProgram.Open strSQL,con
%>

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Output:
ID Unit Subject Title Long_Summary Body_Text Related_Events Air_Date AudioLink

(Reading across the screen from left to right)
1234 WK1 Subject Title a summary some body text Event text 4/13/2007 wkdy20070413-a.rm

Here is the URL used for testing:
http://Test Server IP/test/defaultweekday2.asp

I need to be able to append to this URL an ID number so that not only do I get content by Air_Date but also by ID.

http://Test Server IP/test/defaultweekday2.asp?ID=1234

How to do this?

You might want to look in Books Online about the usage of GROUP BY.

Code Snippet

GROUP BY convert( varchar(10), getdate(), 101 )) , Unit

|||Ok I will do that but for now is your example a working example? If not what other examples could I try?|||

It 'should' work IF you change the SELECT to

"SELECT *, Air_Date = convert( varchar(10), getdate(), 101 )), Unit FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

|||I tested your suggested by replacing the second half of my select query with your code starting with WHERE...

When I tested it I got this

Microsoft OLE DB Provider for SQL Server error '80040e14'

GROUP BY expressions must refer to column names that appear in the select list.

/test/defaultweekday2.asp, line 24

Code Snippet

strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

|||Ok I see what your getting at however I just tested it in my browser and got this:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near ')'.

/test/defaultweekday2.asp, line 22

Code Snippet

strSQL = "SELECT *, Air_Date = convert( varchar(10), getdate(), 101 )), Unit FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

I counted the ( ) to make sure there was the correct number of left and rights ones. Does it not like the end of the line or what?|||

As the error message indicates (and you might want to read up on using GROUP BY), any column in the GROUP BY MUST also be in the SELECT list.

Your GROUP BY includes Unit, and the SELECT list does not.

I think that the query I posted earlier 'should' work. But this alteration will not.

GROUP BY requires ALL columns in the SELECT list to EITHER be in the GROUP BY clause, or be aggregations. Trying to select all columns with a [SELECT * ] will not work.

I suggest that you start out small, perhaps with the query that I posted earlier, try to understand how GROUP BY works, and then expand your query a small piece at a time.

|||Arnie -

Sounds good. I'm reading up on it now as I learn and thank you for your most recent reply. Your thinking is helping me think. I'm sure it's obvious I'm new to SQL. I'll be glad when I get good enough that I can provide the people I work for and with the answers they seek in a relatively quick turnaround.

There is nothing worse then having work that is over your head and your figuring out how to do it while your solving real world business problems. Can be stressful. But hey it's one way to ensure I'll remember it.