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 users did what actions. I am trying to delete users from an asp datagrid. Heres 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. Youll 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 its trying to look for a column named the GUID. But I dont 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 dont 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. " Whats that all about? Its 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 youre 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 youve 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 GUIDs 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.