Can variable be used in SQL UPDATE statement in VB.NET

Hy, i have this problem in vb.net:

I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code:

Dim variable_name As String

Dim variable As Integer

Dim sqlStringAsString = ("UPDATE table_name SET " variable_name" = " & variable &" WHERE UserID = '" & UserID &"'")

Dim cmdSqlCommandAsNew SqlCommand(sqlString, conConnetion)

cmdSqlCommand.ExecuteNonQuery()

When I don't use a variable after SET statement, everything work fine. This code works fine:

Dim variable As Integer

Dim sqlStringAsString = ("UPDATE table_name SET column_name = " & variable &" WHERE UserID = '" & UserID &"'")

Dim cmdSqlCommandAsNew SqlCommand(sqlString, conConnetion)

cmdSqlCommand.ExecuteNonQuery()

Please, if someone can help me in this...thanks..

Hi,

Did you missing ampersand sign in ?

Dim sqlStringAsString = ("UPDATE table_name SET " variable_name" = " & variable &" WHERE UserID = '" & UserID &"'")

Try

Dim sqlStringAsString = ("UPDATE table_name SET " & variable_name &" = " & variable &" WHERE UserID = '" & UserID &"'")

|||

thanks on answering..i was getting numeric value, but need string value, that was a problem, variable_name has numeric value, and that was error in sql statement...thanks on help

Can Update Statistics with fullscan cause contention problems?

I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!
Update stats will not cause blocking, but it can be CPU intensive. For more
info on what type of locks are employed during statistics updation, see:
http://support.microsoft.com/default...b;en-us;195565
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:Os3tkWcZEHA.3988@.tk2msftngp13.phx.gbl...
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!

Can Update Statistics with fullscan cause contention problems?

I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!Update stats will not cause blocking, but it can be CPU intensive. For more
info on what type of locks are employed during statistics updation, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;195565
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:Os3tkWcZEHA.3988@.tk2msftngp13.phx.gbl...
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!

Can update in SQL 2000 but not express beta 2

I have come across a interesting problem when creating a datagrid view in Sql express beta 2.

I created a database shop and table customers using sql manager qeries

create database shop;

use shop
create table customers(customerID int);
use shop
insert into customers VALUES ('1');

Pathetically simple I know!!, I created the same table in SQL 2000 using enterprise manager.

When I create a new C# windows project in VS2005, create a new data source and use the express data base by dragging the datagrid straight from the data sources window, I run it and it fails to update 1 to 2
the code it failing at is

return this.Adapter.Update(dataTable);

in dataset1.designer.cs

However simply creating a new project and adding the SQL 2000 instance of the database works fine

I was just wondering if anybody else has come across this problem

Regards Ross

So I worked out the problem,

in SQL express manager when I was making the tables using sql statements I neglected to set a primary key, when I made the tables in sql 2000 Enterprise manager I added primary keys out of habbit, so basically make sure primary keys are set in the tables.

Can update accumulate?

I need to write an UPDATE statement that adds to a field from data in
another table. Can someone help? below is sample:
UPDATE TableA
SET Total = Total + TableB.Amount
FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
WHERE TableB.PrdYr = 2005
When I do this, it does not add in the incremented Total field and I end up
with the last TableB.Amount value.
Thanks.
David>> I need to write an UPDATE statement that adds to a field from data in
Yes, but you will have to provide sufficient information for others to
understand your problem. Pl. read www.aspfaq.com/5006 and post your DDLs,
sample data & expected results
Anith|||Try this, it will keep a running total in TableA each time the query is
run. If this is going to be run and needs all of the values to start
out 0 (no running total), then remove the 'Total + ' part of the query.
UPDATE TableA
SET Total = Total +
( SELECT ISNULL(SUM(TableB.Amount),0)
FROM TableB
WHERE TableB.PrdYr = 2005
and TableB.EmpNo = TableA.EmpNo
)
Kalvin|||David,
An UPDATE statement will only make one assignment
to each column. UPDATE .. FROM is a T-SQL extension
to standard SQL that allows poorly defined statements, and
while it can be handy, it can also cause confusion. I wish an
error were raised in situations like this, but that's not the case.
To do what you want, you probably need something like
update TableA set
Total = Total + (
select sum(TableB.Amount)
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
)
Steve Kass
Drew University
David wrote:

