string concat problem

[VBLady]

I need some help with this:

 

I am trying to build an sql query in a string and I am having a hard time getting the end quote on the string! I've tried this:

 

sQuery = "select myfield from mytable where thisvalue= '" & sMyVariable & "'"

 

and I've tried:

 

sQuery = [string].concat("select myfield from mytable where thisvalue = '", sMyVariable, "'")

 

BOTH of them result in the following string

 

select myfield from mytable where thisvalue = 'thevalueofthevar

 

With NO end quote!! This is of course causing my sql query to fail. The variable sQuery is long enough. Please help!

 

Frances

[Robby]

If thisvalue is of string type in the table then you'refirst one is correct.

If the field is numeric then

sQuery = "select myfield from mytable where thisvalue= " & sMyVariable

[PlausiblyDamp]

Just tried it here and it worked fine. Are both sQuery and sMyVariable defined as strings? If so what are you assigning to sMyVariable?

Also could you show the code leading up to this?

[VBLady]

Here is the code:

 

sAccountno = New String(Chr(0), 22)
GMW_DB_Read(lWA, "Accountno", sAccountno, 22)
GMW_DB_Delete(lWA)
GMW_DB_Close(lWA)

lWA = GMW_DB_Open("Contact2")
GMW_DB_SetOrder(lWA, "CONTACT2")
iResult = GMW_DB_Seek(lWA, sAccountno)

If iResult = 1 Then
    GMW_DB_Delete(lWA)
End If

GMW_DB_Close(lWA)

sQuery = New String(Chr(0), 256)

sQuery = [string].Concat("select recid from contsupp where accountno = '", sAccountno, "'")

 

sAccount is declared as a string. And Robby, I know that numeric values in an sql query do not have quotes around them. That has nothing to do with my question. I need quotes around this value, but the trailing quote is not showing up.

[Mehyar]

Are you sure your string variable does not contain a '

 

Just a thought...

[PlausiblyDamp]

You are filling the string with null values (Chr(0)). The nulls are probably acting as a string terminator. Is there any reason you are padding it with nulls?

[VBLady]

The C++ API I am passing the strings to requires them to be initialized with the nulls. If I don't, they don't get populated.

[PlausiblyDamp]

You may need to build the string and then pad it with nulls to the required size.

[VBLady]

Isn't that what I'm already doing with the

 

sAccountno = New String(chr(0),22)

 

???

[PlausiblyDamp]

How does the sAccountno get filled in - I'm guessing inside the C dll.

Have you tried stripping the nulls from the variable after it is returned from the DLL function?

[pendragon]

PlausiblyDamp beat me to it, If there is a null at the end of sAccountno anything to the right of it will be lost. :D

[VBLady]

That's just lame. The concat should append up to the null in sAccountno and then add on the end quote. It worked fine in VB6. How do I strip the end nulls from the sAccountno?

[pendragon]

sAccountno = sAccountno.replace(Chr(0), "") worked on my test program.