name of the variable (228 row(s) affected)

hello,
after executing a insert in the query anlayser i get a info like (228 row(s)
affected)
How can i acces this value. Is there a internal variablename @.@....
I want to use this value for writing it in a logtable...
Is there any overview of the internal variables which can be used ...
thanks> How can i acces this value. Is there a internal variablename @.@....
Use @.@.ROWCOUNT. A good practice is to save the value in a local variable
for subsequent use.

> Is there any overview of the internal variables which can be used ...
See 'System Functions' in the Books Online for a list of these scalar
functions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:086F2B36-0CE9-460D-BA39-5D7633B4F286@.microsoft.com...
> hello,
> after executing a insert in the query anlayser i get a info like (228
> row(s)
> affected)
> How can i acces this value. Is there a internal variablename @.@....
> I want to use this value for writing it in a logtable...
> Is there any overview of the internal variables which can be used ...
> thanks|||SELECT @.@.ROWCOUNT;
Only available in the very next statement.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:086F2B36-0CE9-460D-BA39-5D7633B4F286@.microsoft.com...
> hello,
> after executing a insert in the query anlayser i get a info like (228
> row(s)
> affected)
> How can i acces this value. Is there a internal variablename @.@....
> I want to use this value for writing it in a logtable...
> Is there any overview of the internal variables which can be used ...
> thanks|||thanks
Xavier
"Dan Guzman" wrote:

> Use @.@.ROWCOUNT. A good practice is to save the value in a local variable
> for subsequent use.
>
> See 'System Functions' in the Books Online for a list of these scalar
> functions.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:086F2B36-0CE9-460D-BA39-5D7633B4F286@.microsoft.com...
>
>

N'' character in INSERT

Hi,

for example:

INSERT INTO TABLE (FIELD1, FIELD2) VALUES (N' value1', N' value2') WHERE ......

What does N' mean?

Thank you.

N identifies the given string is the UNICODE string. Which will be used when you store the values on NVARCHAR.

If you want to store other than English(or Latin based) in your table (globalization) then you have to use NVARCHAR as your string datatype the N is prefixed to identify the given string is Unicode char.

If you failed to give N prefix on Unicode string the SQL Server try to covert the value to ascii based string and the output printed with question marks

Select '日本語' Output

/*

-

?

*/

If you prefix N then string parsed perfectly,

Select N'日本語' Output

/*

Output

日本語

*/

|||Ok. Thank you very much.

N'' character in INSERT

Hi,

for example:

INSERT INTO TABLE (FIELD1, FIELD2) VALUES (N' value1', N' value2') WHERE ......

What does N' mean?

Thank you.

N identifies the given string is the UNICODE string. Which will be used when you store the values on NVARCHAR.

If you want to store other than English(or Latin based) in your table (globalization) then you have to use NVARCHAR as your string datatype the N is prefixed to identify the given string is Unicode char.

If you failed to give N prefix on Unicode string the SQL Server try to covert the value to ascii based string and the output printed with question marks

Select '日本語' Output

/*

-

?

*/

If you prefix N then string parsed perfectly,

Select N'日本語' Output

/*

Output

日本語

*/

|||Ok. Thank you very much.

N'' character in INSERT

Hi,

for example:

INSERT INTO TABLE (FIELD1, FIELD2) VALUES (N' value1', N' value2') WHERE ......

What does N' mean?

Thank you.

N identifies the given string is the UNICODE string. Which will be used when you store the values on NVARCHAR.

If you want to store other than English(or Latin based) in your table (globalization) then you have to use NVARCHAR as your string datatype the N is prefixed to identify the given string is Unicode char.

If you failed to give N prefix on Unicode string the SQL Server try to covert the value to ascii based string and the output printed with question marks

Select '日本語' Output

/*

-

?

*/

If you prefix N then string parsed perfectly,

Select N'日本語' Output

/*

Output

日本語

*/

|||Ok. Thank you very much.

MySQL Style INSERT INTO SET

MySQL allowed INSERT statements that looked like this:

INSERT INTO Table SET col1 = value1, col2 = value2, col3 = value3

Basically this closely matched the format of the UPDATE statements.

Here is why this was good: I could do something like this:

if (RecordExists ==false) { SQLquery ="INSERT INTO Table SET ";}else { SQLquery ="UPDATE Table SET ";}SQLquery +="col1 = value1, ";SQLquery +="col2 = value2, ";SQLquery +="col3 = value3, ";SQLquery +="col4 = value4 ";if (RecordExists ==true) { SQLquery +="WHERE id = " + ourID;}

If I had a good 50 columns then it means I can reuse a sizable chunk of code for both INSERT and UPDATE statements (since both use the same general format). Whereas if I have to use "INSERT INTO Table(columns) VALUES (values)" then I'm look at duplicating a lot of code.

Does MS-SQL support something similar to "INSERT INTO SET"? How are others dealing with this?

Thanks,

Gabe
-------

No. That is a proprietary MySQL syntax.

How do I deal with it? I don't. I don't use SQL concatenation techniques. They make the application/web apps insecure, difficult to port, slow, hard to maintain, and locks the application developer into doing database design (Which isn't good for team development). Not only that but it limits what you can do from the database design, and in multi-application environments where you have many applications using the same data tables, difficult to coordinate since the data-logic has now been moved to each application.

And lastly, for some applications it's simply isn't even an option. For example in many HIPAA applications, since you are allowing applications to retrieve data without the ability to forcably log the request, it's not even an option to do it that way.

|||

Motley, you answered my question and for that I thank you.

The code I was showing was simplified for forum purposes. (I just wanted to demonstrate the overall logic.) In a real situation I would make use of the AddParameter method. But this probably still qualifies as using SQL concactenation and in-line SQL, which I know many consider a sin.

In my experience application developers are almost always are up to their eye-balls in database design anyway...even when working with a DBA. I know I always am. Many database search queries I deal with are tied closely to application conditions and have a lot of processing logic involved in them. I could do that processing logic in C# (which is extremely feature rich, has great development tools available & benefits from access to my application objects) or I could do it in a SQL stored procedure which seems generally clunky & not nearly as robust. Using lots of stored procedures normally just means I have one more place I have to update something or one more person I have to involve for a fix.

I'm not going to pretend to be an expert on this and some of this just comes down to getting the job done. Many of the security advantages you speak about could probably be mimiced by simply having well defined user roles. From what I've read the performance advantages aren't as overwhelming as people seem to believe. I have read that many of the performance enhancements MS-SQL makes to queries are extended to in-line queries as well.

From an application standpoint having a well developed set of database interaction objects becomes a dream. I know a ton of people are going to disagree with me and I may even change my mind on this subject someday. For me personally, most of the stuff you speak of has just proven to be a giant PITA.

Gabe
============

|||What is a PITA?|||And why SQL concatenation techniques make the application/web apps insecure??|||

And why SQL concatenation techniques make the application/web apps insecure??

I would like to use the same style.

|||

Hey

SQL concatenation techniques will result ini sql injection.

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

You could take a look atSQL Injection for details.

MySQL Insert Conversion

Hi,
I have encoutnered the following MySQL statement and would like to
convert it to work with SQL Server 2005.
Is it possible to have two selects inserting into a row and if
affermative, how should it be done?
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Susan', 'Barker', '2002-09-12',
(select dept_id from department where name = 'Administration'),
'Vice President',
(select branch_id from branch where name = 'Headquarters'));
Thank You,
AlTry INSERT...SELECT:
INSERT INTO employee (
emp_id,
fname,
lname,
start_date,
dept_id,
title,
assigned_branch_id)
SELECT
NULL,
'Susan',
'Barker',
'2002-09-12',
(SELECT dept_id
FROM department
WHERE name = 'Administration'),
'Vice President',
(SELECT branch_id
FROM branch
WHERE name = 'Headquarters');
Hope this helps.
Dan Guzman
SQL Server MVP
<chribonn@.gmail.com> wrote in message
news:1138537484.578388.31890@.g43g2000cwa.googlegroups.com...
> Hi,
> I have encoutnered the following MySQL statement and would like to
> convert it to work with SQL Server 2005.
> Is it possible to have two selects inserting into a row and if
> affermative, how should it be done?
> insert into employee (emp_id, fname, lname, start_date,
> dept_id, title, assigned_branch_id)
> values (null, 'Susan', 'Barker', '2002-09-12',
> (select dept_id from department where name = 'Administration'),
> 'Vice President',
> (select branch_id from branch where name = 'Headquarters'));
> Thank You,
> Al
>|||insert employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
select null, 'Susan', 'Barker', '2002-09-12',
d.dept_id, 'Vice President', b.branch_id
from department d, branch b
where d.name = 'Administration'
and b.name = 'Headquarters'
"chribonn@.gmail.com" wrote:

> Hi,
> I have encoutnered the following MySQL statement and would like to
> convert it to work with SQL Server 2005.
> Is it possible to have two selects inserting into a row and if
> affermative, how should it be done?
> insert into employee (emp_id, fname, lname, start_date,
> dept_id, title, assigned_branch_id)
> values (null, 'Susan', 'Barker', '2002-09-12',
> (select dept_id from department where name = 'Administration'),
> 'Vice President',
> (select branch_id from branch where name = 'Headquarters'));
> Thank You,
> Al
>|||Thank you for your help.
Regards,
Al

Mysql and C++

hello
someone could tell me how could I insert SQL statement in a C++ code ?
If you know online documents easy to read about this subject, it will be
very welcome(in english or in french)..
Thanks[posted and mailed]

Borhen BOUAZIZ (bouazib4@.cti.ecp.fr) writes:
> someone could tell me how could I insert SQL statement in a C++ code ?
> If you know online documents easy to read about this subject, it will be
> very welcome(in english or in french)..

If you are using MySQL, you have posted to the wrong newsgroup. This
forum is for MS SQL Server.

If you are indeed using MS SQL Server, there are samples in Books Online.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

My SP works on 2005 bit not on 2000 it doesn''t step through my code.

Hi

I have a SP that works on SQL 2000 but not on 2005

It is just suppose to step through my code and insert values into tables where it finds the "ticked" values

here is apiece of my code. I hope it Helps.

Code Snippet

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

-My Code Stops here

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

Any help would be greatly appreciated

Kind Regards

Carel greaves

Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.

Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.

|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?

Without seeing the entire SP and sample data, there is no telling what is causing your problem.

|||

Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.

Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.

Here is all the code for my SP.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[csp_MemberUploader]

AS

DECLARE @.CurrentValue INT

DECLARE @.numValues INT

DECLARE @.MaxValue INT

SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)

FROM StageMemberUploading

WHILE @.numValues <> 0

BEGIN

DECLARE @.DateOfBirth DATETIME

DECLARE @.Male VARCHAR(50)

DECLARE @.Female VARCHAR(50)

DECLARE @.Single VARCHAR(50)

DECLARE @.Married VARCHAR(50)

DECLARE @.Divorced VARCHAR(50)

DECLARE @.Widowed VARCHAR(50)

DECLARE @.Height VARCHAR(50)

DECLARE @.Weight VARCHAR(50)

DECLARE @.Absentism VARCHAR(50)

DECLARE @.UsergroupID INT

DECLARE @.ClientID INT

SELECT @.DateOfBirth = [Date of birth],

@.Male = [Male],

@.Female = [Female],

@.Single = [Single],

@.Married = [Married],

@.Divorced = [Divorced],

@.Widowed = [Widowed],

@.Height = [Height],

@.Weight = [Weight],

@.Absentism = [Absentism],

@.UsergroupID = UsergroupID,

@.ClientID = ClientID

FROM StageMemberUploading

WHERE ID = @.numValues

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

IF @.Single = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 8)

END

IF @.Married = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 7)

END

IF @.Divorced = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 9)

END

IF @.Widowed = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 10)

END

IF @.Height = '1.22' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 3, 85)

END

IF @.Weight = 'x' BEGIN

INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 4, @.Weight)

END

IF @.Absentism = '1' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 158)

END

IF @.Absentism = '2' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 159)

END

IF @.Absentism = '3' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 160)

END

IF @.Absentism = '4' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 161)

END

SET @.numValues = @.numValues - 1

END

Kind Regards

Carel Greaves

|||

As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.

I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.

PRINT @.NumValues

PRINT @.CurrentValue

PRINT @.Absentism

....

|||My first guess is your values for @.Male, @.Female, etc are NULL.

Add some commands like this and see what you get:

IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'

My SP works on 2005 bit not on 2000 it doesn''t step through my code.

Hi

I have a SP that works on SQL 2000 but not on 2005

It is just suppose to step through my code and insert values into tables where it finds the "ticked" values

here is apiece of my code. I hope it Helps.

Code Snippet

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

-My Code Stops here

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

Any help would be greatly appreciated

Kind Regards

Carel greaves

Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.

Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.

|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?

Without seeing the entire SP and sample data, there is no telling what is causing your problem.

|||

Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.

Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.

Here is all the code for my SP.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[csp_MemberUploader]

AS

DECLARE @.CurrentValue INT

DECLARE @.numValues INT

DECLARE @.MaxValue INT

SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)

FROM StageMemberUploading

WHILE @.numValues <> 0

BEGIN

DECLARE @.DateOfBirth DATETIME

DECLARE @.Male VARCHAR(50)

DECLARE @.Female VARCHAR(50)

DECLARE @.Single VARCHAR(50)

DECLARE @.Married VARCHAR(50)

DECLARE @.Divorced VARCHAR(50)

DECLARE @.Widowed VARCHAR(50)

DECLARE @.Height VARCHAR(50)

DECLARE @.Weight VARCHAR(50)

DECLARE @.Absentism VARCHAR(50)

DECLARE @.UsergroupID INT

DECLARE @.ClientID INT

SELECT @.DateOfBirth = [Date of birth],

@.Male = [Male],

@.Female = [Female],

@.Single = [Single],

@.Married = [Married],

@.Divorced = [Divorced],

@.Widowed = [Widowed],

@.Height = [Height],

@.Weight = [Weight],

@.Absentism = [Absentism],

@.UsergroupID = UsergroupID,

@.ClientID = ClientID

FROM StageMemberUploading

WHERE ID = @.numValues

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

IF @.Single = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 8)

END

IF @.Married = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 7)

END

IF @.Divorced = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 9)

END

IF @.Widowed = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 10)

END

IF @.Height = '1.22' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 3, 85)

END

IF @.Weight = 'x' BEGIN

INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 4, @.Weight)

END

IF @.Absentism = '1' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 158)

END

IF @.Absentism = '2' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 159)

END

IF @.Absentism = '3' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 160)

END

IF @.Absentism = '4' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 161)

END

SET @.numValues = @.numValues - 1

END

Kind Regards

Carel Greaves

|||

As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.

I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.

PRINT @.NumValues

PRINT @.CurrentValue

PRINT @.Absentism

....

|||My first guess is your values for @.Male, @.Female, etc are NULL.

Add some commands like this and see what you get:

IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'

My SP works on 2005 bit not on 2000 it doesn''t step through my code.

Hi

I have a SP that works on SQL 2000 but not on 2005

It is just suppose to step through my code and insert values into tables where it finds the "ticked" values

here is apiece of my code. I hope it Helps.

Code Snippet

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

-My Code Stops here

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

Any help would be greatly appreciated

Kind Regards

Carel greaves

Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.

Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.

|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?

Without seeing the entire SP and sample data, there is no telling what is causing your problem.

|||

Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.

Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.

Here is all the code for my SP.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[csp_MemberUploader]

AS

DECLARE @.CurrentValue INT

DECLARE @.numValues INT

DECLARE @.MaxValue INT

SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)

FROM StageMemberUploading

WHILE @.numValues <> 0

BEGIN

DECLARE @.DateOfBirth DATETIME

DECLARE @.Male VARCHAR(50)

DECLARE @.Female VARCHAR(50)

DECLARE @.Single VARCHAR(50)

DECLARE @.Married VARCHAR(50)

DECLARE @.Divorced VARCHAR(50)

DECLARE @.Widowed VARCHAR(50)

DECLARE @.Height VARCHAR(50)

DECLARE @.Weight VARCHAR(50)

DECLARE @.Absentism VARCHAR(50)

DECLARE @.UsergroupID INT

DECLARE @.ClientID INT

SELECT @.DateOfBirth = [Date of birth],

@.Male = [Male],

@.Female = [Female],

@.Single = [Single],

@.Married = [Married],

@.Divorced = [Divorced],

@.Widowed = [Widowed],

@.Height = [Height],

@.Weight = [Weight],

@.Absentism = [Absentism],

@.UsergroupID = UsergroupID,

@.ClientID = ClientID

FROM StageMemberUploading

WHERE ID = @.numValues

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

IF @.Single = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 8)

END

IF @.Married = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 7)

END

IF @.Divorced = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 9)

END

IF @.Widowed = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 10)

END

IF @.Height = '1.22' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 3, 85)

END

IF @.Weight = 'x' BEGIN

INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 4, @.Weight)

END

IF @.Absentism = '1' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 158)

END

IF @.Absentism = '2' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 159)

END

IF @.Absentism = '3' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 160)

END

IF @.Absentism = '4' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 161)

END

SET @.numValues = @.numValues - 1

END

Kind Regards

Carel Greaves

|||

As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.

I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.

PRINT @.NumValues

PRINT @.CurrentValue

PRINT @.Absentism

....

|||My first guess is your values for @.Male, @.Female, etc are NULL.

Add some commands like this and see what you get:

IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'

My SP works on 2005 bit not on 2000 it doesn''t step through my code.

Hi

I have a SP that works on SQL 2000 but not on 2005

It is just suppose to step through my code and insert values into tables where it finds the "ticked" values

here is apiece of my code. I hope it Helps.

Code Snippet

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

-My Code Stops here

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

Any help would be greatly appreciated

Kind Regards

Carel greaves

Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.

Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.

|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?

Without seeing the entire SP and sample data, there is no telling what is causing your problem.

|||

Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.

Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.

Here is all the code for my SP.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[csp_MemberUploader]

AS

DECLARE @.CurrentValue INT

DECLARE @.numValues INT

DECLARE @.MaxValue INT

SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)

FROM StageMemberUploading

WHILE @.numValues <> 0

BEGIN

DECLARE @.DateOfBirth DATETIME

DECLARE @.Male VARCHAR(50)

DECLARE @.Female VARCHAR(50)

DECLARE @.Single VARCHAR(50)

DECLARE @.Married VARCHAR(50)

DECLARE @.Divorced VARCHAR(50)

DECLARE @.Widowed VARCHAR(50)

DECLARE @.Height VARCHAR(50)

DECLARE @.Weight VARCHAR(50)

DECLARE @.Absentism VARCHAR(50)

DECLARE @.UsergroupID INT

DECLARE @.ClientID INT

SELECT @.DateOfBirth = [Date of birth],

@.Male = [Male],

@.Female = [Female],

@.Single = [Single],

@.Married = [Married],

@.Divorced = [Divorced],

@.Widowed = [Widowed],

@.Height = [Height],

@.Weight = [Weight],

@.Absentism = [Absentism],

@.UsergroupID = UsergroupID,

@.ClientID = ClientID

FROM StageMemberUploading

WHERE ID = @.numValues

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

IF @.Single = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 8)

END

IF @.Married = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 7)

END

IF @.Divorced = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 9)

END

IF @.Widowed = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 10)

END

IF @.Height = '1.22' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 3, 85)

END

IF @.Weight = 'x' BEGIN

INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 4, @.Weight)

END

IF @.Absentism = '1' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 158)

END

IF @.Absentism = '2' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 159)

END

IF @.Absentism = '3' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 160)

END

IF @.Absentism = '4' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 161)

END

SET @.numValues = @.numValues - 1

END

Kind Regards

Carel Greaves

|||

As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.

I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.

PRINT @.NumValues

PRINT @.CurrentValue

PRINT @.Absentism

....

|||My first guess is your values for @.Male, @.Female, etc are NULL.

Add some commands like this and see what you get:

IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'

My SP works on 2005 bit not on 2000 it doesn''t step through my code.

Hi

I have a SP that works on SQL 2000 but not on 2005

It is just suppose to step through my code and insert values into tables where it finds the "ticked" values

here is apiece of my code. I hope it Helps.

Code Snippet

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

-My Code Stops here

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

Any help would be greatly appreciated

Kind Regards

Carel greaves

Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.

Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.

|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?

Without seeing the entire SP and sample data, there is no telling what is causing your problem.

|||

Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.

Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.

Here is all the code for my SP.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[csp_MemberUploader]

AS

DECLARE @.CurrentValue INT

DECLARE @.numValues INT

DECLARE @.MaxValue INT

SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)

FROM StageMemberUploading

WHILE @.numValues <> 0

BEGIN

DECLARE @.DateOfBirth DATETIME

DECLARE @.Male VARCHAR(50)

DECLARE @.Female VARCHAR(50)

DECLARE @.Single VARCHAR(50)

DECLARE @.Married VARCHAR(50)

DECLARE @.Divorced VARCHAR(50)

DECLARE @.Widowed VARCHAR(50)

DECLARE @.Height VARCHAR(50)

DECLARE @.Weight VARCHAR(50)

DECLARE @.Absentism VARCHAR(50)

DECLARE @.UsergroupID INT

DECLARE @.ClientID INT

SELECT @.DateOfBirth = [Date of birth],

@.Male = [Male],

@.Female = [Female],

@.Single = [Single],

@.Married = [Married],

@.Divorced = [Divorced],

@.Widowed = [Widowed],

@.Height = [Height],

@.Weight = [Weight],

@.Absentism = [Absentism],

@.UsergroupID = UsergroupID,

@.ClientID = ClientID

FROM StageMemberUploading

WHERE ID = @.numValues

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

IF @.Single = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 8)

END

IF @.Married = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 7)

END

IF @.Divorced = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 9)

END

IF @.Widowed = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 10)

END

IF @.Height = '1.22' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 3, 85)

END

IF @.Weight = 'x' BEGIN

INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 4, @.Weight)

END

IF @.Absentism = '1' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 158)

END

IF @.Absentism = '2' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 159)

END

IF @.Absentism = '3' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 160)

END

IF @.Absentism = '4' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 161)

END

SET @.numValues = @.numValues - 1

END

Kind Regards

Carel Greaves

|||

As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.

I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.

PRINT @.NumValues

PRINT @.CurrentValue

PRINT @.Absentism

....

|||My first guess is your values for @.Male, @.Female, etc are NULL.

Add some commands like this and see what you get:

IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'

My SP works on 2005 bit not on 2000 it doesn''t step through my code.

Hi

I have a SP that works on SQL 2000 but not on 2005

It is just suppose to step through my code and insert values into tables where it finds the "ticked" values

here is apiece of my code. I hope it Helps.

Code Snippet

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

-My Code Stops here

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

Any help would be greatly appreciated

Kind Regards

Carel greaves

Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.

Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.

|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?

Without seeing the entire SP and sample data, there is no telling what is causing your problem.

|||

Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.

Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.

Here is all the code for my SP.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[csp_MemberUploader]

AS

DECLARE @.CurrentValue INT

DECLARE @.numValues INT

DECLARE @.MaxValue INT

SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)

FROM StageMemberUploading

WHILE @.numValues <> 0

BEGIN

DECLARE @.DateOfBirth DATETIME

DECLARE @.Male VARCHAR(50)

DECLARE @.Female VARCHAR(50)

DECLARE @.Single VARCHAR(50)

DECLARE @.Married VARCHAR(50)

DECLARE @.Divorced VARCHAR(50)

DECLARE @.Widowed VARCHAR(50)

DECLARE @.Height VARCHAR(50)

DECLARE @.Weight VARCHAR(50)

DECLARE @.Absentism VARCHAR(50)

DECLARE @.UsergroupID INT

DECLARE @.ClientID INT

SELECT @.DateOfBirth = [Date of birth],

@.Male = [Male],

@.Female = [Female],

@.Single = [Single],

@.Married = [Married],

@.Divorced = [Divorced],

@.Widowed = [Widowed],

@.Height = [Height],

@.Weight = [Weight],

@.Absentism = [Absentism],

@.UsergroupID = UsergroupID,

@.ClientID = ClientID

FROM StageMemberUploading

WHERE ID = @.numValues

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

IF @.Single = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 8)

END

IF @.Married = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 7)

END

IF @.Divorced = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 9)

END

IF @.Widowed = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 10)

END

IF @.Height = '1.22' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 3, 85)

END

IF @.Weight = 'x' BEGIN

INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 4, @.Weight)

END

IF @.Absentism = '1' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 158)

END

IF @.Absentism = '2' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 159)

END

IF @.Absentism = '3' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 160)

END

IF @.Absentism = '4' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 161)

END

SET @.numValues = @.numValues - 1

END

Kind Regards

Carel Greaves

|||

As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.

I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.

PRINT @.NumValues

PRINT @.CurrentValue

PRINT @.Absentism

....

|||My first guess is your values for @.Male, @.Female, etc are NULL.

Add some commands like this and see what you get:

IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'

My INSERT statements works... 8/10 times

I'm using the following code to add some data to a table:
Dim rand As Random = New Random
Dim num As Int32 = rand.Next(10000000)
Dim strConn as string = "......"
Dim sql as string = "INSERT INTO tblitemid (itemid, userid, datetime, supplier, comment, commenttype, uniqueid) VALUES ('" & label1.Text & "', '" & user.identity.name & "', '"& System.DateTime.Now & "','3763' ,'" & textbox1.text & "' , 'C' ,'" & num & "')"
Dim conn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(sql, conn)
Try
conn.Open()
Catch SQLExp as SQLException
Response.Write ("An SQL Server Error Occurred: " & e.toString())
Finally
cmd.ExecuteNonQuery
conn.Close()
End Try

As far as I can tell the code works fine. But for some odd reason I click the button, the code execute and the page closes as it should, but the data is never inserted into the database. I cant really seem to pick up on any paterns for why this would be happening. As a rough guess I'd say it doesnt insert the data 1 out of every 5 times or so it seems. Anyone every have any experience with this? Any comments would be helpful, cuz I'm at a loss.
If youd like to see more code let me know...
Thanks,
Scottscott, cmd.ExecuteNonQuery() should be in the try, not finally. because if you're getting an sql error, it'll error again when the cmd tries to execute w/o an open connection.

that's one. try stepping through it. Furthermore, don't use close, use conn.Dispose(), and cmd.Dispose(), and equal them to null.

Also, are you sure it's going through that part of the code?|||Are you sure all the vars have the correct data in them? I've not used the .net Rand function yet, should you be seeding it or you may get repeated numbers there too easily?|||I ended up getting it working, thanks guys. Turns out it was some javascript frather down in the code that was f'ing it all up.