>I need to write an UPDATE statement that adds to a field from data in
>another table. Can someone help? below is sample:
>UPDATE TableA
>SET Total = Total + TableB.Amount
>FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
>WHERE TableB.PrdYr = 2005
>When I do this, it does not add in the incremented Total field and I end up
>with the last TableB.Amount value.
>Thanks.
>David
>
>|||Kalvin caught one thing I didn't. This needs either COALESCE
or a WHERE condition on the update, to avoid NULLing out
Total values when there's no match in TableB. Here's a WHERE
condition that ought to do it.
update TableA set
Total = Total + (
..
)
where exists (
select *
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
and TableB.Amount is not null
)
SK
Steve Kass wrote:
> David,
> An UPDATE statement will only make one assignment
> to each column. UPDATE .. FROM is a T-SQL extension
> to standard SQL that allows poorly defined statements, and
> while it can be handy, it can also cause confusion. I wish an
> error were raised in situations like this, but that's not the case.
> To do what you want, you probably need something like
> update TableA set
> Total = Total + (
> select sum(TableB.Amount)
> from TableB
> where TableB.EmpNo = TableA.EmpNo
> and TableB.PrdYr = 2005
> )
>
> Steve Kass
> Drew University
> David wrote:
>|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
2) Would you like to learn REAL SQL or only some proprietary kludges
that have unpredicatable results, as you have posted?
Did you actually split out a year as a temporal column'!! Surely not
!
Why don't you know that column and field are **totally** different?
Why don't you know that there is no such thing as a generic, magical
"amount" -- it has to be the amount of something. Have you ever had a
BASIC -- repeat BASIC in capital letters -- data modeling class?
You can probably get enough kludges in a newsgroup to slip past your
boss unitl you get to the next job to screw up them too.
I got an email tonight form a kid who volunteered to do a DB for an
African Relief agency and seriously screwed it up. I got the consult
after things got messed up and I posted this in some newsgroups as an
example. I guess he found me via those postings.
I know his design crippled some children; I am not sure about causing
deaths and a part of me does not want to know. Please care enough not
to do that. To other people. To other people.sql

can u ignore errors in a trigger

if there is an error in the trigger then the update to the table does not happen. is there a way to make sql ignore errors in a trigger and still update the tableI don't think so.

What kind of errors are you getting?|||you might be able to eat them in 2005 with try/catch. however it seems better to eliminate the root cause of the error, rather than covering it up.|||DROP TRIGGER <trigger_name>

But I would fix the trigger|||I suppose you could temorarily disable triggers. That seems like a dumb thing to do, but you could... I guess.

can this query be rewritten?

update CMS_RISK_SCORES
set MAX_MCARA_RISK_RTE = (select max(MCARA_RISK_RTE) from XTAW0200_MEM_DTL A
where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM),
MAX_MCARD_RISK_ADJ_RTE = (select max(MCARD_RISK_ADJ_RTE) from XTAW0200_MEM_DTL A
where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM)

Can I get the same results with one join instead of two without creating a temporary table?

Thanks much.

:confused:update CMS_RISK_SCORES
set MAX_MCARA_RISK_RTE = MaxValues.MCARA_RISK_RTE,
MAX_MCARD_RISK_ADJ_RTE = MaxValues.MCARD_RISK_ADJ_RTE
from CMS_RISK_SCORES
inner join --MaxValues
(select HIC_NUM,
max(MCARA_RISK_RTE) as MCARA_RISK_RTE,
max(MCARD_RISK_ADJ_RTE) as MCARD_RISK_ADJ_RTE
from XTAW0200_MEM_DTL
group by HIC_NUM) MaxValues
on CMS_RISK_SCORES.HIC_NUM = MaxValues.HIC_NUM|||Thank You.|||While there is a difference in syntax, I don't think there will be any significant difference in execution plan between the two statments. SQL Server is very good at combining redundant queries like this.

-PatPsql

Can SQL Profiler Track a Field in a Table?

Greetings,
I have a field, "MyDate", that is being updated everytime a trigger
runs. MyDate is supposed to update with a getdate() value. However, i
look at the data, and i see some null values in there!
Is there a way I can use profiler to track when MyDate changes value,
and what value is updating MyDate?
I tried to have the trigger dump all MyDate values into a table, and i
have access to that data, but i still don't know WHY there are some NULL
values in there!
Thanks,
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Profiler can track the execution of statements, but cannot be used to track
the actual value of variables and the like. If your trigger is supposed to
update the column, then I would suspect faulty trigger logic. Have you
considered disallowing null for the column? Another alternative is to
create a separate trigger, mark it to execute last, and do nothing but check
for NULL in the inserted/updated rows (with a corresponding
raiserror/rollback). This would at least allow you to figure out what is
causing the problem.
"don larry" <donlarry17@.hotmail.com> wrote in message
news:e$Dg$gcoEHA.868@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I have a field, "MyDate", that is being updated everytime a trigger
> runs. MyDate is supposed to update with a getdate() value. However, i
> look at the data, and i see some null values in there!
> Is there a way I can use profiler to track when MyDate changes value,
> and what value is updating MyDate?
> I tried to have the trigger dump all MyDate values into a table, and i
> have access to that data, but i still don't know WHY there are some NULL
> values in there!
> Thanks,
> Don
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Can SQL Profiler Track a Field in a Table?

Greetings,
I have a field, "MyDate", that is being updated everytime a trigger
runs. MyDate is supposed to update with a getdate() value. However, i
look at the data, and i see some null values in there!
Is there a way I can use profiler to track when MyDate changes value,
and what value is updating MyDate?
I tried to have the trigger dump all MyDate values into a table, and i
have access to that data, but i still don't know WHY there are some NULL
values in there!
Thanks,
Don
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Profiler can track the execution of statements, but cannot be used to track
the actual value of variables and the like. If your trigger is supposed to
update the column, then I would suspect faulty trigger logic. Have you
considered disallowing null for the column? Another alternative is to
create a separate trigger, mark it to execute last, and do nothing but check
for NULL in the inserted/updated rows (with a corresponding
raiserror/rollback). This would at least allow you to figure out what is
causing the problem.
"don larry" <donlarry17@.hotmail.com> wrote in message
news:e$Dg$gcoEHA.868@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I have a field, "MyDate", that is being updated everytime a trigger
> runs. MyDate is supposed to update with a getdate() value. However, i
> look at the data, and i see some null values in there!
> Is there a way I can use profiler to track when MyDate changes value,
> and what value is updating MyDate?
> I tried to have the trigger dump all MyDate values into a table, and i
> have access to that data, but i still don't know WHY there are some NULL
> values in there!
> Thanks,
> Don
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

