SQL Configure: input

Showing posts with label input. Show all posts

Thursday, 8 March 2012

Connect 2 DropDownList and Received "Input string was not in a correct format"

I have 2 DropDownList, the first one to display the product category and the other one to display the products based on the selected category. However, when I run code I received the following error message:Input string was not in a correct format.

Please find the code below.

Thanks,

Andy.

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Default.aspx.cs"Inherits="_Default" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"

DataTextField="Name"DataValueField="Name">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Assignment2ConnectionString %>"

SelectCommand="SELECT [Name] FROM [Category]"></asp:SqlDataSource>

<br/>

<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource2"

DataTextField="Name"DataValueField="Name">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:Assignment2ConnectionString %>"

SelectCommand="SELECT [Name] FROM [Product] WHERE ([CategoryID] = @.CategoryID2)">

<asp:ControlParameterControlID="DropDownList1"Name="CategoryID2"PropertyName="SelectedValue"

Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

</div>

</form>

</body>

</html>

Assume you have a categoryID field in your Category table: You add CategoryID to the SELECT statement and changeDataValueField="CategoryID" .

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"

DataTextField="Name"DataValueField="CategoryID">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Assignment2ConnectionString %>"

SelectCommand="SELECT [Name],[CategoryID] FROM [Category]"></asp:SqlDataSource>

<br/>

Wednesday, 7 March 2012

Confused By Error Msg

I am using a VB aspx page to pass a parameter to a stored procedure and to
get two parameters returned. The input parameter is an integer, one of the
output parameters is also and integer and the other output parameter is an
nvarchar. When I run the execute command I get an error:
Implicit conversion from data type sql_variant to nvarchar is not allowed.
Use the CONVERT function to run this query.
I have no idea where the sql_variant type comes from? Any thoughts?
Here is the portion of my code that makes the call followed by the SP code.
========================================
=========
Dim cmd4 As New OleDb.OleDbCommand("GetAdClicks", CommonClass.myConn)
cmd4.CommandType = CommandType.StoredProcedure
cmd4.Parameters.Add("@.id", SqlDbType.Int)
cmd4.Parameters("@.id").Value = Request.Params("ID")
cmd4.Parameters.Add("@.clickcount", SqlDbType.Int)
cmd4.Parameters("@.clickcount").Direction = ParameterDirection.Output
cmd4.Parameters.Add("@.url", SqlDbType.NVarChar, 100)
cmd4.Parameters("@.url").Direction = ParameterDirection.Output
cmd4.ExecuteNonQuery()
=========== SP Code ====================
ALTER Procedure GetAdClicks
(
@.id int,
@.clickcount int OUTPUT,
@.url nvarchar(100) OUTPUT
)
AS
SELECT @.clickcount = clickcount, @.url=url
FROM adserver
WHERE id=@.idWayne,
What is the data type for the URL column in the adserver table?
EXEC SP_COLUMNS AUTHORS
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%23%23Zm2BP2FHA.2440@.TK2MSFTNGP10.phx.gbl...
>I am using a VB aspx page to pass a parameter to a stored procedure and to
>get two parameters returned. The input parameter is an integer, one of the
>output parameters is also and integer and the other output parameter is an
>nvarchar. When I run the execute command I get an error:
> Implicit conversion from data type sql_variant to nvarchar is not allowed.
> Use the CONVERT function to run this query.
> I have no idea where the sql_variant type comes from? Any thoughts?
> Here is the portion of my code that makes the call followed by the SP
> code.
> ========================================
=========
> Dim cmd4 As New OleDb.OleDbCommand("GetAdClicks", CommonClass.myConn)
> cmd4.CommandType = CommandType.StoredProcedure
> cmd4.Parameters.Add("@.id", SqlDbType.Int)
> cmd4.Parameters("@.id").Value = Request.Params("ID")
> cmd4.Parameters.Add("@.clickcount", SqlDbType.Int)
> cmd4.Parameters("@.clickcount").Direction = ParameterDirection.Output
> cmd4.Parameters.Add("@.url", SqlDbType.NVarChar, 100)
> cmd4.Parameters("@.url").Direction = ParameterDirection.Output
> cmd4.ExecuteNonQuery()
>
> =========== SP Code ====================
> ALTER Procedure GetAdClicks
> (
> @.id int,
> @.clickcount int OUTPUT,
> @.url nvarchar(100) OUTPUT
> )
> AS
> SELECT @.clickcount = clickcount, @.url=url
> FROM adserver
> WHERE id=@.id
>
>|||It is nvarchar
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uj3rHFP2FHA.3788@.tk2msftngp13.phx.gbl...
> Wayne,
> What is the data type for the URL column in the adserver table?
> EXEC SP_COLUMNS AUTHORS
> HTH
> Jerry
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%23%23Zm2BP2FHA.2440@.TK2MSFTNGP10.phx.gbl...
>|||Wayne,
What happens if you run the sproc from Query Analyzer?
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:OEExaLP2FHA.3448@.TK2MSFTNGP10.phx.gbl...
> It is nvarchar
> Wayne
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uj3rHFP2FHA.3788@.tk2msftngp13.phx.gbl...
>|||Jerry;
When I run it from QA everything seems fine, I get the count as a number and
the url is a string
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uXH7iPP2FHA.3108@.tk2msftngp13.phx.gbl...
> Wayne,
> What happens if you run the sproc from Query Analyzer?
> HTH
> Jerry
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:OEExaLP2FHA.3448@.TK2MSFTNGP10.phx.gbl...
>|||Wayne,
Ok...that probably removes SQL as the cause. Might want to follow this up
with a programming NG for the programming language you're using.
HTH
Jerry
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%23XKQHtP2FHA.3108@.tk2msftngp13.phx.gbl...
> Jerry;
> When I run it from QA everything seems fine, I get the count as a number
> and the url is a string
> Wayne
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uXH7iPP2FHA.3108@.tk2msftngp13.phx.gbl...
>|||OK - will do.
Thanks for the help.
Wayne
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:upfzonY2FHA.3416@.tk2msftngp13.phx.gbl...
> Wayne,
> Ok...that probably removes SQL as the cause. Might want to follow this up
> with a programming NG for the programming language you're using.
> HTH
> Jerry
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%23XKQHtP2FHA.3108@.tk2msftngp13.phx.gbl...
>

