Oledb Update With Parameters

Dec 11, 2007

hi, please someone can help me. I get error 0x80040E14L // The command contained one or more errors. I think that the error is in the sql update command.
this is my code:


HRESULT hr = NOERROR;

IDBCreateCommand * pIDBCrtCmd = NULL;

ICommandText * pICmdText = NULL;

IRowset * pIRowset = NULL;

ICommandPrepare * pICmdPrepare = NULL;

ICommandWithParameters * pICmdWParams = NULL;

ULONG ulNumCols;

IColumnsInfo * pIColumnsInfo = NULL;

LONG lNumCols;

IAccessor * pIAccessor = NULL;

ULONG cParams;

DBPARAMINFO * rgParamInfo = NULL;

OLECHAR * pNamesBuffer = NULL;

ULONG ulNumRowsRetrieved;

HROW hRows[5];

HROW * pRows = &hRows[0];

BYTE * pData = NULL;

DBCOLUMNINFO * pDBColumnInfo = NULL;

WCHAR * pStringsBuffer = NULL;

DBBINDING * prgBinding = NULL;

DBBINDING rgBindings[2];

ULONG cbRowSize;

DBPARAMS params;

HACCESSOR hAccessor;


hr = m_pIDBCreateSession->CreateSession(NULL,IID_IDBCreateCommand,(IUnknown**)&pIDBCrtCmd);

if (FAILED(hr))


goto Exit;

//IID_ICommandWithParameters

hr = pIDBCrtCmd->CreateCommand(NULL,IID_ICommandWithParameters, (IUnknown**)&pICmdWParams);

if (FAILED(hr))


goto Exit;

hr = pICmdWParams->QueryInterface( IID_ICommandText,(void**)&pICmdText);

if (FAILED(hr))


goto Exit;

hr = pICmdWParams->QueryInterface( IID_ICommandPrepare,(void**)&pICmdPrepare);

if (FAILED(hr))


goto Exit;

LPCTSTR wSQLString = OLESTR("UPDATE Pendientes SET Pendiente = ? WHERE IDAnimal = ?");

hr = pICmdText->SetCommandText( DBGUID_DBSQL,wSQLString);

if (FAILED(hr))


goto Exit;

pICmdPrepare->Prepare(1);

if (FAILED(hr))


goto Exit;

pICmdWParams->GetParameterInfo(&cParams, &rgParamInfo, &pNamesBuffer);

hr = pICmdText->QueryInterface( IID_IAccessor, (void**)&pIAccessor);

if (FAILED(hr))


goto Exit;

rgBindings[0].iOrdinal = 1;

rgBindings[0].obStatus = 0;

rgBindings[0].obLength = rgBindings[0].obStatus + sizeof(DBSTATUS);

rgBindings[0].obValue = rgBindings[0].obLength + sizeof(ULONG);

rgBindings[0].pTypeInfo = NULL;

rgBindings[0].pObject = NULL;

rgBindings[0].pBindExt = NULL;

rgBindings[0].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;

rgBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;

rgBindings[0].eParamIO = DBPARAMIO_INPUT;

rgBindings[0].cbMaxLen = sizeof(int);

rgBindings[0].dwFlags = 0;

rgBindings[0].wType = DBTYPE_I4;

rgBindings[0].bPrecision = 0;

rgBindings[0].bScale = 0;


rgBindings[1].iOrdinal = 2;

rgBindings[1].obStatus = rgBindings[0].obValue + rgBindings[0].cbMaxLen;;

rgBindings[1].obLength = rgBindings[1].obStatus + sizeof(DBSTATUS);

rgBindings[1].obValue = rgBindings[1].obLength + sizeof(ULONG);

rgBindings[1].pTypeInfo = NULL;

rgBindings[1].pObject = NULL;

rgBindings[1].pBindExt = NULL;

rgBindings[1].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;

rgBindings[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;

rgBindings[1].eParamIO = DBPARAMIO_INPUT;

rgBindings[1].cbMaxLen = 8;

rgBindings[1].dwFlags = 0;

rgBindings[1].wType = DBTYPE_I8;

rgBindings[1].bPrecision = 0;

rgBindings[1].bScale = 0;


cbRowSize = rgBindings[1].obValue + rgBindings[1].cbMaxLen;


hr = pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,2,rgBindings,cbRowSize,&hAccessor,NULL);

if (FAILED(hr))


goto Exit;

pData = (BYTE*) malloc(cbRowSize);
if(!(pData))

{


hr = E_OUTOFMEMORY;

goto Exit;

}

memset(pData,0,cbRowSize);


*(DBSTATUS*)(pData + rgBindings[0].obStatus) = DBSTATUS_S_OK;

*(int*)(pData + rgBindings[0].obValue) = 9;

*(ULONG*)(pData + rgBindings[0].obLength) = 8;


*(DBSTATUS*)(pData + rgBindings[1].obStatus) = DBSTATUS_S_OK;

*(int*)(pData + rgBindings[1].obValue) = 0;

*(ULONG*)(pData + rgBindings[1].obLength) = 4;


params.pData = pData;

params.cParamSets = 1;

params.hAccessor = hAccessor;


hr = pICmdText->Execute(NULL, IID_NULL,&params,&lNumCols,NULL);

if (FAILED(hr))


goto Exit;

Exit:...

View 1 Replies


ADVERTISEMENT

OLEDB Datasources And Parameters

Feb 1, 2006

I have discovered some shortcomings in the way inline table valued function parameters are treated in the OLEDB datasource. You can select the user designed function ine the Generic Query Builder and test it with the required parameters. However when you attempt to set up the parameters for the result ing SQL Command Text you get and error message to the effect that the parameters cannot be retrieved from the datasource. Once again this is disappointing because Report Services seems to deal with the parameters perfectly well.

Dick Campbell

View 2 Replies View Related

Optional OLEDB Parameters : Need Help

Nov 8, 2007

I am using Oracle 7.3 against SSRS. I have created an inline query with 7 unnamed parameters. I have named them in SSRS parameters window and selected NULL and ALLOW BLANK check boxes for all the parameters.


a.)
What I think should happen is: I should be able to pass the combination of parameters NOT all of them, because I have selected NULL and Blank check boxes. But in my case the query is not giving me any results if I pass 2 of 7 parameters. I can see the results only when I pass all 7 parameters.

Please Help me...

b.)
Is there a way I can create a Dynamic WHERE condition using ORACLE 7.3 as database and OUT REF cursor as out parameter for generating parameters. An Example would be great.......


I am much familar with SQL Server and creting a dynamic query is no problem. Because of this new assignment in Oracle 7.3 I am pulling my hair to solve this perticlaur problem...

Please guys / gals help..


Thanks,
Deepak

View 6 Replies View Related

OleDB Source And Parameters

May 11, 2007

Hi,



Firtsly - I am new to SSIS if my approach could be improved then I welcome suggestions.



Scenario: I have a large SSIS package that consolidates / summarizes work week information from several data sources. Currently each data flow task in the control flow calculates the from and to date that is filtered on, for example:



DECLARE @FromDT AS DATETIME
SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)

DECLARE @ToDT AS DATETIME
SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)



I would like to remove these statements that appear in most steps and replace them with a global variable that is used throughout the package. This statement would only appear once & it would make the package much easier to run after failure etc.



Problem: I am using Data Reader Source with the 'SQLCommand' property specified. It looks like parameters are only supported if an OleDB connection is used?



So I switched to an OleDB connection and no parameters are recognised in the string - a forum search reveals that parameters in sub queries are not always found properly. The solution to this problem appears to be, to set 'Bypass Prepare' to True but this is a property for the Execute SQL task, not the Data Flow Task source.



Questions:

Does the Data Reader Source control from Data Flow Source toolbox section support parameters?
Can anyone suggest a fix to the OleDB Source issue with Parameters?
Is there a better way to solve my problem e.g. Using Execute SQL Task instead of Data Flow tasks etc

Example SQL:

This SQL is an example of the SQL for the OleDB Data Source (within a Data Flow task)



------------------------------
--RADIUS LOGINS
------------------------------
DECLARE @FromDT AS DATETIME
SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)



DECLARE @ToDT AS DATETIME
SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)



DECLARE @Attempts AS BIGINT
SET @Attempts =
(SELECT COUNT(*)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN @FromDT AND @ToDT)



DECLARE @Failures AS BIGINT
SET @Failures =
(SELECT COUNT(*)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN @FromDT AND @ToDT
AND Authen_Failure_Code IS NOT NULL)



DECLARE @Successes AS BIGINT
SET @Successes = @Attempts - @Failures

