MS SQL Server :: Update Table From MS2005 To Oracle 9.2

Oracle Parameterized Queries To Update Oracle Table Do Not Work

Apr 23, 2007

Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.

Anyone knows how to update an Oracle table through SSIS?

Thanks!

Wenbiao

View 5 Replies View Related

Where Are The Solution File For Adventure Works DW Saved?

Jun 12, 2006

Hi, all here,

I am trying to locate the solution file for the Adventure works DW included as part of the installation of SQL Server 2005 to be opened in Business Intelligence Development Studio, But where is the solution file saved? I can see its database from Management studio but I need to know its solution file to locate it in Business intelligence studio. Please could anyone here give me some idea about that. Thanks a lot in advance for any help and guidance for that.

View 3 Replies View Related

Havin Trouble Inserting Records To A Table.. Update Works Fine

Mar 6, 2008

Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
Any help will be appreciated..
Regards,
Karen

View 6 Replies View Related

Oracle Connection In Solution Explorer

Mar 10, 2008

When trying to create a data source in Solution Explorer the Provider is greyed out and only allows me to create a connection to a SQL server database although I can connect to an oracle database in Server Explorer

View 5 Replies View Related

Get Recordcount From Oracle And Update Sql Server Table

Aug 29, 2007

I posted this question a little while ago but was not able to implement it. Now I am back to the same issue. Basically I want to get a recordcount from a table in Oracle and update an existing record in sql server with the value.

I am trying to accomplish this using a Execute SQL Task. In this task I am pointing to a Oracle DB that I am able to query from SSIS so connectivity is not an issue.

I have defined a variable EmpRC of type int32.

I have a following the the SQL Task:

query: select count(*) from emp;

result set=single row.

and on result set tab ResultName =0 and variable name is same defined above : User::EmpRC

I get an error when I run this:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "EmpCompRC": "Unsupported data type on result set binding 0.".

I have tried using different data types for EmpRC but having no luck. any ideas?

View 7 Replies View Related

SSIS: Oracle Import Works, But Not When Scheduled

Dec 26, 2006

Hi,

I am running SQL Server 2005 and have built a simple SSIS package to import data from an oracle database to my SQL Server 2005 database. When I run it in SSIS, it works and it imports just fine. When I schedule it, it gives me problems. Help!

It might be a problem with Oracle Provider client I installed. Is there a client version I can download and install? the one I downloaded from oracle doesn't work. I bet i did something wrong though.

Here is my version:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: IDE Standard

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

Thanks,

Ryan

View 5 Replies View Related

How To Convert Pl/sql Code Of Oracle To Something Equivalent Which Works On Sql Server 7.0?

Dec 21, 2000

i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated..

with regards,

vijay.

sql server 7.0 on winnt
pl/sql code on oracle 8.0

View 2 Replies View Related

SQL Server Agent And X64 Using OraOLEDB.Oracle.1: Dtexec From Cmd Works, But Job Fails

Aug 16, 2007

I am extracting data from an Oracle database and have installed the latest x64 ODAC. When I run the 64 bit dtexec in cmd the package runs fine with no errors, but when creating and executing a SSIS job in the agent it fails. I've tried creating the job using CmdExec as well and I get the same errors:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:12:43 AM
Error: 2007-08-15 11:12:45.63
Code: 0xC0202009
Source: Load Dimension Data Connection manager "ora"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x800703E6 Description: "Invalid access to memory location.".
End Error
Error: 2007-08-15 11:12:45.65
Code: 0xC020801C
Source: CopyCustomers CustomerSource [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ora" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2007-08-15 11:12:45.65

The CmdExec job step uses the _exact_ same command as the one I execute successfully in cmd:
"c:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

I have managed to find two work arounds, but they are quite ugly:

1) Schedule the package execution using Windows Task Scheduler, basically create a bat file which runs the package.
2) Schedule the package in SQL Server agent, but as T-SQL script and use xp_cmdshell, i.e. EXEC xp_cmdshell 'dtexec /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW'

Both work and use the 64 bit dtexec. Not that elegant though..

CmdExec and the 32 bit version of dtexec works, but is not good enough for me since we bought new hardware and 64 bit software just to be able to address more memory. Has anyone managed to execute a SSIS package successfully using the agent and the 64 bit OraOLEDB.Oracle.1?