can someone tell me what I am doing wrong

HI all,
I have this trigger on a table
**********
CREATE TRIGGER addtotal ON dbo.ClaimFinancialLoss
AFTER INSERT, UPDATE, DELETE
AS
if update(Ammount)
begin
Declare @.tot int, @.id int
select @.id = ClaimID from inserted
--Print 'Id after insert' +str(@.id)
if exists (select * from deleted)
Begin
select @.id = ClaimID from deleted
--Print 'Id after deleted' +str(@.id)
end
Select @.tot=sum(Ammount) from ClaimFinancialLoss group by ClaimID Having
ClaimID = @.id
update claim set total = @.tot where ClaimID = @.id
end
**************
This updates the claim table. On the claim table I have these two triggers
(they are kept seperate just for simplicity at the moment)
****************
CREATE TRIGGER dateupdated ON dbo.Claim
AFTER INSERT
AS
declare @.id int
select @.id=claimid from inserted
update claim set updated = getdate() where claimid = @.id
*************
CREATE TRIGGER layerchange ON [dbo].[Claim]
FOR INSERT, UPDATE AS
if update(layerid)
select layerid as ins from inserted
select layerid as del from deleted
declare @.id int
select @.id=claimid from inserted
begin
delete from ClaimDeductables where claimid = @.id
insert into claimdeductables SELECT PolLayer.LayerCap,
PolLayer.PayOrder,claim.claimid, Layer.LayerID
FROM Claim INNER JOIN
CP_Covertype ON Claim.LayerID = CP_Covertype.CPCKey
INNER JOIN
Covertype ON CP_Covertype.CTKey = Covertype.CTKey
INNER JOIN
PolLayer ON Covertype.CTKey = PolLayer.CTKey INNER
JOIN
Layer ON PolLayer.layerid = Layer.LayerID
WHERE (Claim.ClaimID = @.id)
raiserror('You have made changes to the layers of this claim',9,1,1)
end
*****************
THe raiserror always fires, when ever I update Claimfinancialloss.ammount.
To me this error should not be raised As I have a if update(Layerid), and I
am not updating the layerid of the claim, only the total (through the
trigger.)
Is having two update triggers on the same table, one of them updating the
claimtable again, causing the layerid column to simulate a be updated.
Thanks
Robertyou need a begin after if update(layerid) because if without begin
looks only at the first statement after the if
example
declare @.x int
select @.x =4
if @.x <> 4
print 'yes'
print'blah'
you see, blah will alway be printed
declare @.x int
select @.x =4
if @.x <> 4
begin
print 'yes'
print'blah'
end
If you use begin and end this won't happen since the if will skip that
whole statement
http://sqlservercode.blogspot.com/|||Without investigating this further, you missed that a trigger is fired
per STATEMENT NOT per ROW, so you better should investigate eliminating
this misdesign.
HTH, jens Suessmeyer.|||HI,
YEp, you hit the nail on the head.
Stupid me, I did have a begin statement after the iff statement, But then
during testing I inserted two select statements after the if statement,
naturally the first select statement was executed as a direct result of the
if statment then the other statments executed as a matter of course.
Thanks
Robert
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140101176.000869.264260@.g43g2000cwa.googlegroups.com...
> you need a begin after if update(layerid) because if without begin
> looks only at the first statement after the if
> example
> declare @.x int
> select @.x =4
> if @.x <> 4
> print 'yes'
> print'blah'
> you see, blah will alway be printed
>
> declare @.x int
> select @.x =4
> if @.x <> 4
> begin
> print 'yes'
> print'blah'
> end
> If you use begin and end this won't happen since the if will skip that
> whole statement
> http://sqlservercode.blogspot.com/
>|||HI Jens,
Yes I realise that, as I said this is a tempory measure, testing. But
anyway, I was pointed to the fact that I never had my begin statement in the
right place by another post.
Thanks anyway for you valued input
Robert
<Jens.Suessmeyer@.googlemail.com> wrote in message
news:1140101824.576386.320190@.g14g2000cwa.googlegroups.com...
> Without investigating this further, you missed that a trigger is fired
> per STATEMENT NOT per ROW, so you better should investigate eliminating
> this misdesign.
> HTH, jens Suessmeyer.
>

Can someone post a working sample of this?

I need to write an UPDATE using a SET where I fill in a NULL if a default
field is blank. Like the below:

UPDATE table SET birthdate = { expression | default | null }

I simply don't know the correct syntax (even after reading the online
books). I really need a working sample with anything close to this. I
can't get it to work and end-up with the birthdate pulling from a text
field and only placing a null in the table when no birthday is given.

Thanks in advance.

BobbyJI thnk you want COALESCE ( expression [ ,...n ] ), it returns the first non-null argument or Null if all argumetns are null.|||Originally posted by BobbyJ
I need to write an UPDATE using a SET where I fill in a NULL if a default
field is blank. Like the below:

UPDATE table SET birthdate = { expression | default | null }

I simply don't know the correct syntax (even after reading the online
books). I really need a working sample with anything close to this. I
can't get it to work and end-up with the birthdate pulling from a text
field and only placing a null in the table when no birthday is given.

Thanks in advance.

BobbyJ

Hi

Try
UPDATE table
set birthdate = isnull(yourtextitem,' ')|||All-

Thanks for the replies. I will try it out this weekend.|||I tried this from a prompt and got the same results (1/1/1900).

Any other ideas?|||Which one did you try? For sure walshx's will not work. Inserting a '' value into a date/time field results in the '1/1/1900' that you see. I thought Paul's suggestion would work, but I get the following error when doing a test:

None of the result expressions in a CASE specification can be NULL.

The test code I ran was:

declare @.temp varchar(20)

select @.temp = coalesce(null,null,null)

select @.temp as results

I even tried it with ansi_warnings off with no luck. I ran into a similar issue with a vb script i was running. My solution (bad as it is) was to append an update script that searched for 1/1/1900 and changed those entries to null.

I am sorry, but I don't have any good answers right now.

Hugh Scott

Originally posted by BobbyJ
I tried this from a prompt and got the same results (1/1/1900).

Any other ideas?|||It's funny you should mention that. I was thinking the exact same thing
before I started doing any of this. I figured I could write a server service
to periodically check the table and replace empty values or 1/1/1900 with nulls. I'll do this for now and keep searching for a simpler way as
I go. Thanks.

BobbbyJ|||1.select isnull(isnull(A,B),null) works

2.Look at this code. Periodic running of code is not needed.

create table XXXX
(
idX int identity(1,1) primary key
,X int null
)
GO

create trigger ti_XXXX_I on XXXX
instead of insert as
insert XXXX(X)
select isnull(inserted.X,0) from inserted
GO
create trigger ti_XXXX_U on XXXX
instead of update as
update t set
t.X=isnull(i.X,0)
from XXXX t
join inserted i on t.idx=i.idx
GO

insert XXXX values(NULL)
insert XXXX values(3)
select * from XXXX
update XXXX set X=NULL where X=3
select * from XXXX
GO

drop table XXXX
GO|||I'm sorry, I forgot to mention I'm doing this all within Visual Studio .NET (VB) and SQL2000 standard calls. I'm not too familiar with straight SQL
without referring to a book. Can I save a null in an

UPDATE table column = value and if value is empty save it as a null?

BobbyJ|||Empty means NULL is SQL.|||I'm referring to empty as VB sees a textbox with nothing typed in it or
where the length is 0 bytes.

All the coding I've done always places a 1/1/1900 in SQL. I don't have
the code in front of me (it's at work but looks like this - from memory).

UPDATE tblEMPLOYEE SET BIRTHDATE = ISNULL(txtBIRTH.text,'') WHERE EMPLOYEEID = form.EMPLOYEEID

There are more fields being update, I just selected one for this sample
in VB coding. The second half of the ISNULL I even replaced with
system.dbnull.value and get the same result. Can't figure out what's
wrong. I suppose VB never makes it a null as SQL needs to see it (just
an empty string coming in - at times).

BobbyJ|||I am not familiar with VB(.NET). In VB(6) Textbox property Text cannot store NULL values. If you want to use NULL, try variable for example Text1IsNull as boolean or Textbox.BackColor indication.|||I follow you (I think). Is this what you're saying:

Example:

Dim xBIRTHDATE as string (strings can be null if I recall)

'Birthdate is a textbox on a webform
if BIRTHDATE.TEXT <> STRING.EMPTY then
xBIRTHDATE = BIRTHDATE.TEXT
endif

'So at this point if the textbox is empty xBIRTHDATE is still set to null
'and it is safe to save.

UPDATE table SET dbBIRTHDATE = ISNULL(xBIRTHDATE,'')

Correct??|||bobbyj, can you have a look at the table definition please

if the birthdate column is defined NOT NULL you will never get a null in there

alternatively, it may have DEFAULT 0 which would explain the 1/1/1900 (this is the date that a day number of 0 converts to)

so before you write any weird script, check whether the database will even let you put a null in there

as for the syntax, try this --

script logic to generate update statement:
update table
set foo ='bar'
if birthdate form field is empty
, birthdate = null
else
, birthdate = form field value
endif|||Yes. It does allow nulls and I'll give that script a shot (maybe later tonight).

Thanks,|||I think I've figured out what's wrong. In Visual Studio .NET VB, it doesn't
set variables to NULL but something called NOTHING. When NOTHING
is passed to ISNULL, ISNULL thinks it's an empty string and not a NULL.

Do I need to declare my VS.NET variables as SQLTYPES in order to get
a true NULL? At first I thought this was a simple SQL issue but starting to
think otherwise.

BobbyJ|||Try
IIF(YourVar is nothing,"NULL","'+replace(YourVar,"'","''")+'")
for string variables to pass variable to sql.|||I will try this later today (I'm actually off today - after the SuperBowl)
when I remote in to check mail. We had system problems from the
worm virus that started out last Saturday (so hopefully the systems
are available - SQL).

Thanks,

BobbyJ|||I tried it but my compiler has issues with the syntax of the replace
statement. It's getting hung up on the single ' marks (thinks it's a comment of sorts). I really appreciate you and other taking time to
help with this. It's GREATLY appreciated.

BobbyJ|||Corrected in VB6, I dont know if this syntax can be used in .NET.

IIf(YourVar Is Nothing, "NULL", "'" + Replace(YourVar, "'", "''") + "'")|||Thanks. I'll try again.|||No matter what happens, the YOURVAR is always returned as NOTHING
and not NULL. It must be an issue with .NET. Your logic looks fine as did
my old code but I can't get a NULL for a return value. I think I need to
do some research on how to obtain a NULL value in the .NET. I suppose
I may have to look deeper into the SQLTYPES as I know there should be
a DBNULL.VALUE I can load into SQL in order to get a NULL in the database. This whole thing is really strange.

BobbyJ|||SOLVED! Code I used:

Dim strRANKDATE As String

If Me.txtGradeDate.Text <> String.Empty Then
strRANKDATE = "RANK = '" & Me.txtGradeDate.Text & "', "
Else
strRANKDATE = "RANK = NULL ,"
End If

'Building SQL string

strUpdateStatement = "UPDATE tblEMPLOYEE SET " & _
"FIRSTNAME = '" & Me.txtFirstName.Text & "', " & _
"MIDDLENAME = '" & Me.txtMiddle.Text & "', " & _
"LASTNAME = '" & Me.txtLastName.Text & "', " & _
"SUFFIX = '" & Me.ddlSuffix.SelectedItem.Text & "', " & _
"NICKNAME = '" & Me.txtNickname.Text & "', " & _
"SERVICE = '" & Me.ddlService.SelectedItem.Text & "', " & _
"GRADE = '" & Me.ddlGrade.SelectedItem.Text & "', " & _
strRANKDATE & _
"RANK = '" & Me.ddlRankTitle.SelectedItem.Text & "', " & _

etc. Now the NULL is properly added to the table when the user
removes the date from date fields on the webform. The If else
can be modied to a shorter IIF or a function can be made of it.

BobbyJ

Can several UPDATE statements deadlock within serializable transaction

This can cause conversion deadlock:
====
set transaction isolation level serializable
begin tran
select * from authors where au_id = 'bla'
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
because shared locks in serializable transactions are held for the duration
of the transaction and exculsive locks are not compatible with shared locks
from another transaction.
Here is the question - can this deadlock as well?
====
set transaction isolation level serializable
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
If this can deadlock, how can I prevent it?
I am trying to resolve COM+ deadlocking issues...
Thanks,
-StanWhy are you using a SERIALIZABLE level?
In the update, could you get away with specifying
an UPDLOCK hint instead?
"Stan" <nospam@.yahoo.com> wrote in message
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====
> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> because shared locks in serializable transactions are held for the
duration
> of the transaction and exculsive locks are not compatible with shared
locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====
> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||1. I am not using serializable, COM+ is
2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using a SERIALIZABLE level?
> In the update, could you get away with specifying
> an UPDLOCK hint instead?
> "Stan" <nospam@.yahoo.com> wrote in message
> news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> duration
> locks
>|||2. UPDATE hint should be put on select statement
Thank you,
Alex
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
>|||Use the hint on the SELECT
ie
SELECT mycolumn
FROM mytable WITH (UPDLOCK)
WHERE ID = @.ID
I don't know anything about COM+ so I couldn't
say how to suppress the SERIALIZABLE... it
seems too drastic.
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
>|||Hi Stan,
I've been reading a lot about locking and transaction isolation level
lately, as I was troubleshooting deadlocks from dll's running under COM+ as
well. I don't think a single update statement can cause deadlocks. Is there
a select statement in the calling client code that runs within the same
transaction? In C# code - which I was reviewing - I had to look for methods
with the attribute "Autocomplete()" within classes with the attribute
"Transaction(TransactionOption.Required)". This meant that the execution of
this method will be encapsulated in 1 transaction. If no unhandled exception
occurs, the transaction is automatically committed and otherwise it is
rolled back. Which leads me to the question if the T-SQL really has a
"commit" statement, because it isn't needed and could maybe even cause an
error (I'm not sure how COM+ reacts to a "no current transaction available"
when it tries to commit after the transaction is already closed, it may or
may not check the @.@.trancount function.
This is al speculative, now some solid advice: execute the following
statement in query analyzer as sa user: "dbcc traceon(-1, 1204)". 1204
instructs Sql Server to log deadlock information to the error log file - you
can find it under Management in the enterprise manager -; -1 makes the
traceflag global instead of limited to the current session. If you search in
google with "deadlock 1204" you'll learn how to interpret the error log.
This was a great help for me when investigating the deadlock situations.
Cheers,
Henk Kok
"Stan" <nospam@.yahoo.com> schreef in bericht
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====
> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> because shared locks in serializable transactions are held for the
> duration
> of the transaction and exculsive locks are not compatible with shared
> locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====
> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||> transaction? In C# code - which I was reviewing - I had to look for
methods
> with the attribute "Autocomplete()" within classes with the attribute
> "Transaction(TransactionOption.Required)". This meant that the execution
of
> this method will be encapsulated in 1 transaction.
I understand that. However all my "get" stored procedures have
"set transaction isolation level read uncommitted" statement. This should
removed the shared locks immidiately after select statement, but I still
have deadlocks...
"Update" stored procedures do not have this statement and I was wondering if
this can be a cause of deadlocking..

Can several UPDATE statements deadlock within serializable transaction

This can cause conversion deadlock:
==== set transaction isolation level serializable
begin tran
select * from authors where au_id = 'bla'
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
because shared locks in serializable transactions are held for the duration
of the transaction and exculsive locks are not compatible with shared locks
from another transaction.
Here is the question - can this deadlock as well?
==== set transaction isolation level serializable
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
If this can deadlock, how can I prevent it?
I am trying to resolve COM+ deadlocking issues...
Thanks,
-StanWhy are you using a SERIALIZABLE level?
In the update, could you get away with specifying
an UPDLOCK hint instead?
"Stan" <nospam@.yahoo.com> wrote in message
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> because shared locks in serializable transactions are held for the
duration
> of the transaction and exculsive locks are not compatible with shared
locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||1. I am not using serializable, COM+ is
2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using a SERIALIZABLE level?
> In the update, could you get away with specifying
> an UPDLOCK hint instead?
> "Stan" <nospam@.yahoo.com> wrote in message
> news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > This can cause conversion deadlock:
> >
> > ====> > set transaction isolation level serializable
> >
> > begin tran
> >
> > select * from authors where au_id = 'bla'
> >
> > update authors set au_lname = au_lname where au_id = 'bla'
> >
> > commit
> >
> > ==> >
> > because shared locks in serializable transactions are held for the
> duration
> > of the transaction and exculsive locks are not compatible with shared
> locks
> > from another transaction.
> >
> > Here is the question - can this deadlock as well?
> >
> > ====> > set transaction isolation level serializable
> >
> > update authors set au_lname = au_lname where au_id = 'bla'
> >
> > commit
> >
> > ==> >
> > If this can deadlock, how can I prevent it?
> >
> > I am trying to resolve COM+ deadlocking issues...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||2. UPDATE hint should be put on select statement
--
Thank you,
Alex
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> > Why are you using a SERIALIZABLE level?
> >
> > In the update, could you get away with specifying
> > an UPDLOCK hint instead?
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > > This can cause conversion deadlock:
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > begin tran
> > >
> > > select * from authors where au_id = 'bla'
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > because shared locks in serializable transactions are held for the
> > duration
> > > of the transaction and exculsive locks are not compatible with shared
> > locks
> > > from another transaction.
> > >
> > > Here is the question - can this deadlock as well?
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > If this can deadlock, how can I prevent it?
> > >
> > > I am trying to resolve COM+ deadlocking issues...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||Use the hint on the SELECT
ie
SELECT mycolumn
FROM mytable WITH (UPDLOCK)
WHERE ID = @.ID
I don't know anything about COM+ so I couldn't
say how to suppress the SERIALIZABLE... it
seems too drastic.
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> > Why are you using a SERIALIZABLE level?
> >
> > In the update, could you get away with specifying
> > an UPDLOCK hint instead?
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > > This can cause conversion deadlock:
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > begin tran
> > >
> > > select * from authors where au_id = 'bla'
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > because shared locks in serializable transactions are held for the
> > duration
> > > of the transaction and exculsive locks are not compatible with shared
> > locks
> > > from another transaction.
> > >
> > > Here is the question - can this deadlock as well?
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > If this can deadlock, how can I prevent it?
> > >
> > > I am trying to resolve COM+ deadlocking issues...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||Hi Stan,
I've been reading a lot about locking and transaction isolation level
lately, as I was troubleshooting deadlocks from dll's running under COM+ as
well. I don't think a single update statement can cause deadlocks. Is there
a select statement in the calling client code that runs within the same
transaction? In C# code - which I was reviewing - I had to look for methods
with the attribute "Autocomplete()" within classes with the attribute
"Transaction(TransactionOption.Required)". This meant that the execution of
this method will be encapsulated in 1 transaction. If no unhandled exception
occurs, the transaction is automatically committed and otherwise it is
rolled back. Which leads me to the question if the T-SQL really has a
"commit" statement, because it isn't needed and could maybe even cause an
error (I'm not sure how COM+ reacts to a "no current transaction available"
when it tries to commit after the transaction is already closed, it may or
may not check the @.@.trancount function.
This is al speculative, now some solid advice: execute the following
statement in query analyzer as sa user: "dbcc traceon(-1, 1204)". 1204
instructs Sql Server to log deadlock information to the error log file - you
can find it under Management in the enterprise manager -; -1 makes the
traceflag global instead of limited to the current session. If you search in
google with "deadlock 1204" you'll learn how to interpret the error log.
This was a great help for me when investigating the deadlock situations.
Cheers,
Henk Kok
"Stan" <nospam@.yahoo.com> schreef in bericht
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> because shared locks in serializable transactions are held for the
> duration
> of the transaction and exculsive locks are not compatible with shared
> locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||> transaction? In C# code - which I was reviewing - I had to look for
methods
> with the attribute "Autocomplete()" within classes with the attribute
> "Transaction(TransactionOption.Required)". This meant that the execution
of
> this method will be encapsulated in 1 transaction.
I understand that. However all my "get" stored procedures have
"set transaction isolation level read uncommitted" statement. This should
removed the shared locks immidiately after select statement, but I still
have deadlocks...
"Update" stored procedures do not have this statement and I was wondering if
this can be a cause of deadlocking..

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
Eddie
It could be blocking problems, or that the operations takes so long time because lots of data and lack of
indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that I
> can retrieve the records from every table in the database (by "select"), but
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to get
> rid of this problem?
> Thanks.
> Eddie
>
|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
that I[vbcol=seagreen]
but[vbcol=seagreen]
and[vbcol=seagreen]
get
>
|||To check if you are suffering from blocks run a start a Trace in SQL Profiler before you try and run one of the qeries that times out, make sure that you select all of the locks events, you add these to the trace in the Trace properties events tab.
I addition check that the queries you are running actually reference the indexes you have created - are the inserts or updates particulary complicated statements?
Ed
|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
EddieIt could be blocking problems, or that the operations takes so long time because lots of data and lack of
indexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that I
> can retrieve the records from every table in the database (by "select"), but
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to get
> rid of this problem?
> Thanks.
> Eddie
>|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> > I have a problem in the SQL 2000 Server. In one my database, I found
that I
> > can retrieve the records from every table in the database (by "select"),
but
> > when I wanted to update or insert the record in each table, It failed
and
> > return error "timeout expired". What is the problem? What should I do to
get
> > rid of this problem?
> >
> > Thanks.
> >
> > Eddie
> >
> >
>|||To check if you are suffering from blocks run a start a Trace in SQL Profiler before you try and run one of the qeries that times out, make sure that you select all of the locks events, you add these to the trace in the Trace properties events tab.
I addition check that the queries you are running actually reference the indexes you have created - are the inserts or updates particulary complicated statements
Ed|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
EddieIt could be blocking problems, or that the operations takes so long time bec
ause lots of data and lack of
indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx
.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that
I
> can retrieve the records from every table in the database (by "select"), b
ut
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to g
et
> rid of this problem?
> Thanks.
> Eddie
>|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
that I[vbcol=seagreen]
but[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
>|||To check if you are suffering from blocks run a start a Trace in SQL Profile
r before you try and run one of the qeries that times out, make sure that yo
u select all of the locks events, you add these to the trace in the Trace pr
operties events tab.
I addition check that the queries you are running actually reference the ind
exes you have created - are the inserts or updates particulary complicated s
tatements?
Ed|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can Query Designer Handle Subqueries`

When a subquery is part of an insert, update or just a from or where clause, it doesn't seem to have a way to structure it. Is there a procedure for that?

Thanks,

DavidHi David -
The only structuring functionality for your scenario is to use the block indent/unident function. You can find these menu items/shortcuts under the Edit menu.

Michael Raheem
Program Manager
SQL Server Tools Team|||Hi David,
One other alternative starting with the APril CTP is to use the query designer within the Query Editor to design each sub-query by selecting the text to design and then issuing the Design Query in Editor command.
Thank you,
Bill Ramos

Can ONE report parameter update MULTIPLE query parameters?

Hi there,
Is it possible to have a single report parameter actually be used to update
several query parameters used by a stored procedure in my report. The stored
procedure I'm using requires 7 parameters ... but I can determine what the
values should be for the last six based on the value the user assigns to the
first one. Therefore, instead of forcing the user to assign all 7 ... I
wanted to be able to set the remaining six based on what they assign to the
first.
Is this possible? And if so, how?
Thanks - GGreg,
I haven't tried doing this with stored procedure queries, so I'll let
someone else respond how that works. I can tell you that you definitely can
do a text query and then reference your report parameter as necessary.
Worst case, if the query parameters can all be determined from one input,
you could create a wrapper SP with only 1 parameter and put the logic inside
this SP to call the existing SP with the 7 calculated values.
Have you gotten an error message trying to set all 7 SP parameters? I would
have expected this to be something pretty easy to do from the parameters
dialog.
Ted
"Greg" wrote:
> Hi there,
> Is it possible to have a single report parameter actually be used to update
> several query parameters used by a stored procedure in my report. The stored
> procedure I'm using requires 7 parameters ... but I can determine what the
> values should be for the last six based on the value the user assigns to the
> first one. Therefore, instead of forcing the user to assign all 7 ... I
> wanted to be able to set the remaining six based on what they assign to the
> first.
> Is this possible? And if so, how?
> Thanks - G|||True, another way even more straight forward. I do this all the time with
dates. Create 7 query parameters. RS automatically creates 7 report
parameters. Go to parameters tab (click on ..., parameters tab). For
parameter 2-7 you map to an expression that references the first parameter
and does whatever you want to it. If need be use code behind report to
really manipulate it. Then go into the Report->Parameters menu from layout
and delete the unneeded Report Parameters.This way is really cleaner than my
first suggestion.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ted K" <tedk@.nospam.nospam> wrote in message
news:FDADCBCC-8FAA-4004-A6F8-D3C1276C49AB@.microsoft.com...
> Greg,
> I haven't tried doing this with stored procedure queries, so I'll let
> someone else respond how that works. I can tell you that you definitely
> can
> do a text query and then reference your report parameter as necessary.
> Worst case, if the query parameters can all be determined from one input,
> you could create a wrapper SP with only 1 parameter and put the logic
> inside
> this SP to call the existing SP with the 7 calculated values.
> Have you gotten an error message trying to set all 7 SP parameters? I
> would
> have expected this to be something pretty easy to do from the parameters
> dialog.
> Ted
> "Greg" wrote:
>> Hi there,
>> Is it possible to have a single report parameter actually be used to
>> update
>> several query parameters used by a stored procedure in my report. The
>> stored
>> procedure I'm using requires 7 parameters ... but I can determine what
>> the
>> values should be for the last six based on the value the user assigns to
>> the
>> first one. Therefore, instead of forcing the user to assign all 7 ... I
>> wanted to be able to set the remaining six based on what they assign to
>> the
>> first.
>> Is this possible? And if so, how?
>> Thanks - G

Can ONE report parameter update MULTIPLE query parameters?

Hi there,
Is it possible to have a single report parameter actually be used to update
several query parameters used by a stored procedure in my report. The stored
procedure I'm using requires 7 parameters ... but I can determine what the
values should be for the last six based on the value the user assigns to the
first one. Therefore, instead of forcing the user to assign all 7 ... I
wanted to be able to set the remaining six based on what they assign to the
first.
Is this possible? And if so, how?
Thanks - GIf you can write T-SQL this is very easy. Go to the generic query designer:
Do something like this:
declare @.SQL varchar(255)
select @.SQL = 'select name as somename from ' + @.Database + '.dbo.sysobjects
where xtype = ''U'' order by name'
exec (@.SQL)
I know you are doing a stored procedure but the concept is the same. Note
that @.SQL is declared by @.Database isn't. That is because @.Database is
mapped to a report parameter. If you put the above in the generic query
designer and execute it you are prompted for the Database. If a report
parameter is not automatically created in the form design go to
Report->Parameters and create the report parameter then come back to the
Data tab, click on ..., go to Parameters tab and map the Query Parameter to
the Report Parameter.
I suggest first getting my example to work and understand what is happening
and then move on to your stored procedure.
Hope that helps.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:7E59E3DB-F8EE-40B1-9440-944935BD85B6@.microsoft.com...
> Hi there,
> Is it possible to have a single report parameter actually be used to
> update
> several query parameters used by a stored procedure in my report. The
> stored
> procedure I'm using requires 7 parameters ... but I can determine what the
> values should be for the last six based on the value the user assigns to
> the
> first one. Therefore, instead of forcing the user to assign all 7 ... I
> wanted to be able to set the remaining six based on what they assign to
> the
> first.
> Is this possible? And if so, how?
> Thanks - G

Can not Update/Insert big5 characters in to sql server 2000

I have current current sql server 2000 database containing some columns in big5. To display these cols correctly, my asp.net nust have directive with CodePage="1252" ContentType="text/html;charset=BIG5". I can not update, or insert big5 character into these columns via .aspx page. I'm using .net framework 2.0.

Please help me, thanks a lot for any help.

The quick question is why are you using Latin code page to save data going into a Chinese alphabet database column?|||

I really don't want to use that method, but it's a legacy database used with asp. We are migrating to asp.net while the asp version's still running. So, I can not change it. But your question may give me some ideas, thank you very much.

By the way, I've try the solution in http://forums.asp.net/518209/ShowPost.aspx, It seem to be ok. But there're some words becoming '?' after updated into database.

Any hints for me

|||

I have read that thread but not everything the person said is correct so here is what you to avoid character conversion, in VS2005 the advanced option let you save your code with code pages any langauge, and you can also do encoding of the page when you save it. These will help you with the application layer but also make sure you use column level collation in the database because the Latin alphabet is 26 characters, the Chinese is more than 2000 characters, they cannot be passed arround as you want. The links below will help you. Hope this helps.

https://www.microsoft.co.ke/middleeast/msdn/arabicsupp.aspx#7

http://www.developerland.com/DotNet/General/99.aspx

|||

ThankCaddre very much,

I'll read them. My important problem is: I cannot column change the 'level collation in the database' as you said because it's a legacy database. Actually, I don't know much 'bout it. I'll check with my DBA.

regards,

|||If your database is in SQL Server 7.0 youmust migrate it or you cannot store Chinese in it correctly. Hope this helps.|||

thanks a lot,

I'm using sql server 2000, and I'm trying to use UTF-8 charset only

|||

You cannot use UTF-8 in SQL Server because SQL Server uses UC-S 2 a version of UTF-16 but here is a thread I helped someone do Chinese collation in SQL Server 2000. But you can do encoding in the application layer in UTF-8. Hope this helps.

http://forums.asp.net/1067798/ShowPost.aspx