DECLARE @OcaV1Hits AS BIGINT
SET @OcaV1Hits = (SELECT COUNT(DISTINCT LoginName)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN
@FromDT AND @ToDT
AND EAPTypeID = 25)



DECLARE @OcaV2Hits AS BIGINT
SET @OcaV2Hits = (SELECT COUNT(DISTINCT LoginName) AS OcaV2Hits
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN
@FromDT AND @ToDT
AND EAPTypeID = 13)


SELECT
@Attempts AS ConnectionAttempts,
@Failures AS ConnectionFailures,
(CAST(@Successes AS DECIMAL(38,2)) / CAST(@Attempts AS FLOAT) * 100) AS SuccessRate,
@OcaV1Hits AS OcaV1Hits,
@OcaV2Hits AS OcaV2Hits



Please remember, I'm new to SSIS - so be detailed in your response. Thanks for your help!

View 5 Replies View Related

Parameters In A OleDb Command Transformation

Jan 9, 2007

Hi there,

In order to prevent lookup errors in a lookup transformation, I've decided to go for an OleDb Command Transformation.

This transformation should check the lookup and, if it turns out to be null, ir returns a dummy value. Otherwise, it would return the lookup value.

This should be done by doing something like this:

select coalesce( (select ID_Table2 from ID_Table2 where FK_Table1 = ?), 0)

suposing Table2 has an atribute called "FK_Table1" that should match a column in the data flow.

Now, such command result in this message:

"An OLE DB record is available. Source "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax eror, permission violation, or other nonspecific error".

But, it I remove the coalesce and type the following command:

select ID_Table2 from ID_Table2 where FK_Table1 = ?

It presents me no errors and allows me to continue.

Did i did anything wrong or is this something that is not possible to be done?

I know i have the option to use a script task to do this operation, but that would turn the maintenance process a little more difficult.

Otherwise, i know i could also re-direct the error from the lookup transformation and handle it. Though, my package has about 10 lookups and that would turn my package a lot more complex than



Thanks in advance

Best Regards

André Santana

View 6 Replies View Related

Question OLEDB Source With SQL And Parameters

Mar 14, 2008

Hello All,

I understand that the parser is having issues parsing my second command but I was wonder if anyone had stumbled on a work around for this:

-- OLEDB Source has no problem with this:
SELECT *
FROM some_table
where
last_update_dt = ?


-- OLEDB Source errors on this:
SELECT *
FROM some_table
where
Convert( char(12), last_update_dt, 112 ) = Convert( char(12), Cast( ? as DateTime), 112 )

The first SQL statement won't return the desired rows because of the wonderful time stamp, the second works well but the provider won't parse it. I have used the "SQL command from variable" to work around this but we have some tables with 200+ columns, so the only way to use the "SQL command from variable" is to do a select *, which I'm trying to avoid, for both performance reasons and company standards.

Any thoughts?

Thanks,

Raymond

View 4 Replies View Related

Maxmimum Number Of Parameters For OleDB Command

Oct 31, 2007

I have a stored proc with 28 commmands but only 24 are loaded. If I move the parameters around the ones that didn't show are visible and the ones after the 24th parameter don't show up. Is there some sort of limit on parameters for OLEDB commands that execute stored procs in the exec procname @p = ? construct?

View 3 Replies View Related

FoxPro OLEDB Driver - Pass Parameters

Feb 2, 2006



Hi,

Does anyone know if the VisualFoxPro 9.0 OLEDB driver supports parameters? Trying to pass parameterised queries from SSIS results in the error reported below, which implies it does support parameters, but that SSIS is not calling the driver properly somehow.

Regards,

Richard

-----------------------------------------------

Error MsgBox:

TITLE: Microsoft Visual Studio
------------------------------

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

------------------------------
ADDITIONAL INFORMATION:

Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Visual FoxPro)


View 1 Replies View Related

Parameters For OLEDB Oracle Source Query

Jul 5, 2006

Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)


I am getting the above error while opening the parameter box at OLEDB source for Oracle using SQL command option at Data Access Mode?? Can you any one please help me in this regard and trouble shoot this problem..

View 8 Replies View Related

System.Data.OleDb.OleDbException: No Value Given For One Or More Required Parameters.

Jan 23, 2007

Can someone help me with this error, so the page can show the rocords, its works on my PC but not at my host. 
I get this error:
Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.Source Error:



