aspsqlvb
10-19-2005, 05:58 PM
Hello guys,
I am using the ASP 3.0 Command object along with a Stored pROCEDURE in SQL Server 2000 to add one record. After the one record is added, then a password is generated, sending the newly registered member there username and password to there email account. Well, when they submit there info to the database, more than 1 record is created.
Resulting in one USERNAME and 2 different PASSWORDS. This is sent to there email account. Why?.........
...Ive ran the stored procedure along with the sql statements in the QUERY ANALYZER and they seem to be working fine.
Dim StaffIdValue,FirstName,MiddleName,LastName,Position,Region,HomeTelephone,EmailHome,EmailWork,Cellula rPhone,UserName,Password
Dim Conn,RS,strSQL,Comm,UsersRegion
Set Conn = Server.CreateObject("ADODB.Connection")
'Conn.Open "Provider=SQLOLEDB;Data Source=eel2k5.nashusa.net;Initial Catalog=aspsqlv_LatestMarriot;User Id=aspsqlv_admin;Password=aspvbsql"
Conn.Open "Provider=SQLOLEDB;Data Source=moscomputer;Initial Catalog=New_Marriott;User Id=aspsqlvb;Password=aspvbsql"
'Set RS = Server.CreateObject("ADODB.Recordset")
FirstName = Trim(Request.Form("FirstName"))
MiddleName = Trim(Request.Form("MiddleName"))
LastName = Trim(Request.Form("LastName"))
Position = Request.Form("Position")
Region = Request.Form("Region")
HomeTelephone = Trim(Request.Form("HomeTelephone"))
WorkTelephone = Trim(Request.Form("WorkTelephone"))
CellularPhone = Trim(Request.Form("CellularPhone"))
EmailHome = Trim(Request.Form("EmailHome"))
EmailWork = Trim(Request.Form("EmailWork"))
UserName = Trim(Request.Form("UserName"))
Password = Password_GenPass(4, LastName)
'''StaffId = ' Will be generated from an IF '' THEN statement
' depending on the Region that was selected will
' determine the StaffId to be inserted in the Validate_Table
If Region = "MVCI" Then 'MVCI
StaffIdValue = 3
ElseIf Region = "ALL REGIONS" Then 'MVCI
StaffIdValue = 3
ElseIf Region = "LATIN" Then 'LATIN
StaffIdValue = 5
ElseIf Region = "DORAL" Then 'DORAL
StaffIdValue = 4
ElseIf Region ="ARUBA" Then 'ARUBA
StaffIdValue = 6
End If
'Response.Write StaffIdValue
' Response.End
'////////////////////////////////////////////////////////////////////////////////////
Set Comm = Server.CreateObject("ADODB.Command")
With Comm
.ActiveConnection = Conn
.CommandText = "NewEmployee"
.CommandType = adcmdStoredProc
End With
Comm.Parameters.Append Comm.CreateParameter("EmployeeId",adInteger,adParamReturnValue,4)
Comm.Parameters.Append Comm.CreateParameter("@StaffId", adInteger,adParamInput,4,StaffIdValue)
Comm.Parameters.Append Comm.CreateParameter("@Position",adVarChar,adParamInput,50,Position)
Comm.Parameters.Append Comm.CreateParameter("@FirstName",adVarChar,adParamInput,15,FirstName)
Comm.Parameters.Append Comm.CreateParameter("@MiddleName",adVarChar,adParamInput,15,MiddleName)
Comm.Parameters.Append Comm.CreateParameter("@LastName",adVarChar,adParamInput,20,LastName)
Comm.Parameters.Append Comm.CreateParameter("@HomeTelephone",adVarChar,adParamInput,15,HomeTelephone)
Comm.Parameters.Append Comm.CreateParameter("@WorkTelephone",adVarChar,adParamInput,15,WorkTelephone)
Comm.Parameters.Append Comm.CreateParameter("@CellularPhone",adVarChar,adParamInput,15,CellularPhone)
Comm.Parameters.Append Comm.CreateParameter("@EmailHome",adVarChar,adParamInput,50,EmailHome)
Comm.Parameters.Append Comm.CreateParameter("@EmailWork",adVarchar,adParamInput,50,EmailWork)
Comm.Parameters.Append Comm.CreateParameter("@Region",adVarchar,adParamInput,30,Region)
Comm.Parameters.Append Comm.CreateParameter("@UserName",adVarchar,adParamInput,10,UserName)
'Append the EmployeeId to the Password to avoid Password duplicates in the Validation_Table
Comm.Parameters.Append Comm.CreateParameter("@Password",adVarchar,adParamInput,10,Password)
Comm.Execute,,adExecuteNoRecords
'/////////////////////////////////////////////////////////////////////////////////////
'UPDATE PASSWORD BY APPENDING THE EMPLOYEEID WITH THE OLD PASSWORD
'/////////////////////////////////////////////////////////////////////////////////////
strSQL = strSQL & "Update Validation_Table"
strSQL = strSQL & " " & "Set [Password]=" & "'" & Password & Comm.Parameters("EmployeeId") & "'"
strSQL = strSQL & " " & "Where EmployeeId=" & Comm.Parameters("EmployeeId")
Conn.Execute strSQL
'//////////////////////////////////////////////////////////////////////////////////////
'UPDATE PASSWORD BY APPENDING THE EMPLOYEEID WITH THE OLD PASSWORD
'/////////////////////////////////////////////////////////////////////////////////////
Dim mailTo,mailToSupport, from, cc, bcc, subject,link, message
UserName = UserName
mailTo = EmailHome
mailToSupport = "support@keenesolutions.com"
from = "support@keenesolutions.com"
cc = ""
bcc = ""
subject = "Welcome to Keene Solutions"
'link = "http://computer/Marriott/Login.htm"
link = "http://keenesolutions.com/Login.htm"
message = "Hello" & " " & FirstName & "." &
Dim objMail
Set objMail = Server.CreateObject("CDONTS.NewMail")
'On ERROR Resume Next
With objMail
.To = mailTo
.From = from
.Cc = cc
.Bcc = from
.Subject = subject
.Body = message
.Send
End With
I am using the ASP 3.0 Command object along with a Stored pROCEDURE in SQL Server 2000 to add one record. After the one record is added, then a password is generated, sending the newly registered member there username and password to there email account. Well, when they submit there info to the database, more than 1 record is created.
Resulting in one USERNAME and 2 different PASSWORDS. This is sent to there email account. Why?.........
...Ive ran the stored procedure along with the sql statements in the QUERY ANALYZER and they seem to be working fine.
Dim StaffIdValue,FirstName,MiddleName,LastName,Position,Region,HomeTelephone,EmailHome,EmailWork,Cellula rPhone,UserName,Password
Dim Conn,RS,strSQL,Comm,UsersRegion
Set Conn = Server.CreateObject("ADODB.Connection")
'Conn.Open "Provider=SQLOLEDB;Data Source=eel2k5.nashusa.net;Initial Catalog=aspsqlv_LatestMarriot;User Id=aspsqlv_admin;Password=aspvbsql"
Conn.Open "Provider=SQLOLEDB;Data Source=moscomputer;Initial Catalog=New_Marriott;User Id=aspsqlvb;Password=aspvbsql"
'Set RS = Server.CreateObject("ADODB.Recordset")
FirstName = Trim(Request.Form("FirstName"))
MiddleName = Trim(Request.Form("MiddleName"))
LastName = Trim(Request.Form("LastName"))
Position = Request.Form("Position")
Region = Request.Form("Region")
HomeTelephone = Trim(Request.Form("HomeTelephone"))
WorkTelephone = Trim(Request.Form("WorkTelephone"))
CellularPhone = Trim(Request.Form("CellularPhone"))
EmailHome = Trim(Request.Form("EmailHome"))
EmailWork = Trim(Request.Form("EmailWork"))
UserName = Trim(Request.Form("UserName"))
Password = Password_GenPass(4, LastName)
'''StaffId = ' Will be generated from an IF '' THEN statement
' depending on the Region that was selected will
' determine the StaffId to be inserted in the Validate_Table
If Region = "MVCI" Then 'MVCI
StaffIdValue = 3
ElseIf Region = "ALL REGIONS" Then 'MVCI
StaffIdValue = 3
ElseIf Region = "LATIN" Then 'LATIN
StaffIdValue = 5
ElseIf Region = "DORAL" Then 'DORAL
StaffIdValue = 4
ElseIf Region ="ARUBA" Then 'ARUBA
StaffIdValue = 6
End If
'Response.Write StaffIdValue
' Response.End
'////////////////////////////////////////////////////////////////////////////////////
Set Comm = Server.CreateObject("ADODB.Command")
With Comm
.ActiveConnection = Conn
.CommandText = "NewEmployee"
.CommandType = adcmdStoredProc
End With
Comm.Parameters.Append Comm.CreateParameter("EmployeeId",adInteger,adParamReturnValue,4)
Comm.Parameters.Append Comm.CreateParameter("@StaffId", adInteger,adParamInput,4,StaffIdValue)
Comm.Parameters.Append Comm.CreateParameter("@Position",adVarChar,adParamInput,50,Position)
Comm.Parameters.Append Comm.CreateParameter("@FirstName",adVarChar,adParamInput,15,FirstName)
Comm.Parameters.Append Comm.CreateParameter("@MiddleName",adVarChar,adParamInput,15,MiddleName)
Comm.Parameters.Append Comm.CreateParameter("@LastName",adVarChar,adParamInput,20,LastName)
Comm.Parameters.Append Comm.CreateParameter("@HomeTelephone",adVarChar,adParamInput,15,HomeTelephone)
Comm.Parameters.Append Comm.CreateParameter("@WorkTelephone",adVarChar,adParamInput,15,WorkTelephone)
Comm.Parameters.Append Comm.CreateParameter("@CellularPhone",adVarChar,adParamInput,15,CellularPhone)
Comm.Parameters.Append Comm.CreateParameter("@EmailHome",adVarChar,adParamInput,50,EmailHome)
Comm.Parameters.Append Comm.CreateParameter("@EmailWork",adVarchar,adParamInput,50,EmailWork)
Comm.Parameters.Append Comm.CreateParameter("@Region",adVarchar,adParamInput,30,Region)
Comm.Parameters.Append Comm.CreateParameter("@UserName",adVarchar,adParamInput,10,UserName)
'Append the EmployeeId to the Password to avoid Password duplicates in the Validation_Table
Comm.Parameters.Append Comm.CreateParameter("@Password",adVarchar,adParamInput,10,Password)
Comm.Execute,,adExecuteNoRecords
'/////////////////////////////////////////////////////////////////////////////////////
'UPDATE PASSWORD BY APPENDING THE EMPLOYEEID WITH THE OLD PASSWORD
'/////////////////////////////////////////////////////////////////////////////////////
strSQL = strSQL & "Update Validation_Table"
strSQL = strSQL & " " & "Set [Password]=" & "'" & Password & Comm.Parameters("EmployeeId") & "'"
strSQL = strSQL & " " & "Where EmployeeId=" & Comm.Parameters("EmployeeId")
Conn.Execute strSQL
'//////////////////////////////////////////////////////////////////////////////////////
'UPDATE PASSWORD BY APPENDING THE EMPLOYEEID WITH THE OLD PASSWORD
'/////////////////////////////////////////////////////////////////////////////////////
Dim mailTo,mailToSupport, from, cc, bcc, subject,link, message
UserName = UserName
mailTo = EmailHome
mailToSupport = "support@keenesolutions.com"
from = "support@keenesolutions.com"
cc = ""
bcc = ""
subject = "Welcome to Keene Solutions"
'link = "http://computer/Marriott/Login.htm"
link = "http://keenesolutions.com/Login.htm"
message = "Hello" & " " & FirstName & "." &
Dim objMail
Set objMail = Server.CreateObject("CDONTS.NewMail")
'On ERROR Resume Next
With objMail
.To = mailTo
.From = from
.Cc = cc
.Bcc = from
.Subject = subject
.Body = message
.Send
End With