.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

update fomr OLEDB command goes wrong

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :ASP.Net

Hello all,

I use a conditinal split to check if records exists and have changed. Is so the dataflow goes to a storedprocedure to update the table. The stranges thing happens, all the records got updated with the same change, so for instance all the birthdates of users are the same after the update. It seems like the update commande holds the same values for the variables, when i look with a dataviewer I see different data then the data inserted. Anyone a clue what this can be?

within the oldb command I cal the stored procedure as "exec spUpdateall ?,?,?,?,?,?,?,?,?,?,?,?,?,?"
Where the questionmarks are the ammount of variables.

Thank you


View Complete Post

More Related Resource Links

Output parameters in OLEDB Command component - SSIS 2008

I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.   Is there an issue with output parameters in an OLE DB Command in SSIS 2008?   Thanks!http://bobp1339.blogspot.com

Access the SQL Command Query Value Property from the OLEDB Container within a Script Task

Hello, I'd like to Access the SQL Command Query Value Property from the OLEDB Container within a Script Task so I can audit the queries which are executing. What is the syntax for this or does anyone have a sample on how to do this? Thank you

How to update single cell of excel sheet using oledb connection,

 Friends i got some codes from net  by using that i can update a excel sheet ,but there we have to specify some header fields name in command string like as fallows ,Update [Sheet1$] set ProductName="IBG" where ProductId=5but i want to update a single cell of excel sheet only,let say O12 cell i want to change somedata,how it is possible that i don't know.plz help me out... thanks in advance...  

declarative #Bind doesn't bind in update command

I have a bunch of controls like the following in the EditItemTemplate of a ListView, with LINQDataSource:<asp:TextBox ID="Email" CausesValidation="true" EnableViewState="False" Font-Bold="false" Text='<%#Bind("Email")%>' BackColor="#FFFFFF" Enabled="true" TabIndex="50" runat="server" /> I'm curious to know why the database won't update on the click of the Update button:<asp:Button ID="btnUpdate" CommandName="Update" Text="Update" UseSubmitBehavior="False" TabIndex="170" runat="server" /> According to all I've read, the above code should be sufficient. Since CommandName is set to Update, it would seem that no code-behind is necessary.

SQL Update command on button click

