Manage TextBox.Value in other TextBox for calculations

Hi,
I need to know if and how can I do this:
tbTotal tbNumber1 tbNumber2
? 12 5
? 3 7
If I've some rows and I want insert in expression of tbTotal the pseudo-code
tbNumber1+tbNumber2
how can I do this? I need this method because the real formula is more
complex with condition etc..
Help me please!!!!! :-)Dario,
you can do this way
ReportItems!tbNumber1.Value+ReportItems!tbNumber2.value
or
Fields!Number1.Value+Fields!Number2.Value
Kiran
"Dario Concilio [MCP]" <dco@.experta-spa.com> wrote in message
news:eP3TQqxcFHA.3940@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need to know if and how can I do this:
>
> tbTotal tbNumber1 tbNumber2
> ? 12 5
> ? 3 7
>
> If I've some rows and I want insert in expression of tbTotal the
> pseudo-code
> tbNumber1+tbNumber2
> how can I do this? I need this method because the real formula is more
> complex with condition etc..
> Help me please!!!!! :-)
>

Manage Data

Hello,
I am working on an ASP.NET web site using an SQL database.
Is there a way to Insert/Delete and Update data in the database
without creating all the forms in ASP.NET?
Since I am the only person to work with the database it would be
easier for me than creating all ASP.NET forms.
Thanks,
MiguelHi
Hmm, so get in SSMS and/or Query Builder and issue DML there.
"shapper" <mdmoura@.gmail.com> wrote in message
news:d128e2f4-c3fc-4873-b491-1ccc1ee40928@.t54g2000hsg.googlegroups.com...
> Hello,
> I am working on an ASP.NET web site using an SQL database.
> Is there a way to Insert/Delete and Update data in the database
> without creating all the forms in ASP.NET?
> Since I am the only person to work with the database it would be
> easier for me than creating all ASP.NET forms.
> Thanks,
> Miguel

Manage Data

Hello,
I am working on an ASP.NET web site using an SQL database.
Is there a way to Insert/Delete and Update data in the database
without creating all the forms in ASP.NET?
Since I am the only person to work with the database it would be
easier for me than creating all ASP.NET forms.
Thanks,
Miguel
Hi
Hmm, so get in SSMS and/or Query Builder and issue DML there.
"shapper" <mdmoura@.gmail.com> wrote in message
news:d128e2f4-c3fc-4873-b491-1ccc1ee40928@.t54g2000hsg.googlegroups.com...
> Hello,
> I am working on an ASP.NET web site using an SQL database.
> Is there a way to Insert/Delete and Update data in the database
> without creating all the forms in ASP.NET?
> Since I am the only person to work with the database it would be
> easier for me than creating all ASP.NET forms.
> Thanks,
> Miguel

Making an Existing column an Identity column

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.

Ajay

