SQL question marks

[Carol Grismore]

This is kind of a long one. I've been working on it for about a week, off
and on. I am running the following code, which I stole fair and square from
a website:

Function fGetFullNameOfLoggedUser(Optional strUserName As String) As String
'
' Returns the full name for a given UserID
' NT/2000 only
' Omitting the strUserName argument will try and
' retrieve the full name for the currently logged on user
'
On Error GoTo ErrHandler
Dim pBuf As Long
Dim dwRec As Long
Dim pTmp As USER_INFO_2
Dim abytPDCName() As Byte
Dim abytUserName() As Byte
Dim lngRet As Long
Dim i As Long

' Unicode
abytPDCName = fGetDCName() & vbNullChar
If (Len(strUserName) = 0) Then strUserName = fGetUserName()
abytUserName = strUserName & vbNullChar

' Level 2
lngRet = apiNetUserGetInfo( _
abytPDCName(0), _
abytUserName(0), _
2, _
pBuf)
If (lngRet = ERROR_SUCCESS) Then
Call sapiCopyMem(pTmp, ByVal pBuf, Len(pTmp))
fGetFullNameOfLoggedUser = fStrFromPtrW(pTmp.usri2_full_name)
End If

Call apiNetAPIBufferFree(pBuf)
ExitHere:
Exit Function
ErrHandler:
fGetFullNameOfLoggedUser = vbNullString
Resume ExitHere
End Function

The function seems to be doing what it's supposed to do, in spite of the
fact that I'm running XP. However, when I try to use the name in a SQL
statement, like so:

stUser = fGetFullNameOfLoggedUser("cgrismore")

mySQL = "INSERT INTO dbo_email_log "
mySQL = mySQL & "([send_type], [send_via], [sent_to_list],
[subject], "
mySQL = mySQL & "[attachment_name], [num_emails], [date_sent],
[sent_by]) "
mySQL = mySQL & "SELECT "
mySQL = mySQL & "'" & stSend & "', '" & stVia & "', '" & stSendTo &
"', '" & stSub & "', '" _
& stAtt & "', " & email_count & ", #" & Now() & "#, '" & stUser
& "'"

and then look at the resultant string, I get this weird question mark, like
so:

INSERT INTO dbo_email_log ([send_type], [send_via], [sent_to_list],
[subject], [attachment_name], [num_emails], [date_sent], [sent_by]) SELECT
'ind', 'Eudora', 'tblMailingList1', 'Type your subject here', 'Attachment
name here', 0, #1/5/2006 2:01:58 PM#, 'Carol Grismore?

Of course, the SQL statement won't run. If I replace the function with a
variable

stUser = "Carol Grismore"

all is well.

The question mark doesn't appear if I just print out the value of the stUser
string, nor does it appear in a msgbox or if I just assign the variable to
another variable.

The question is -- where is the ? coming from -- or alternatively -- how do
I get the full user name some other way?

PS -- if I change the position of stUser in the SQL query, all the
information that appears after its position is changed to question marks!!
It's really eerie.

TIA!

 

[John Nurick]

Could it be that fStrFromPtrW() is returning a null-terminated string?
If so, you'll need to chop off the null.

This is kind of a long one. I've been working on it for about a week, off
and on. I am running the following code, which I stole fair and square from
a website:

Function fGetFullNameOfLoggedUser(Optional strUserName As String) As String
'
' Returns the full name for a given UserID
' NT/2000 only
' Omitting the strUserName argument will try and
' retrieve the full name for the currently logged on user
'
On Error GoTo ErrHandler
Dim pBuf As Long
Dim dwRec As Long
Dim pTmp As USER_INFO_2
Dim abytPDCName() As Byte
Dim abytUserName() As Byte
Dim lngRet As Long
Dim i As Long

' Unicode
abytPDCName = fGetDCName() & vbNullChar
If (Len(strUserName) = 0) Then strUserName = fGetUserName()
abytUserName = strUserName & vbNullChar

' Level 2
lngRet = apiNetUserGetInfo( _
abytPDCName(0), _
abytUserName(0), _
2, _
pBuf)
If (lngRet = ERROR_SUCCESS) Then
Call sapiCopyMem(pTmp, ByVal pBuf, Len(pTmp))
fGetFullNameOfLoggedUser = fStrFromPtrW(pTmp.usri2_full_name)
End If

Call apiNetAPIBufferFree(pBuf)
ExitHere:
Exit Function
ErrHandler:
fGetFullNameOfLoggedUser = vbNullString
Resume ExitHere
End Function

The function seems to be doing what it's supposed to do, in spite of the
fact that I'm running XP. However, when I try to use the name in a SQL
statement, like so:

stUser = fGetFullNameOfLoggedUser("cgrismore")

mySQL = "INSERT INTO dbo_email_log "
mySQL = mySQL & "([send_type], [send_via], [sent_to_list],
[subject], "
mySQL = mySQL & "[attachment_name], [num_emails], [date_sent],
[sent_by]) "
mySQL = mySQL & "SELECT "
mySQL = mySQL & "'" & stSend & "', '" & stVia & "', '" & stSendTo &
"', '" & stSub & "', '" _
& stAtt & "', " & email_count & ", #" & Now() & "#, '" & stUser
& "'"