An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:



[OleDbException (0x80040e10): No value given for one or more required parameters.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +267
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +192
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +48
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +106
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +111
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +141
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

 
My code is:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CMS_Default.aspx.vb" Inherits="cmssystem_CMS_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="CM_form" runat="server">
<div>
&nbsp;
<asp:GridView ID="GridView1" runat="server" DataSourceID="CMSqlDataSource">
</asp:GridView>
<asp:SqlDataSource ID="CMSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:CMConnectionString %>"
ProviderName="<%$ ConnectionStrings:CMConnectionString.ProviderName %>" SelectCommand="SELECT [SiteMainID], [SiteMainIdentity], [SiteMainText] FROM [MainSiteText] ORDER BY [SiteMainID]">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>

View 7 Replies View Related

SQLServer2005 / OLEDB ICommandWithParameters::GetParametersInfo() Problem With Named Parameters

Aug 6, 2007

Hello,

I'm trying to use named parameters on SQL Server 2005 express from OLEDB and i can't get ICommandWithParameters::GetParametersInfo() to retrieve parameters when they're named.

Consider the following query:

SELECT foo.bar FROM foo WHERE foo.id = @my_named_param

I set the command text successfully (ICommandText :: SetCommandText), prepare the command successfully (ICommandPrepare :: Prepare) but when i call GetParametersInfo from the ICommandWithParameters interface it returns S_OK with 0 as count, although there is a parameter.

If i use an unnamed parameter i.e: SELECT foo.bar FROM foo WHERE foo.id = ? it works

I tried both with the SQLOLEDB provider and the SQL native client (SQLNCLI). Are named parameters unsupported or unretrievable through GetParametersInfo() ? Any input is welcome.

Regards.

View 1 Replies View Related

SSIS OLEDB Data Source Query Parameters Connect To Oracle

Oct 26, 2007

Hi,

I'm trying to use query parameters with an Oracle OLEDB Source in a data

flow task and I'm having problems.

I've tried formatting the query each of the following ways...

--

select

frq_code,

frq_name,

update_frq,

uptime_frq

from frequency_bcs

where update_frq > ?

and update_frq <= ?

--

Parameters cannot be extracted from the SQL command. The provider might not

help to parse parameter information from the command. In that case, use the

"SQL command from variable" access mode, in which the entire SQL command is

stored in a variable.

Additional information

---> Provider cannot derive parameter information and SetParameterInfo has

not been called. (Microsoft OLE DB Provider for Oracle).

View 7 Replies View Related

Integration Services :: Using Parameters With OleDB Command Provider For Oracle Into SSIS Package?

Dec 8, 2011

I need to delete some records in a Oracle RDBMS based at a SQL Server's query. I'm using the follow structure SSIS's package:

View 4 Replies View Related

Update && Oledb

Feb 26, 2004

Dear gurus,

My problem: I have developed an application based on sqloledb to access a SQLserver application.

One of the operations is to update certain column of a certain table. This works fine. But with a specific catalog (database) of a specific customer the update hangs for ever on the execution of that sql sentence. Size of the mdb file is around 1 Gb.
The funny thing is that if I execute the same sql sentence (same record and same catalog) from the Query Analyzer ( that I belive it uses odbc ) the operation is done.
The update sql sentence does an update on a non-indexed column with the criteria ( where ...) using a index column (non-clustered)
I have tried the sqlmaint to rebuild indexes and check integrity and no special error report is given.

Also another funny behaviour is that if I stop and I restart sqlserver, the update of that specific record fails, but it continues of the following records to be updated.

This is not executed inside a transaction. Candidate records to be updated are stored in memory (maximum 1000), and one by one are updated.

Does anyone know an specific tool to integrity or to monitor what is going on?
I have tried the profiler , what can I monitor to detect this lock?

Best regards,
Jose

View 1 Replies View Related

Integration Services :: Calling Oracle Stored Procedure With Output Parameters In OleDB Command Task?

Nov 7, 2015

I want to call "oracle" stored procedure with output parameter from SSIS ole db command task.

Actually I am able to successfully call the procedure but my Output value is not updating in the mapped column.

I used below PL/SQL query.

DECLARE
IS_VALID VARCHAR2(200);
BEGIN
IS_VALID(
PARAM1 => ?,
PARAM2 => ?,
IS_VALID => IS_VALID
);
? := IS_VALID;
END;

If I try to supply "OUTPUT" word I get error:

"ORA-06550: line 1, column 45:

PLS-00103: Encountered the symbol "OUTPUT" when expecting one of the following:   . ( ) , * @ % & = - + < / >"
BEGIN
IS_VALID(
?,
?,
? OUTPUT
);
END;

how to receive output parameter value of oledb command while calling oracle stored procedures.

View 4 Replies View Related

OleDB Update To Excel Spreadsheet

Mar 12, 2008

Is there a limit to the number of fields that can be set in an OleDB Update Statement?

This works with 6 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000'"

This Fails with 7 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '123'"

The range should be plenty big with A2:AP2. In the end I'm trying to push 42 fields.


The complete segment is:

Dim ExcelConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ExcelFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim conn As New System.Data.OleDb.OleDbConnection(ExcelConnection)
Dim cmd As New System.Data.OleDb.OleDbCommand()

conn.Open()
cmd.Connection = conn

cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '1234'"

cmd.ExecuteNonQuery()

View 1 Replies View Related

OLEDB Versus ADO.NET For UPDATE Statement Using Parms

Jun 14, 2006

In SSIS, I have an Execute SQL Task that runs a direct SQL statement as follows to update an MS/SQL database table:

Update auditTable SET PackageName  =  @pkgname

In the parameters mapping, @pkgname is mapped to the System variable System::PackageName (as input).

This task runs fine if I use ADO.NET as the data source without any error.

But if I use OLEDB as the source it fails miserably and requires that the @pkgname variable be defined (as if it's a user variable, when in fact it was intended as a parm for the update statement only).

Any idea why that happens?

Thanks

Tommy

View 7 Replies View Related

Problem With Update Statement In OLEDB Command

Nov 15, 2007



HI,

In my mapping i have used one OLEDB command which updates a table.
It updates 70 columns of that table.
The problem is that it is taking long time to execute that(almost 15-20 minutes)
It is updating almost 3k-5k rows.
I tried to put the update statement in SP and called that in OLEDB command.
Still it is taking same time.
Please advice how to solve this problem.


Thanks in Advance.

View 6 Replies View Related

OLEDB Command Usage When Update/delete On Db2

Dec 11, 2007



We have SS2K5 source and DB2 Target. I downloaded and installed MS OLE DB provider for DB2. It is configured correctly. The data insert works fine. But I am having problem with update/delete on DB2 when passing string parameters.

The OLEDB command works fine when I hard code the values:
delete from TableName where Col1='abc' and Col2='xyz'

But when I use parameters the package executes successfully, but the data is not delete on DB2.

Any one having similar experience or solution for this is really appreciated.

Thanks,


View 7 Replies View Related

Issue With The Use Of OLEDB Command Task To Update Db2 Target Table

Dec 6, 2007



I am having a problem on updating data in DB2 target table.

I followed BJ Custard's (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1058272&SiteID=1&mode=1)(thanks amillion!) post and configured OLEDB destination to insert data. But I have to also update or delete data from the target table based on flag from source.

I tried using OLEDB command which uses the OLEDB connection created by following the steps posted in above link.

Trail 1real requirement):
When I used the SQL query:


delete from table where Col1=? and Col2=?

I am unable to map to the parameters. When I click refresh button after writing the query, I get "There is a data source column with no name. Each data source column must have a name." message. Added to before message, there are no parameters to map to.


Trail 2:
When I hard code the parameters :

delete from table1 where Col1='abc' and Col2='xyz'

no parameters will come up, so no mapping. So when I execute the mapping I get the following error:



Error: 0xC0202009 at Load .....................................................: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
Lookup on above error codes show those are related more to target Db2 database.

I am sure some one might have used the OLEDB command task, not only just insert task.

Any help is really appreciated.

View 14 Replies View Related

Problem In Update Existing Records Using OLEDB Commmand(ORAcle DataBase) In SSIS Package

Sep 4, 2006

Hi when i am using OLDB Command for Update Existing Records the Follwing ERror Code I am getting . Any one pls help me on this one.



1)[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (9282)
failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.


2)

[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.



3)

[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.

View 1 Replies View Related

Error When An OLEDB Source Points To An OLEDB Destination.

Oct 10, 2006

Hi all,

I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.

But i got the below error:

Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.

Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".

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)



View 5 Replies View Related

Update Parameters

Jun 6, 2006

My problem is that I can' t update my gridview. I am stuck at this problem for several days now and have no clue what the sollution could be. Hope somebody can give me some ideas of what I am doing wrong.
<asp:GridView ID="GridView_phl_berichten" runat="server" AutoGenerateColumns="False" BorderColor="DimGray" BorderStyle="Solid" BorderWidth="1px" DataKeyNames="phlb_id" DataSourceID="SqlDataSource_phl_berichten" GridLines="Horizontal" Width="716px">
<Columns>
<asp:TemplateField HeaderText="Van datum" SortExpression="phlb_van">
<EditItemTemplate>
&nbsp;<asp:Calendar ID="phlb_van" runat="server" SelectedDate='<%# eval("phlb_van") %>'
VisibleDate='<%# eval("phlb_van") %>'></asp:Calendar>
</EditItemTemplate>
<ItemStyle VerticalAlign="Top" Width="5cm" />
<ItemTemplate>
<asp:Label ID="Label_datum_van" runat="server" Text='<%# eval("phlb_van", "{0:d}") %>'></asp:Label>
<br />
<br />
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit"
Text="Edit"></asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Delete"
Text="Delete" OnClientClick=" return confirm('Bent u zeker dat u dit bericht wil verwijderen ?') "></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Tot datum" SortExpression="phlb_tot">
<EditItemTemplate>
&nbsp;<asp:Calendar ID="phlb_tot" runat="server" SelectedDate='<%# eval("phlb_tot") %>'
VisibleDate='<%# eval("phlb_tot") %>'></asp:Calendar>
</EditItemTemplate>
<ItemStyle VerticalAlign="Top" Width="4cm" />
<ItemTemplate>
<asp:Label ID="Label_datum_tot" runat="server" Text='<%# eval("phlb_tot", "{0:d}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Bericht" SortExpression="phlb_bericht">
<EditItemTemplate>
&nbsp;<asp:TextBox ID="phlb_bericht" runat="server" Height="98px" Text='<%# eval("phlb_bericht") %>'
TextMode="MultiLine" Width="527px"></asp:TextBox><br />
<br />
&nbsp; &nbsp;&nbsp;
<asp:LinkButton ID="LinkButtonUpdate" runat="server" CausesValidation="False" CommandName="Update"
Font-Size="Small" OnClientClick="return confirm('Bent u zeker dat u dit bericht wil opslaan ?')"
Text="Update"></asp:LinkButton>
&nbsp; &nbsp; &nbsp; &nbsp;
<asp:LinkButton ID="LinkButtonCancel" runat="server" CausesValidation="False" CommandName="Cancel"
Font-Size="Small" Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<ItemTemplate>
&nbsp;<asp:TextBox ID="TextBox_bericht" runat="server" Height="98px" ReadOnly="True"
Text='<%# eval("phlb_bericht") %>' TextMode="MultiLine" Width="527px"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="phlb_id" SortExpression="phlb_id" Visible="False">
<EditItemTemplate>
<asp:Label ID="phlb_id" runat="server" Text='<%# Bind("phlb_id") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label_bericht_id" runat="server" Text='<%# Bind("phlb_id") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle BackColor="#F3F3F3" />
</asp:GridView>
and here is my sqldatasource-object :
<asp:SqlDataSource ID="SqlDataSource_phl_berichten" runat="server" ConnectionString="<%$ ConnectionStrings:ConEP %>"
SelectCommand="sp_berichten_PHL_alle" SelectCommandType="StoredProcedure" UpdateCommand="UPDATE T_bericht_PHL set&#13;&#10;phlb_bericht = @phlb_bericht &#13;&#10;where phlb_id = @phlb_id">
<UpdateParameters>
<asp:Parameter Name="phlb_bericht" Type="String" />
<asp:Parameter Name="phlb_id" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
It always stay giving the message that you can ' t insert null values in the column " bericht " ... ; All my variables have the same names as the columns in my table. So it is finding the id , but it doesn' t seem to find the value of my edititemtemplatefield "bericht". Hope somebody can give some suggestions on what I can try to do else.
 