Sunday, 19 February 2012

Confirmation on Script Task Behavior

Hello,

I am looking for some confirmation on a behavior of the SSIS Script Task. I have a custom script task that takes an input file, and archives it after it has been processed into the database.

When I run this package in the Visual Studio GUI, if the destination drive is full, it throws an exception telling me that there is not enough disk space. So, my questions are:

1) If this happens when the package is running through the command line, would this exception still be thrown? (I am thinking it will be)

2) Also, Do I need to explicitly fail the script task in the event handler, in order to ensure this .Net exception being thrown will cause the component to fail. (I am fairly certain I do, since this is what I had to do inside of the Visual Studio GUI, but does anyone know if this same behavior would occur when running from the command line?)

Thanks,

Chris

If you don't catch the exception, it should cause your script task to fail. Otherwise, you would need to explicitly fail it by setting Dts.TaskResult=Dts.Results.Failure. The exception-related behavior will be the same between the GUI and the command-line.

Configuring SSIS package to read a value before executin

Hi,

Is it possible in any ways to Configure a SSIS package in such a way that based on the User Input the package runs. For e.g if there is a table which has say 10 distinct groups. Normal SSIS package would ideally pick all the data from the source to the Destination

I want to know how to configure in Such a way that I should be able to say Group X as the input and data related to GroupX alone should be copied.

Select * from SomeTable where GroupName = @.CongigVar.

how to achieve something like this?

thanks

Hello,

What kind of input are you intrested in getting?

If you have the group name that you want to get information from in a table, you can, in control flow, make a query to a table, and put it on a variable, then on the data flow you can use the variable as a parameter.

To use it on a parameter your query should be

"Select * from SomeTable where GroupName = ?"

Then in the parameter button on the left you choose the variable that will pass tha value to the question mark.

Is that good for you?

Regards,

|||

Hello,

I think that might just be what I need, but when I tried creating one, its failing with the error, 'Parameter Name Unrecognized'. I think am doing it wrongly. Can you post some links that explains how to put a value from a table onto a variable, and then use it as i/p to a Data flow?

thanks

|||

Nfrf wrote:

Hello,
What kind of input are you intrested in getting?
If you have the group name that you want to get information from in a table, you can, in control flow, make a query to a table, and put it on a variable, then on the data flow you can use the variable as a parameter.
To use it on a parameter your query should be
"Select * from SomeTable where GroupName = ?"
Then in the parameter button on the left you choose the variable that will pass tha value to the question mark.
Is that good for you?
Regards,

There's no need to store values in a table. You can parameterize your package by passing it values when you call it. Explore the /SET option of dtexec. All the information you require is in Books Online.

-Jamie

|||

Yes it is true. I just gave him a way.

The thing that i thnik he wanted to know was the parameter on the query.

Even so thanks

Friday, 10 February 2012

Configure SSIS package

I would like to develop a SSIS package where in there are 'X' number of input data sources like flat files, MS Access mdb files etc. The details about the number of input data sources, type of each data sources, the physical path of the data file, name os database server, username for the database server, password for the database server etc has to come from the configuration file.

Can anyone help on this ?

While you can use configurations to set things like database connections, user names, etc, SSIS packages can't change the metadata used in a dataflow at runtime. They aren't self-modifying. If you are trying to build a "generic" package that can handle any data dynamically, you will need to build an application that creates the package on the fly. SSIS has a good API, so this is certainly in the realm of possiblity, but it is still a lot of work.

Thursday, 8 March 2012

Connect 2 DropDownList and Received "Input string was not in a correct format"

Wednesday, 7 March 2012

Confused By Error Msg

Sunday, 19 February 2012

Confirmation on Script Task Behavior

Configuring SSIS package to read a value before executin

Friday, 10 February 2012

Configure SSIS package

SQL Configure

Blog Archive

About Me