Using GUID's

[tehon3299]

I have a GUID associated with each user in my SQL database. Each user has a unique GUID and it is in every table so I can sort out what user's did what actions. I am trying to delete users from an asp datagrid. Here's the code...I get an error when I try to delete a user.

		Sub MyDataGrid_DeleteCommand(s As Object, e As DataGridCommandEventArgs)
			Dim strConn as String = "server=localhost;database=checking;Trusted_Connection=yes"
			Dim DeleteCmd As String = "DELETE from tmUsers2 Where GUID = @Id"
			Dim MyConn as New SQLConnection(strConn)
			Dim Cmd as New SqlCommand(DeleteCmd, MyConn)
			Cmd.Parameters.Add(New SqlParameter("@ID", MyDataGrid.DataKeys(CInt(e.Item.ItemIndex))))
			MyConn.Open()
			Cmd.ExecuteNonQuery()
			BindData
			
			DeleteUser(s, e)
		End Sub
		
		Sub DeleteUser(s As Object, e As DataGridCommandEventArgs)
			Dim strConn as String = "server=localhost;database=checking;Trusted_Connection=yes"
			Dim DeleteCmd As String = "DELETE from tmUserInfo2 Where GUID = @Id"
			Dim MyConn as New SQLConnection(strConn)
			Dim Cmd as New SqlCommand(DeleteCmd, MyConn)
			Cmd.Parameters.Add(New SqlParameter("@ID", MyDataGrid.DataKeys(CInt(e.Item.ItemIndex))))
			MyConn.Open()
			Cmd.ExecuteNonQuery()
			BindData
		End Sub

 

Thanks

[quwiltw]

Can you give some insight into what exactly the error is? It saves us from just blindly analyzing your code without any focus. You'll likely find that your questions get answered quicker that way too.

[Heiko]

Just a wild guess, but I suppose you can not delete the user, because his or her GUID is still being referenced to in the other tables.

 

Are you using relations in the database?

 

HTH

Heiko

[tehon3299]

It says something about an invalid column name "DF6589-....". The GUID is in quotes. It looks like it's trying to look for a column named the GUID. But I don't know?? Any ideas??

 

Thanks

[quwiltw]

Are you sure you have a Column in your database named GUID? Also, it looks like you have all this in a ?dataset? bound to a datagrid? If so, why not just let the dataadapter do the dirty work for you and just call its Update method with your delete command handler?

[tehon3299]

My table does have a GUID column. Heres more code:

	<asp: Datagrid runat="server"
				Id="MyDataGrid"
				GridLines="Both"
				cellpadding="0"
				cellspacing="0"
				Headerstyle-BackColor="#8080C0"
				Headerstyle-Font-Name="Times New Roman"
				Headerstyle-Font-Bold="True"
				Headerstyle-Font-Size="14"
				BackColor="#8080FF"
				Font-Name="Times New Roman"
				Font-Size="14"
				AlternatingItemStyle-BackColor="#C0C0C0"
				AlternatingItemStyle-Font-Name="Times New Roman"
				AlternatingItemStyle-Font-Size="14"
				BorderColor="Black"
				AllowPaging = "True"
				PageSize = "8"
				PagerStyle-Mode = "NumericPages"
				PagerStyle-HorizontalAlign="Center"
				PagerStyle-PageButtonCount = "10"
				OnPageIndexChanged = "Page_Change"
				AutogenerateColumns="False"
				OnDeleteCommand="MyDataGrid_DeleteCommand"
				DataKeyField="id"
				Width="50%">
				<Columns>
					<asp:ButtonColumn Text="Delete" HeaderText="Delete" CommandName="Delete"></asp:ButtonColumn>
					<asp:BoundColumn DataField="UserID" HeaderText="User ID"></asp:BoundColumn>
					<asp:BoundColumn DataField="Password" HeaderText="Password"></asp:BoundColumn>
				</Columns>
			</asp: DataGrid>

 

I don't know where this error comes from??

 

Thanks

[tehon3299]

OK - The problem that I have now is that it says "Syntax error converting the nvarchar value 'DC962722-1825-4256-B0B1-E532D5A4942C ' to a column of data type int. " What's that all about? It's pointing to the ExecuteNonQuery line:

		Sub MyDataGrid_DeleteCommand(s As Object, e As DataGridCommandEventArgs)
			Dim strConn as String = "server=localhost;database=checking;Trusted_Connection=yes"
			Dim DeleteCmd As String = "DELETE from tmUsers2 Where GUID = @ID"
			Dim MyConn as New SQLConnection(strConn)
			Dim Cmd as New SqlCommand(DeleteCmd, MyConn)
			Cmd.Parameters.Add(New SqlParameter("@ID", MyDataGrid.DataKeys(CInt(e.Item.ItemIndex))))
			MyConn.Open()
			Cmd.ExecuteNonQuery()
			BindData
			
			DeleteUser(s, e)
		End Sub

 

Any ideas??

[quwiltw]

A GUID is not an int, which is what you're trying to convert it to with CInt().

[Heiko]

CInt(e.Item.ItemIndex)

Itemindex however, probably *is* an int :-)

[quwiltw]

Yeah, I should learn not to answer based on first glance:( Either way you've clearly got a datatyping problem of some sort. If GUID is truly a uniqueidentifier, I think you have to put it in single quotes. You also might want to use Option Strict too as it seems that many of your problems are a result of wrong datatypes.

[tehon3299]

Now my GUID column in the table is an nvarchar(50). Can I convert the Itemindex to a nvarchar?

[Heiko]

Oops.

 

GUIDs are 36 characters.

 

You can "produce" them via

Private aGUID as string = Guid.NewGuid.ToString

 

As (these) GUIDs contain hyphens, there is no way to convert them to integers. However the are (guaranteed) unique. Worldwide.

 

Is that the same sort of GUID that you are talking of, when you say GUID ?

[tehon3299]

Yes that is the exact was that I am producing them. I just need to be able to take that code I have and use GUID's in it. I was previously using an ID which was an int but I want to use the GUID now.

[Heiko]

Ah. You probably have to chance the AddParameter line, cause currently you are adding an int parameter. this needs to be changed. See the help for addparameter, as I will not be online again till monday 11am GMT.