Any help would be greatly appreciated. Thanks!

Btw, I am using Windows Server 2003 x64 and SQL Server with SP2.

View 9 Replies View Related

Update Trigger... Best Solution

May 3, 2006

Hi All

I have two questions:

1. the below trigger, is it the best way to update a inserted or updated column?

Create trigger tr_update_acc_status
on customer
for insert, update
as

begin
update customer
set deleted = 1
where account_status = 4 and account_status = 6
end

begin
update customer
set deleted = 0
where account_status = 5
end

it works ok but takes a long time to update the deleted column as it scans all records, which takes me on to my next question:

2. is it possible to amend the quiry above to it only updates the record i have added or updated

Thanks

View 10 Replies View Related

Dateadd Update Works But Adds 2 Or 3 Yrs-not 1

Dec 31, 2003

I have a page that is supposed to add a year to a record when it loads. The problem is that it adds 2 or three years instead.
Here is the page_load event:Sub page_load(sender as object, e as eventargs)
Try
Dim connection As SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim command As SqlCommand = new SqlCommand("Updateexpiredate", connection)
command.CommandType = CommandType.StoredProcedure

Dim param0 As SqlParameter = new SqlParameter("@memberid",SqlDbType.Int)
param0.Direction = ParameterDirection.Input
param0.Value = memberid
command.Parameters.Add(param0)

connection.Open()
command.ExecuteNonQuery()
connection.Close()
myerror.Text = "Thank You! Your account was updated"
Catch ex As Exception
myerror.Text = ex.Message
End Try
End SubAnd here is the SPROC:CREATE PROCEDURE Updateexpiredate
(
@memberid int
)
AS
UPDATE
members
SET
expiredate=(dateadd(year,1,expiredate)) <--I also tried expiredate=(dateadd(month,12,expiredate)) with the same results
WHERE
memberID = @memberID
GO

View 4 Replies View Related

Update Statement Works Ok But No Result

Nov 24, 2005

here is my update statement in a stored procedure:

create proc proc_add_comp
    @comp_answer nvarchar(300),
    @admin nvarchar(100),
    @comp_id int
as

update tbComp set

comp_answer = comp_answer + ' - ' + @admin + ', ' + @comp_answer

where comp_id=@comp_id

then I try it like this :
exec proc_add_comp 'new answer','by me',1

result is : (1 row(s) affected)

but when I look in the db, nothing was changed, comp_answer still has its old value..
comp_answer is nvarchar type column..isnt add operation allowed in update statement?
thanks...

View 1 Replies View Related

Update Statement That Works For One Record?

Apr 20, 2015

have this update statement that works for one record. How do I write it to include multiple records at once. see sample below.

update
mklopt
set

FRMDAT = '12/31/2014'

where

JOBCOD = 'PH14789'

I also want to include the following instead of running it one at a time

PH17523

PH17524

PH17525

PH17553

PH17555

PH17556

PH17557

PH17558

PH17571

PH17573

PH17574

PH17575

PH17576

PH17577

PH1757

View 5 Replies View Related

SELECT Works But UPDATE Fails. ?

Jul 23, 2005

This statement failsupdate ded_temp aset a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)With this error:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'a'.But this statement:select * from ded_temp awhere a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)Runs without error:Why? and How should I change the first statement to run my update. Thisstatement of course works fine in Oracle. :)tksken.

View 17 Replies View Related

Inserting Into Oracle Table That Has DATE_HIGH As A Partition And Need Oracle Sequence Used

May 11, 2007

Hi Everyone,

I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..

Source: MS SQL Server

Destination: Oracle 10g

The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand

OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.

Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?

If these are simple questions - my appologies, I am new to the product.

Best Regards,

Steve Collins

View 1 Replies View Related

DB Update Fails In Program, Works In Management Studio?

Jul 4, 2007

I have an SQL statement that, when run through SQL Server management studio works fine. However, when I run it on my ASP page, it doesn’t update the data! I have tried both as a stored procedure and as a simple commandText update statement.
All I do is simly update the value of a column based on another column – nothing particularly complex: update customer set dateChanged = System.DateTime.Now.ToString("dd-MMM-yyyy"), CURRSTAT = 'Active',custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END,cust_Changing_To = NULLFROM customers_v WHERE CURRSTAT = 'Changing'