WORD4LIFE
(http://www.word4life.com)Nope...can't do it...

If you have a table with a int column and want to change it...you can graphically do it in EM...

But guess what it's really doing...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1a
(
col1 int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a ON
GO
IF EXISTS(SELECT * FROM dbo.Table1a)
EXEC('INSERT INTO dbo.Tmp_Table1a (col1)
SELECT col1 FROM dbo.Table1a TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a OFF
GO
DROP TABLE dbo.Table1a
GO
EXECUTE sp_rename N'dbo.Tmp_Table1a', N'Table1a', 'OBJECT'
GO
GRANT SELECT ON dbo.Table1a TO public AS dbo
COMMIT

Just make sure you don't already have a temp_table...

What a hack

M$ is good at them...lots of practice...|||Look it up in BOL

TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.

And the IDENTITY_INSERTS Are inb the correct order...

btw, sql server itself generated it...look a save sql server change script in EM after you make a change to a table...sql

Make a Job continue after Primary Key Violation?

i have a stored procedure that processes new incoming data. during this
procedure i have many INSERT commands that may result with a duplicate key
violation.
this kind of error should not stop the process and the algorithm continue.
when i run it manually ( EXEC SP1 ...) it behaves ok and continue even when
primary key violation occur.
BUT, when i run it via a job (single step) it quits on the 1st error!!!
WHY ?
how can i force the job/SP to continue running after data errors like key
violations '
thanks
rafiThere are errors in sql that are not trappable. Erland has some good writing
that you should take a look:
http://www.sommarskog.se/error-handling-I.html
-oj
"Rafi" <Rafi@.discussions.microsoft.com> wrote in message
news:93C834D3-71DA-48AF-B2C6-2151EC06F136@.microsoft.com...
>i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even
> when
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi|||Hi
Why not add a where clause to the insert statement to check the PK value
does not exist? If they do exist you may want to log the fact.
It may not a good idea to SET XACT_ABORT OFF as you may miss something that
is important.
John
"Rafi" wrote:

> i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even whe
n
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi

Make a copy of parts of a ROW, but with a new Key.

Hi there,

I want to insert a new record into my database table, giving new field
values for all but one (an image field), which I can copied from an existing
record. The background to this is that I am allowing a user to create a new
"template" from an existing one, thus I want a new record, but also to copy
the existing template data blob into the new record.

My code looks like this, but obviously doesn't work (I don't supply a field
for the first field "ID" in my SELECT statement which should be a unique ID.
Obviously, I want SQL to automatically generate one for me!). Is there any
way to do this with SQL?

IF @.Error = 0
BEGIN
SELECT @.DateCreated,
@.Title,
@.Description,
@.Thumbnail,
ReportTemplate.Report

INTO

ReportTemplate

FROM

ReportTemplate

SET @.Error = @.@.ERROR
ENDI think I got it. Like this:

IF @.Error = 0
BEGIN
INSERT INTO
ReportTemplate
(
DateCreated,
Title,
Description,
IsStatic,
Thumbnail,
Report
)

SELECT

@.DateCreated,
@.Title,
@.Description,
@.IsStatic,
@.Thumbnail,
ReportTemplate.Report

FROM

ReportTemplate

WHERE

ReportTemplate.ID = @.ID

SET @.Error = @.@.ERROR
END

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c635gr$483$1$8302bc10@.news.demon.co.uk...
> Hi there,
> I want to insert a new record into my database table, giving new field
> values for all but one (an image field), which I can copied from an
existing
> record. The background to this is that I am allowing a user to create a
new
> "template" from an existing one, thus I want a new record, but also to
copy
> the existing template data blob into the new record.
> My code looks like this, but obviously doesn't work (I don't supply a
field
> for the first field "ID" in my SELECT statement which should be a unique
ID.
> Obviously, I want SQL to automatically generate one for me!). Is there
any
> way to do this with SQL?
> IF @.Error = 0
> BEGIN
> SELECT @.DateCreated,
> @.Title,
> @.Description,
> @.Thumbnail,
> ReportTemplate.Report
> INTO
> ReportTemplate
> FROM
> ReportTemplate
> SET @.Error = @.@.ERROR
> END

Major problem with @@IDENTITY, Treeview and GUID

Hi,

I'm trying to insert data into locally stored database (SQL Server).
The data I want inserted, is presented in a Treeview control and the data is fetched from a Webservice. The data is returned in form of a dataset.
The treeview contains checkboxes allowing a user to select what to install in the locally stored database.

To sum up:

1. Get data from a webservice' not my problem
2. Present data in a Treview control' not my problem
3. Allow to user to select which data to install' not my problem
4. Insert data that the user has selected into my db' MY PROBLEM!!!!

The Treeview looks like this.

- Group1
| | -- Rule1.1
| | -- Rule1.2
|
- Group2
| | -- Rule2.1
| | -- Rule2.2
| | -- Rule2.3
....

The Treeview is generated with DataRelations between Group and Rule.

My locally stored database is designed by a third party provider and therefore the database must not be altered.
The table I want to store data in is called "Groups" and it looks like this:

GroupID uniqueidentifier ' (newid())
GroupName nvarchar(50)
ParentGroupID uniqueidentifier' if grouptype = 0 then ParentGroupID must have a value.
GroupType tinyint ' 0 = subgroup, 1 = "top"group
Data in the table "Groups" would look like this:
GroupID GroupNameParentGroupIDGroupType
------------------
{000001...}Group1<NULL>1
{000011...}Rule1.1{000001...}0
{000012...}Rule1.2{000001...}0
{000002...}Group2<NULL>1
{000021...}Rule2.1{000002...}0
{000022...}Rule2.2{000002...}0
{000023...}Rule2.3{000002...}0

The third party also created a stored procedure called pr_AddGroup taking the following parameters:

@.GroupName ' can be both the RuleName and the GroupName
@.GroupType ' can be 0 for subgroup or 1 for "top"group
@.ParentGroup ' GUID

The problem with this stored procedure is that it does not have return value, which is here my problem actually lies.
If it returned @.@.IDENTITY I could use this as the parameter for @.ParentGroup.
Instead I figure I must create two sqlCommand's (one calling pr_AddGroup and another calling SELECT @.@.IDENTITY to get the newly created record).

My SQL Commands look like this

Dim cmd As SqlCommand
Dim Conn As SqlConnection = New SqlConnection
Conn.ConnectionString = "Data Source=myServer;Initial Catalog=myTable;Integrated Security=SSPI"
cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = Conn
cmd.CommandText = "pr_AddGroup"
cmd.Parameters.Add(New SqlParameter("@.GroupName", SqlDbType.NVarChar, 50, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.GroupType", SqlDbType.TinyInt, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.ParentGroup", SqlDbType.UniqueIdentifier, ParameterDirection.Input))
dim cmd2 as SqlCommand
cmd2 = new SqlCommand
cmd2.commandtype = commandtype.Text
cmd2.commandtext = "SELECT @.@.IDENTITY as ID FROM Groups"
cmd2.connection = Conn
dim ParentGroupGUID as system.guid

To get the data inserted in the Groups table I would something like the following, but the code is very ugly
(and it doesn't work either);

 For Each Group In TreeView1.Nodes ' Loop through Groups
 If Group.Checked Then
 cmd.Parameters("@.GroupName").Value = Group.Text.ToString
 cmd.Parameters("@.GroupType").Value = 1
 cmd.ExecuteNonQuery()
 ParentGroupGUID = cmd2.executescalar()
 End If
 For Each Rule In Group.Nodes ' Loop through Rules.
 If Rule.Checked Then
 cmd.Parameters("@.GroupName").Value = Group.Text.ToString
 cmd.Parameters("@.GroupType").Value = 1
 cmd.Parameters("@.ParentGroup").value = ParentGroupGUID
 cmd.ExecuteNonquery()
 End If
 Next
 Next

I've spent the last 5 hours figuring out this problem, so ANY help is appreciated :-)I'd recommend getting the stored proc changes because the time gap between running the stored proc and the select stmt can return a wrong ID for multiple users. So to main integrity of the stored proc and the ID its best to add the select stmt inside the stored proc itself. Also use SCOPE_IDENTITY() instead of @.@.IDENTITY.

hth|||Hi ndinakar,

Thanks for replying - but what do mean by:
"I'd recommend getting the stored proc changes"

I'm not allowed to change the stored proc because it's provided by a third party and if I modify the stored proc, the third party application is likely to break (and I don't wanna risk this).

??|||I meant the change be made in the stored proc it self to get accurate results. Even if you make a call to the table to return the latest Id there is no guratantee you would get the id for the isnert you did. if there are 5 ppl doing the insert at the same time the id's returned could get mixed up. so if you need to get the accurate id that was generated due to your insert..do the select immediately after the insert IN the stored proc itself. you need to xplain to the third party people and sort it out with them. else take the risk of not getting the right value.

hth