Writing a GUID to a SQL table

Hi and merry Christmas to everyone!

I create a guid using the following code:

accountguid = server.createobject ("scriptlet.typelib").guid

and get it to response.write OK to the screen. However, when I try to write
the accountguid to a sql table using the following:

writeToTable = "INSERT into tbl_users(fld_firstname, "&_
" fld_surname,"&_
"fld_userGUID) " &_
" VALUES ('"& Request.Form("fld_firstname") & "', " &_
" '"& Request.Form("fld_surname") & "', " &_
" '"&accountguid&"' )"

I receive an error. Can anyone tell me why?

Thanks!

Phil

Re: Writing a GUID to a SQL table

"Phil Kelly" wrote in message
news:OWUJ4RoCGHA.2704@TK2MSFTNGP11.phx.gbl...
> Hi and merry Christmas to everyone!
>
> I create a guid using the following code:
>
> accountguid = server.createobject ("scriptlet.typelib").guid
>
> and get it to response.write OK to the screen. However, when I try to
write
> the accountguid to a sql table using the following:
>
> writeToTable = "INSERT into tbl_users(fld_firstname, "&_
> " fld_surname,"&_
> "fld_userGUID) " &_
> " VALUES ('"& Request.Form("fld_firstname") & "', " &_
> " '"& Request.Form("fld_surname") & "', " &_
> " '"&accountguid&"' )"
>
> I receive an error. Can anyone tell me why?

Perhaps if you told us what the error was!

What does the response.write show?

Also, what database and version are you using.


http://www.w3schools.com/sql/sql_insert.asp

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)

Perhaps:

writeToTable = "INSERT INTO tbl_users(
fld_firstname, fld_surname, fld_userGUID) VALUES ('" &
Request.Form("fld_firstname") & "', '" &
Request.Form("fld_surname") & "', '" &
accountguid & "')"

Re: Writing a GUID to a SQL table

Hi and thanks for your response!

The response.write displays a perfectly formed GUID, no more. (if I create a
guid using NEWID() and select records and response.write them to the screen
I get exactly the same format (the guid is obviously different though!) so I
know the formats are correct.

The error is something about an unterminated string before the GUID. I don't
have the error to hand at the moment.

I looked at your suggestion and can't actually see where it differes from
mine.

Phil


"McKirahan" wrote in message
news:8ZidnVL3l_r1KC3enZ2dnUVZ_sidnZ2d@comcast.com...
> "Phil Kelly" wrote in message
> news:OWUJ4RoCGHA.2704@TK2MSFTNGP11.phx.gbl...
>> Hi and merry Christmas to everyone!
>>
>> I create a guid using the following code:
>>
>> accountguid = server.createobject ("scriptlet.typelib").guid
>>
>> and get it to response.write OK to the screen. However, when I try to
> write
>> the accountguid to a sql table using the following:
>>
>> writeToTable = "INSERT into tbl_users(fld_firstname, "&_
>> " fld_surname,"&_
>> "fld_userGUID) " &_
>> " VALUES ('"& Request.Form("fld_firstname") & "', " &_
>> " '"& Request.Form("fld_surname") & "', " &_
>> " '"&accountguid&"' )"
>>
>> I receive an error. Can anyone tell me why?
>
> Perhaps if you told us what the error was!
>
> What does the response.write show?
>
> Also, what database and version are you using.
>
>
> http://www.w3schools.com/sql/sql_insert.asp
>
> INSERT INTO table_name (column1, column2,...)
> VALUES (value1, value2,....)
>
> Perhaps:
>
> writeToTable = "INSERT INTO tbl_users(
> fld_firstname, fld_surname, fld_userGUID) VALUES ('" &
> Request.Form("fld_firstname") & "', '" &
> Request.Form("fld_surname") & "', '" &
> accountguid & "')"
>
>

Re: Writing a GUID to a SQL table

Phil Kelly wrote:
> Hi and thanks for your response!
>
> The response.write displays a perfectly formed GUID, no more.

You are answering the wrong question.
We cannot debug a sql statement without knowing what it is. Seeing the code
that generates the statement is only a small part of the story. We need to
see the result of "Response.Write writeToTable "

Also, we need to know the type and version of the database you are using. I
strongly suspect that it is SQL Server, but I dislike guessing ;-)

You may find these posts helpful:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

http://tinyurl.com/jyy0

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Writing a GUID to a SQL table

Bob et al

Sorry if I was a bit non-committal (I did say I wasn't near the machine in
question!)

Anyway, when I choose the submit button to write the data from the form to
the database (Microsoft SQL 2000 SP3a) the results of the
response.write(accountguid) are as follows:

{3D0917C3-996D-456E-B9C6-019C933D6840}

This is then followed by:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Unclosed quotation mark before the character string
'{3D0917C3-996D-456E-B9C6-019C933D6840}'.

/testapp/Step1CreateAccountWrite.asp, line 37

The code which generates this error is:

writeToTable = "INSERT into tbl_users(fld_firstname, "&_
" fld_surname,"&_
"fld_userGUID) " &_
" VALUES ('"& Request.Form("fld_firstname") & "', " &_
" '"& Request.Form("fld_surname") & "', " &_
" '"&accountguid&"' )"

If I substitute NEWID() for accountguid, the row is correctly written to the
table, but I don't want this because I want to store the guid as a variable,
write it to one table and then write it to another table immediately
afterwards.

Thanks for your help!

Phil

"Bob Barrows [MVP]" wrote in message
news:uflS9KvCGHA.740@TK2MSFTNGP12.phx.gbl...
> Phil Kelly wrote:
>> Hi and thanks for your response!
>>
>> The response.write displays a perfectly formed GUID, no more.
>
> You are answering the wrong question.
> We cannot debug a sql statement without knowing what it is. Seeing the
> code that generates the statement is only a small part of the story. We
> need to see the result of "Response.Write writeToTable "
>
> Also, we need to know the type and version of the database you are using.
> I strongly suspect that it is SQL Server, but I dislike guessing ;-)
>
> You may find these posts helpful:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> http://tinyurl.com/jyy0
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Writing a GUID to a SQL table

