Stored procedure having issue populating record set
0
I'm having some issues with an ADODB.Recordset. I call a stored procedure that returns a password and login from the database. Once the usp is executed both the password and login are loaded into the record set. If any line of code is placed before setting the password to a string the password value reverts to Null. This doesn't with the login, only the password. Any thoughts on what I can do to fix this?
Dim objConn As ADODB.Connection
Dim rstResult As ADODB.recordset
Dim objCmd As New ADODB.Command
Dim Param As cSQLParam
Dim ParamValue As Variant
Dim lngNumRec As Long
Set objConn = New ADODB.Connection
objConn.Open "DSN=" & GetProfileString(gRootFolder & "InputFolder" & "ODBC.bds", "General", "DSN")
Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = objConn
.CommandText = "[dbo].[usp_Credentials_GetUserCredentials]"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
For Each Param In Params
.Parameters.Append .CreateParameter(Param.Name, Param.DataType, Param.Direction, Param.Size, IIf(Param.Value = "", Null, Param.Value))
Next
End With
Set rstResult = objCmd.Execute
'any time I add code here the password shows up as Null, If no code is here the
'correct password is displayed. Could this have to do with the password being
'encrypted with a Salt on the server?
'Capture the password
gDataRecord.Password = rstResult("Password")
'Capture the Login
gDataRecord.Login = rstResult("LoginName")
rstResult.Close
objConn.Close
sql vba odbc adodb
edited Nov 21 '18 at 17:58
Mathieu Guindon
44.5k770155
asked Nov 21 '18 at 17:38
ZacZac
12
add a comment |
0
I'm having some issues with an ADODB.Recordset. I call a stored procedure that returns a password and login from the database. Once the usp is executed both the password and login are loaded into the record set. If any line of code is placed before setting the password to a string the password value reverts to Null. This doesn't with the login, only the password. Any thoughts on what I can do to fix this?
Dim objConn As ADODB.Connection
Dim rstResult As ADODB.recordset
Dim objCmd As New ADODB.Command
Dim Param As cSQLParam
Dim ParamValue As Variant
Dim lngNumRec As Long
Set objConn = New ADODB.Connection
objConn.Open "DSN=" & GetProfileString(gRootFolder & "InputFolder" & "ODBC.bds", "General", "DSN")
Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = objConn
.CommandText = "[dbo].[usp_Credentials_GetUserCredentials]"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
For Each Param In Params
.Parameters.Append .CreateParameter(Param.Name, Param.DataType, Param.Direction, Param.Size, IIf(Param.Value = "", Null, Param.Value))
Next
End With
Set rstResult = objCmd.Execute
'any time I add code here the password shows up as Null, If no code is here the
'correct password is displayed. Could this have to do with the password being
'encrypted with a Salt on the server?
'Capture the password
gDataRecord.Password = rstResult("Password")
'Capture the Login
gDataRecord.Login = rstResult("LoginName")
rstResult.Close
objConn.Close
sql vba odbc adodb
edited Nov 21 '18 at 17:58
Mathieu Guindon
44.5k770155
asked Nov 21 '18 at 17:38
ZacZac
12
"any time I add code here" - like what code? It doesn't matter if the password is encrypted (+/- salt) - whatever value the dB returns should be in the recordset.
– Tim Williams
Nov 21 '18 at 17:41
What isParams?
– Mathieu Guindon
Nov 21 '18 at 17:56
1
What isgDataRecord(and if thatgmeans "global", why on earth are you bothering with encryption if the plain-text password is right there in global scope)? ...the whole approach looks like security theater to me. Why aren't the passwords hashed and why would credentials even need to be anywhere in the VBA execution context?
– Mathieu Guindon
Nov 21 '18 at 18:06
Gdatarecord is a datastation for BWS. And I'm using this for building automations rather than macros.
– Zac
Nov 22 '18 at 18:14
add a comment |
0
0
0
I'm having some issues with an ADODB.Recordset. I call a stored procedure that returns a password and login from the database. Once the usp is executed both the password and login are loaded into the record set. If any line of code is placed before setting the password to a string the password value reverts to Null. This doesn't with the login, only the password. Any thoughts on what I can do to fix this?
Dim objConn As ADODB.Connection
Dim rstResult As ADODB.recordset
Dim objCmd As New ADODB.Command
Dim Param As cSQLParam
Dim ParamValue As Variant
Dim lngNumRec As Long
Set objConn = New ADODB.Connection
objConn.Open "DSN=" & GetProfileString(gRootFolder & "InputFolder" & "ODBC.bds", "General", "DSN")
Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = objConn
.CommandText = "[dbo].[usp_Credentials_GetUserCredentials]"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
For Each Param In Params
.Parameters.Append .CreateParameter(Param.Name, Param.DataType, Param.Direction, Param.Size, IIf(Param.Value = "", Null, Param.Value))
Next
End With
Set rstResult = objCmd.Execute
'any time I add code here the password shows up as Null, If no code is here the
'correct password is displayed. Could this have to do with the password being
'encrypted with a Salt on the server?
'Capture the password
gDataRecord.Password = rstResult("Password")
'Capture the Login
gDataRecord.Login = rstResult("LoginName")
rstResult.Close
objConn.Close
sql vba odbc adodb
edited Nov 21 '18 at 17:58
Mathieu Guindon
44.5k770155
asked Nov 21 '18 at 17:38
ZacZac
12
I'm having some issues with an ADODB.Recordset. I call a stored procedure that returns a password and login from the database. Once the usp is executed both the password and login are loaded into the record set. If any line of code is placed before setting the password to a string the password value reverts to Null. This doesn't with the login, only the password. Any thoughts on what I can do to fix this?
Dim objConn As ADODB.Connection
Dim rstResult As ADODB.recordset
Dim objCmd As New ADODB.Command
Dim Param As cSQLParam
Dim ParamValue As Variant
Dim lngNumRec As Long
Set objConn = New ADODB.Connection
objConn.Open "DSN=" & GetProfileString(gRootFolder & "InputFolder" & "ODBC.bds", "General", "DSN")
Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = objConn
.CommandText = "[dbo].[usp_Credentials_GetUserCredentials]"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
For Each Param In Params
.Parameters.Append .CreateParameter(Param.Name, Param.DataType, Param.Direction, Param.Size, IIf(Param.Value = "", Null, Param.Value))
Next
End With
Set rstResult = objCmd.Execute
'any time I add code here the password shows up as Null, If no code is here the
'correct password is displayed. Could this have to do with the password being
'encrypted with a Salt on the server?
'Capture the password
gDataRecord.Password = rstResult("Password")
'Capture the Login
gDataRecord.Login = rstResult("LoginName")
rstResult.Close
objConn.Close
sql vba odbc adodb
sql vba odbc adodb
edited Nov 21 '18 at 17:58
Mathieu Guindon
44.5k770155
asked Nov 21 '18 at 17:38
ZacZac
12
edited Nov 21 '18 at 17:58
Mathieu Guindon
44.5k770155
asked Nov 21 '18 at 17:38
ZacZac
12
edited Nov 21 '18 at 17:58
Mathieu Guindon
44.5k770155
edited Nov 21 '18 at 17:58
Mathieu Guindon
44.5k770155
edited Nov 21 '18 at 17:58
Mathieu Guindon
44.5k770155
44.5k770155
asked Nov 21 '18 at 17:38
ZacZac
12
asked Nov 21 '18 at 17:38
ZacZac
12
asked Nov 21 '18 at 17:38
ZacZac
12
12
"any time I add code here" - like what code? It doesn't matter if the password is encrypted (+/- salt) - whatever value the dB returns should be in the recordset.
– Tim Williams
Nov 21 '18 at 17:41
What isParams?
– Mathieu Guindon
Nov 21 '18 at 17:56
1
What isgDataRecord(and if thatgmeans "global", why on earth are you bothering with encryption if the plain-text password is right there in global scope)? ...the whole approach looks like security theater to me. Why aren't the passwords hashed and why would credentials even need to be anywhere in the VBA execution context?
– Mathieu Guindon
Nov 21 '18 at 18:06
Gdatarecord is a datastation for BWS. And I'm using this for building automations rather than macros.
– Zac
Nov 22 '18 at 18:14
add a comment |
"any time I add code here" - like what code? It doesn't matter if the password is encrypted (+/- salt) - whatever value the dB returns should be in the recordset.
– Tim Williams
Nov 21 '18 at 17:41
What isParams?
– Mathieu Guindon
Nov 21 '18 at 17:56
1
What isgDataRecord(and if thatgmeans "global", why on earth are you bothering with encryption if the plain-text password is right there in global scope)? ...the whole approach looks like security theater to me. Why aren't the passwords hashed and why would credentials even need to be anywhere in the VBA execution context?
– Mathieu Guindon
Nov 21 '18 at 18:06
Gdatarecord is a datastation for BWS. And I'm using this for building automations rather than macros.
– Zac
Nov 22 '18 at 18:14
"any time I add code here" - like what code? It doesn't matter if the password is encrypted (+/- salt) - whatever value the dB returns should be in the recordset.
– Tim Williams
Nov 21 '18 at 17:41
"any time I add code here" - like what code? It doesn't matter if the password is encrypted (+/- salt) - whatever value the dB returns should be in the recordset.
– Tim Williams
Nov 21 '18 at 17:41
What is
Params?– Mathieu Guindon
Nov 21 '18 at 17:56
What is
Params?– Mathieu Guindon
Nov 21 '18 at 17:56
1
1
What is
gDataRecord (and if that g means "global", why on earth are you bothering with encryption if the plain-text password is right there in global scope)? ...the whole approach looks like security theater to me. Why aren't the passwords hashed and why would credentials even need to be anywhere in the VBA execution context?– Mathieu Guindon
Nov 21 '18 at 18:06
What is
gDataRecord (and if that g means "global", why on earth are you bothering with encryption if the plain-text password is right there in global scope)? ...the whole approach looks like security theater to me. Why aren't the passwords hashed and why would credentials even need to be anywhere in the VBA execution context?– Mathieu Guindon
Nov 21 '18 at 18:06
Gdatarecord is a datastation for BWS. And I'm using this for building automations rather than macros.
– Zac
Nov 22 '18 at 18:14
Gdatarecord is a datastation for BWS. And I'm using this for building automations rather than macros.
– Zac
Nov 22 '18 at 18:14
add a comment |
1 Answer
1
active
oldest
votes
0
I figured out the issue. Password was defined as a NVARCHAR(MAX), when I changed it to (255). The recordset held the variable. Thanks everyone who took a look at this.
answered Nov 26 '18 at 15:23
ZacZac
12
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417743%2fstored-procedure-having-issue-populating-record-set%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I figured out the issue. Password was defined as a NVARCHAR(MAX), when I changed it to (255). The recordset held the variable. Thanks everyone who took a look at this.
answered Nov 26 '18 at 15:23
ZacZac
12
add a comment |
0
I figured out the issue. Password was defined as a NVARCHAR(MAX), when I changed it to (255). The recordset held the variable. Thanks everyone who took a look at this.
answered Nov 26 '18 at 15:23
ZacZac
12
add a comment |
0
0
0
I figured out the issue. Password was defined as a NVARCHAR(MAX), when I changed it to (255). The recordset held the variable. Thanks everyone who took a look at this.
answered Nov 26 '18 at 15:23
ZacZac
12
I figured out the issue. Password was defined as a NVARCHAR(MAX), when I changed it to (255). The recordset held the variable. Thanks everyone who took a look at this.
answered Nov 26 '18 at 15:23
ZacZac
12
answered Nov 26 '18 at 15:23
ZacZac
12
answered Nov 26 '18 at 15:23
ZacZac
12
answered Nov 26 '18 at 15:23
ZacZac
12
12
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417743%2fstored-procedure-having-issue-populating-record-set%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
"any time I add code here" - like what code? It doesn't matter if the password is encrypted (+/- salt) - whatever value the dB returns should be in the recordset.
– Tim Williams
Nov 21 '18 at 17:41
What is
Params?– Mathieu Guindon
Nov 21 '18 at 17:56
1
What is
gDataRecord(and if thatgmeans "global", why on earth are you bothering with encryption if the plain-text password is right there in global scope)? ...the whole approach looks like security theater to me. Why aren't the passwords hashed and why would credentials even need to be anywhere in the VBA execution context?– Mathieu Guindon
Nov 21 '18 at 18:06
Gdatarecord is a datastation for BWS. And I'm using this for building automations rather than macros.
– Zac
Nov 22 '18 at 18:14