As you can see, nothing that complex. The line that is causing the problem is the case:
custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END
all it does is if another nullable integer column is not null, sets it to the value of that column, else it retains its existing value.
Like I say, this works in Management studio, but I cannot get it to execute programatticaly from an asp page.
No exceptions are being thrown, it just doesn’t update the data.
Any ideas?
Thanks

View 1 Replies View Related

Insert Works, But Delete And Update Don't In A DetailsView Control

Mar 28, 2008

I have 2 Gridviews and a DetailsView for each GridView. The first Gridview and DetailsView work fine and I can Insert, Delete and Update the DetailsView just fine. However the second Gridview/DetailsView will only let me Insert but not Delete or Update. When I click on the "Delete" button it just ignores me. If I do an "Edit", when I try to click on the "Update" button it is ignored again and I have to click on "Cancel". I don't get any error messages...

Anyone have an idea what might be wrong?

View 4 Replies View Related

Primary Key In Datarow After Update Works In Access Not In Sql Server

Feb 15, 2005

Heys

a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key
of a datarow you had just inserted into the database.

After a long set of trial and error i came up with the following:

- add the tablemappings of a table
- call the dataadapte.fillschema method

then after inserting a new row into the database the primary key gets filled in automatically!

now thing is

i was hoping to duplicate this in sql server

but it doesn't seem to work at all

so after i insert a row into my datatable
and update it
the row is in the database
but in vb the datarow primary key is not filled in!
anyone have an idea?

prefereabely one that does not resort to stored procedures with return parameters etc

thx a million in advance!

View 1 Replies View Related

SQL Server 2012 :: How Update Works For Unique Values

Nov 25, 2014

Look at the following code,

Create table #test
(
id int primary key,
Name varchar(100)
)
insert into #test values (1,'John')
insert into #test values (2,'Walker')

[Code] ....

-- Query 1 :
update #test set name = 'Joney' where id = 1

-- Query 2 :
set rowcount 1
update #test set name = 'Joney' where id = 1
set rowcount 0

1. #test table have primary key & clustered index.
2. Obviously only one row will be available for an id.
3. In query 1, will the sql server look for matching rows even after it found 1 row?
4. Will query 2 really gains some performance?

View 5 Replies View Related

DTC Select Works - Insert Update Fails Cannot Begin Distributed Transaction

Mar 31, 2015

We have a rather large environment and have just a couple of boxes out there that we are getting cannot begin distributed transaction on inserts and updates but works fine on selects. Inserts and updates work fine outside the begin tran / commit so it's definitely DTC

We have checked the configuration on and the source box is set to No authentication required same for destination.

We have: Verified credentials running the service, changed them, same problem. Uninstalled and re-installed MSDTC per Microsoft instructions.

Have run all the tools for checking DTC DTCPing etc and followed those procedures which typically in the past has resolved any DTC issues. Other than swapping out the offending pc for a new one we are at a loss.

View 2 Replies View Related

Power Pivot :: Update Error (works If Importing Into SSAS Tabular)

Jun 25, 2015

The thing is I can´t make update powerpivot, it raises a generic error. Tried to import into ssas tabular and worked fine.

Is there any way to get a better error detail or debug it someway?

View 4 Replies View Related

INSERT New Record Works OK In Local Table, BUT Not If The Target SS DB/table Is In A Different Physical Server

Apr 23, 2008

Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.

Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.

What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination

RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.

The package is run thru BIDS manually so there is no sucurity restrictions attached to it.

I am not sure what I am missing. And I do not see error in my package either. It is not failing.

Thanks in advance!

View 6 Replies View Related

SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare

Oct 17, 2006

This one has me stumped.

I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.

There error generated is:

Server: Msg 16957, Level 16, State 4, Line 3

FOR UPDATE cannot be specified on a READ ONLY cursor

Here is the cursor declaration:

declare some_cursor CURSOR

for

select *

from part_view

FOR UPDATE

Any ideas, guys? Thanks in advance for knocking your head against this one.

PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.

View 2 Replies View Related

Solution To Store Million Of Data In A Table.

Jun 27, 2007

Dear all,

i need to design a database table which will store supplier's demand information. 1 supplier will probably have 10000 records and there are posibility that there are 10,000 suppliers. So, in total, the number of records will be 10000 * 10000 = XXXXA LOT XXXX which will be very large number of record to be inserted into a table. So, how can i design an table and structure to cater this scenario? Thanks.

Hope to hear from you..

View 10 Replies View Related

Dynamic Destination Table Issue - Can't Find Solution In Archives

Oct 11, 2007

I have searched the archives and haven't found a solution that works.

I started out with a package based on the Excel import wizard and have modified it to include a for each loop for processing more than one Excel source file and also modified it to do some dynamic SQL like dropping and creating the tables with names that are variable based. The package is cabable of processing more than one file, and each file has the same data elements or columns in it, but each file has to go in it's own table. The drop and preperation tasks use string variable SQL that does dynamically create the SQL and runs great. The data flow task and it's OLE destination task (which the wizard did intially create) does not perform as expected. There were some posts in the archives stating that for dynamic destination table names it can be done in the OLE destination but I can't get it to work. I tried using an access mode of table/view name variable and select a variable that is string expression based. But this variable isn't fully populated until run time and so when I'm trying to configure this I get a msg stating the table name object doesn't exist and I can't save the task.

Am I doing something wrong, or trying to do something that wrong way or trying to do something that isn't possible?

View 10 Replies View Related

Update From A FormView To An Oracle DB

Jul 23, 2007