You still aren't showing us the result of

Response.Write writeToTable

We cannot help until we see this.

Phil Kelly wrote:
> Bob et al
>
> Sorry if I was a bit non-committal (I did say I wasn't near the
> machine in question!)
>
> Anyway, when I choose the submit button to write the data from the
> form to the database (Microsoft SQL 2000 SP3a) the results of the
> response.write(accountguid) are as follows:
>
> {3D0917C3-996D-456E-B9C6-019C933D6840}
>
> This is then followed by:
>
> Microsoft OLE DB Provider for SQL Server error '80040e14'
>
> Unclosed quotation mark before the character string
> '{3D0917C3-996D-456E-B9C6-019C933D6840}'.
>
> /testapp/Step1CreateAccountWrite.asp, line 37
>
> The code which generates this error is:
>
> writeToTable = "INSERT into tbl_users(fld_firstname, "&_
> " fld_surname,"&_
> "fld_userGUID) " &_
> " VALUES ('"& Request.Form("fld_firstname") & "', " &_
> " '"& Request.Form("fld_surname") & "', " &_
> " '"&accountguid&"' )"
>
> If I substitute NEWID() for accountguid, the row is correctly written
> to the table, but I don't want this because I want to store the guid as a
> variable, write it to one table and then write it to another table
> immediately
> afterwards.
>
> Thanks for your help!
>
> Phil
>
> "Bob Barrows [MVP]" wrote in message
> news:uflS9KvCGHA.740@TK2MSFTNGP12.phx.gbl...
>> Phil Kelly wrote:
>>> Hi and thanks for your response!
>>>
>>> The response.write displays a perfectly formed GUID, no more.
>>
>> You are answering the wrong question.
>> We cannot debug a sql statement without knowing what it is. Seeing
>> the code that generates the statement is only a small part of the story.
>> We need to see the result of "Response.Write writeToTable "
>>
>> Also, we need to know the type and version of the database you are
>> using. I strongly suspect that it is SQL Server, but I dislike guessing
>> ;-)
>>
>> You may find these posts helpful:
>> http://mvp.unixwiz.net/techtips/sql-injection.html
>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>>
>> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>>
>> http://tinyurl.com/jyy0
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Writing a GUID to a SQL table

Hi Bob

OK, this is the exact code that I am using:

accountguid = server.createobject ("scriptlet.typelib").guid
response.write(accountguid)

WriteToTable = "INSERT into tbl_users(fld_firstname, "&_
" fld_surname, "&_
" fld_address1, "&_
" fld_address2, "&_
" fld_city, "&_
" fld_county, "&_
" fld_postcode, "&_
" fld_country, "&_
" fld_accountGUID) "&_
" VALUES "&_
" ('"& Request.Form("fld_firstname") &"', "&_
" '"& Request.Form("fld_surname") &"', "&_
" '"& Request.Form("fld_address1") &"', "&_
" '"& Request.Form("fld_address2") &"', "&_
" '"& Request.Form("fld_city") &"', "&_
" '"& Request.Form("fld_county") &"', "&_
" '"& Request.Form("fld_postcode") &"', "&_
" '"& Request.Form("fld_country") &"', "&_
" '"&accountguid&"')"

'Set CreateAccountAction = Connect5.Execute(writeToTable)
response.write(Connect5.Execute(writeToTable))

When I execute this code (to provide the response.write), I get the
following (which appears identical to the error I gave earlier, apart from
the line number of the error):

{DA0FCCBE-E3C2-42CA-8505-37080DD1D8D2}
Microsoft OLE DB Provider for SQL Server error '80040e14'

Unclosed quotation mark before the character string
'{DA0FCCBE-E3C2-42CA-8505-37080DD1D8D2}'.

/testapp/Step1CreateAccountWrite.asp, line 38

Does it make sense now?

"Bob Barrows [MVP]" wrote in message
news:e4cdvlxCGHA.3980@TK2MSFTNGP12.phx.gbl...
> You still aren't showing us the result of
>
> Response.Write writeToTable
>
> We cannot help until we see this.
>
> Phil Kelly wrote:
>> Bob et al
>>
>> Sorry if I was a bit non-committal (I did say I wasn't near the
>> machine in question!)
>>
>> Anyway, when I choose the submit button to write the data from the
>> form to the database (Microsoft SQL 2000 SP3a) the results of the
>> response.write(accountguid) are as follows:
>>
>> {3D0917C3-996D-456E-B9C6-019C933D6840}
>>
>> This is then followed by:
>>
>> Microsoft OLE DB Provider for SQL Server error '80040e14'
>>
>> Unclosed quotation mark before the character string
>> '{3D0917C3-996D-456E-B9C6-019C933D6840}'.
>>
>> /testapp/Step1CreateAccountWrite.asp, line 37
>>
>> The code which generates this error is:
>>
>> writeToTable = "INSERT into tbl_users(fld_firstname, "&_
>> " fld_surname,"&_
>> "fld_userGUID) " &_
>> " VALUES ('"& Request.Form("fld_firstname") & "', " &_
>> " '"& Request.Form("fld_surname") & "', " &_
>> " '"&accountguid&"' )"
>>
>> If I substitute NEWID() for accountguid, the row is correctly written
>> to the table, but I don't want this because I want to store the guid as a
>> variable, write it to one table and then write it to another table
>> immediately
>> afterwards.
>>
>> Thanks for your help!
>>
>> Phil
>>
>> "Bob Barrows [MVP]" wrote in message
>> news:uflS9KvCGHA.740@TK2MSFTNGP12.phx.gbl...
>>> Phil Kelly wrote:
>>>> Hi and thanks for your response!
>>>>
>>>> The response.write displays a perfectly formed GUID, no more.
>>>
>>> You are answering the wrong question.
>>> We cannot debug a sql statement without knowing what it is. Seeing
>>> the code that generates the statement is only a small part of the story.
>>> We need to see the result of "Response.Write writeToTable "
>>>
>>> Also, we need to know the type and version of the database you are
>>> using. I strongly suspect that it is SQL Server, but I dislike guessing
>>> ;-)
>>>
>>> You may find these posts helpful:
>>> http://mvp.unixwiz.net/techtips/sql-injection.html
>>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>>>
>>> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>>>
>>> http://tinyurl.com/jyy0
>>>
>>> --
>>> Microsoft MVP - ASP/ASP.NET
>>> Please reply to the newsgroup. This email account is my spam trap so
>>> I don't check it very often. If you must reply off-line, then remove
>>> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Writing a GUID to a SQL table

