SQL question marks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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!
Click to expand...
 
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


John Nurick said:
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!
Click to expand...
Click to expand...
 
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)
 
Back
Top