|
I'm working on a program that needs to be able to update/delete rows in tables which do not (for whatever reason) have primary keys. I've done extensive searching on the subject and so far all I can turn up are suggestions to write my own custom update/delete SQL statements and attach them to the DataAdapter. The problem with this is that this program doesn't connect to one or two tables exclusively; rather, it allows the user to bring up -any- table.
The only thoughts my coworkers and I have had are to use Oracle's (we're using and Oracle database btw) rowid property, or to maintain a server-side cursor and somehow update/delete using that. The problem with these two solutions is:
1) Oracle's rowid is based off the row's location on the hard drive and is unreliable for uniquely referring to a row
2) I've been reading that ADO.NET has no server-side cursor
Does anyone have -any- ideas at all on how to solve this problem?
|
|
|
|
|
Well, it seems like the Table requires some designing. Assuming you cannot change the schema (like me, I had a similar situation), put all the columns with RowVersion.Original into the where clause. Sounds crazy but let me know if there is a better way.
|
|
|
|
|
Unfortunately, no, I can't change the table design - I am just a lowly developer  I thought about generating a where clause like you said, but that doesn't help us if there are two duplicate rows. Anyone have any other suggestions?
I know this has got to be a common problem, how are people solving it?
|
|
|
|
|
I think first of all if there are duplicate records, you need to delete them. Having duplicate records in a database table does not make sense to me at all.
You can get the duplicate records using a query similar to the following.
select au_lname, au_fname, city, state, count(*)
from dup_authors
group by au_lname, au_fname, city, state
having count(*) > 1
order by count(*) desc, au_lname, au_fname
The following statements will resolve the duplicates.
select distinct *
into #holding
from dup_authors
truncate table dup_authors
insert dup_authors
select *
from #holding
drop table #holding
|
|
|
|
|
I thought the same thing, but 2 things:
1) I'm not in charge of what data is in the database
2) It does make sense if you're keeping track of transactions (ie: $1 was added on 10/20, and then later another $1 was added on 10/20). Suffice to say, the company I work for needs these duplicate rows for some reason.
We are now pursuing using Oracle's pseudo primary key: ROWID. I'll let you know how this works out, but according to someone from Microsoft, this is how the original ADO did it.
|
|
|
|
|
yah i was going to say that they might not actually be duplicates IF a unique key was in the table
"there is no spoon"
{biz stuff} {about me}
|
|
|
|
|
Jasonc221 wrote: I'm working on a program that needs to be able to update/delete rows in tables which do not (for whatever reason) have primary keys.
I don’t know how to do this in ADO.NET or if it’s even possible, but cant you append a client side index column. Delete the row, and update it?
Just an idea.
ADOX 2.8 API Reference[^]
Append Method (Columns)[^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
without a primary key how do u identify the rows to delete?
i think ur actually asking the impossible unless u somehow add a primary key in some way either permanently or temporarily ... whoever designed the database table should be shot quite frankly
"there is no spoon"
{biz stuff} {about me}
|
|
|
|
|
Well we're migrating from an ADO application and I believe it knew which to update because of a server-side cursor of some sort. Unfortunately, ADO.NET does not have such a cursor. I don't know who designed the tables, but keep in mind we're not just talking about a few tables here - this program can query literally any table in this (quite large) company. So there's a fair chance that some of these tables weren't designed by DBA's, but by people who's specialty lies in other fields (like accounting) and just needed a place to dump some data.
I wish they were all designed by DBA's, but you really can't blame the people who created these tables.
|
|
|
|
|
Hi,
I looked into a few forums but while I am sure someone else had the problem before, I just can't seem to find an answer.
Here it is:
I am simply following the MS Press lab for "Dev. XML web services" for the exam.
When implementing the Airline example (ch.5), I keep getting an SQL exception that "the SQL server does not exist or access is denied".
I have tried connect to the server and it is running.
I have tried implementing it with the service (in the app) and it works.
But when I try to access it thru the ServicedComponent derived class, it always get the error.
Any pointers?
regards,
Eric.
|
|
|
|
|
Nevermind!
Apparently I had an old version of my COM+ component left.
I had to Delete it using the Component Services smc.
now it works 
Hope it can help someone else later.
Eric.
|
|
|
|
|
From what I read, you need to encode the unicode text in UTF8 format and then save it into a regular CHAR or VARCHAR field, and you need to decode the UTF8 text when you want to use the data later (like, convert it to UTF16 format).
Is that true? Is there a NCHAR or NVARCHAR type in Oracle that corresponds to the same type in SQL Server?
Thanks.
My articles and software tools
|
|
|
|
|
There are unicode types in Oracle, nchar and nvarchar2 are there. You should create database with a unicode - characterset (i.e. AL32UTF8) for better performance and sorting.
see also
http://www.oracle.com/technology/tech/globalization/index.html
|
|
|
|
|
|
I want to encrypt a connection string so the password won't be sent in plain text. How would I attempt this? Thanks
|
|
|
|
|
I'm presuming you're talking about what's sent over the wire.
If you're talking about SQL Server 2000, the password isn't sent in plain text. However it looks like the encryption is pretty trivial - I can see that every character always encodes to the same byte every time, and that the 0 bytes (the password is sent as UTF-16, so for a password that uses only ISO-8859-1 [Latin-1] characters every other byte is a 0) are always 0xa5. So it's really not secure.
Instead of sending a username and password, you should consider using a trusted connection (set Integrated Security=SSPI) which will use a better protocol for authentication - NTLM or I think even Kerberos. However, you can't specify the username to be used - the security identity of the current thread will be used.
[EDIT] You can also enable SSL. See 'Net-Library Encryption' in Books Online. [/EDIT]
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, I'll give that a shot.
|
|
|
|
|
Hey all, I have a problem with openxml. In my ASP.NET application I use XmlSerializer to turn a object into XML, things work fine. I then send the xml to a stored procedure on the SQL server (2000). The weird thing is the same XML can be sent to two different SPs and one does not work when declaring one of the XML fields as ntext.
ie. in one I do an insert,
insert into table1 (cust_id, job_requested, job_status)
select @useCustId,
xmlDetails.Description,
xmlDetails.StatusId,
from OPENXML(@idoc, 'JobList/Job', 2)
WITH (CustomerId int, Description ntext, StatusId int) xmlDetails
Now this is trimed down, but it works fine. now when the same XML gets sent to another SP that updates like:
update job_main set
job_requested = xmlDetails.Description,
job_status = xmlDetails.StatusId,
from OPENXML(@idoc, 'JobList/Job', 2)
with (Id int, Description ntext, StatusId int) xmlDetails
where job_id = xmlDetails.Id
Now this one fails with 'Internal SQL Server error.' And I have no idea why, as it is the same XML as in the insert statement. If i change the ntext to nvarchar or varchar it works, but text and ntext do not.
Anyone seen anything like this, or know if i'm doing something stupid? I have no idea.
Thanks all
Luke
|
|
|
|
|
Is it possible to have 2 fields to be a datamember of a listbox? I want to have last and first name display. Thanks
|
|
|
|
|
Concat them together in your query.
<br />
select firstname + ' ' + lastname as fullname<br />
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I am a new teacher and the program we use with the students won't uplaod and keeps giving me these error messages everytime we try to log on! I know that my server isn't reading my database but what do I do? The computer lab is closed until I can fix this! Please Help!
|
|
|
|
|
I'm going to guess that JZooL should have been 'JZ001'. Looking for JZ001 and JZ006 on a search engine found this page[^] on Sybase's support site for their JDBC provider.
JZ001 sounds like a configuration error or someone typing the wrong user name. It looks like the user name is limited to 30 characters for this database.
JZ006 sounds like a general network error - that the server cannot be contacted. Check that the correct server name/IP is specified in the configuration. If it is, this is a task for your network administrators (if any) to work out why the one cannot see the other. Presumably this worked before the summer break? Check that no firewalls have been added or reconfigured between the client and the server. See whether a ping works (it may not do even if the server is working and contactable since it's now common for firewall software or hardware to reject pings).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi All,
I am getting the following error,
Msg 245, Level 16, State 1, Line 13
Syntax error converting the nvarchar value 'INSERT INTO [mydb].[dbo].[temp]
(
[f1],
[f2],
[f3],
[f4]
)
VALUES(
222,
333,
44,
' to a column of data type int.
while executing the below code
DECLARE @t_f1 as int
DECLARE @t_f2 as nvarchar(50)
DECLARE @t_f3 as nvarchar(50)
DECLARE @t_f4 as nvarchar(50)
set @t_f1 = 111
set @t_f2 = 222
set @t_f3e = 333
set @t_f4 = 44
declare @q as nvarchar(500)
set @q = 'INSERT INTO [mydb].[dbo].[temp]
(
[fld1],
[fld2],
[fld3],
[fld4]
)
VALUES(
'+@t_f1+',
'+@t_f2+',
'+@t_f3+',
'+@t_f4+'
)'
exec @q
My database table structure is as follows,
CREATE TABLE temp
(
fld1 int,
fld2 nvarchar(50),
fld3 nvarchar(20),
fld4 nvarchar(20),
)
Thanks in advance...
~Mahantesh V H
|
|
|
|
|
Hi!!!!
Execute statement only takes nvarchar,char....datatype only
so u change data type like
DECLARE @t_f1 as nvarchar(50)
DECLARE @t_f2 as nvarchar(50)
DECLARE @t_f3 as nvarchar(50)
DECLARE @t_f4 as nvarchar(50)
Regards
Shashank
|
|
|
|
|
Is there any other alternative means for integer data type?
~Mahantesh V H
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