"Phil Kelly" wrote in message
news:em#ToIyCGHA.2912@tk2msftngp13.phx.gbl...
> Hi Bob
>
> OK, this is the exact code that I am using:
>
> accountguid = server.createobject ("scriptlet.typelib").guid
> response.write(accountguid)
>
> WriteToTable = "INSERT into tbl_users(fld_firstname, "&_
> " fld_surname, "&_
> " fld_address1, "&_
> " fld_address2, "&_
> " fld_city, "&_
> " fld_county, "&_
> " fld_postcode, "&_
> " fld_country, "&_
> " fld_accountGUID) "&_
> " VALUES "&_
> " ('"& Request.Form("fld_firstname") &"', "&_
> " '"& Request.Form("fld_surname") &"', "&_
> " '"& Request.Form("fld_address1") &"', "&_
> " '"& Request.Form("fld_address2") &"', "&_
> " '"& Request.Form("fld_city") &"', "&_
> " '"& Request.Form("fld_county") &"', "&_
> " '"& Request.Form("fld_postcode") &"', "&_
> " '"& Request.Form("fld_country") &"', "&_
> " '"&accountguid&"')"
>
> 'Set CreateAccountAction = Connect5.Execute(writeToTable)
> response.write(Connect5.Execute(writeToTable))
>
> When I execute this code (to provide the response.write), I get the
> following (which appears identical to the error I gave earlier, apart from
> the line number of the error):
>
> {DA0FCCBE-E3C2-42CA-8505-37080DD1D8D2}
> Microsoft OLE DB Provider for SQL Server error '80040e14'
>
> Unclosed quotation mark before the character string
> '{DA0FCCBE-E3C2-42CA-8505-37080DD1D8D2}'.
>
> /testapp/Step1CreateAccountWrite.asp, line 38
>
> Does it make sense now?
>
[snip]

A Response.Write does not execute the SQL statement;
it just displays the contents of the variable "WriteToTable".

Please, just run it with these two lines after "WriteToTable =":

Response.Write(WriteToTable)
Response.End

Post what is displayed.

Re: Writing a GUID to a SQL table

