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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Update with unique random generated values.

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
 
Hi there, I have an issue with an update of a column with unique random generated values, using SQL 2005. What I want to achieve is to create unique username for a list of let's say 1000 rows. My approach, which may be wrong, is as follows: to create a view to retrieve a random generated number of 7 digits; to use a function that retrieves the number from the view and checks if it is unique in the table or not (if it is, the number is returned, if it is not, a new one will be generated and so on); to update the table using that random generated number. The problem is that it does not work. It does not generate unique numbers, not all the times (you have to test by running the code few times). I suspect it is something with the update, but not sure how to solve it. Here are the steps to reproduce the problem. Create the table: CREATE TABLE [dbo].[TB_Temp]( [ID] [int] IDENTITY(1,1) NOT NULL, [ADM_USERNAME] [nvarchar](7) COLLATE Latin1_General_CI_AS NULL, ) ON [PRIMARY] Add some test rows: DECLARE @i INT SET @i = 0 WHILE @i <= 1000 BEGIN INSERT INTO TB_TEMP (ADM_USERNAME) VALUES (') SET @i = @i +1 END Create the view to random generate 7 digits numbers: CREATE VIEW vw_RandNumberForAdminUserName AS SELECT ROUND((9999999 - 1000000 - 1) * RAND() + 1000000, 0) AS RandNumber Create the function to retrieve the unique 7 digits number: CREATE FUNCTION [dbo].[fn


View Complete Post


More Related Resource Links

update parentpage gridview with the values from child page?

  

hi   i am having a gridview on each row i am having a update and add button.

on click of either of the button i am opening a popup where the end user will enter data and on clck of ok button in the child page

based on the click ie

if i clicked update i need to update that particular row with the values from child page.

if i clicked on add i need to add a row below the row where i clicked add button .

can anyone help me with this?


wss2.0 update/delete/hide lookup column that does not display any values

  

Hi All,

I have a document library that contains a Category column that is a lookup field. This is a default column that is a required field when uploading documents to the document library. The Category column is empty and I am unable to amend, hide, make it not required or delete it.

I have gone to Modify settings and columns -> clicked on the Category field to edit, but there is no option to amend the content or delete it. I am only able to amend the Column name and Description.

Since then, I have amended the column name to eg. Category1 and created a new Category field as a lookup and linked it to the correct list.

The problem I am facing now, is that I cannot hide, delete or make the Category1 (old Category) field NOT required. Either I would like to update the original field to display the correct values or alternately hide, delete or make the column not required.

Please help.


Cannot retrieve values of dynamically generated controls

  
Hi All, I am creating a user control which contains checkboxes and labels that needs to be created on fly. The dynamic generation of these controls depend on the values retreived from a SPlist. I have several panels that display items queried from the list on the basis of a selection criteria. On a button click event I need to capture all the selected values from the controls that were generated dynamically.  When I traverse each dynamic control that I added to the panel, I dont see their values. I tried using recursive retrieval of the control from the page as well but it just behaves crazy, it treats a radio button as a checkbox and returns a value that I am not looking for. I am not using the the page_load() or any other event, I use the button handler on my custom method. I was thinking for ViewState, ControlsState or SessionState. I assume ViewState may not work on this as the dynamically generated controls need to be in the page_load event. Please guide as this getting way more complicated than what I had ever thought of it to be.

Create unique constraint on a column which has null values

  
Hi All, I have a table suppose 'Temp' having one of the column as 'ColA' which has some null values as well as non null values. Now i have a requirement to create a unique constraint on it. We have tried but couldnt do it.Apart from having a trigger on insert and update statements is there any other alternate. Can any one please help me on this. Thanks & Regards, Srikanth  

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign

  
Using Visual Studio with MySQL.In my XSD dataset I created a query. It runs perfect. I can preview the data fine.In my BLL I wrote code (see below) to retrieve the query results and I'm getting...Using db As New dsDemoTableAdapters.DemoTableAdapter Dim dt As New DataTable dt = db.GetDemo(DemoId) ' ERROR HAPPENS HEREFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.Why would previewing the data work but in code it fails?Any ideas?

update stored procedure for existing values of column(salperyr)

  
tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place (salary*12)in one shot.so plz suggest me update stored procedure for this.

update stored procedure for existing values of column(

  
tbl_salary salary            salperyr    hike20       hike20yr 10000.0000    NULL      12000.00    144000.00 12000.0000    NULL      14400.00    172800.00 14000.0000    NULL      16800.00    201600.00 15000.0000    NULL      18000.00    216000.00 18000.0000    NULL      21600.00    259200.00 20000.0000    NULL      24000.00    288000.00 22000.0000    NULL      26400.00    316800.00 in above table salaryper yr (salperyr ) has to be modified after caliculation my null values has to be removed and place (salary*12 )in one shot.   so plz suggest me update stored procedure for this

Entity Framework: No support for server-generated keys and server-generated values

  
HelloI tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".How can I add entities to my Sqlce- database?I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.What do I have to change to make successfully a SaveChanges()?Thanks for your help,Gerald

Update or table values by of new value

  

You got to love management, they can never make their minds up on what they want. 

I'm currently working on a asp.net project using a datagridview that acts for the most part like a spreadsheet.  What I have to do now is update all the values in the table from the selected date, hour, min, max and cost forward.  There are 7 days worth of data in the table, when the user alters either the min, max or cost I need all the values from that point onward to have the new altered value.

Here's an example:

In hour 10 of the day the user want to increase the max from 5 to 19, the min stays the same and so does the cost.  Mind you any or all of these values could change.  The only once that will remain as-is is the Date and Hour field:

Date Hour

Cannot update existing table with values from staging table

  

Hello 

In my SSIS dataflow I am inserting values into a staging table. When performing the update query in the control flow, to update values from my staging table in the final table, I am having trouble executing my SQL query. 

It look like this

UPDATE final set Value = staging.Value
WHERE final.Id = staging.Id
AND final.ValDate = staging.ValDate
AND final.ForDate = staging.ForDate

It gives me an error:

Error: 0x0 at Update: The multi-part identifier "staging.Id" could not be bound.
Error: 0x0 at Update: The multi-part identifier "staging.ValDate" could not be bound.

 

What is wrong? Any inputs?

 

 


Cannot update existing table with values from staging table

  

Hello 

In my SSIS dataflow I am inserting values into a staging table. When performing the update query in the control flow, to update values from my staging table in the final table, I am having trouble executing my SQL query. 

It look like this

UPDATE final set Value = staging.Value
WHERE final.Id = staging.Id
AND final.ValDate = staging.ValDate 
AND final.ForDate = staging.ForDate

It gives me an error:

 

Error: 0x0 at Update: The multi-part identifier "staging.Id" could not be bound.
Error: 0x0 at Update: The multi-part identifier "staging.ValDate" could not be bound.

 

 

What is wrong? Any inputs?

 

Jacob


Unique values for a filed

  

Hi,

My requirement is, my filed should enforce unique values.Im using MOSS 2007. how to achive this? please guide me.Thanks in advance


how to generate a unique random number on a form submission?

  

I want to generate a unique random number everytime a user submits a form. As the form is submited the data of the form should be placed in a database and a unique random number should be generated so that the user can later use this unique random number to reterieve his details from the database that refer to his unique random id. The unique random number should be atleast 9 digits long. Please help me on this.


How to force update all child controls values (like PropertyChanged but for entire child descendant

  

Hi

We need after event user fired (change at dataContext object) uptade all values binded to datacontext on controls descendant of grid control

¿Its is possible?

Thanks


Julio Trujillo Leon. Microsoft Certified Professional Developer. .NET Developer and TFS admin. . Please, if my contribution is useful for you press on "useful button" Por favor, si les ha sido útil pulsen en "el mensaje ha sido útil" https://www.mcpvirtualbusinesscard.com/VBCServer/JulioTrujilloLeon/interactivecard

Selecting Unique Combinations of multiple row values

  

Hi,

I have a table called 'Permutations' that lists every single combination of 5 numbers that could be selected from numbers 1 to 10.

(the first few rows are as follows, there are 30240 in total):

 

Number 1| Number 2| Number 3| Number 4| Number 5|

1 | 2 | 3 | 4 | 5

2 | 3 | 4 | 5 | 1

3 | 4 | 5 | 1 | 2

4 | 5 | 1 | 2 | 3

5 | 1 | 2 | 3 | 4

2| 3 | 4 | 5 | 6

3 | 4 | 5 | 6 | 2

 

and so on.

 

I want to write a query that only returns the unique combinations of these. So, from the rows provided above, the only rows that the query would return would be

Number 1| Number 2| Number 3| Number 4| Number 5|

1 | 2 | 3 | 4 | 5
2 | 3 | 4 | 5 | 6

Is there an easy way of doing this (note that the correct query would return 252 rows)?

Thanks


need help to generate unique random numbers

  

I want to generate a unique random number everytime a user submits a form. As the form is submited the data of the form should be placed in a database and a unique random number should be generated so that the user can later use this unique random number to reterieve his details from the database that refer to his unique random id. The unique random number should be atleast 9 digits long. Please help me on this.


How to get nodes based on some values, update other element or delete that whole object

  

Hi,

This is my xml string.

<xml version="1.0" encoding="utf-16">
<objA>
    <x> test value 1 </x>
    <y> test value 2</y>

    <objB>
        <x> test value 3</x>
        <y> test value 4</y>

            <objA>
                <x> test value 5</x>
                <y> test value 6 </y>
            </objA>

            <objA>
                <x> test value 7</x>
                <y> test value 8 </y>

                <objB>
                    <x> test value 9</x>
                    <y> test value 6</y>
                </objB>

            </objA>

    </objB>

</objA>


</xml>


 

 

I have a big xml same as above containing a deep hierarchy.

Now, I am looking to do two things.

1. I want to find a particular node/obj by checking <x> element value. so I want to get node ofwhich <x> value is "test value 3" and for that object, I want to update <y> value to "Some New Value".

2. I want to find all the nodes/objects by checking <y> element value is "test value 6" OR "test value 8" OR "test value

Categories: 
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