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


ADVERTISEMENT

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

SQL 2012 :: Generate Unique Values

Apr 23, 2014

I am working on an ASP.net web application which inserts new record into an underlying table.

It is actually a ConfirmationNumber and should be unique. I have used abs(checksum(newid()))

For this purpose. Is there a better way to accomplice this?

View 9 Replies View Related

SQL Server 2012 :: Update Table Based On Existing Values In Multiple Rows?

Oct 1, 2015

The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.

Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-

The CustID is the same.

Order #2 has a more recent order date.

Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).

So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.

I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.

update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,

[code]....

View 4 Replies View Related

SQL 2012 :: After Trigger Not Able To Update Multiple Records With Unique Constraints

Jan 15, 2015

I have a After insert, update trigger. When I update multiple records with unique constraints column in it update fails. But if this a single record update it works.

Could like to know the reason.

View 9 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.

The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

Below is the schema of the three tables

I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.

DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'

[code].....

View 2 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

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

Random Unique Values In SQL Server 2005

Jan 14, 2008

 
Hi I am using VS 2005 with SQL 2005.
I had a Datagrid and i Need to bind data into it.
I need to show Random Unique ID's based on the Parameter i pass.
I wrote select top 5 * from Employee order by NEWID()
for this i had created a Stored Procedure as
create Proc Demo(@N int)as beginselect top @N * from Employee order by NEWID()end
Its showing a syntax error near @N. How can i pass the parameter for Select Top @N * from .......

View 6 Replies View Related

SQL Server 2012 :: Why Partition Function Works For Datetime2 But Not For Datetime1

Dec 20, 2013

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime = '2007-09-01 00:00:00.000';
WHILE @i < '2008-10-01 00:00:00.000'
BEGIN
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';

[Code] ....

Msg 7705, Level 16, State 2, Line 1
Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.

However if I change to datetime2 it works

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime2 = '2007-09-01 00:00:00.000';
WHILE @i < '2008-10-01 00:00:00.000'
BEGIN
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';

[Code] ...

Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.

In this case why isn't datetime works?

version is as follow:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

from [URL] .....

Table and index partitioning is supported in this edition

so I don't know why it fails!

View 1 Replies View Related

Unique Constraint Doesn't Allow Multiple Null Values In Server?

Jun 2, 2014

Why we the Unique Constraint doesn't allow the multiple null values in Sql Server?

View 2 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

SQL Server 2012 :: Unique Index On Table

Mar 20, 2015

I am trying to create a unique index on a table such that the combination of 2 columns is unique. How do I go about that?

View 5 Replies View Related

SQL Server 2012 :: Unique Identifier As Identity Field

Dec 27, 2013

So for years I was using the int identity(1,1) primary_key for all the tables I created, and then in this project I decided, you know, I like the uniqueidentifier using newsequentialid() to ensure a distinctly unique primary key.

then, after working with the php sqlsrv driver, I realized huh, no matter what, i am unable to retrieve the scope_identity() of the insert

So of course I cruised back to the MSSMS and realized crap, I can't even make the uniqueidentifier an identity.

So now I'm wondering 2 things...

1: Can I short cut and pull the uniqueidentifier of a newly inserted record, even though the scope_identity() will return null or
2: do I now have to add a column to each table, keep the uniqueidentifier (as all my tables are unified by that relationship) and also add a pk field as an int identity(1,1) primary_key, in order to be able to pull the scope_identity() on insert...

View 3 Replies View Related

SQL Server 2012 :: Selecting Unique Record From A Table

Feb 10, 2014

I have a table (Billing Table) having multiple records for people having one record per person per each month.

How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.

View 4 Replies View Related

SQL SERVER ACCESS DENIED!! BUT Everything Works On MSDE And Works SHOWING RECORDS ON SQL SERVER!! PLEASE HELP

Jul 26, 2004

I've got a popular problem so i get a message that server acces denied! ..

But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...

On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by

RETTO - name of my server

server=RETTO;uid=sa;pwd=password;database=db1;
or by
Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;