View 3 Replies View Related

Sql Update With Command.Parameters

Oct 10, 2006

 Im looking for example code to make a sql update... I want to use command.Parameters and variables from text boxes and i'm unsure how to do this...  Please help. This code below doesn't work but it is an example of what i've been working with..  <code>     {               string conn = string.Empty;            ConnectionStringsSection connectionStringsSection = WebConfigurationManager.GetSection("connectionStrings") as ConnectionStringsSection;            if (connectionStringsSection != null)            {                ConnectionStringSettingsCollection connectStrings = connectionStringsSection.ConnectionStrings;                ConnectionStringSettings connString = connectStrings["whowantsConnectionString"];                conn = connString.ConnectionString;                using (SqlConnection connection = new SqlConnection(conn))                using (SqlCommand command = new SqlCommand("UPDATE users SET currentscore=5)", connection))                {                    updateCommand.Parameters.Add("@currentscore", SQLServerDbType.numeric, 18, "currentscore");                    connection.Open();                    command.ExecuteNonQuery();                    connection.Close();                }            }        }</code>   

View 3 Replies View Related

Update Query With Parameters

Apr 18, 2007

I have a web form that is populated from the DB values. User can update teh record by keying in new values in the form fields. In my .vb class file I have an update statement -
strSql = "UPDATE msi_Persons SET Firstname=@firstname where Id=@id"
 I have the parameters defined as
