?
Solved

Error with string to GUID in SQL server

Posted on 2004-10-06
8
Medium Priority
?
222 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 1000 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Simple Linear Regression
Six Sigma Control Plans
Suggested Courses

800 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