Hi, I followed a msdn2 tutorial http://msdn2.microsoft.com/fr-fr/library/system.web.ui.webcontrols.formview(VS.80).aspx using a formview.I got one error when trying to update a field : System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/numberHere is my DataBase definition :ColumnType Nullable Primary Key
EMPLOYEE_IDNUMBER(6,0) No - 1
FIRST_NAMEVARCHAR2(20) Yes - -
LAST_NAMEVARCHAR2(25) No - -If someone has already got this error before or see why it happen, i'll be very happy if he tell it to me why. Here is my aspx page code : <html xmlns="http://www.w3.org/1999/xhtml"><head><title>Titre Forview</title></head> <body> <form id="Form1" runat="server"> <h3>FormView Example</h3> <asp:FormView id="EmployeeFormView"
datasourceid="EmployeeSource"
allowpaging="false"
datakeynames="Employee_ID"
headertext="Employee Record"
emptydatatext="No employees found."
onitemupdating="EmployeeFormView_ItemUpdating"
onmodechanging="EmployeeFormView_ModeChanging" runat="server"> <headerstyle backcolor="CornFlowerBlue"
forecolor="White"
font-size="14"
horizontalalign="Center" wrap="false"/> <rowstyle backcolor="LightBlue"
wrap="false"/> <pagerstyle backcolor="CornFlowerBlue"/> <itemtemplate> <table> <tr><td rowspan="6"></td> <td colspan="2"></td> </tr> <tr><td><b>Name:</b></td> <td><%# Eval("First_Name") %> <%# Eval("Last_Name") %></td> </tr> <tr><td><b>Employee_ID:</b></td> <td><%# Eval("Employee_ID") %></td> </tr> <tr><td><b>Hire Date:</b></td> <td><%# Eval("Hire_Date","{0:d}") %></td> </tr> <tr><td></td><td></td></tr> <tr><td colspan="2"> <asp:linkbutton id="Edit"
text="Edit"
commandname="Edit"
runat="server"/></td> </tr> </table> </itemtemplate> <edititemtemplate> <table> <tr><td rowspan="6"></td> <td colspan="2"></td> </tr> <tr><td><b>Name:</b></td> <td><asp:textbox id="FirstNameUpdateTextBox"
text='<%# Bind("First_Name") %>'
runat="server"/> <asp:textbox id="LastNameUpdateTextBox"
text='<%# Bind("Last_Name") %>'
runat="server"/></td> </tr> <tr><td></td><td></td></tr> <tr><td><b>Hire Date:</b></td><td> <asp:textbox id="HireDateUpdateTextBox"
text='<%# Bind("Hire_Date", "{0:d}") %>'
runat="server"/> </td> </tr> <tr valign="top"><td></td><td></td></tr> <tr> <td colspan="2"> <asp:linkbutton id="UpdateButton"
text="UPDATE"
commandname="Update"
runat="server"/> <asp:linkbutton id="CancelButton"
text="Cancel"
commandname="Cancel"
runat="server"/> </td> </tr> </table> </edititemtemplate> </asp:FormView> <asp:label id="MessageLabel"
forecolor="Red"
runat="server"/> <asp:sqldatasource id="EmployeeSource"
selectcommand="Select Employee_ID, Last_Name, First_Name, Hire_Date From Employees where EMPLOYEE_ID=99"
updatecommand="update EMPLOYEES set LAST_NAME='Last_Name', FIRST_NAME='First_Name' where EMPLOYEE_ID=99"
ConnectionString="<%$ ConnectionStrings:ConnectionStringOracle %>"
ProviderName="<%$ ConnectionStrings:ConnectionStringOracle.ProviderName %>" runat="server"/> </form> </body></html> And my aspx.cs page code : using System;using System.Data;using System.Configuration;using System.Collections;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.Collections.Specialized;using System.Data.OracleClient;using System.Windows.Forms;public partial class A_Supprimer_Aussi : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } public void EmployeeFormView_ItemUpdating(Object sender, FormViewUpdateEventArgs e) { // Validate the field values entered by the user. This // example determines whether the user left any fields // empty. Use the NewValues property to access the new // values entered by the user.
ArrayList emptyFieldList = ValidateFields(e.NewValues);

if (emptyFieldList.Count > 0) { // The user left some fields empty. Display an error message. // Use the Keys property to retrieve the key field value.
String keyValue = e.Keys["EmployeeID"].ToString(); MessageLabel.Text = "You must enter a value for each field of record " + keyValue + ".<br/>The following fields are missing:<br/><br/>"; // Display the missing fields.
foreach (String value in emptyFieldList) { // Use the OldValues property to access the original value // of a field.
MessageLabel.Text += value + " - Original Value = " + e.OldValues[value].ToString() + "<br>"; } // Cancel the update operation.
e.Cancel = true; } else
{
// The field values passed validation. Clear the // error message label.
MessageLabel.Text = ""; } } ArrayList ValidateFields(IOrderedDictionary list) { // Create an ArrayList object to store the // names of any empty fields.
ArrayList emptyFieldList = new ArrayList(); // Iterate though the field values entered by // the user and check for an empty field. Empty // fields contain a null value.
foreach (DictionaryEntry entry in list) { if (entry.Value == String.Empty) { // Add the field name to the ArrayList object.
emptyFieldList.Add(entry.Key.ToString());
}
}

return emptyFieldList; } public void EmployeeFormView_ModeChanging(Object sender, FormViewModeEventArgs e) { if (e.CancelingEdit) { // The user canceled the update operation. // Clear the error message label.
MessageLabel.Text = ""; } }}

View 1 Replies View Related

Select Into Works, Create Table Does Not

Jul 23, 2005

HiWe are migrating from Access to SQL server. The code is in VB and usesDAO3.6. I have successfully made the connection to the databases onthe SQL server and done operations with recordsets. I have also used aSELECT INTO command to create new tables. However CREATE TABLE andALTER TABLE commands do not work.Any ideas?The DBA says we have owner privileges on the databases and thereforeshould be able to do what we like.Many thanksSi

View 1 Replies View Related

OPENQUERY With Update To Oracle Issue

Aug 15, 2000

Currently we are running SQL Server 7 with SP1 installed on an NT box. I need to update a field in a table in Oracle. I setup a linkedserver in SQL Server using the Microsoft OLE DB Provider for ODBC (MSDASQL) and didn't have any problems selecting data from the linked Oracle tables using OPENQUERY. For example: SELECT * FROM OPENQUERY(STATSDEV, "Select * from CR_EXPORT") This query works fine.

However, now I need to update a field in the CR_EXPORT table. So I have written the following query and I tried to run it:

UPDATE OPENQUERY(STATSDEV,
"SELECT * FROM CR_EXPORT WHERE REQUEST_NUMBER = 1")
SET STATUS = 2

I got the following error message:

Server: Msg 7352, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.

The Oracle table has a unique index, so I think that the issue might be associated with the version of the OLE DB provider or the Service Pack, but I can not find any documentation to support my theory. Any help you could give would be VERY appreciated!

View 1 Replies View Related

DTS - How To Update And Insert Data From SQL To ORACLE ?

Sep 21, 1999

Hi guys,

I have a situation that i have to insert or update
the data from sql server 7.0 to Oracle 8+Database.

the requirement is that
if the data is not in Oracle
then Insert the data
else if the data exists
then Update the data.

I dont know how to use in DTS. DTS Documentation says
that u can use DataQuery but they had not given the example.

Second thing is how to connect to the Oracle
using ACtive X Script - DTS.

Please, Can anyone help me.

Thx in Adv.

Sreedhar Vankayala

View 1 Replies View Related

Convert UPDATE From Oracle To SQL Server.

Feb 15, 2008

I have this update working in an Oracle database and I need to make it run on SQL Server.

UPDATE table3 C
SET C.column1 = 'A'
WHERE (C.column2, C.column3) IN (SELECT B.column2, B.column3 FROM table1 A, table2 B WHERE A.column2 = B.column2 AND A.column3 = B.column3 AND B.column4 = 1)

Help please.

Thanx!

View 1 Replies View Related

SOLUTION! - VB.NET 2005 Express And SQL Server 2005 - NOT Saving Updates To DB - SOLUTION!

Nov 30, 2006

VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!

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

The following article is bogus and confusing:

How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

Do not copy

The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

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

The above article is bogus and confusing.

This is rediculous!

This is the most vague and convoluted bunch of nonsince I've ever come accross!

Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.

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

I've tried it and it doesn't work for me.

Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.

If you have a step-by-step procedure that can be reproduced this properly please PM me.

I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.

Many thanks.

This is the exact procedure I have come up with:

NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.

NOTE 2: DO NOT hand copy the database to a folder/directory in your project.

Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.

Create a folder for your data following NOTE 2.

Copy your data to that folder. * mine was C:mydatamyData.mdf

Create a NEW project.

Remove any Data Connections. ( no matter what)

Save it.

Data | View Data Sources

Add New Data Source

select NEW CONNECTION ( No Matter what, do it!

Select the database. * again mine was C:mydatamyData.mdf

Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.

To Test ----------

From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.

The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.

Save it.

1) Run the app.

Add one database record to the database by pressing the Add(+) icon

Just add some quick junk data that you don't mind getting lost if it doesn't save.

YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Now exit the application.

2) Run the app again.

And verify there is one record already there.

Now add a second database record to the database by pressing the Add (+) icon.

NOW add some quick junk data that you WILL intentionally loose.

*** DO NOT *** press the save icon.

Just Exit the app.

3) Again, Run the app.

Verify that the first record is still there.

Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.

Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.

As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.

Whewh!

I hope this saves me some time the next time I want to start a new database project.

Oh, and uh, for anyone else reading this post.

Thanks,
Barry G. Sumpter

Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express

Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.

View 17 Replies View Related

Common UPDATE Syntax For SqlServer And Oracle

Jul 20, 2005

The UPDATE table FROM syntax is not supported by Oracle.I am looking for a syntax that is understood by both Oracle and SqlServer.Example:Table1:id name city city_id1 john newyork null2 peter london null3 hans newyork nullTable2:id city23 london24 paris25 newyorkUPDATE table1SET city_id = table2.idFROM table1, table2WHERE table1.city = Table2.cityIf possible I do not want to have two different statements for Oracle andSqlServerPlease do not tell me that these tables are not normalized, it's just anexample!Thanks for any hints.Jan van Veldhuizen

View 8 Replies View Related

Pairwise Comparison In Update Statement Like Oracle In Server

Nov 15, 2012

I would like to know what can be the equivalent update command in SQL server?

Code:
update Table1 set EndFlag = 1
where (c1,c2) in (select c1, MAX(c2) from Table1 group by c1)

View 2 Replies View Related

Update Table From MS2005 To Oracle 9.2 - No Solution Works For Me