Hello!I don't know what is the problem with my code. SQL statement is good (I checked it outside of the application) but for some reason it doesn't update the data.Let me explain a bit better. I have a web form which is supposed to be used for editing data in textboxes and on button click the data should be updated via SQL query.public partial class Administracija_Clanak : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string ClanakID = Page.Request.QueryString["idClanak"]; String connString = WebConfigurationManager.ConnectionStrings["CMS"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); using (SqlCommand command = new SqlCommand("SELECT Clanak.* , Kategorije.naziv FROM Clanak Clanak INNER JOIN Kategorije ON Clanak.idKategorije = Kategorije.idKategorija WHERE Clanak.idClanak='" + ClanakID + "'", conn)) { SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { txtIDClanak.Text = reader.GetInt32(0).ToString(); ddlKategorija.Text = reader.GetInt32(1).ToString(); txtNaslov.Text = reader.GetString(2).ToString(); txtAutor.Text = reader.GetString(4).ToString(); txtSadrzaj.Content = re

Update Command!

Hello,I'm using DetailsView and also used the code below to allow the user to (Edit and Update his or her info):UPDATE DatabaseTable SET      Column1 = @Column1,     Column2 = @Column2,      Column3 = @Column3 WHERE UserId = @UserIdIn the Datasource (Update query property).I have no error during debugging or using the web page, The only problem i have is:When i insert a new value and hit (Update), The same old value shows and no new values saved? Thanks in advance.

OLE DB Command Transfornmation-- Update

Can any one help me on OLEDB Command Transformation... Below is the Scenario.. Table1 Structure: EMPID Name Location 111 xxx   Table2 Structure: EMPID Location 111 YYY When EMPID Matching in Both table, I just wat to update the Location detail from Table 2 with the help of OLEDB Command Transformation.... I did this one... It's executing perfectly... and showing like 1 row's transferred... While Checking a table1 no updation Occurs...        

OLEDB Command




I am trying to insert a row into Table X if that row doesn't exist in Table Y. TO accomplish this, I wrote the following statement in the the sql command in OLEDB transformation:


But the above statement is not parsing. Also, how should i get the logic to only insert rows that don't exist in table Y? PLease advise, thanks in advance.

Cumulative Update 5 - wrong SQL 2008 version number


Hi all,

I installed Service Pack 1 for SQL 2008 on one of my servers. This server has multiple instances. In this case, it is the instance "SHAREPOINT2010" that is giving me difficulties.

For Sharepoint 2010, we need to install cumulative update 5. This update fails due to the fact I do not have the correct SQL version. It is expecting version 10.0.2531 (SP1), but it says I have the 10.0.1600 (RTM). When I check in my management studio and via the @@version, I do see the confirmation I have version 10.0.2531. (also see attached screenshot).

Does anyone know why the cumulative update fails to see the correct server version ? If I try to install it on another SQL 2008 SP1, it works fine..;

Thank you !



screenshot sql version

Multiple sql server update statments in SSIS OLE DB Command


Is it possible to issue multiple sql server update statements in SSIS OLE DB Command?


I Tried the following with no luck


update table1 set column1 = 1


update table2 set column1 = 1

GridView Update Command


I am trying to use an Update Command within my GridView.  I need to find the "FindControl" for the Edit Command textboxes.

Ie. When the user clicks the Edit button, the row is populated with dropdown boxes, textboxes, etc for editting. Then when the user clicks on Update (button) and the GridView1_RowCommand function is activated with 'if (e.CommandName == "Update")' statement with my FindControl. etc. and my sql UPDATE command statements are used.  How do I convert my FooterRow info to my Edit FINDCONTROL info?

This is what I have:

protected void GridView11_RowCommand(Object sender, GridViewCommandEventArgs e)

if (e.CommandName == "Update")


GridViewRow footer = GridView1.FooterRow;
            ddCityXXX = footer.FindControl("ddCityXXXInsert") as DropDownList;
            ddOvernightLSN = footer.FindControl("ddOvernightLSNInsert") as DropDownList;
            txtHotelName = footer.FindControl("txtHotelNameInsert") as TextBox;
            txtHotelAdd = footer.FindControl("txtHotelAddI") as TextBox;
            txtHotelCity = footer.FindControl("txtHotelCityI") as TextBox;
            txtHotelState = footer.

Microsoft Matrix Framework DataGrid - Update Command - Convert empty strings to NULL



we have a AutoGenerateColumns-enabled WmxDataGrid in which we are able to update table rows in a DataBase connected with a SqlDataSource.

Each time we clear a field an empty string is written to the database where we want to write DBNull values.

At the moment we generate the update string via UPDATE... SET.. and the SQL Server function NULLIF(value, '').

Works proper but when I want to add the values to e.NewValues in BeginUpdate event I'm not able to read the values from the auto generated text boxes.

How do I do that? Is there an easier way to automatically convert empty strings to NULL values?


Calling scalar valued Function from SSIS OleDB Command Transformation


Hi There,

I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..

'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.

I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.

Thanks a lot!! 

Gridview - Update with Dynamic SELECT Command?


Hi All...

We created a GridView-based webform with Update using the design wizards - very nice and it works great.  But then we wanted to the ability to create the SELECT command dynamically based on other controls on the page.  We added something like the following to Page_Load()...

            SqlDataSource1.SelectCommand = "SELECT [StationId], [IP] FROM [Stations]";

The GridView populates normally, but the Update no longer works.  It changes the "Edit" link to "Update", gives the user the edit boxes, but when "Update" is clicked it never seems to write the new data to the database.  No error - it just doesnt work...

Any thoughts?  How can we change the SELECT command (and Connection String) dymamically and still retain the Update functionality?

Many thanks - Curt.  Code follows...

    protected void Page_Load(object sender, EventArgs e)
        //  Doesn't work with or without this conditional
        //if (!IsCallback)
            SqlDataSource1.SelectCommand = "SELECT [StationId], [IP] FROM [Stations]";

what is wrong with the Following Update Statement


I have a Select that is defined like this



 select C.Number,A.GrpName from TBL_CNTC C
    ON A.CntcID = C.ID
    where A.GrpName <> C.Number


and it give me records that dont match in the the field "GrpName" and "Number"  and now i want to Update one table from the other like this



                UPDATE TBL_CNTC 
		SET Number = A.GrpName
		ON A.CntcID = C.ID
		where A.GrpName <> C.Number

Now as you can see above i  i want to Update the table TBL_CNTC from the table TBL_ACTV and it looks like like it updated data

(167 row(s) affected)


Update with OLE DB Command


Hi All,

I want to do an update using OLEDB command and I wrote the following code in the OLE DB Command component.

SET Column1= ?,
Column2= ?
WHERE Column3= ? AND Column2<> ?

However, it seems that I need to have as many distinct columns as the parameters. I have 4 parameters but 3 columns, how do I map them? PLease adise.

Thanks in  advance.

update SharePoint list with data from infopath form using the Command Prompt or similar


i have a InfoPath form that is updating a SharePoint list when it is submitted. Now i added some extra Fields to the Sharepoint List. All these new Fields are empty till I open the InfoPath form and hit the submit buttion again.

Is there a way to update all the empty fields from the old entrys I made.

My first Idea was to search for a Powershell Solution but unfortunately I am using Windows Server 2003 R2, Sharepoint 2007 and IndoPath 2007.

Hopefully someone can Help ;)

thanking you in anticipation

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend