Convert Access Query to Transact SQL 2000 IIF to CASE

I need to convert a MS Access 2000 query into SQL Server 2000. Since
the IIF statements are not allowed in Transact-SQL, I 'm trying to
use the CASE Statement
I have two tables, Results and Sections. I need to import the section
records into the Results table. I tried to do a CASE statement, but so
far I cant' get the second IIF right.
In a nutshell, this is what this update query does:
Update the TotalScore in the tblResults table. If the tblResults.type
field is A, B, or C, then make it null. Otherwise, check if the
tblSections.MainScore1 field is null. If it is, then use the
tblResults.TotalScore value. If is not null, then use the
tblSections.MainScore1 value.
Here is the actual Access query syntax :
UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
[tblSections].TestID
SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
[Type]="C",Null,
IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
I have several other fields to update in this query, but they're very
similar to this one.
Any help would be appreciated it.
Thanks.let's see..
update tblResults
set TotalScore = case when r.type in ('A', 'B', 'C') then null else
coalesce(s.MainScore1, r.TotalScore) end
from tblResults r, tblSections s
where r.testID = s.testID
dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145560609.392893.303600@.e56g2000cwe.googlegroups.com...
>I need to convert a MS Access 2000 query into SQL Server 2000. Since
> the IIF statements are not allowed in Transact-SQL, I 'm trying to
> use the CASE Statement
> I have two tables, Results and Sections. I need to import the section
> records into the Results table. I tried to do a CASE statement, but so
> far I cant' get the second IIF right.
> In a nutshell, this is what this update query does:
> Update the TotalScore in the tblResults table. If the tblResults.type
> field is A, B, or C, then make it null. Otherwise, check if the
> tblSections.MainScore1 field is null. If it is, then use the
> tblResults.TotalScore value. If is not null, then use the
> tblSections.MainScore1 value.
> Here is the actual Access query syntax :
> UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
> [tblSections].TestID
> SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
> [Type]="C",Null,
> IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
>
> I have several other fields to update in this query, but they're very
> similar to this one.
> Any help would be appreciated it.
> Thanks.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can't use Access SQL syntax in SQL'r. Use Standard SQL syntax.:
UPDATE tblResults
SET TotalScore =
CASE WHEN [Type] IN ('A','B','C') THEN NULL
ELSE CASE WHEN MainScore1 IS NULL THEN TotalScore
ELSE (SELECT MainScore1 FROM tblSections
WHERE TestID = tblResults.TestID)
END
END
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBREfx1oechKqOuFEgEQLAkgCfZlAFT1+TbFfW
cP+zDhv5c5hCnqgAnAji
grqbw9lVWW3Mm+3fC54YuPJj
=Rv7/
--END PGP SIGNATURE--
ILCSP@.NETZERO.NET wrote:
> I need to convert a MS Access 2000 query into SQL Server 2000. Since
> the IIF statements are not allowed in Transact-SQL, I 'm trying to
> use the CASE Statement
> I have two tables, Results and Sections. I need to import the section
> records into the Results table. I tried to do a CASE statement, but so
> far I cant' get the second IIF right.
> In a nutshell, this is what this update query does:
> Update the TotalScore in the tblResults table. If the tblResults.type
> field is A, B, or C, then make it null. Otherwise, check if the
> tblSections.MainScore1 field is null. If it is, then use the
> tblResults.TotalScore value. If is not null, then use the
> tblSections.MainScore1 value.
> Here is the actual Access query syntax :
> UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
> [tblSections].TestID
> SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
> [Type]="C",Null,
> IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
>
> I have several other fields to update in this query, but they're very
> similar to this one.|||Hello Guys, thanks for replying. I've been trying both suggestions and
both give me the same results. I'm able to get some of the total
scores, but not all of them.
I can get some of the total scores for not all of them. My guess is
that the error happens when the query is evaluating for null or perhaps
the declared join for the 2 tables is wrong.
the Table tblResults has 1 instance of TestID. The Table tblSections
has several instances of a TestID and they can have something in the
MainScore1 or be Null.
Here's an example of the tblSections table. In this example, I'm able
to get the 43 TotalScore for the 214371148 ID, but not for the other 2.
When running the old Access 2000 update query, I do get the 3
TotalScores.
Type TestID MainScore1
M 214209195 58
L 214371148 43
U 214217823 74
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823|||Hi,
Could you please post the DDL for the tables in question, INSERTs for the
sample data, and the expected results?
Thanks
Dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145640771.510008.142980@.v46g2000cwv.googlegroups.com...
> Hello Guys, thanks for replying. I've been trying both suggestions and
> both give me the same results. I'm able to get some of the total
> scores, but not all of them.
> I can get some of the total scores for not all of them. My guess is
> that the error happens when the query is evaluating for null or perhaps
> the declared join for the 2 tables is wrong.
> the Table tblResults has 1 instance of TestID. The Table tblSections
> has several instances of a TestID and they can have something in the
> MainScore1 or be Null.
> Here's an example of the tblSections table. In this example, I'm able
> to get the 43 TotalScore for the 214371148 ID, but not for the other 2.
> When running the old Access 2000 update query, I do get the 3
> TotalScores.
> Type TestID MainScore1
> M 214209195 58
> L 214371148 43
> U 214217823 74
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
>|||Here's the DDLs
CREATE TABLE [dbo].[tblResults] (
[InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestDate] [datetime] NULL ,
[TotalScore] [float] NULL ,
[MainScore1] [float] NULL ,
[MainStatus1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainScore2] [float] NULL ,
[MainStatus2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainScore3] [float] NULL ,
[MainStatus3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Sec1c] [float] NULL ,
[Sec1i] [float] NULL ,
[Sec2c] [float] NULL ,
[Sec2i] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblSections] (
[InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NULL ,
[MainScore1] [float] NULL ,
[MainScore2] [float] NULL ,
[MainScore3] [float] NULL ,
[MainStatus1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainStatus2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainStatus3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Sec1c] [float] NULL ,
[Sec1i] [float] NULL ,
[Sec2c] [float] NULL ,
[Sec2i] [float] NULL
) ON [PRIMARY]
GO
Since I already posted a short example of the tblSections table, here's
the one for the tblResults table
TestID SSN ExamType TotalScore
214209195 123456789 M
214371148 987654321 L
214398090 234567890 B
214398102 098765432 A
214217823 590380445 U
214430778 567890123 C
Based on the update query, I should get the totalscore for the records
with the TestID: 214209195, 214371148, and the 214217823. However, I
only get the results for the 214371148. The other 2 total scores are
still null.
Hope this helps.|||Hi,
Thanks for the DDL. And if you've posted the actual INSERT script, would it
look something like this?
insert tblResults (TestID, SSN, Type)
select
214209195, '123456789', 'M'
union all
select
214371148, '987654321', 'L'
union all
select
214398090, '234567890', 'B'
union all
select
214398102, '098765432', 'A'
union all
select
214217823, '590380445', 'U'
union all
select
214430778, '567890123', 'C'
insert tblSections (TestID, MainScore1)
select
214209195, 58
union all
select
214371148, 43
union all
select
214217823, 74
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
If so, the update I posted yesterday should give the expected result. Once
again:
update tblResults
set TotalScore = case when r.type in ('A', 'B', 'C') then null else
coalesce(s.MainScore1, r.TotalScore) end
from tblResults r, tblSections s
where r.testID = s.testID
select TestId, TotalScore from tblResults
TestId TotalScore
-- ----
214209195 58.0
214371148 43.0
214398090 NULL
214398102 NULL
214217823 74.0
214430778 NULL
(6 row(s) affected)
Dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145644726.162771.207080@.g10g2000cwb.googlegroups.com...
> Here's the DDLs
> CREATE TABLE [dbo].[tblResults] (
> [InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
> [TestID] [int] NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TestDate] [datetime] NULL ,
> [TotalScore] [float] NULL ,
> [MainScore1] [float] NULL ,
> [MainStatus1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainScore2] [float] NULL ,
> [MainStatus2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainScore3] [float] NULL ,
> [MainStatus3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Sec1c] [float] NULL ,
> [Sec1i] [float] NULL ,
> [Sec2c] [float] NULL ,
> [Sec2i] [float] NULL
> ) ON [PRIMARY]
>
>
> CREATE TABLE [dbo].[tblSections] (
> [InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
> [TestID] [int] NULL ,
> [MainScore1] [float] NULL ,
> [MainScore2] [float] NULL ,
> [MainScore3] [float] NULL ,
> [MainStatus1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainStatus2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainStatus3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Sec1c] [float] NULL ,
> [Sec1i] [float] NULL ,
> [Sec2c] [float] NULL ,
> [Sec2i] [float] NULL
> ) ON [PRIMARY]
> GO
>
> Since I already posted a short example of the tblSections table, here's
> the one for the tblResults table
> TestID SSN ExamType TotalScore
> 214209195 123456789 M
> 214371148 987654321 L
> 214398090 234567890 B
> 214398102 098765432 A
> 214217823 590380445 U
> 214430778 567890123 C
>
> Based on the update query, I should get the totalscore for the records
> with the TestID: 214209195, 214371148, and the 214217823. However, I
> only get the results for the 214371148. The other 2 total scores are
> still null.
> Hope this helps.
>

Convert .MDF (Master Database file) into ANSI SQL statements

Hi,

We have .MDF (Master Database File). from the Microsoft SQL Server. Is there a way to generate a ANSI sql statements from it. The Goal is to use this .MDF file for other database like (MySQL and Oracle). Once we have ANSI sql statements (e.g. Create Table Test)..
we can use it to create a Database tables on the fly on any Database whether it is Oracle or Mysql or Microsoft. If there is better route then this one please advice me how to do it.

There are tools out there which can do this. I beleive that TOAD can handle this.

You will need the SQL Server Engine installed to do this.

|||Hi,

Thanks for your prompt reply. Using Microsoft SQL Server Management Studio Express.
I was able to Generate Script from the .MDF file. This options creates .sql file but this is TSQL file....Is there a way to convert this file into Ansi SQL file which will work on any database...whether it is (oracle, mysql or MS sql)..

This is the syntax how it look like

USE [RonakPatel]
GO
/****** Object: Table [dbo].[AuditEvents] Script Date: 05/09/2007 11:44:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AuditEvents](
[ID] [nchar](38) NOT NULL,
[AuditDateTime] [datetime] NOT NULL,
[AuditCode] [int] NOT NULL,
[AuditedUserID] [nchar](38) NOT NULL,
[AuditedConstructID] [nchar](38) NOT NULL,
[AuditText] [nchar](38) NOT NULL,
CONSTRAINT [PK_AuditEvents] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I know that GO and SET are not part of Standard SQL i had to remove them inorder for the script to work with my c# application.
|||There are several tools out there which can do the T/SQL to ANSI-SQL conversition for you. When I Googled for "convert T/Sql to ansi-sql" I got several hits.|||Hi,

Thanks for your suggestion mrdenny, I actually found one software..Advent net SwisSQL that does convert any SQL into ANSI SQL..now the next part is I am trying to execute that ansi sql statements using OleDbConnection ..and Server is : MS SQL some how it does not know datatype BLOB...

|||

Help me out here. Isn't ANSI SQL relegated to CRUD operations only and the basic datatypes. And each provider has their propietary extensions for schema creation and control. If I've got that wrong -set me straight.

It seems that there are many parts of the schema that are not ANSI specific, but in fact, provider specific.

You may find a tool that will convert T-SQL schema code to another product schema code (PSQL) -BUT I don't think that either will be ANSI SQL.

|||

RonakPPatel wrote:

Hi,

MS SQL some how it does not know datatype BLOB...

Correct. The BLOB data type isn't a valid data type in Microsoft SQL Server. Most of the vendors have there own data type names and definations. There is no cross platform standard.

|||Hi Arnie,

Here is the link of tool. This will convert a single SQL query or statement into any other format you want (Oracle, MS SQL, MySQL, Sybase, DB2, Ansi SQL etc)...the problem is that ANSI SQL it generates when i use it to execute using the Oledbconnection i am getting weird error about datatypes. But if i use the T-SQL syntax instead of Ansi SQL from this software everything works fine..and it also creates a table...

Site...
http://www.swissql.com/

Download this one
GUI based tool that converts SQL queries from one database dialect to another.

Here is MSSQL Syntext that works

CREATE TABLE automateconstructs11
(
ResourceID varchar (38) NOT NULL ,
ResourceName TEXT ,
ParentID varchar (38) DEFAULT NULL ,
ResourceType NUMERIC (11) NOT NULL ,
CompletionState NUMERIC (11) NOT NULL ,
Notes TEXT ,
CreatedBy varchar (38) NOT NULL ,
CreatedOn datetime NOT NULL ,
ModifiedOn datetime NOT NULL ,
Version NUMERIC (11) NOT NULL ,
VersionDate datetime NOT NULL ,
Empty BINARY (1) NOT NULL ,
Enabled BINARY (1) NOT NULL ,
PRIMARY KEY (ResourceID)
)

Here is MySQL syntext that works...

CREATE TABLE `automateconstructs`
(
`ResourceID` varchar (38) NOT NULL ,
`ResourceName` longtext ,
`ParentID` varchar (38) DEFAULT NULL ,
`ResourceType` int (11) NOT NULL ,
`CompletionState` int (11) NOT NULL ,
`Notes` longtext ,
`CreatedBy` varchar (38) NOT NULL ,
`CreatedOn` datetime NOT NULL ,
`ModifiedOn` datetime NOT NULL ,
`Version` int (11) NOT NULL ,
`VersionDate` datetime NOT NULL ,
`Empty` TINYINT NOT NULL ,
`Enabled` TINYINT NOT NULL ,
PRIMARY KEY (`ResourceID`)
)

Here is Ansi SQL syntaxt that does not work...

CREATE TABLE automateconstructs
(
ResourceID varchar (38) NOT NULL ,
ResourceName BLOB ,
ParentID varchar (38) DEFAULT NULL ,
ResourceType int (11) NOT NULL ,
CompletionState int (11) NOT NULL ,
Notes BLOB ,
CreatedBy varchar (38) NOT NULL ,
CreatedOn TIMESTAMP NOT NULL ,
ModifiedOn TIMESTAMP NOT NULL ,
Version int (11) NOT NULL ,
VersionDate TIMESTAMP NOT NULL ,
Empty bit (1) NOT NULL ,
Enabled bit (1) NOT NULL ,
PRIMARY KEY (ResourceID)
)

|||

This tool is designed to convert a QUERY to ANSI ("single SQL query or statement ") -NOT a CREATION statement. ANSI SQL is QUERY language (DML).

Each Vendor has their own extension to the SQL Language for schema (DDL) and security (DCL). There is NO ANSI standard . Vendors DDL and DCL are vendor specific and not interchangable. (Well, some parts may be -but there is no guarantee.)

You will have to create vendor specific DDL or DCL, and execute the correct version depending upon the Server support.

Conversion from MS Access SQL Code...nested if statements

INSERT INTO EligSummary ( PlanVariation)
SELECT DISTINCT

IIf(Left([BPI],1)=2 Or Left([BPI],1)=3,

'1. MED EE',

'2. MED DEP' AS PlanVariation)

The above is the code i would use in access to Assign either the value

'MED EE' or 'MED DEP' to the PlanVariation field.

I am new to SQL Server - how would I accomplish this in SQL Server 2000?

If I use SQL

INSERT INTO EligSummary (PlanVariation)
SELECT Planvariation =

CASE left([BPI],1)
WHEN 1 THEN 'EE NON MED'
WHEN 2 THEN 'MED DEP'

..it requires me to GROUP by on BPI which would cause it to enter 15 different rows for each BPI code as opposed to 2 for EE NON MED and MED DEP......

I would appreciate any help you could give me with this!

CASE has a second syntax, which will give you what you want

INSERT INTO EligSummary ( PlanVariation)
SELECT DISTINCT
CASE WHEN (left([BPI], 1) = '2' OR left([BPI], 1) = '3' THEN '1. MED EE'
ELSE '2. MED DEP' END AS PlanVariation

|||

Thanks! That did the trick..i appreciate the help.

Controlling flow in a stored procedure

I have a stored procedure with two UPDATE statements in it. The second UPDATE statement relies on the completion of the first UPDATE statement to run correctly.

The problem I am running into is that SQL Server sometimes runs the second statement before completing the first.

To get around this, I tried putting the second UPDATE statement in a different stored procedure called within the first procedure, but I am still having problems.

I do not believe I am doing anything wrong, but just in case, here is the relevant code from the proc:

-- Look up County ID

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET CountyID =

(SELECT CountyID

FROM tblCountyLkp

WHERE tblCountyLkp.CountyName = LTRIM(RTRIM(tmpZoneTypes.CountyName)))

COMMIT TRANSACTION

-- Look up existing Zone Type IDs

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET ZoneTypeID =

(SELECT tblZoneTypes.ZoneTypeID

FROM tblZoneTypes

WHERE tblZoneTypes.CountyID = tmpZoneTypes.CountyID

AND tblZoneTypes.FieldNbr = tmpZoneTypes.FieldNbr

AND LTRIM(RTRIM(tblZoneTypes.ZoneAbbrev)) = LTRIM(RTRIM(tmpZoneTypes.ZoneAbbrev))

AND LTRIM(RTRIM(tblZoneTypes.ZoneFull)) = LTRIM(RTRIM(tmpZoneTypes.ZoneFull)))

COMMIT TRANSACTION

Is there a way to control the flow so the second update statement won't run until the first statement has been completed? I thought about maybe using a trigger to fire whenever the CountyID field is updated. Other options?

chris

1 variant (for SQL 2000 & SQL 2005):

begin transaction

declare @.ErrorVar int

update .... --The First Update

set @.ErrorVar = @.@.Error

if @.ErrorVar <>0

begin

-- Insert your error hadling code

rollback --For Example rollback transaction

end

else

begin

update ... --The second update

commit

end

2 variant (for SQL 2005 only):

begin tran

begin try

update ... --The first update

--If you have error in fist update you go to catch block

update ... - The second update

commit

end try

begin catch

-- Insert your error hadling code

rollback --For Example rollback transaction

end catch

|||SQL always executes "top down" and completes the first statement before starting the 2nd. What makes you think it is not complete?

The only way I see you would get different results than expected with what you posted, would be if you have the isolation level set to "read uncommitted". You can set the isolation level by using:

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

at the top of your stored proc and that will force all updates to be committed and locks to be placed on the data until you are done.|||

Thanks for the suggestions from both of you. It turns out the problem was a bug in a subsequent UPDATE statement that was changing my ZoneTypeID back to NULL. I fixed the bug, and now the proc works perfectly.

chris

Continuation of long SQL statement syntax

Hi All -

I am updating four values. What is the proper syntax to have the
following 4 update statements as one statement?

set objRec = objDB.Execute("Update orientform set session = '" &
strSession & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set fname = '" & strfname
& "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set gender = '" &
strgender & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set lname = '" & strlname
& "' where id = '" & strid & "'")

Thanks,

Joey"update orientform set sessions = '" & strSession & "', fname = '" &
strfname & '", gender = etc etc
where id = '" & strid & "'"

Notes I see you called your command objRec... maybe just habit but you
aren't creating a recordset earlier in the piece are you? Not needed for
updates/inserts/deletes. Also if your id (in the table) has an int dataype
then forget the single quotes around your strid

Jay

<joseph.jasinski@.quinnipiac.edu> wrote in message
news:1102635650.764000.267730@.z14g2000cwz.googlegr oups.com...
> Hi All -
> I am updating four values. What is the proper syntax to have the
> following 4 update statements as one statement?
> set objRec = objDB.Execute("Update orientform set session = '" &
> strSession & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set fname = '" & strfname
> & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set gender = '" &
> strgender & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set lname = '" & strlname
> & "' where id = '" & strid & "'")
> Thanks,
> Joey