cmd.Parameters.Add(New SqlParameter("@firstname", _FirstName))
cmd.Parameters.Add(New SqlParameter("@id", _Id))
 _FirstName & _Id are the private variables whose values a set thru set and get methods.  I expect _FirstName to have the new value I am keying in the form.
Can any body help me with what's wrong here? This is not updating the database. When I do trace.write it shows me the Update statement as "UPDATE msi_Persons SET Firstname=@firstname where Id=@id" instead of @firstname and @id being replace by actual values.
Please help.
Thanks,Shaly

View 23 Replies View Related

Update Parameters Syntax

May 12, 2008

 Hi All,The following code runs without error but does not update the database. Therefore I must be missing something.I am sure that this a simple task but as newbie to asp.net its got me stumpted. Protected Sub updatePOInfo_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles updatePOInfo.Updating        Dim quantity As Integer        Dim weight As Integer        Dim packed As Integer        quantity = CInt(bagsOnPallet.Text)        weight = CInt(lbsInBags.SelectedValue)        packed = weight * quantity        e.Command.Parameters("@packedLbs").Value += packed        e.Command.Parameters("@AvailableLbs").Value -= packed    End Sub    

View 4 Replies View Related

How Do You Use SQL Parameters To UPDATE A Column?

Sep 27, 2006

Hi all,

I have a problem here where I am trying to use SQL Parameters to update a column in the database, but the problem is that I need to concatenate the same column to the SQL parameter. The code I have is below, but it throws a Format Exception...

UPDATE tbl_NSP_Inspection SET Description = Description + @InspectionDescription

...It is because I am trying to Append teh data in the description column to the SQL Parameter (
@InspectionDescription). How do I actually do this using SQL Parameters?

Thanks

View 3 Replies View Related

SQLDataSource Update Using Parameters Not Working

Jul 10, 2006

I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast.  And then firing off the update command that contains that parameter from a button.  But it's not changing my data on my server when I do so.
I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes.  All this works fine, just not sp that should update my data when I hit the submit button.
It's supposed to update one of my tables to whatever the selected value is from my drop down list.  But it doesn't even change anything.  It just refreshes the page and goes back to the original value for my drop down list.
Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc.  It just won't change the data from the page when I try to.
 
This is what the stored procedure looks like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS
BEGIN
UPDATE [Current_tbl]
SET ID = @SelectedID
WHERE PrimID = '1'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my aspx page:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"
UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;">
<tr>
<td colspan="2" style="font-family:Tahoma; font-size:10pt;">
Please select one of the following:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%;">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%;">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label>
</td>
</tr>
</table>
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my code behind:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Imports System.Data
Imports System.Data.SqlClient
Partial Class Editor
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Saved_lbl.Text = ""
Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;")
Dim View1 As New DataView
Dim args As New DataSourceSelectArguments
View1 = SQLDS_Fill.Select(args)
Dim Row As DataRow
For Each Row In View1.Table.Rows
Ver_ddl.SelectedValue = Row("ID")
Next Row
SQLDS_Fill.Dispose()
End Sub
Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click
SQLDS_Update.Update()
Saved_lbl.Text = "Thank you. Your changes have been saved."
SQLDS_Update.Dispose()
End Sub
End Class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Any help is much appreciated.

