|
I'm literally trying to clone data rows, nothing very complicated, infact, they aren't even going to be changed during this section of the project.
I've captured the rows I want to clone using a custom SQL query, but I'm not sure how to insert them back into the database with new ID's(primary key is autonumber)...
can't I just run a .update of some sort?
I've tried copying the data table from results of my query and merging into the complete table, that doesn't make any change.
I dont seem to be able to .insert anything other then independant values for each column.
I'm using VS 2005 and C#.
Thanks.
-- modified at 17:09 Monday 24th July, 2006
|
|
|
|
|
Just run an insert ignoring the autonumber primary key. It will be filled in for you.
INSERT INTO MyTable
(
Field1,
Field2,
Field3
)
SELECT
Field1,
Field2,
Field3
FROM
MyTable
WHERE
Field2 = 'MyCriteria'
|
|
|
|
|
I'm trying that, and I dont think I'm doing something right in the query.
Is field1 supposed to be the primary key?(as per default)?
I'm getting "primary key doesn't allow nulls" error, and my field1 is set to the autonum.
By autonum, I mean that SQL is set for INT for primary key, and I've got my dataset flagged to incriment 1, seed 1.
I think this is the correct way to set for autonum? I'm used to there being a default "autonum" from access for the field type...
-- modified at 18:26 Monday 24th July, 2006
|
|
|
|
|
try.....
insert into Employees
SELECT * from Employees where name = 'Joe'
Watch out for Unquie fields and indentity.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Do not specify the primary key. A new one will be generated for you.
Note: If you expect to get a full clone (copy the primary key also) then you will never accomplish this in a single table. Primary keys can not be duplicated.
|
|
|
|
|
If I use
INSERT INTO tbABC
SELECT (all but primary key)
FROM tbABC
WHERE Name = @Name
I get an error back saying that the Column Name or number of supplied values does not match the table definition.
If I use
INSERT INTO tbABC
SELECT *
FROM tbABC
WHERE Name = @Name
I get a name saying that the primary key can't be duplicated (duh)... I've been looking all over the table design and properties in SQL and I can't find where I can actually pick an autonumber for the field type.
I thought that inside my dataset I'd set to AutoIncriment, seed 1, step 1..
isn't this correctly setting autonumbering for an SQL database? I'm used to access just having autonum as a field type choice.
Thanks.
|
|
|
|
|
Sweet! I just found out I can flag the primary key as the identity column, now it works!!! Yay!
Thanks alot for your help guys.
-- modified at 15:04 Tuesday 25th July, 2006
|
|
|
|
|
Hi All,
I'm not sure if this should go here, or in the ASP.Net forum, so I'll try here and if someone wants to move it later, that's fine by me 
I'm developing a web application using ASP.Net 2.0, which will be back-ended by a SQL Server 2005 database (Express for the moment, but once it prooves to be succesful, I think the bean counters will stump up for enterprise  ).
The application is multi-user, so it must provide security for each user's data in that one user should not be able to view another user's data unless given permission to do so. To this end, I've decided that I need to implement row based security for each record. The plan is that each user is assigned to a role using the ASP.Net 2.0 security / login features, and each role is unique to each customer who uses the system. For example, take Mike - he works for CompanyA; in my application CompanyA is defined as a Role, and Mike is assigned to this Role. Now each and every record Mike enters is identified with the RoleID of CompanyA, and so Mike can only enter records for CompanyA and can only view records for CompanyA.
This seems like a pretty good way of acheiving this, but as always it's one of my home-brew plans, so I wondered if anyone had any experience of this kind of thing they could share, or can point out any pitfalls in my plans.
Thanks,
Martin.
|
|
|
|
|
SQL Server does not support row based authorisation as you describe. The business logic in the applications will have to ensure that Mike can only create or update the rows he is permitted to.
|
|
|
|
|
Don't you mean "Role Based Security" each user is assigned a role or trust level within the application that can if required use different database access levels as well.
You also have to consider how users with authenicate ( Windwos , Forms or both ?) and get a user id you can assign a Role or Memebership to. This can get fairly complex for mixed authenicated systems.
You could as a user is authenicated assign various Session State variable or better still a serialisable user object that can travel round with the user on each page. This will state user, customername or scope and access or trust level within the application.
Start simply and build from there.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Nope - I definitely mean Row based security - but those are some good tips anyway
|
|
|
|
|
Hi,
I have a query that needs to act as one single query:
Here's the first part:
select distinct a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21 ' and j.fstatus = 'PAID' group by a.officer
Here's the second part:
x.amount as New from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer having x.amount <> 0.0
Here's the third part:
b.amount as Refi from escrow c inner join e120 b on c.escrow = b.escrow
inner join a10 f on c.escrow = f.escrow where c.ftype = 'R' group by b.amount, f.officer having b.amount <> 0.0
Is there a way to make this act as one single query? I have an application that reads only one cursor at a time.
Thanks In Advance,
Nino
|
|
|
|
|
I must ask why is the second part using different alias names from the third part? The queries seem to only differ by escrow.ftype.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
My reasoning behind that was to get the values of two different fields.
The second part is getting the values for the 'S' which is defined as a "New" escrow sale.
The third part is getting the values for the 'R' which is defined as a "Refi" or refinance.
After these two fields are retrieved they are then added based on the escrow field based on the join.
Nino
|
|
|
|
|
I might ask why the second & third part have 'GROUP BY' & 'HAVING' clauses when there is only 1 field selected and it is not an aggregate.
I also question the counting of distinct dates in the first query. Can't an officer handle more then one open/closed/cancelled transaction per day?
|
|
|
|
|
I have a database that I created localy and the owner is listed as me. But when trying to access the database (conn.open()) I get the following error:
SqlException: Cannot open the database requested in login 'labels'.
Login failed for user 'AFFORDABLERTO\brian'
I triple checked the ownership, and the user named in the error is the owner.Here is the connection string
SqlConnection conn = new SqlConnection("Data Source=affordable02;Initial Catalog=Labels;Trusted_Connection=true");
What else could I check....this is the first time I have had this problem.
Thanx in advance!
Jude
|
|
|
|
|
Delete the user 'AFFORDABLERTO\brian' from the database security and then add the user back in again in database security. Also look at the database security rights for 'AFFORDABLERTO\brian' on the database ( are they Public or Dbower or even SysAdmin for the whole box).
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
|
i m working on the stored procedures in SQL that computes the division of two columns having smallint as their datatype,for each row , the results are stored in a column of another table the datatype of the result is taken as float but it is correct upto 1 decimal place
How do i increase the precision of float data type that gives the correct result "
|
|
|
|
|
You could cast the two smallints to floating point numbers before doing te calculation. You can also cast the result to any precision you would like as well.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
7/1612 = 0.004342431761.............
6/1397 = 0.0042949176807.................
above examples shows the division of 2 smallints giving a floating pt. result. but in our case I m getting the result column as 0.0 only for all values.
y is this happening, y isn't it considering the whole result or atleast it should give 3 to 4 values after decimal.
|
|
|
|
|
Hi saneng
In my tests i'm getting the full results which are:
7/1612 = 4.3424317617866E-03
6/1397 = 4.29491768074445E-03
I do the same. Values (of type smallint) are stored in one table, and division results (of type float) are stored in another table. Moreover with all my search I didn't find that how to change/set the precision + scale of float type. Its default precision is 53 which cannot be changed.
saneng wrote: 7/1612 = 0.004342431761.............
6/1397 = 0.0042949176807.................
above examples shows the division of 2 smallints giving a floating pt. result. but in our case I m getting the result column as 0.0 only for all values.
y is this happening, y isn't it considering the whole result or atleast it should give 3 to 4 values after decimal.
I guess you are using some other type like numeric or decimal. If this is so then check and set its precision + scale value.
Wish you all the best.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Hello,
I am looking to cancel a long running ODBC query. The ODBCCommand.Cancel() method doesn't seem to be working. Basicly I have a DataSet.Fill command executing on a worker thread. My WinForm's cancel button calls my CancelDownload method which calls the DataAdapter.SelectCommand.Cancel() method on the worker thread.
I've been struggling with this for a few days now. From what I have read from Microsoft this is supposed to work. Other products like Hummingbrird BI Query appear to cancel the query. What is the secret?
Thanks for your help.
|
|
|
|
|
Could you try using a transaction. Use trans.Rollback() then cmd.cancel. I don't know why the command.cancel wouldn't work. What version of sql server are you using?
//Open the connection and begin a SQL Transaction
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
trans.Rollback()
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
hi all
i have string = "Jonh, Enghland $& Mike, VietName $& Bush, America"
i have table "Temp" have 2 colum : Name , Contry
I want use SqlServer 2000 insert to table "Temp" with 3 row Seperate by $&
how will i do ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
