Name of running procedure

Is it possible in 2000 (or 2005) to get the name of the currently running
procedure, from inside the procedure?
I would like to write code to log procedure events, without having to change
the procedure name as I copy it from one procedure to another.
Thanks,
JayOn Tue, 18 Sep 2007 10:59:51 -0700, "Jay" <nospan@.nospam.org> wrote:
>Is it possible in 2000 (or 2005) to get the name of the currently running
>procedure, from inside the procedure?
Yes.
CREATE PROCEDURE testprocedure AS
SELECT @.@.PROCID AS 'ProcID',
OBJECT_NAME(@.@.PROCID) AS 'Procedure'
GO
EXEC testprocedure
GO
Roy Harvey
Beacon Falls, CT|||Thanks Roy.
"Roy Harvey (MVP)" <roy_harvey@.snet.net> wrote in message
news:t260f39s14nj0ph8k4iofg7i6furlusfi7@.4ax.com...
> On Tue, 18 Sep 2007 10:59:51 -0700, "Jay" <nospan@.nospam.org> wrote:
>>Is it possible in 2000 (or 2005) to get the name of the currently running
>>procedure, from inside the procedure?
> Yes.
> CREATE PROCEDURE testprocedure AS
> SELECT @.@.PROCID AS 'ProcID',
> OBJECT_NAME(@.@.PROCID) AS 'Procedure'
> GO
> EXEC testprocedure
> GO
> Roy Harvey
> Beacon Falls, CT

Name cannot begin with the '>' character, hexadecimal value 0x3E

Hi,

This is my first attempt at SSRS, I've added code section to the xml file. But when I use <> for not equal to, it is giving me this error. What should I do? The code is as posted here.

Thanks,

Debi

***************************************************************************************

<Code>
Dim PrevId as Object
Dim a_Count As Double
Dim b_Count As Double
Dim c_Count As Double
Dim d_Count As Double
Dim e_Count As Double
Dim Total As Double = 0
Function SumMPCount(ByVal CurrID As Object,ByVal code As Object, ByVal NewCount As Object) As Double

If(CurrID is Nothing or code is Nothing or NewCount is Nothing)Then
Exit Function
End if
If PrevId Is Nothing Then
PrevId = CurrId
End if
If CurrID <> PrevID Then
a_Count = 0
b_Count = 0
c_Count = 0
d_Count = 0
e_Count = 0
Total = 0
End if
Select Case mp_Code
Case "a"
a_Count = a_Count + NewCount
SumMPCount = a_Count
case "b"
b_Count = b_Count + NewCount
SumMPCount = b_Count
case "c"
c_Count = c_Count + NewCount
SumMPCount = c_Count
case "d"
d_Count = d_Count + NewCount
SumMPCount = d_Count
case "e"
Total = a_Count + b_Count + c_Count + d_Count + e_Count
SumMPCount = Total
case else
e_Count = e_Count + NewCount
SumMPCount = e_Count
End Select

PrevId = CurrId
End Function
</Code>

It looks like you directly copy&pasted the code snippet into the RDL(XML) file and therefore the <> is not encoded an is interpreted as invalid xml element.

You have two options - either copy&paste the code snippet into the report designer custom code window (and report designer will automatically encode the characters correctly), or replace the <> in the RDL file with &lt;&gt; (which is the encoded representation).

-- Robert

|||

Thanks for the reply. Where is the custom code window? When I right click on the report and select properties, it just shows me the usual Properties tab on the RHS which shows background color, image, etc..

I actually worked around it using Object variables and using "Is" instead of <> .

|||I think he might have meant
right click on the report.rdl -> Prooperties -> Code tab -> paste theresql

n00b Question Re: MSDE SQL Server Install

I am trying to install some code on my website which is called EasyDisk. It
creates a forum, like any forum you see anywhere. Anyways it requires an SQL
Server, so I acquired MSDE SQL Server 2000. I created an instance of it, and
can see the folder on my C drive. The instance is called
MYFIRSTDBMSSQL$MYFIRSTDB oddly enough since I guess I didn't understand the
instruction for naming an instance. In any case, the forum software requires
I know 1. The name of my SQL Server, 2. My username 3. My password and 4. My
database name.
So, best I can tell my db name is my instance name, and my username and
password are what I use to log onto Windows XP. How far off am I? ... and I
have no idea what my sql server name is. How do I find out? The Books online
is no help whatsoever in this regard.
Thanks,
Jim
hi Jim,
"newgenre" <newgenre@.mindspring.com> ha scritto nel messaggio
news:%xchc.3922$e4.2441@.newsread2.news.pas.earthli nk.net...
>.....
> I know 1. The name of my SQL Server, 2. My username 3. My password and 4.
My
> database name.
> So, best I can tell my db name is my instance name, and my username and
> password are what I use to log onto Windows XP. How far off am I? ... and
I
> have no idea what my sql server name is. How do I find out? The Books
online
> is no help whatsoever in this regard.
your instance name shoul'd be MYFIRSTDB (and you can verify this opening
your service management applet, in the part right to the $ sign)... the
service manager in your tray bar area shoul'd provide this information
too...
so the full instance name is YourComputerName\MYFIRSTDB
your db name for the connection string is not the instance name, but the
database you want to connect to..
as regard your credential, it depends on the authentication mode you want to
connect to SQL Server with..
SQL Server accepst 2 kinds of authentication mode:
trusted (WinNT) authentication, where you are not required to provide user's
information;
SQL Server authentication, where you have to provide them in the form of
"user id=username;password=yourStrongPwd;" as indicated in
http://www.connectionstrings.com/ ...
you can find more about authentication modes beginning at
http://msdn.microsoft.com/library/de...setup_6p9v.asp
you can find which kind of authentication your MSDE instance supports by
inspecting the registry at the
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstanceName\MSSQLServer\LoginMode key
a value of 0 or 2 specifies Mixed mode (both modes), while a value of 1
spefies truste authentication only.
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

N' prefix required or not?

Need to provide international support for the product. Use
SqlServer 2000 for repository; DataDirect JDBC driver and
Java code for the application.
Using nvarchar datatypes for columns. Data entry/retrieval
will be through Java code. When send SQL commands through
Java/JDBC; do we need to prefix the constants with the N'
prefix example - INSERT INTO abc VALUES (N'xyz') or will
INSERT INTO abc VALUES ('xyz') store the data in unicode.
If the datatype supports unicode chars do we still need to
use the N'prefix? Also assuming data is stored in unicode;
when using in where clause while selecting will we have to
use SELECT * from abc where f1 =N'xyx' for it to be
retrieved? FYI, All database access is through Java/JDBC
which both claim to provide full international/unicode
support.
Thanks,If you are sending in hard-coded strings, then you will need to prefix them
with N to indicate that the string is Unicode. Otherwise, unicode strings
will be stored as a series of junk characters in SQL Server.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"AB" <anonymous@.discussions.microsoft.com> wrote in message
news:900901c3e9f5$a1fc1f20$a501280a@.phx.gbl...
> Need to provide international support for the product. Use
> SqlServer 2000 for repository; DataDirect JDBC driver and
> Java code for the application.
> Using nvarchar datatypes for columns. Data entry/retrieval
> will be through Java code. When send SQL commands through
> Java/JDBC; do we need to prefix the constants with the N'
> prefix example - INSERT INTO abc VALUES (N'xyz') or will
> INSERT INTO abc VALUES ('xyz') store the data in unicode.
> If the datatype supports unicode chars do we still need to
> use the N'prefix? Also assuming data is stored in unicode;
> when using in where clause while selecting will we have to
> use SELECT * from abc where f1 =N'xyx' for it to be
> retrieved? FYI, All database access is through Java/JDBC
> which both claim to provide full international/unicode
> support.
> Thanks,

N' prefix required or not?

Need to provide international support for the product. Use
SqlServer 2000 for repository; DataDirect JDBC driver and
Java code for the application.
Using nvarchar datatypes for columns. Data entry/retrieval
will be through Java code. When send SQL commands through
Java/JDBC; do we need to prefix the constants with the N'
prefix example - INSERT INTO abc VALUES (N'xyz') or will
INSERT INTO abc VALUES ('xyz') store the data in unicode.
If the datatype supports unicode chars do we still need to
use the N'prefix? Also assuming data is stored in unicode;
when using in where clause while selecting will we have to
use SELECT * from abc where f1 =N'xyx' for it to be
retrieved? FYI, All database access is through Java/JDBC
which both claim to provide full international/unicode
support.
Thanks,If you are sending in hard-coded strings, then you will need to prefix them
with N to indicate that the string is Unicode. Otherwise, unicode strings
will be stored as a series of junk characters in SQL Server.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"AB" <anonymous@.discussions.microsoft.com> wrote in message
news:900901c3e9f5$a1fc1f20$a501280a@.phx.gbl...