I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!


PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!

I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??

View 3 Replies View Related

SQL Server 2014 :: Split Out A Field Of Comma Separated Values Based On Unique Code In Same Row?

Oct 21, 2014

I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.

E.g

Unique Code Comma Separated Field

14587934 1,5,17,18,19,40,51,62,70

6998468 10,45,62,18,19

79585264 1,5,18

These needs to be in column format or held in an array to be used as conditional criteria.

Unique Code Comma Separated Value

79585264 1

79585264 5

79585264 18

View 5 Replies View Related

SQL Server 2008 :: No Option To Automatically Partition Table Based On Unique Values Of Column?

Jun 17, 2015

A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.

View 4 Replies View Related

SQL Server 2012 :: How To Use Unique Identifiers In Where Clause Not In Select Statement

Jun 8, 2015

I have a two tables each having a uniqueidentifier column person_id

I am trying to a select statement where I want a list of the person_id's in one table that are not in another table.

-- insert into wch_needed those who need checked

insert into #wch_needed (person_id, rendered_by )
select distinct e.person_id, e.rendered_by
from #wch_who o, encounter e
where o.person_id not in (select distinct person_id from #wch_have )
and o.person_id = e.person_id

the where conditional

where o.person_id not in (select distinct person_id from #wch_have )

does not work.

How can I do this?

View 4 Replies View Related

SQL Server 2012 :: Use Unique Custom Error Numbers For THROW Statements?

Apr 20, 2015

My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".

We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.

Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?

View 2 Replies View Related

Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works

Jun 4, 2007

Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430' 
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha

View 2 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

SQL Server 2012 :: Sum Values With If?

Oct 28, 2014

I need to generate a report from a table using the values from a filed to determine what to add up.

create table fun_test(
packtype nvarchar(50),
price money)
insert into fun_test
values ('Single',''),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single',''),('Deposit',''),('Deposit','')

[code]....

order by PackTypeWhat I need is if the PackType is Deposit and need to multiply the number of records by 35, If the PackType is Monthly I need to multiply the records by 25 and if the PackType is Single I need to sum the values in the price column.

View 4 Replies View Related

SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows

Dec 12, 2014

I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).

SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

View 5 Replies View Related

Inserting Unique Values

Apr 24, 2001

What would be the best way to insert unique values into a table/unique column ?
I cannot make that table/unique column as indentity. Right now, I use a staging table with indentity column, insert rows then insert rows back to
final table.

Suggestions are much appreciated.

Ivan

View 2 Replies View Related

Unique Values Between Two Tables.

Jul 20, 2005

Hi,I have two tables such that in each table I need to make sure thatcolumn x in table A and column y in table B have a unique valuesmeaning that a user cannot insert a value to column A if its alreadyexist in column B and vice versa.How can I enforce it? Please remember that this two different tables.Thanks,Ori.

View 2 Replies View Related

Unique Values Query

Apr 21, 2006

Hi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.

Thanks

View 5 Replies View Related

New Values Ignored In SQL Server UPDATE Query

Nov 4, 2004

I am new to both ASP.net and this forum. I have seen some posts close to this, but none address this problem.

I have a SQL Server database on JOHN1 called 'siu_log' with a table called 'siu_log'. It has two fields: Scenarios char[20] and Machines char[20].

I have been adapting code from Build Your Own ASP.NET Website in C# & VB.NET by Zac Ruvalcaba to learn the language. Much of what you will see is his work adapted for my use.

First, the html:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="SIU_Assign.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>SIU Interface Scenario Assignments</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:datagrid id="scenariosDataGrid" runat="server" CellPadding="4" AutoGenerateColumns="False"
OnUpdateCommand="dg_Update" OnCancelCommand="dg_Cancel" OnEditCommand="dg_Edit" DataKeyField="Scenarios">
<ItemStyle BackColor="#00DDDD" ForeColor="#000000" />
<HeaderStyle BackColor="#003366" ForeColor="#FFFFFF" />
<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
<asp:BoundColumn DataField="Scenarios" HeaderText="Scenario" ReadOnly="True" />
<asp:TemplateColumn>
<HeaderTemplate>
Machine
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItem("Machines") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtMachine" Runat=server Text='<%# Container.DataItem("Machines") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
<asp:Label id="resultLabel" runat="server"></asp:Label></form>
</body>
</HTML>


Then the CodeBehind:

Sub dg_Update(ByVal s As Object, ByVal e As DataGridCommandEventArgs)
Dim strMachineName, strScenarioName As String
Dim intResult As Integer
strScenarioName = Trim(scenariosDataGrid.DataKeys(e.Item.ItemIndex))
strMachineName = CType(e.Item.FindControl("txtMachine"), TextBox).Text
cmd = New SqlCommand("UPDATE siu_log SET Machines=@Machine " & _
"WHERE Scenarios=@Scenario", conn)
cmd.Parameters.Add("@Machine", strMachineName)
cmd.Parameters.Add("@Scenario", strScenarioName)
conn.Open()
intResult = cmd.ExecuteNonQuery()
resultLabel.Text = "The result was " & intResult & "."
conn.Close()
scenariosDataGrid.EditItemIndex = -1
BindData()
End Sub


The problem is the strMachineName variable always contains the previous contents of the text box -- not the new one. This makes the UPDATE query just push the old data back into the table.

Any suggestions?

Thanks!
John

View 5 Replies View Related

Telnet Connection Works, Sql Cmd Connection Works, SQL Server Managment Studio 2005 Does Not

Jun 20, 2007

I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running



telnet sql5.hostinguk.net 1433 and

sqlcmd -S sql5.hostinguk.net -U username -P password



See below:



Active Connections

Proto Local Address Foreign Address State

TCP 0.0.0.0:25 0.0.0.0:0 LISTENING

TCP 0.0.0.0:80 0.0.0.0:0 LISTENING

TCP 0.0.0.0:135 0.0.0.0:0 LISTENING

TCP 0.0.0.0:443 0.0.0.0:0 LISTENING

TCP 0.0.0.0:445 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED

TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED

TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING

TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING

UDP 0.0.0.0:445 *:*

UDP 0.0.0.0:500 *:*

UDP 0.0.0.0:1025 *:*

UDP 0.0.0.0:1030 *:*

UDP 0.0.0.0:3456 *:*

UDP 0.0.0.0:4500 *:*

UDP 81.105.102.47:123 *:*

UDP 81.105.102.47:1900 *:*

UDP 81.105.102.47:5353 *:*

UDP 127.0.0.1:123 *:*

UDP 127.0.0.1:1086 *:*

UDP 127.0.0.1:1900 *:*

Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.

The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:



TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT



Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)

I would expect this as the DNS has not been advised to encrypt the conection.

This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.


This is on a XP machine trying to connect to the remote webhosting company via the internet.

I can ping the server

I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled

I do not have any aliases set up

No I do not force encryption

I wonder if you have any further suggestions to this problem?

View 7 Replies View Related

Code!func To Get Values In Header Only Works In IDE And Not Deployed Report (also Worked Ok In RS2000)

Jul 18, 2007

I have code



Function GetDealCount(reportItems)

return iif(IsNothing(reportItems!txtDetailCountRows.Value), 0, reportItems!txtDetailCountRows.Value)

End Function

Function GetSumNotionalAmount(reportItems)

return iif(IsNothing(reportItems!txtDealSumNotionalAmount.Value), 0, reportItems!txtDealSumNotionalAmount.Value)

End Function



That I am calling from a textboxes in the page header

= Code.GetDealCount(ReportItems) & " Deal(s)"

also

= Parameters!BaseCurrency.Value + " " + Format(Code.GetSumNotionalAmount(ReportItems),"N2").ToString().Replace(",","'")





When I preview the report in VS.NET I get values showing.

When I deploy the report I just get #Error showing.



Also this report used to work fine in RS2000



Does anyone know the cause of this issue?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved