Solved

Error with string to GUID in SQL server

Posted on 2004-10-06
8
217 Views
Last Modified: 2010-04-17
I have created a column in my Clients SQL table of type uniqueidentifier.  The Problem I am having is this.  Since I have done this I am not able to delete data directly (or otherwise) from the table.  I highligh all the fields and values and select delete.. Then I get the following error:


XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"No rows were updated."                

"The data in row was not commited."
"The source: System.data.
"Error Message: Failed parameter value from a string to Guid."

"Correct the errors and retry or press ESC to cancel the change(s)."

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

What did I do to deserve this?  :)...   - or - more importatly.. what can I do to change this.

Thank you,

-TH
0
Comment
Question by:travishaberman
  • 4
  • 4
8 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 12239151
how are you passing the GUID field to the SQL - It MUST be a GUID or UNIQUEIDENTIFIER type value

What code/Language are you using to attempt this?  Are you using a Stored Procedure, or are you attempting to pass the SQL directly?

AW
0
 

Author Comment

by:travishaberman
ID: 12239218
I am using Microsofts Visual Web Developer 2005 Express beta edition.  It comes equipt with SQL server 2005.  My code is VB.  The field value is a uniqueidentifier.  I set the binding field to (newid()) as instructed by another Expert from a previous post.  I can delete the fields if I decide to change the field to something other than uniqueidentifier.  

I am rather new to SQL and VB...

Does that help?

-TH

P.S.

If you feel up to it.. I have another 500 pts on a differt and basic question about SQL.

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12239629
you are using VB.NET then, I take it.

Are you using a Stored Procedure in SQL Server?

AW
0
 

Author Comment

by:travishaberman
ID: 12239691
I don't know what that is...  As I said I am getting the value by placing the value newid() into the binding field..  I dont see any field indicating if I am or not.. How can I tell?..

-TH
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12239838
I do not follow what you are doing.  If you are writing code in Web Developer 2005, then, by definition, you ARE using VB.NET.

Are you generating SQL to do this?  The problem is that in a control, the NewID() is being converted to a STRINGH value, and that is NOT a UniqueIdentifier type.  Can you post some of the code that you have attempted to write.

AW
0
 

Author Comment

by:travishaberman
ID: 12239879

Gladly...here is a function that I made using another program.  It works great.. and the field value for my uniqueidentifier automatically populates..  I just can seem to go in after and delete any or all of the values..  I am happy to show you anything else if it helps.


-TH



Function FinalizeAccount(ByVal userName As String, ByVal password As String, ByVal emailAddress As String, ByVal timeStamp As Date) As Integer
        Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Clients'"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
   
        Dim queryString As String = "INSERT INTO [Account] ([UserName], [Password], [EmailAddress], [TimeStamp]) VALUE" & _
"S (@UserName, @Password, @EmailAddress, @TimeStamp)"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection
   
        Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_userName.ParameterName = "@UserName"
        dbParam_userName.Value = userName
        dbParam_userName.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_userName)
        Dim dbParam_password As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_password.ParameterName = "@Password"
        dbParam_password.Value = password
        dbParam_password.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_password)
        Dim dbParam_emailAddress As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_emailAddress.ParameterName = "@EmailAddress"
        dbParam_emailAddress.Value = emailAddress
        dbParam_emailAddress.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_emailAddress)
        Dim dbParam_timeStamp As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_timeStamp.ParameterName = "@TimeStamp"
        dbParam_timeStamp.Value = timeStamp
        dbParam_timeStamp.DbType = System.Data.DbType.DateTime
        dbCommand.Parameters.Add(dbParam_timeStamp)
   
        Dim rowsAffected As Integer = 0
        dbConnection.Open()
        Try
            rowsAffected = dbCommand.ExecuteNonQuery
        Finally
            dbConnection.Close()
        End Try
   
        Return rowsAffected
    End Function
0
 

Author Comment

by:travishaberman
ID: 12242746
Ok... Here is what I know.. I changed the variable type from uniqueidentifire to varchar.. The thing seems to work fine now.  I still get a string that is a uniqueidentifier but I am now able to delete and change data in the rows.  So, I just have one more  question befor I give you all the 500.  Is there any harm in what I have done?  That is, do I suffer anything by using a varchar for my variable type rather than using the uniqueiden...  

Thank you for all the help.

_TH
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12266952
no, that will work just fine.

AW
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now