quote:

---

> Need to provide international support for the product. Use
> SqlServer 2000 for repository; DataDirect JDBC driver and
> Java code for the application.
> Using nvarchar datatypes for columns. Data entry/retrieval
> will be through Java code. When send SQL commands through
> Java/JDBC; do we need to prefix the constants with the N'
> prefix example - INSERT INTO abc VALUES (N'xyz') or will
> INSERT INTO abc VALUES ('xyz') store the data in unicode.
> If the datatype supports unicode chars do we still need to
> use the N'prefix? Also assuming data is stored in unicode;
> when using in where clause while selecting will we have to
> use SELECT * from abc where f1 =N'xyx' for it to be
> retrieved? FYI, All database access is through Java/JDBC
> which both claim to provide full international/unicode
> support.
> Thanks,

---

sql

MySQL to MsSQL 2005

Hi!

How can i convert this code to work with MsSQL 2005?

/Tomas

PartialClass skaalbInherits System.Web.UI.PageProtected Sub Button1_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Button1.ClickDim strConnectionStringAs String Dim strQueryAs String Dim MyConnectionAs OdbcConnectionDim myCommandAs OdbcCommandDim pathAs String = Server.MapPath("~/album") &"/"Dim albNameAs String = Trim(Replace(txtAlbum.Text,"'", "''")) Dim folderName As String = Trim(Replace(txtAlbum.Text, "'", "''")) folderName = Replace(folderName," ","_")Try If Not My.Computer.FileSystem.DirectoryExists(path & folderName)Then My.Computer.FileSystem.CreateDirectory(path & folderName) labelStatus.Text ="Folder <b>" & folderName &"</b> created!"Dim BeskrivningAs String = Trim(Replace(txtBeskrivning.Text,"'", "''")) strConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxxxxxxx; DATABASE=xxxxxxx; UID=xxxxxxxx; PASSWORD=xxxxxxxxx; OPTION=3" MyConnection = New OdbcConnection(strConnectionString) MyConnection.Open() strQuery = "INSERT INTO tbl_albumet(alb_Namn, alb_Beskrivning, alb_Mapp) VALUES (?, ?, ?)" myCommand = New OdbcCommand(strQuery, MyConnection) myCommand.Parameters.AddWithValue("?", albName) myCommand.Parameters.AddWithValue("?", Beskrivning) myCommand.Parameters.AddWithValue("?", folderName) myCommand.ExecuteNonQuery() MyConnection.Close() Else labelStatus.Text = "Folder excist, pick another name!" End If Catch ex As Exception labelStatus.Text = "Unable to create folder!"End Try End SubEnd Class

Just useDriver={SQL Native Client}; in the connectionstring

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 Query is broken and I don't know how to fix it...

I am trying to set up a filtered paging ObjectDataSource for a gridvoew control.

My code works, my query doesn't...

This is OK: Get all rows of data:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM CountryCodes "
Returns all records as expected

This is OK: Getting rows of data with no Filter:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes ) As TempRowTable WHERE Row >= 0 AND Row <= 10"
Returns expected data

This is OK: Making sure my WHERE filter works:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM CountryCodes WHERE country_name LIKE '%u%' "
Returns 85 records

This is BROKEN!!!: I want to return 10 rows of filtered data:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes ) As TempRowTable WHERE ( Row >= 0 AND Row <= 10 ) AND ( country_name LIKE '%u%' ) "
It Returns 0 records!!!

What am I doing wrong in the last query?
Or even better how do I fix it?

Thanks,
Roger

Why don't you just do SELECT TOP 10 UCO,...|||

if you don't want use top 10.

The problem is that your where clause needs to be in the derived table.

SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes WHERE country_name LIKE '%u%') As TempRowTable WHERE ( Row >= 0 AND Row <= 10 )

|||

You will have to post a repro script demonstrating the problem. There is nothing wrong with your query. And it is more efficient using the TOP clause in this case to get the top 10 rows instead of using ROW_NUMBER and then filtering on it.

|||Roger,

Your query is most likely working as it should - it should

return 0 rows, given your data, I believe, since you asked

for those rows with country_name like '%u%' that appear in

the first 10 rows of CountryCodes (ordered by country_name).

Apparently none of the first 10 rows have country_name values

like '%u%'.

If you wanted the first 10 rows with country_name like '%u%',

you can either change the problem Ryan noticed (shown here

using a similar query from the AdventureWorks sample database).

select

FirstName, LastName

from (

select

FirstName, LastName,

row_number() over (order by ContactID) as row

from Person.Contact

where LastName like '%x%'

) as C

where row > 0 and row <= 10

go

or use Ryan's and Anith's suggestion of TOP:

select top (10)

FirstName, LastName

from Person.Contact

where LastName like '%x%'

order by ContactID

Note that a query like yours will return no rows here

as well:

select

FirstName, LastName

from (

select

FirstName, LastName,

row_number() over (order by ContactID) as row

from Person.Contact

) as C

where LastName like '%x%'

and row > 0 and row <= 10

go

Steve Kass

Drew University

rogerw@.discussions.microsoft.com wrote:

> I am trying to set up a filtered paging ObjectDataSource for a gridvoew

> control.

>

> My code works, my query doesn't...

>

> This is OK: Get all rows of data:

> "SELECT UCO, country_code, country_name, enabled, concurrency FROM

> CountryCodes "

> Returns all records as expected

>

> This is OK: Getting rows of data with no Filter:

> "SELECT UCO, country_code, country_name, enabled, concurrency FROM

> (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO,

> country_code, country_name, enabled, concurrency FROM CountryCodes ) As

> TempRowTable WHERE Row >= 0 AND Row <= 10"

> Returns expected data

>

> This is OK: Making sure my WHERE filter works:

> "SELECT UCO, country_code, country_name, enabled, concurrency FROM

> CountryCodes WHERE country_name LIKE '%u%' "

> Returns 85 records

>

> This is BROKEN!!!: I want to return 10 rows of filtered data:

> "SELECT UCO, country_code, country_name, enabled, concurrency FROM

> (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO,

> country_code, country_name, enabled, concurrency FROM CountryCodes ) As

> TempRowTable WHERE ( Row >= 0 AND Row <= 10 ) AND ( country_name LIKE

> '%u%' ) "

> It Returns 0 records!!!

>

> What am I doing wrong in the last query?

> Or even better how do I fix it?

>

> Thanks,

> Roger

>

>|||

Duh - I thought I had tried that. Must be a clear case of tunnel vision.

Thanks for the help,
Roger

my procedure not returning any value

hi evry one
is there any problem with my code it dos'nt returen any value
please help
CREATE PROCEDURE [dbo].[mMaxId]
AS
set nocount on
declare @.Id bigint
declare @.mID bigint
select @.id = max(TransId) from tbltransaction
if (@.id is null)
begin
set @.mid = 1
end
if ( @.id is not null)
begin
set @.mid = @.id +1
end
return
GOWhat is it supposed to return ? Mid , ID ? Then you have to specify an outpu
t
paramter to catch the value from the calling procedure, if you have a
resultset you can use the resultset by inserting that in temp table while
executing
Insert into #SomeTable
EXEC(Someprocedure)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"mowafy" wrote:

> hi evry one
> is there any problem with my code it dos'nt returen any value
> please help
> CREATE PROCEDURE [dbo].[mMaxId]
> AS
> set nocount on
> declare @.Id bigint
> declare @.mID bigint
> select @.id = max(TransId) from tbltransaction
> if (@.id is null)
> begin
> set @.mid = 1
> end
> if ( @.id is not null)
> begin
> set @.mid = @.id +1
> end
> return
> GO
>|||Hi
Although you can use a select statement to return @.mid as a result set,
depending on what your requirements are, it may be more efficient to use an
output parameter
CREATE PROCEDURE [dbo].[mMaxId] @.mID bigint OUTPUT
AS
set nocount on
SET @.mid =ISNULL(SELECT max(TransId) from dbo.tbltransaction ),0) + 1
return
GO
DECLARE @.nextId bigint
EXEC [dbo].[mMaxId] @.nextId OUTPUT
...
If you are only wanting this number to allocate unique numbers to the
transaction id, then an identity column may be an easier way to implement
this functionality (although they may not be contiguous). You will need to
make sure that you don't write the code in such a way that two processes can
use the same transaction id.
John
"mowafy" wrote:

> hi evry one
> is there any problem with my code it dos'nt returen any value
> please help
> CREATE PROCEDURE [dbo].[mMaxId]
> AS
> set nocount on
> declare @.Id bigint
> declare @.mID bigint
> select @.id = max(TransId) from tbltransaction
> if (@.id is null)
> begin
> set @.mid = 1
> end
> if ( @.id is not null)
> begin
> set @.mid = @.id +1
> end
> return
> GO
>

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.