INSERT into tbl_accounts(fld_firstname, fld_surname, fld_address1,
fld_address2, fld_city, fld_county, fld_postcode, fld_country,
fld_accountGUID) VALUES ('Firstname', 'Surname', 'addr1', 'addr2', 'Bury',
'Lancashire', 'BB11BB', 'GB', '{1E0E9020-8025-4A11-8B8A-DF2F29882054}

Which I assume tells me that the INSERT statement is not correctly
terminated - but looking at the code it seems to be???



"McKirahan" wrote in message
news:EdWdnR4ZRN2INCzeRVn-ig@comcast.com...
> "Phil Kelly" wrote in message
> news:em#ToIyCGHA.2912@tk2msftngp13.phx.gbl...
>> Hi Bob
>>
>> OK, this is the exact code that I am using:
>>
>> accountguid = server.createobject ("scriptlet.typelib").guid
>> response.write(accountguid)
>>
>> WriteToTable = "INSERT into tbl_users(fld_firstname, "&_
>> " fld_surname, "&_
>> " fld_address1, "&_
>> " fld_address2, "&_
>> " fld_city, "&_
>> " fld_county, "&_
>> " fld_postcode, "&_
>> " fld_country, "&_
>> " fld_accountGUID) "&_
>> " VALUES "&_
>> " ('"& Request.Form("fld_firstname") &"', "&_
>> " '"& Request.Form("fld_surname") &"', "&_
>> " '"& Request.Form("fld_address1") &"', "&_
>> " '"& Request.Form("fld_address2") &"', "&_
>> " '"& Request.Form("fld_city") &"', "&_
>> " '"& Request.Form("fld_county") &"', "&_
>> " '"& Request.Form("fld_postcode") &"', "&_
>> " '"& Request.Form("fld_country") &"', "&_
>> " '"&accountguid&"')"
>>
>> 'Set CreateAccountAction = Connect5.Execute(writeToTable)
>> response.write(Connect5.Execute(writeToTable))
>>
>> When I execute this code (to provide the response.write), I get the
>> following (which appears identical to the error I gave earlier, apart
>> from
>> the line number of the error):
>>
>> {DA0FCCBE-E3C2-42CA-8505-37080DD1D8D2}
>> Microsoft OLE DB Provider for SQL Server error '80040e14'
>>
>> Unclosed quotation mark before the character string
>> '{DA0FCCBE-E3C2-42CA-8505-37080DD1D8D2}'.
>>
>> /testapp/Step1CreateAccountWrite.asp, line 38
>>
>> Does it make sense now?
>>
> [snip]
>
> A Response.Write does not execute the SQL statement;
> it just displays the contents of the variable "WriteToTable".
>
> Please, just run it with these two lines after "WriteToTable =":
>
> Response.Write(WriteToTable)
> Response.End
>
> Post what is displayed.
>
>

Re: Writing a GUID to a SQL table

"Phil Kelly" wrote in message
news:uTUMhqzCGHA.2040@TK2MSFTNGP14.phx.gbl...
> INSERT into tbl_accounts(fld_firstname, fld_surname, fld_address1,
> fld_address2, fld_city, fld_county, fld_postcode, fld_country,
> fld_accountGUID) VALUES ('Firstname', 'Surname', 'addr1', 'addr2', 'Bury',
> 'Lancashire', 'BB11BB', 'GB', '{1E0E9020-8025-4A11-8B8A-DF2F29882054}
>
> Which I assume tells me that the INSERT statement is not correctly
> terminated - but looking at the code it seems to be???


Where's the closing single quotation mark?

Re: Writing a GUID to a SQL table

> Where's the closing single quotation mark?

And the closing bracket???

It's there in the code.....


"McKirahan" wrote in message
news:3dedneY_zeqJVizenZ2dnUVZ_sednZ2d@comcast.com...
> "Phil Kelly" wrote in message
> news:uTUMhqzCGHA.2040@TK2MSFTNGP14.phx.gbl...
>> INSERT into tbl_accounts(fld_firstname, fld_surname, fld_address1,
>> fld_address2, fld_city, fld_county, fld_postcode, fld_country,
>> fld_accountGUID) VALUES ('Firstname', 'Surname', 'addr1', 'addr2',
>> 'Bury',
>> 'Lancashire', 'BB11BB', 'GB', '{1E0E9020-8025-4A11-8B8A-DF2F29882054}
>>
>> Which I assume tells me that the INSERT statement is not correctly
>> terminated - but looking at the code it seems to be???
>
>
> Where's the closing single quotation mark?
>
>

Re: Writing a GUID to a SQL table

Phil Kelly wrote:
> INSERT into tbl_accounts(fld_firstname, fld_surname, fld_address1,
> fld_address2, fld_city, fld_county, fld_postcode, fld_country,
> fld_accountGUID) VALUES ('Firstname', 'Surname', 'addr1', 'addr2',
> 'Bury', 'Lancashire', 'BB11BB', 'GB',
> '{1E0E9020-8025-4A11-8B8A-DF2F29882054}
> Which I assume tells me that the INSERT statement is not correctly
> terminated - but looking at the code it seems to be???
>

What do you see when you do this:

Response.Write vartype(accountguid)

I'm thinking that you may need to explicitly cast accountguid as a string
before attempting to concatenate it into your dynamic sql (ugh!) statement.
Like this:
accountguid=cstr(accountguid)

My suspicion is that an error is occurring when attempting to do that final
concatenation, an error that is being masked by an "on error resume next"
statement, which explains why the ending quote and closing parenthesis are
missing from the result.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Writing a GUID to a SQL table

> What do you see when you do this:
>
> Response.Write vartype(accountguid)


This returns an 8 character - a string value?

> I'm thinking that you may need to explicitly cast accountguid as a string
> before attempting to concatenate it into your dynamic sql (ugh!)
> statement. Like this:
> accountguid=cstr(accountguid)

The problem remains the same when I attempt this!

> My suspicion is that an error is occurring when attempting to do that
> final concatenation, an error that is being masked by an "on error resume
> next" statement, which explains why the ending quote and closing
> parenthesis are missing from the result.

No 'on error resume next' exists in this code. :(

Re: Writing a GUID to a SQL table

Phil Kelly wrote:
>> What do you see when you do this:
>>
>> Response.Write vartype(accountguid)
>
>
> This returns an 8 character - a string value?
>
>> I'm thinking that you may need to explicitly cast accountguid as a
>> string before attempting to concatenate it into your dynamic sql
>> (ugh!) statement. Like this:
>> accountguid=cstr(accountguid)
>
> The problem remains the same when I attempt this!
>
>> My suspicion is that an error is occurring when attempting to do that
>> final concatenation, an error that is being masked by an "on error
>> resume next" statement, which explains why the ending quote and
>> closing parenthesis are missing from the result.
>
> No 'on error resume next' exists in this code. :(

I've just done some testing and realized you need to trim the final two
characters from the guid before attempting to use it, like this:

accountguid = left(accountguid,38)


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Writing a GUID to a SQL table

Bob Barrows [MVP] wrote:
> Phil Kelly wrote:
>>> What do you see when you do this:
>>>
>>> Response.Write vartype(accountguid)
>>
>>
>> This returns an 8 character - a string value?
>>
>>> I'm thinking that you may need to explicitly cast accountguid as a
>>> string before attempting to concatenate it into your dynamic sql
>>> (ugh!) statement. Like this:
>>> accountguid=cstr(accountguid)
>>
>> The problem remains the same when I attempt this!
>>
>>> My suspicion is that an error is occurring when attempting to do
>>> that final concatenation, an error that is being masked by an "on
>>> error resume next" statement, which explains why the ending quote
>>> and closing parenthesis are missing from the result.
>>
>> No 'on error resume next' exists in this code. :(
>
> I've just done some testing and realized you need to trim the final
> two characters from the guid before attempting to use it, like this:
>
> accountguid = left(accountguid,38)

And I suspect this is the reason:
http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/17f9befc31631ca0


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Writing a GUID to a SQL table

Bob Barrows [MVP] wrote:
>
> And I suspect this is the reason:
> http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/17f9befc31631ca0
Confirmed:
http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/856f34c6b04ce55e
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Writing a GUID to a SQL table

>I've just done some testing and realized you need to trim the final two
>characters from the guid before attempting to use it, like this:
>
>accountguid = left(accountguid,38)

Bob - Top Answer! Thanks a lot for that - I owe you all a pint!

Why is it that the guid value needs to be trimmed though?

Thanks again; it worked perfectly :-)

Re: Writing a GUID to a SQL table

Excellent! Thanks Bob - well tracked down.


"Bob Barrows [MVP]" wrote in message
news:O0pJBF0CGHA.3984@TK2MSFTNGP14.phx.gbl...
> Bob Barrows [MVP] wrote:
>>
>> And I suspect this is the reason:
>> http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/17f9befc31631ca0
> Confirmed:
> http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/856f34c6b04ce55e
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Writing a GUID to a SQL table

"Phil Kelly" wrote in message
news:OjPtaF0CGHA.1032@TK2MSFTNGP11.phx.gbl...
> >I've just done some testing and realized you need to trim the final two
> >characters from the guid before attempting to use it, like this:
>>
>>accountguid = left(accountguid,38)
>
> Bob - Top Answer! Thanks a lot for that - I owe you all a pint!
>
> Why is it that the guid value needs to be trimmed though?

Len(accountguid) might answer that. If greater than 38, there's your
answer: extra junk is being tacked-on to the string, as rendered by the
object.property you're using

A guid, as commonly shown in squiggly bracekets, is a text representation of
a binary structure (mostly for COM registration purposes, I think, since
registry key names must be strings.) This happens to coincide with the SQL
type uniqueidentifier, which is implicitly converted from text by SQL Server
[according to the BOL] as such: "a string constant in the following form
(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal
digit in the range 0-9 or a-f). For example,
6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value."
(Evidently, it ignores the squigglies if present, but experiments show that
if either is present, both must be present.)

I suspect that SQL stores uniqueidentifier identically to binary(16), but
apparently treats it as a structure internally: the first segment represents
a 32 bit integer; the second and third segments each represent 16 bit
integers; the last two segments represent bit streams. (It flips the endian
of the first three segments as part of the conversion to/from hex-text --
this can be verified by a simple T-SQL script, if anyone is interested.)

If you will never be generating these values from within SQL (via call to
NEWID) you might be better off storing them in your table as varchar (or
char(38)), just to avoid implicit conversion to/from string virtually every
time you handle them. This would also make the values easier to pass as
parameters... (The obvious downside being that strings will consume more
space on disk.)


-Mark




> Thanks again; it worked perfectly :-)
>