and then look at the resultant string, I get this weird question mark, like
so:

INSERT INTO dbo_email_log ([send_type], [send_via], [sent_to_list],
[subject], [attachment_name], [num_emails], [date_sent], [sent_by]) SELECT
'ind', 'Eudora', 'tblMailingList1', 'Type your subject here', 'Attachment
name here', 0, #1/5/2006 2:01:58 PM#, 'Carol Grismore?

Of course, the SQL statement won't run. If I replace the function with a
variable

stUser = "Carol Grismore"

all is well.

The question mark doesn't appear if I just print out the value of the stUser
string, nor does it appear in a msgbox or if I just assign the variable to
another variable.

The question is -- where is the ? coming from -- or alternatively -- how do
I get the full user name some other way?

PS -- if I change the position of stUser in the SQL query, all the
information that appears after its position is changed to question marks!!
It's really eerie.

TIA!

 

[Carol Grismore]

A null-terminated string, eh? Hmm... er... uh... duh...

Yeah, that's right; I'm clueless. I would be guessing that a
null-terminated string is what it sounds like, but I can't imagine how it
gets that way, let alone how I would chop off the null. I'm not good at
chopping things; too squeamish.

Maybe stName = left(stName,len(stName))?

--
Carol

Could it be that fStrFromPtrW() is returning a null-terminated string?
If so, you'll need to chop off the null.

This is kind of a long one. I've been working on it for about a week, off
and on. I am running the following code, which I stole fair and square from
a website:

Function fGetFullNameOfLoggedUser(Optional strUserName As String) As String
'
' Returns the full name for a given UserID
' NT/2000 only
' Omitting the strUserName argument will try and
' retrieve the full name for the currently logged on user
'
On Error GoTo ErrHandler
Dim pBuf As Long
Dim dwRec As Long
Dim pTmp As USER_INFO_2
Dim abytPDCName() As Byte
Dim abytUserName() As Byte
Dim lngRet As Long
Dim i As Long

' Unicode
abytPDCName = fGetDCName() & vbNullChar
If (Len(strUserName) = 0) Then strUserName = fGetUserName()
abytUserName = strUserName & vbNullChar

' Level 2
lngRet = apiNetUserGetInfo( _
abytPDCName(0), _
abytUserName(0), _
2, _
pBuf)
If (lngRet = ERROR_SUCCESS) Then
Call sapiCopyMem(pTmp, ByVal pBuf, Len(pTmp))
fGetFullNameOfLoggedUser = fStrFromPtrW(pTmp.usri2_full_name)
End If

Call apiNetAPIBufferFree(pBuf)
ExitHere:
Exit Function
ErrHandler:
fGetFullNameOfLoggedUser = vbNullString
Resume ExitHere
End Function

The function seems to be doing what it's supposed to do, in spite of the
fact that I'm running XP. However, when I try to use the name in a SQL
statement, like so:

stUser = fGetFullNameOfLoggedUser("cgrismore")

mySQL = "INSERT INTO dbo_email_log "
mySQL = mySQL & "([send_type], [send_via], [sent_to_list],
[subject], "
mySQL = mySQL & "[attachment_name], [num_emails], [date_sent],
[sent_by]) "
mySQL = mySQL & "SELECT "
mySQL = mySQL & "'" & stSend & "', '" & stVia & "', '" & stSendTo &
"', '" & stSub & "', '" _
& stAtt & "', " & email_count & ", #" & Now() & "#, '" & stUser
& "'"

and then look at the resultant string, I get this weird question mark, like
so:

INSERT INTO dbo_email_log ([send_type], [send_via], [sent_to_list],
[subject], [attachment_name], [num_emails], [date_sent], [sent_by]) SELECT
'ind', 'Eudora', 'tblMailingList1', 'Type your subject here', 'Attachment
name here', 0, #1/5/2006 2:01:58 PM#, 'Carol Grismore?

Of course, the SQL statement won't run. If I replace the function with a
variable

stUser = "Carol Grismore"

all is well.

The question mark doesn't appear if I just print out the value of the stUser
string, nor does it appear in a msgbox or if I just assign the variable to
another variable.

The question is -- where is the ? coming from -- or alternatively -- how do
I get the full user name some other way?

PS -- if I change the position of stUser in the SQL query, all the
information that appears after its position is changed to question marks!!
It's really eerie.

TIA!

 

[John Nurick]

In Visual Basic, string variables know how long they are (the variable
points to a data structure in memory that includes four bytes that store
the number of characters in the string). But the Windows API uses C,
where a string variable just points to a single address in memory. To
get the contents of the string, a C program starts reading memory at
that point and continues until it hits a null (in VB, that's Chr(0)).

One consequence of this is that a string in VB can contain nulls, while
a C string can't.

Cutting a long story short: if VB code calls C functions that return
strings, it's necessary to chop off the null and anything that follows
it. Often but not always, this is done by the VB procedure that "wraps"
the C calls. If not, you need something like this in your own code:

stName = Left(stName, InStr(stName, Chr(0)) - 1)