Problem With Variable In DataReader SQLCommand
Nov 20, 2007
Hello,
I'm trying to set the SQLCommand of a DataReader with a string variable (at the DataFlow Task level), and I'm having some problems. The source is a DB2 table. The destination is an MS Excel file. Here is what I have:
1. A variable named SQLStatementDataFlow of type String. EvaluateAsExpression is set to True, and the SQL statement in the expression evaluates without a problem.
2. In the Expression property of the DataFlow Task, I have the [SqlCommand] property configured with the variable.
First, in the component properties of the DataReader, what kind of SQLCommand do I set up here if the DataFlow SQLCommand is configured to use a variable?
Thank you for your help!
cdun2
View 4 Replies
ADVERTISEMENT
Jan 17, 2007
I'm attempting to configure a DataReader's SqlCommand text to use a package-level variable and I'm having trouble. I've read about using the "sql command from variable" option but although I see that during the configuration of Ole DB source, I don't see the same option for DataReader source. Viewing the properties of the DataReader and selecting the SqlCommand property does have button to configure the value but only displays a string value editor window. (I was hoping for the expression editor.)
The configured connection to the DataReader uses the odbc Data Provider (under .Net providers). I'm connecting to mySql, if that matters.
What I'm looking to do is to use a variable in the where clause of the DataReader's SqlCommand. Any hints how to accomplish this or other ideas would be greatly appreciated.
Thanks
View 4 Replies
View Related
Nov 15, 2007
Does anyone know how I can use a user variable in a sqlcommand in a Datareader source with an ODBC connection as the source?
I am storing a date value in a user variable(Date) I fill with a SQL Task and then want to use the value in the sqlcommand I use in the Datareader Source. It won't let me use the @variablename in the sql command.
Can anyone help with some advice on how I can make this work?
Appreciate any help I can get.
Thank you
View 5 Replies
View Related
Aug 27, 2007
Hello,
Newbee here
64 bit SQL 2005 running on Windows Server 2003 X64
I have an exececute SQL task (in the control flow obviously)
SELECT MAX(last_update) AS OrdersLastUpdateFROM orders
This task executes successfully and I can see that my user variable called "User:tmOrdersLastUpdate" populates correctly in the "variables" pane.. ALL GOOD.
The next step of the Control flow is a dataflow task
Details
DataFlow Source = DataReader Source (MySQL .NET connector)
DataFlow Dest = local SQL Server OLE DB.
In the DataFlow Source the DataReader SQLCommand property is
Select * from orders where last_update >= @User:tmOrdersLastUpdate
I've tried every conceivable permutation and I can't get SSIS to itnerpret the variable as such...it always gets passed to the server as a literal.
How do I pass a user-defined global variable to the WHERE clause in a DataRader object?
Thanks
View 5 Replies
View Related
Sep 19, 2006
Hi all,
I have been playing with integration services for a few days and at the moment, its up there with my list of software that I find ......painful.
What I am trying to do is read different tables from my one SQL database, then populate my Access database with its data.
I have put a foreach loop which goes through a collection SQL statements that I have entered into it. It first assigns it to a string variable called tablenameVar which contain statements such as "Select * from Terminals". Then the datareader is supposed to execute it based on the connectstring which never changes, and the SQLCommand value which I passed using the variable @[User::tableName]. However when I try to run it, I'm getting this error.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [DTS.Pipeline]: "component "DataReader Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
Does anyone have an idea of what I should do? or maybe a better way to do it? I appreciate you guys for taking a time to look at this.
Thanks,
Joseph
View 1 Replies
View Related
May 10, 2007
Hi! I need to know what can I do to use a variable in the WHERE condition of the sqlcommand as I show you: current_user = User.Identity.Name
Dim cmd As New SqlCommand("SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = current_user", cn) Obviously it doesn't work and I need your help. Thanks.
View 7 Replies
View Related
May 18, 2007
Can't seem to pass a variable to the sql statement. I'd appreciate any help. I'm trying to pass pColName to CommandText = "ALTER TABLE tb_roomInfo ADD @rColName varchar(50);";Doesn't seem to work though. CODE: [WebMethod] public string addCol(string pColName) { SqlConnection cnn = new SqlConnection(connString); try { cnn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cnn; cmd.CommandText = "ALTER TABLE tb_roomInfo ADD @rColName varchar(50);"; SqlParameter rColName = new SqlParameter("@rColName", pColName); cmd.Parameters.Add(rColName); int i = cmd.ExecuteNonQuery(); cnn.Close(); return "Insert Successful"; } catch { return "Insert Unsuccessful"; } }
View 3 Replies
View Related
Apr 11, 1999
Is it possible to get the result from an EXEC(@sqlcommand) statement into a variable?
As part of a SQL loop, it is necessary for me to run an EXEC() command to process an SQL statement. I have succesfully implemented this, but have been unable to get the results from the EXEC() statement into a variable to allow this data to be inserted into a table. Is this possible?
For Example (I know this doesn't work, but it is effectively what I am trying to achieve):
select @result = EXEC(@sqlcommand)
I could then use the @result variable in an insert statement to update a table with the results from the EXEC command.
Any assistance would be greatly appreciated...
Regards,
Wayne
View 2 Replies
View Related
Apr 7, 2006
Hi all,
I am trying to have a DataReader Source that can run
a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand
that connects to an ODBC connection. If it is possible by any way,
please let me know. Thanks in advance.
Daren
View 14 Replies
View Related
Aug 17, 2007
Dear All,
I have created a DTS Package in Integration Services 2005.
Within the DTS Package declared a variable named xxx and passed a value 1234.
In the control flow i dropped a Data flow task and in the Property Expression Editor of DataFlow Task i defined
Property = [DataReader Source].[sqlCommand]
Expression = Variable name.
Now in the DataFlow Task Canvas dropped DataReaderSource.
How can i pass variable value to the SQLCommand ="Select * from table where name = Variable value.
regards
Sufian
View 4 Replies
View Related
Feb 13, 2007
How can I pass a variable to a DataReader in a DataFlow task?
My SqlCommand for the DataReader is:
SELECT CustName, CustCode FROM Customers WHERE CustCode = '?'
The DataFlow task is nested in a ForEach loop. I confirmed that the variable is changing with each loop by using a ScriptTaks and a message box. However, the DataReader SqlCommand does not seem to be updating.
View 4 Replies
View Related
Sep 21, 2006
Hi,I'm new to ASP.NET, and am currently looking into XML.I'm trying to write XML using data from an SQL Server 2000 table. But I seem to be getting the following error regarding the SQL Server connection:Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid argumentsSource Error:Line 23: {
Line 24: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
Line 25: mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
Line 26: mySqlDataAdapter.Fill(myDataSet);
Line 27: return myDataSet;Source File: c:InetpubwwwrootmappingcreateGeoRSSFile.aspx.cs Line: 25 This is my code:using System;
using System.Data;
using System.Data.SqlClient ;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
public partial class createGeoRSSFile : System.Web.UI.Page
{
protected void Page_Load(object sender, DataSet myDataSet, EventArgs e)
{
string connString = "server=SQLSERV1;database=Historical_Statistics;UID=dbuser;PWD=Password";
string queryString = "SELECT Town, PostCode, Latitude, Longitude FROM UKPostCodes";
using (SqlConnection mySqlConnection = new SqlConnection(connString))
{
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
mySqlDataAdapter.Fill(myDataSet);
return myDataSet;
}
// Create a new XmlTextWriter instance
XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.Unicode);
// Start writing!
writer.WriteStartDocument();
writer.WriteStartElement("item");
// Creating the <town> element
writer.WriteStartElement("town");
writer.WriteElementString("PostCode",myDataSet .Tables[1].Columns("PostCode"));
writer.WriteElementString("geo:lat",myDataSet.Tables[1].Columns("Latitude"));
writer.WriteElementString("geo:lon", myDataSet.Tables[1].Columns("Longitude"));
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Flush();
writer.Close();
}
}What seems to be causing this error?Thanks.
View 4 Replies
View Related
Feb 10, 2008
Hi
Is it possible To pass an SQL command to a ASp.net web service as system.data.SQLclient.sqlcommand?
That means is ispossible to pass the actuall sql command instead of just the string?
If yes how can you do that??
Cheers
View 1 Replies
View Related
Aug 30, 2006
HI!
as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?
how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?
Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?
THANKS, HANNES
View 7 Replies
View Related
Jan 8, 2007
Hi guys. I'm having trouble declaring an sqlcommand. What I want to do is declare a global sqlcommand and I would want this sqlcommand to vary depending on the conditions on my page_load.
Here's the code....
Dim p_s_syounin2 As New SqlCommand
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then
p_s_syounin2 = ("INSERT INTO (p_s_syounin2) SELECT syain_hnm FROM TR_syainID WHERE TR_syainID.syozokubu_id=20 AND TR_syainID.syozokuka_id=21 AND TR_syainID.kaikyuu_id=23, cnn") '''' THIS DOES NOT WORK!
End If
End Sub
p_s_syounin2 .ExecuteNonQuery()
What is the correct way of declaring p_s_syounin2?
Thanks.
Best Regards,
Audrey
View 5 Replies
View Related
Oct 19, 2006
Is it ok to re-use a SqlCommand object? In a method, I am executing 2 separate parameterized sql statements. Before I run the second, I will clear the command objects parameters.(command.parameters.clear()) I'm just checking to see if it is good coding practice or not. thanks,SC
View 2 Replies
View Related
Mar 6, 2007
I created an xmldocument which I would like to insert in a db field with the data type XML.The following code is giving me the error:System.Data.SqlClient.SqlException: XML parsing: line 1, character 38, unable to
switch the encoding SqlCommand cmdUpdate = new SqlCommand("sp_AddHistory", sqlConnection); cmdUpdate.CommandType = CommandType.StoredProcedure; cmdUpdate.Parameters["@FieldsChanged"].Value = xmlDoc.innerXML; // don't know whether this is good cmdUpdate.ExecuteNonQuery(); innerXML:<?xml version="1.0" encoding="utf-8"?><Fields> <Field> <FieldName>comp_Area</FieldName> <OldValue>Area 52</OldValue> <NewValue>Area 51</NewValue> </Field></Fields> The XML seems fine.. any ideas?
View 3 Replies
View Related
Oct 17, 2007
Hello,
I am trying to insert a value into a specific row in a table. The error comes from the myCommand2 statement but i don't know how to solve it. Please help!
My code is as follows:SqlCommand myCommand = new SqlCommand("select count(*) from updatetable", myConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
int count = myReader.GetInt32(0);
SqlCommand myCommand2 = new SqlCommand("insert into interface (Total) where description = 'Graphicads' value ( " + count + ")", myConnection2);myCommand2.CommandType = CommandType.Text;
myCommand2.ExecuteNonQuery();
View 4 Replies
View Related
Oct 26, 2007
Hi,When using the following controls....System.Data.SqlClient.SqlConnection System.Data.SqlClient.SqlCommandIf I want to change my SQL command and execute the query once again what cleanup do I need to do first?Do I need close and dispose the SqlConnection?Do I need to dispose the SqlCommand?Can I use the SqlConnection for more than one SqlCommand?Thanks,Scott
View 5 Replies
View Related
Nov 23, 2007
Hello all, when I write my CommandText, It displays it as insert into videos values (127.0.0.1, 4000, 434), which the ip address is sent int as a string, put i keep getting an error, and I think it is because it is wanting an string, but it is sending in these "." or something, anyways heres my method, I dont know how I can add " " around the ip when i pass it in, since it is a string. thanks.
1 [WebMethod]
2 public void Register(string ip, int port, int[] handles)
3 {
4 SqlConnection dbConn = new SqlConnection(connStr);
5 SqlCommand comm = new SqlCommand();
6 comm.Connection = dbConn;
7 dbConn.Open();
8 for (int i = 0; i < handles.Length; i++)
9 {
10 comm.CommandText = "insert into videos values (" + ip + ", " + port + ", " + handles[i] + ")";
11 comm.ExecuteNonQuery();
12 }
13 dbConn.Close();
14 }
View 1 Replies
View Related
Mar 12, 2008
Using SqlCommand, this is how I am updating a database table:
Sub UpdateDataGrid(obj As Object, ea As DataGridCommandEventArgs) strSQL = "UPDATE Basket SET Quantity = Qty, Total = TotAmt WHERE BasketID = BID AND ProductID = PID" sqlCmd = New SqlCommand(strSQL, sqlConn) With sqlCmd .Parameters.Add("Qty", SqlDbType.Int).Value = CInt(iQty) .Parameters.Add("TotAmt", SqlDbType.Money).Value = CInt(iQty) * CType(ea.Item.FindControl("lblPrice"), Label).Text .Parameters.Add("BID", SqlDbType.VarChar, 50).Value = strBasketID .Parameters.Add("PID", SqlDbType.VarChar, 50).Value = CType(ea.Item.FindControl("lblID"), Label).Text End With sqlConn.Open() sqlCmd.ExecuteNonQuery() sqlConn.Close()End Sub
But the above code generates the following error pointing to the red colored line in the above code:
Invalid column name 'BID'.Invalid column name 'PID'.
BID & PID are not the column names in the actual database table but can't it be done in the way I have done above? In fact, Qty & TotAmt are not the column names in the actual database table as well; so why isn't the error pointing to Qty & TotAmt as they will be evaluated before BID & PID, if I am not mistaken?
View 8 Replies
View Related
Apr 15, 2008
How can I check if the ( SqlCommand ) return empty values
Can some one write code for this, I want know it is return Null values or not
thanx ....
View 4 Replies
View Related
Feb 19, 2006
I want to do something like the following but I get an error: Object reference not set to an instance of an object. SqlConnection sqlConnection = new SqlConnection("server=xxxxx"); SqlCommand [] cmd = new SqlCommand[3]; Object returnValue; cmd[0].CommandText = "DO QUERY"; cmd[1].CommandText = "DO QUERY"; cmd[2].CommandText = "DO QUERY"; cmd[3].CommandText = "DO QUERY"; } sqlConnection.Open();int i = 0;while(i<4){ cmd[i].CommandType = CommandType.Text; cmd[i].Connection = sqlConnection; cmd[i].ExecuteNonQuery(); returnValue[i] = cmd[i].ExecuteScalar();i++} sqlConnection.Close();How should I do the followingThanks
View 9 Replies
View Related
Mar 7, 2006
How to test @au_lname's value sends to the following following sql command?
Dim MyCommand As New SqlCommand("UPDATE [authors] SET [au_lname] = @au_lname", MyConnection)MyCommand.Parameters.Add(New SqlParameter("@au_lname", SqlDbType.NVarChar)).Value = me.au_lname.text
I tried to print the "MyCommand.CommandText.ToString" but only get UPDATE [authors] SET [au_lname] = @au_lname with no value in the command text.
Thanks!
View 5 Replies
View Related
May 4, 2006
Is there documentation on what ExecuteScalar() will return if the SQL statement is returning an image?
View 1 Replies
View Related
May 17, 2006
Hi:
I am using sqlcommand.parameters.add() and up to 74 parameters for one sqlcommand, it gets the error "too many parameters for the sqlcommand", I wonder if someone know is there limitation of the paramters that I can pass to sqlcommand? If so, how many parameters I can pass to the sqlcommand at one time?
thank u in adv.
View 4 Replies
View Related
May 30, 2006
Hello,I was trying to do the following: Dim cmd As SqlCommand Dim objConnection As SqlConnection objConnection = New SqlConnection = Web.configwhere the We.config is where my connection string is set. but I get a sintax error in the Web.config line.is it possible to asign the value of the web.config content to the new sql connection?thanks for any suggestions.
View 1 Replies
View Related
Aug 1, 2006
I am trying to add a DateTime? parameter to SqlCommand. It works when the variable has a value, but when its null, an exception gets thrown saying that parameter was not supplied.What is causing this error?
View 5 Replies
View Related
Jan 29, 2007
Hi
Just a doubt: s it possible to write an ado.net code that uses a sqldataadpter object and a sqlcommand object ( BOTH OBJECTS, IS IT POSSIBLE?) to retrieve data from the database by calling a stored procedure.
Thanks a lot
View 3 Replies
View Related
Oct 16, 2007
With VS2005, there is a new component SqlDataSource, <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource> Then you can assign SP and bind datasource to a get data for this component in .NET code:SqlDataSource1.SelectCommand = "spName"SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure SqlDataSource1.ConnectionString = Comm.connString There is no way to set sqlcommand timeout for this stored procedure like SqlClient.SqlCommand. How can I do this?
View 4 Replies
View Related
Jan 23, 2008
Hi,
I would like to know how I can retrieve the ID (Primary key) of the row I just inserted with a sqlcommand(text not stored procedure).
Thx
View 2 Replies
View Related
Mar 24, 2008
Hi, I've come from desktop to web (say no more?)
I'd like to fire a sqlCommand.StatementCompleted event in ASP.NET & use the sender argument to retrieve the output of a stored proc.
The command is in a button event. The event handler += is also set up in the same button event. Naturally the event handler proc is separate as its own procedure
In the event handling procedure, I cast the sender arg. to an SQLCommand object & try to extract the value of the output parameter.
When I try to do this I get a "No reference set to object" error raised in . Is this because there was a post back after the command completed & the Command object lost its state? If so, what would be the best way to retain the parameters value? Using session state or Profile?
Any help would be greatly appreciated
below is cut down code as to what I'm doing:protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.StatementCompleted += new StatementCompletedEventHandler(c_StatementCompleted); cn.Open(); cmd.ExecuteNonQuery(); cn.Close();
}
void c_StatementCompleted(object sender, StatementCompletedEventArgs e)
{
// get an error here
string result = ((SqlCommand)sender).Parameters["returnPrm"].Value.ToString();
}
View 3 Replies
View Related
Mar 27, 2008
I try to get the value return from GetRandomPosition StoredProc, and it throws a statement saying incorrect syntax.
Error:
Line 1: Incorrect syntax near 'GetRandomPosition'.
Mark up:
myPuzzleCmd.Execute is used in Classic ASP and it works just fine.
I tried to subsitute with ExecuteReader / ExecuteScalar / ... etc, none of them fix it.
View 13 Replies
View Related