View 4 Replies View Related

Problem With Update Command With Parameters

Aug 2, 2007

Hi,i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.This happens in event DetailsView1_ItemUpdating with my own code.It works without parameters (i know, bad way) like this:SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"But when using parameters like here below, i get the error:"Input string was not in a correct format"Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdatingSqlDataSource1.UpdateCommand = "UPDATE mytable set name= @myname"SqlDataSource1.UpdateParameters.Add("@myname", SqlDbType.NVarChar, na)SqlDataSource1.Update()End SubI don't see what's wrong here.Thanks for helpTartuffe

View 4 Replies View Related

Update/Insert Using XML Strings As Parameters

May 20, 2008

Hi All,

We got this little issue of passing around (updated and inserting) small dataSets (20-500 or so records) from fat clients to a remote server running .Net / SQL 2005.

Instead of trying to fudge the data and make updates in .Net, we just decided it would be a lot less nonsense if we just wrap up the dataSets in an XML string, have .Net just pass it thru as a parameter in a SP and let SQL parse it out using openXML. The data is small and server is low use, so I'm not worried about overhead, but I would like to know the best methods and DO's & Don'ts to parse the XML and make the updates/inserts....and maybe a few good examples. The few examples I've come across are kind of sketchy. Seems it's not a real popular method of handling updates.

Thanks in Advance,
Bill

View 5 Replies View Related

How To Write A SQL Update Query Using Parameters?

Mar 23, 2008

Hi, could someone explain to me with sample code how to write a sql query using parameters to update a record, as I am new to this.

thanks

View 4 Replies View Related

Update Command In SQL DateSource Mixing Up My Parameters

Dec 14, 2007

I have an update command with 7 parameters, but at run time the order to the parameters gets mixed up.
I'm using a stored procedure. At first I have the command type set to text, and was calling it using EXEC spName ?,?,?,?,?,?,?
I then named each of the parameters and set their sources.  The parameters are like this (samepl name, then source, then type):
A : QueryString - intB: Control - intC: Control - intD: None - intE: None - decimalF: Control - datetimeG: Control - datetime
At run time I was getting an error that an integer couldn't be converted to date time. So I put a breakpoint in the Updating event and then looked at the parameters prior to update.
This is how they looked (Parameter index, paramter name):
[0] A[1] B[2] D[3] E[4] F[5] G[6] C
It didn't maek any sense. Do, I deleted all of the paramters and readded them. That didnt' work. Then I changed the command to StoredProcedure and refreshed the parameters from the stored proc and it brought them in the right order, but the problem remains the same.
I looked at the page source, and there are no indexes in the page source, but the parameters are listed in the proper creation order, as follows:<UpdateParameters><asp:QueryStringParameter Type="Int32" Name="PROJ_ID" QueryStringField="pid"></asp:QueryStringParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="TASK_UID" ControlID="fvTask"></asp:ControlParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="ASSN_UID" ControlID="gvResources"></asp:ControlParameter><asp:Parameter Type="Int32" Name="RES_UID"></asp:Parameter><asp:Parameter Type="Double" Name="Work"></asp:Parameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Start" ControlID="TASK_START_DATETextBox"></asp:ControlParameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Finish" ControlID="TASK_FINISH_DATETextBox"></asp:ControlParameter></UpdateParameters>
No mater what I do, at run time ASSN_UID is always the last parameter. I've also run a SQL trace to see how it is actually being executed, and sure enough, its passing the value for ASSN_UID as the last parameter, which obviously doesn't work.
Any ideas as to why this would happen or how to fix it?
(I guess I can reorder the patameters in the stored proc to match how they are being passed, but still, that wouldn't be a very comfortable solution, since it could perhaps revert at some point or something)

View 2 Replies View Related

Stored Proc Update Optional Parameters

Dec 3, 2003

I have a stored procedure that updates about a dozen rows.

I have some overloaded functions that I should update different combinations of the rows - 1 function might update 3 rows, another 7 rows.

Do I have to write a stored procedure for each function or I can I handle it in the Stored Procedure. I realise I can have default values but I the default values could overwrite actual data if the values are not supplied but have been previously written.

Many thanks for any guidance.

Simon

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved