Binary Param in RS

Hello,

When I run this VBScript recordset I get a ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

 error;

<%
Dim RSPassReq__MMColParam
RSPassReq__MMColParam = NULL
If (Request("ID") <> "") Then 
  RSPassReq__MMColParam = Request("ID")
End If

Dim RSPassReq__MMColParam2
RSPassReq__MMColParam2 = NULL
If (Request("auth") <> "") Then 
  RSPassReq__MMColParam2 = Request("auth")
End If

Dim RSPassReq
Dim RSPassReq_cmd
Dim RSPassReq_numRows

Set RSPassReq_cmd = Server.CreateObject ("ADODB.Command")
RSPassReq_cmd.ActiveConnection = MM_jster_STRING
RSPassReq_cmd.CommandText = "SELECT ID, Username, Password, FirstName FROM dbo.Candidate WHERE ID = ? AND Password = ?" 
RSPassReq_cmd.Prepared = true
RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param1", 5, 1, -1, RSPassReq__MMColParam) ' adDouble
RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param2", 200, 1, 255, RSPassReq__MMColParam2) ' adVarChar

Set RSPassReq = RSPassReq_cmd.Execute
RSPassReq_numRows = 0
%>

Open in new window


MMColParam2 is binary..

Any thoughts?
garethtnashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
You're defining the parameter as adVarChar when it should be adBinary, which had a numerical value of 128
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Had = has
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
So it should be

RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param2", 128, 1, 255, RSPassReq__MMColParam2)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

garethtnashAuthor Commented:
Hello Big Monty

No joy I'm afraid;

<%
Dim RSPassReq__MMColParam
RSPassReq__MMColParam = NULL
If (Request("ID") <> "") Then
  RSPassReq__MMColParam = Request("ID")
End If

Dim RSPassReq__MMColParam2
RSPassReq__MMColParam2 = NULL
If (Request("auth") <> "") Then
  RSPassReq__MMColParam2 = Request("auth")
End If

Dim RSPassReq
Dim RSPassReq_cmd
Dim RSPassReq_numRows

Set RSPassReq_cmd = Server.CreateObject ("ADODB.Command")
RSPassReq_cmd.ActiveConnection = MM_jster_STRING
RSPassReq_cmd.CommandText = "SELECT ID, Username, Password, FirstName FROM dbo.Candidate WHERE ID = ? AND Password = ?"
RSPassReq_cmd.Prepared = true
RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param1", 5, 1, -1, RSPassReq__MMColParam) ' adDouble
RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param2", 128, 1, 255, RSPassReq__MMColParam2) ' adVarChar

Set RSPassReq = RSPassReq_cmd.Execute
RSPassReq_numRows = 0
%>

Still getting the same error!

Thanks
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I'm going to be online off and on for the rest of the day, just a heads up

Make sure you have a value being passed from Request("auth"), it should be a 1 or 0, or a true / false value. If you're def getting a value back, then get explicitly converting it to a boolean value before setting the parameter by using the CBool()  function
0
garethtnashAuthor Commented:
Hey Big Monty,

I think I can see what the issue is, the binary is being sent in a URL or form post..

And not working... I'm guessing binary can't be sent that way...

Any thoughts?

Thanks
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
What is that value being posted via url or form post?
0
garethtnashAuthor Commented:
Hi,

ID=1&auth=0x3904B00146BE24C3250733FD110423B54B4D1E14

it is the auth part.

It is a password reset function that I've built.

Thanks
0
garethtnashAuthor Commented:
Hello,

So it is a VBScript app, two pages and I'm trying to use a script posted here -

http://www.ericphelps.com/q193998/

The first script, takes the form parameters, collects the data from the sql database returns them to an array and then CDO posts a message -

<%
' this is the script from that site
Function ByteArray2Text(varByteArray)
'Convert byte array into a string with ADODB.Recordset
Dim rs
Const adLongVarChar = 201
    Set rs = CreateObject("ADODB.Recordset")
    rs.Fields.Append "temp", adLongVarChar, LenB(varByteArray)
    rs.Open
    rs.AddNew
    rs("temp").AppendChunk varByteArray
    rs.Update
    ByteArray2Text = rs("temp")
    rs.Close
    Set rs = Nothing
End Function
' end of the script from site

if Request("passwordresetinput") <> "" then

Dim CMDPasswordRequest__Username
CMDPasswordRequest__Username = NULL
if(Request("passwordresetinput") <> "") then CMDPasswordRequest__Username = Trim(Request("passwordresetinput"))

set CMDPasswordRequest = Server.CreateObject("ADODB.Command")
CMDPasswordRequest.ActiveConnection = MM_jster_STRING
CMDPasswordRequest.CommandText = "dbo.JsterCandidatePasswordRequest"
CMDPasswordRequest.CommandType = 4
CMDPasswordRequest.CommandTimeout = 0
CMDPasswordRequest.Prepared = true
CMDPasswordRequest.Parameters.Append CMDPasswordRequest.CreateParameter("@RETURN_VALUE", 3, 4)
CMDPasswordRequest.Parameters.Append CMDPasswordRequest.CreateParameter("@Username", 200, 1,350,CMDPasswordRequest__Username)
set PasswordRS = CMDPasswordRequest.Execute
PasswordRS_numRows = 0

	If not PasswordRS.eof then
		arrRegister = PasswordRS.GetRows()
	End if

	PasswordRS.Close() 'Clean Up
	Set PasswordRS = Nothing 'Clean Up
	Set CMDPasswordRequest = Nothing 'Clean Up

	Dim result
	
	If IsArray(arrRegister) Then 'second if
		dim candlogin 
		For candlogin = 0 to ubound(arrRegister, 2)

			if arrRegister(0,candlogin) = 1 then
				result = 1
			Elseif arrRegister(0,candlogin) = 2 then
	        	Set myMailApplication=CreateObject("CDO.Message")
				URLShort="JobsterUK - Password Reset Link"
				URLLong="/coms/js/passwordrequest.asp?ID=" & (arrRegister(1,candlogin)) & "&auth=" & ByteArray2Text(arrRegister(2,candlogin))
				ReplyMail="**********"
				ToMail="**********"
				FromMail="""**********"" <**********>"
		        myMailApplication.Subject= URLShort
		        myMailApplication.From= FromMail
		        myMailApplication.To= ToMail
		        myMailApplication.ReplyTo= ReplyMail
		        myMailApplication.CreateMHTMLBody URLLong
		        myMailApplication.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1
		        myMailApplication.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") = "c:\inetpub\mailroot\pickup"
		        myMailApplication.Configuration.Fields.Update
		        myMailApplication.Configuration.Fields.Update
		        myMailApplication.Send
		        set myMailApplication=nothing
				result = 2
			end if
		Next
	end If
Response.Write(result)
end if
%>

Open in new window


The second script is the HTML email script with a recordset --

<%
Function ReadByteArray(strFileName)
Const adTypeBinary = 1
Dim bin
    Set bin = CreateObject("ADODB.Stream")
    bin.Type = adTypeBinary
    bin.Open
    bin.LoadFromFile strFileName
    ReadByteArray = bin.Read
End Function

Dim RSPassReq__MMColParam
RSPassReq__MMColParam = NULL
If (Request("ID") <> "") Then 
  RSPassReq__MMColParam = Request("ID")
End If

Dim RSPassReq__MMColParam2
RSPassReq__MMColParam2 = NULL
If (Request("auth") <> "") Then 
  RSPassReq__MMColParam2 = ReadByteArray(Request("auth"))
End If

Dim RSPassReq
Dim RSPassReq_cmd
Dim RSPassReq_numRows

Set RSPassReq_cmd = Server.CreateObject ("ADODB.Command")
RSPassReq_cmd.ActiveConnection = MM_jster_STRING
RSPassReq_cmd.CommandText = "SELECT ID, Username, Password, FirstName FROM dbo.Candidate WHERE ID = ? AND Password = ?" 
RSPassReq_cmd.Prepared = true
RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param1", 5, 1, -1, RSPassReq__MMColParam) ' adDouble
RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param2", 128, 1, 255, RSPassReq__MMColParam2) ' adVarChar

Set RSPassReq = RSPassReq_cmd.Execute
RSPassReq_numRows = 0
%>
<!doctype html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Password Request Reset</title>
<style type="text/css">
	.ReadMsgBody {width: 100%; background-color: #0D2235;}
	.ExternalClass {width: 100%; background-color: #0D2235;}
	body	 {width: 100%; background-color: #0D2235; margin:0; padding:0; -webkit-font-smoothing: antialiased;font-family: Arial, Helvetica, sans-serif}
	table {border-collapse: collapse;}
	
	@media only screen and (max-width: 640px)  {
					body[yahoo] .deviceWidth {width:440px!important; padding:0;}	
					body[yahoo] .center {text-align: center!important;}	 
			}
			
	@media only screen and (max-width: 479px) {
					body[yahoo] .deviceWidth {width:280px!important; padding:0;}	
					body[yahoo] .center {text-align: center!important;}	 
			}

</style>
</head>

<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" yahoo="fix" style="font-family: Arial, Helvetica, sans-serif; background-color:#0D2235">
<!-- new wrapper -->
<table width="100%" border="0" cellpadding="0" cellspacing="0" align="center">
	<tr>
		<td width="100%" valign="top" bgcolor="#0D2235">
<table width="100%" border="0" cellpadding="0" cellspacing="0" align="center">
	<tr>
		<td width="100%" valign="top" bgcolor="#344B5F">
			<table width="100%" border="0" cellpadding="0" cellspacing="0" align="center">
				<tr>
					<td bgcolor="#344B5F" style="padding:10px 0">
                        <table width="580" border="0" cellpadding="0" cellspacing="0" align="center" class="deviceWidth">
				<tr>
					<td width="100%" bgcolor="">

                            <!-- Logo -->
                            <table border="0" cellpadding="0" cellspacing="0" align="left" class="deviceWidth">
                                <tr>
                                    <td style="padding:10px 10px 10px 0px" class="center">
                                        <a href="http://www.**********.co.uk"><img src="http://www.**********.co.uk/images/coms_images/**********.png" alt="**********" width="160" height="50" border="0" title="**********"/></a>                                    </td>
                              </tr>
                            </table><!-- End Logo -->
                            
                            <!-- Nav -->
                            <table border="0" cellpadding="0" cellspacing="0" align="right" class="deviceWidth">
                                <tr>
                                    <td class="center" style="font-size: 13px; color: #272727; font-weight: light; text-align: right; font-family: Arial, Helvetica, sans-serif; line-height: 20px; vertical-align: middle; padding:10px 20px; font-style:italic">
<!--Optional Links Here -->
                                    </td>
                                </tr>
                            </table><!-- End Nav -->

					</td>
				</tr>
			</table>                                                              		
                    </td>
              </tr>
          </table><!-- End 4 Columns -->
						
		</td>
	</tr>
</table>
<table width="100%" border="0" cellpadding="0" cellspacing="0" align="center">
	<tr>
		<td width="100%" align="center" valign="top" bgcolor="#eeeeee" style="padding-top:20px"><!-- End One Column --><!-- spacer --><!-- End One Column --><!-- spacer -->
			<table width="580"  class="deviceWidth" border="0" cellpadding="0" cellspacing="0" align="center" bgcolor="#eeeeed">
                <tr>
                  <td style="font-size: 13px; color: #888888; font-weight: normal; text-align: left; font-family:Arial, Helvetica, sans-serif; line-height: 1.5; vertical-align: top; padding:10px 8px 10px 8px; border-top-left-radius: 4px; border-top-right-radius: 4px;" bgcolor="#ffffff"><p>Hi <%=(RSPassReq.Fields.Item("FirstName").Value)%>,				    </p>
					<p>You recently requested a password reset.
						  
						  To change your ********** password, please click the Reset Password button below.
						  
						  Thanks for using **********!</p></td>
                </tr><tr>
                  <td style="fpadding:10px 8px 10px 8px" bgcolor="#ffffff"><center>
<div style="margin: 0 auto;"><!--[if mso]>
 <v:rect xmlns:v="urn:schemas-microsoft-com:vml" xmlns:w="urn:schemas-microsoft-com:office:word" href="#" style="height:50px;v-text-anchor:middle;width:250px;" arcsize="16%" strokecolor="#49a9ce" fill="t">
    <v:fill type="tile" src="http://imgur.com/5BIp9d0.gif" color="#49a9ce" />
    <w:anchorlock/>
    <center style="color:#ffffff;font-family:sans-serif;font-size:18px;font-weight:bold;">Reset Password</center>
  </v:rect>
<![endif]-->
<div style="margin: 0 auto;mso-hide:all;">
<table align="center" cellpadding="0" cellspacing="0" height="50" width="250" style="margin: 0 auto; mso-hide:all;">
	<tbody>
		<tr>
			<td align="center" bgcolor="#49a9ce" height="53" style="vertical-align:middle;color: #ffffff; display: block;background-color:#49a9ce;background-image:url(http://imgur.com/5BIp9d0.gif);border-radius:4px;border:1px solid #1f7393;mso-hide:all;" width="250">
				<a class="cta_button" href="http:www.**********.co.uk/passwordreset/passreset.asp?ID=<%=(RSPassReq.Fields.Item("ID").Value)%>&UN=<%=(RSPassReq.Fields.Item("Username").Value)%>&Auth=<%=(RSPassReq.Fields.Item("Password").Value)%>" style="font-size:16px;-webkit-text-size-adjust:none; font-weight: bold; font-family:sans-serif; text-decoration: none; line-height:53px; width:250px; display:inline-block;" title="Reset Password">
					<span style="color:#ffffff">Reset Password</span>				</a>			</td>
		</tr>
	</tbody>
</table>
</div>
</div>
</center></td>
                </tr><tr>
                  <td style="font-size: 13px; color: #888888; font-weight: normal; text-align: left; font-family: Arial, Helvetica, sans-serif; line-height: 1.5; vertical-align: top; padding:10px 8px 10px 8px; border-bottom-left-radius: 4px; border-bottom-right-radius: 4px;" bgcolor="#ffffff"></td>
                </tr>              
			</table><!-- End One Column -->
<div style="height:65px">&nbsp;</div><!-- spacer --><!-- spacer -->
			<!-- 4 Columns -->
			<table width="100%" border="0" cellpadding="0" cellspacing="0" align="center">
				<tr>
					<td bgcolor="#0D2235" style="padding:30px 0">
                        <table width="580" border="0" cellpadding="0" cellspacing="0" align="center" class="deviceWidth">
                            <tr>
                                <td>                    
                                        <table width="55%" cellpadding="0" cellspacing="0"  border="0" align="left" class="deviceWidth">
                                            <tr>
                                                <td valign="top" style="font-size: 11px; color: #f1f1f1; color:#999; font-family: Arial, sans-serif; padding-bottom:20px" class="center">

                                                    You are receiving this email because you subscribed via <a href="" style="color:#999;text-decoration:underline;">our website</a><br/><br/>
                                                    Want to be removed? No problem, <a href="" style="color:#999;text-decoration:underline;">click here</a> and we won't bug you again.<br/><br/>&copy; Copyright <% response.write(Year(Date)) %> **********

                                                </td>
                                            </tr>
                                        </table>
                        
                                        <table width="40%" cellpadding="0" cellspacing="0"  border="0" align="right" class="deviceWidth">
                                            <tr>
                                              <td valign="top" style="font-size: 11px; color: #f1f1f1; font-weight: normal; font-family: Arial, Helvetica, sans-serif; line-height: 26px; vertical-align: top; text-align:right" class="center">
                                                    <a href="http://www.**********.co.uk"><img src="http://www.**********.co.uk/images/coms_images/**********.png" alt="**********" width="160" height="50" border="0" title="JobsterUK"/></a>
                                                  <br />
                                                  <a href=""><img src="http://www.**********.co.uk/png/facebook.png" width="22" height="22" alt="Follow us on Facebook" title="Follow us on Facebook" border="0" /></a>
                                                  <a href=""><img src="http://www.**********.co.uk/png/twitter.png" width="22" height="22" alt="Follow our Tweets" title="Follow our Tweets" border="0" /></a>

                                                  <a href=""><img src="http://www.**********.co.uk/png/linkedin.png" width="22" height="22" alt="Join our LinkedIn community" title="Join our LinkedIn community" border="0" /></a>

                                                  <a href=""><img src="http://www.**********.co.uk/png/google.png" width="22" height="22" alt="Join us on Google+" title="Join us on Google+" border="0" /></a>

                                                  <a href=""><img src="http://www.**********.co.uk/png/rss.png" width="22" height="22" alt="Get the latest from our RSS feed" title="Get the latest from our RSS feed" border="0" /></a>
                                                  </td>
                                          </tr>
                                        </table>   
                        
                        		</td>
                        	</tr>
                        </table>                                                              		
                    </td>
                </tr>
            </table><!-- End 4 Columns -->
	  </td>
	</tr>
</table> <!-- End Wrapper -->
</td>
</tr>
</table>
</body>
</html>
<%
RSPassReq.Close()
Set RSPassReq = Nothing
%>

Open in new window


When I do a select statement in SSMS it works, so I'm guessing this is an issue with the binary.

Thanks
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Can you post the sql that works directly on SSMS?
0
garethtnashAuthor Commented:
Sure

SELECT ID, Username, Password, FirstName FROM dbo.Candidate WHERE ID = 1 AND Password = 0x3904B00146BE24C3250733FD110423B54B4D1E14

Open in new window


Works fine

Thanks
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Try setting the exact length of the data in the parameter definition by using the Len()  function:

RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param2", 128, 1, Len( RSPassReq__MMColParam2),  RSPassReq__MMColParam2)
0
garethtnashAuthor Commented:
Hi Big Monty,

Sorry no joy, now I'm getting

ADODB.Command error '800a0d5d'

Application uses a value of the wrong type for the current operation.

/coms/js/passwordrequest.asp, line 25

<%@LANGUAGE="VBSCRIPT"%>
<!--#include virtual="/Connections/jster.asp" -->
<%
Dim RSPassReq__MMColParam
RSPassReq__MMColParam = NULL
If (Request("ID") <> "") Then 
  RSPassReq__MMColParam = Request("ID")
End If

Dim RSPassReq__MMColParam2
RSPassReq__MMColParam2 = NULL
If (Request("auth") <> "") Then 
  RSPassReq__MMColParam2 = Request("auth")
End If

Dim RSPassReq
Dim RSPassReq_cmd
Dim RSPassReq_numRows

Set RSPassReq_cmd = Server.CreateObject ("ADODB.Command")
RSPassReq_cmd.ActiveConnection = MM_jster_STRING
RSPassReq_cmd.CommandText = "SELECT ID, Username, Password, FirstName FROM dbo.Candidate WHERE ID = ? AND Password = ?" 
RSPassReq_cmd.Prepared = true
RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param1", 5, 1, -1, RSPassReq__MMColParam) ' adDouble
RSPassReq_cmd.Parameters.Append RSPassReq_cmd.CreateParameter("param2", 128, 1, Len( RSPassReq__MMColParam2),  RSPassReq__MMColParam2)

Set RSPassReq = RSPassReq_cmd.Execute
RSPassReq_numRows = 0
%>

Open in new window


Any thoughts?

Thanks
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
sorry for the delay in response, I was away on vacation with internet access only by phone last week....

What I'll do is try this tonight at home on my local server, can you post the script that created the table you're using?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garethtnashAuthor Commented:
Hey Big Monty,

Apologies, I've been on holiday myself for the last couple of weeks, and couldn't access the server..

So the Table is -

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Candidate](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Username] [nvarchar](350) NULL,
	[Password] [binary](20) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](80) NULL,
	[Phone] [nvarchar](50) NULL,
	[Mobile] [nvarchar](50) NULL,
	[DateRegistered] [datetime] NULL,
	[RighttoWork] [char](1) NULL,
	[PartnerSites] [char](1) NULL,
	[NewsUpdates] [char](1) NULL,
	[Authorised] [char](1) NULL,
	[JobAlertFrequesncy] [int] NULL,
	[LastAccess] [datetime] NULL,
	[EmploymentStatus] [int] NULL,
	[RecruiterAccess] [char](1) NULL,
	[SALT] [nvarchar](25) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Candidate] ADD  DEFAULT (getdate()) FOR [DateRegistered]
GO

ALTER TABLE [dbo].[Candidate] ADD  DEFAULT ('N') FOR [RighttoWork]
GO

ALTER TABLE [dbo].[Candidate] ADD  DEFAULT ('Y') FOR [PartnerSites]
GO

ALTER TABLE [dbo].[Candidate] ADD  DEFAULT ('Y') FOR [NewsUpdates]
GO

ALTER TABLE [dbo].[Candidate] ADD  CONSTRAINT [DF_Candidate_Authorised]  DEFAULT ('N') FOR [Authorised]
GO

ALTER TABLE [dbo].[Candidate] ADD  CONSTRAINT [DF_Candidate_JobAlertFrequesncy]  DEFAULT ((1)) FOR [JobAlertFrequesncy]
GO

Open in new window


The Stored Proc that registers the users Hashs and Salts the password is -

CREATE PROCEDURE [dbo].[CandidateRegister]
@Username nvarchar(350),
@Password nvarchar(50),
@FirstName nvarchar(50),
@LastName nvarchar(80)
AS
SET NOCOUNT ON;

/*Declare additional variables */
DECLARE @Result int
DECLARE @UserID NVARCHAR(225)
DECLARE @UserAuth NVARCHAR(225)
DECLARE @SALT NVARCHAR(25)
DECLARE @PWDWITHSALT NVARCHAR(125)

BEGIN

IF EXISTS (SELECT ID from dbo.Candidate where Username = @Username)
BEGIN
SET @Result = '1'
END
ELSE
BEGIN
SELECT @SALT = [dbo].[ufn_GeneratePassword] (10)
SET @PWDWITHSALT = @Password + @SALT
SET @Result = '2'
INSERT INTO dbo.Candidate (Username, [Password], FirstName, LastName, DateRegistered, Authorised, JobAlertFrequesncy, LastAccess, SALT)
VALUES (@Username, HASHBYTES('SHA1', @PWDWITHSALT), @FirstName, @LastName, GETDATE(), 'N', 1, GETDATE(), @SALT)
SET @UserID = SCOPE_IDENTITY()
SET @UserAuth = HASHBYTES('SHA1', @PWDWITHSALT)
INSERT INTO dbo.Profile(AccountID, DateCreated)
Values (@UserID, GETDATE())
END
Select 
@Result as Result, 
@UserID as UserID, 
@UserAuth as UserAuth,
@Username as UserEmail
END


GO

Open in new window


Appreciate your help with this.

I suppose the alternative is that I just send the UserID and Email address, but that doesn't feel very secure :)

Thank you
0
garethtnashAuthor Commented:
I found a work around - thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.