Solved

Error with string to GUID in SQL server

Posted on 2004-10-06
8
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Six Sigma Control Plans

695 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