Belificus
asked on
SQL 2000 uniqueidentifier field and using VB .NET
I am trying to learn how to create a unique identifer for an SQL database using VB .NET.
Say this is the table, tblMembers in a database designed to track people.
tblMembers has fields
fldUniqueID as uniqueidentifer
fldFName as varchar
fldLName as varchar
When I try to write to the database I get errors that the type is not compatible with type uniqueidentifier.
I could have used typ Int and simply added 1 each time a new record is created, but I want to learn how to use the uniqueidentifier.
Can anyone explain that to me?
This is just an exercise attempt, here is my user control code off the "Add Member" button...
Public Class ucAddMembers
Private Sub btnAddMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddMember.Click
Dim UniqueID As ?????
Try
cnnMembers.Open()
cmdMembers.Connection = cnnMembers
cmdMembers.CommandText = "INSERT INTO tblMembers VALUES('" & UniqueID & "','" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
cmdMembers.ExecuteNonQuery ()
MsgBox("Record added successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cnnMembers.Close()
End Try
End Sub
End Class
Say this is the table, tblMembers in a database designed to track people.
tblMembers has fields
fldUniqueID as uniqueidentifer
fldFName as varchar
fldLName as varchar
When I try to write to the database I get errors that the type is not compatible with type uniqueidentifier.
I could have used typ Int and simply added 1 each time a new record is created, but I want to learn how to use the uniqueidentifier.
Can anyone explain that to me?
This is just an exercise attempt, here is my user control code off the "Add Member" button...
Public Class ucAddMembers
Private Sub btnAddMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddMember.Click
Dim UniqueID As ?????
Try
cnnMembers.Open()
cmdMembers.Connection = cnnMembers
cmdMembers.CommandText = "INSERT INTO tblMembers VALUES('" & UniqueID & "','" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
cmdMembers.ExecuteNonQuery
MsgBox("Record added successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cnnMembers.Close()
End Try
End Sub
End Class
uniqueidentifier is unique across the database, it should never be repeated in any table. If you are inserting the value then sql server can't be sure you haven't used the value before. Don't give a value for fldUniqueID. When you do the insert sql server will create its own value and stored in the row.
ASKER
Well, I am doing something wrong because SQL did not create a unique ID when the record add attempt was made.
Let me re-create the error messsage and see what it says
Let me re-create the error messsage and see what it says
ASKER
Ok.
When creating the INSERT commandtext, the values are given in the order in which the fields appear.
The error i get if I leave out a variable for the uniqueidentifier is that it cannot convert from type string to type uniqueidentifier.
This is because the INSERT is tyring to put the FNAME text into the first field in the table which is fldUniqueID.
I'd like to learn how to use that table field type, uniqueidentifier.
When creating the INSERT commandtext, the values are given in the order in which the fields appear.
The error i get if I leave out a variable for the uniqueidentifier is that it cannot convert from type string to type uniqueidentifier.
This is because the INSERT is tyring to put the FNAME text into the first field in the table which is fldUniqueID.
I'd like to learn how to use that table field type, uniqueidentifier.
your SQL should read like this:
cmdMembers.CommandText = "INSERT INTO tblMembers (fldFname, fldLName) VALUES('" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
and if the fldUniqueID has been created as Type UniqueIdentifier (which, by the way is the SQL Server equivalent of an Access AUtoNumber field), the fldUniqueID will be automatically created for the record, when it is inserted, so there is no need to provide the value in you insert SQL.
be sure to change (fldFname, fldLName) to the actual names of those two fields in your table.
AW
cmdMembers.CommandText = "INSERT INTO tblMembers (fldFname, fldLName) VALUES('" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
and if the fldUniqueID has been created as Type UniqueIdentifier (which, by the way is the SQL Server equivalent of an Access AUtoNumber field), the fldUniqueID will be automatically created for the record, when it is inserted, so there is no need to provide the value in you insert SQL.
be sure to change (fldFname, fldLName) to the actual names of those two fields in your table.
AW
ASKER
Arthur
I get errors as follows.
Cannot insert the value NULL into column 'fldUniqueID', table 'Members.dbo.tblMembers';c oumn does nto allow nulls. INSERT fails.
The statement has been terminated.
Here is the code:
Try
cnnMembers.Open()
cmdMembers.Connection = cnnMembers
cmdMembers.CommandText = "INSERT INTO tblMembers (fldFname, fldLName) VALUES('" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
cmdMembers.ExecuteNonQuery ()
MsgBox("Record added successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cnnMembers.Close()
End Try
I get errors as follows.
Cannot insert the value NULL into column 'fldUniqueID', table 'Members.dbo.tblMembers';c
The statement has been terminated.
Here is the code:
Try
cnnMembers.Open()
cmdMembers.Connection = cnnMembers
cmdMembers.CommandText = "INSERT INTO tblMembers (fldFname, fldLName) VALUES('" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
cmdMembers.ExecuteNonQuery
MsgBox("Record added successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cnnMembers.Close()
End Try
ok, then you do it this way:
Dim UniqueID As GUID = Guid.NewGuid
Try
cnnMembers.Open()
cmdMembers.Connection = cnnMembers
cmdMembers.CommandText = "INSERT INTO tblMembers VALUES(" & UniqueID & ",'" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
cmdMembers.ExecuteNonQuery ()
MsgBox("Record added successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cnnMembers.Close()
End Try
AW
Dim UniqueID As GUID = Guid.NewGuid
Try
cnnMembers.Open()
cmdMembers.Connection = cnnMembers
cmdMembers.CommandText = "INSERT INTO tblMembers VALUES(" & UniqueID & ",'" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
cmdMembers.ExecuteNonQuery
MsgBox("Record added successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cnnMembers.Close()
End Try
AW
ASKER
This will not build.
Will the GUID create a unique value for each new record added?
Error 1 Operator '&' is not defined for types 'String' and 'System.Guid'. C:\Documents and Settings\binary\My Documents\Visual Studio 2005\Projects\MemberTrakke r\MemberTr akker\ucAd dMembers.v b 27 38 MemberTrakker
Will the GUID create a unique value for each new record added?
Error 1 Operator '&' is not defined for types 'String' and 'System.Guid'. C:\Documents and Settings\binary\My Documents\Visual Studio 2005\Projects\MemberTrakke
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Arthur:
It looks like SQL is returning syntax errors that are in memory.
The msgbox is ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server] Line 1: Incorrect Syntax near 'c03fb'.
c03fb looked liek a memory address so I ran it again and got the same line but with 'b56dc' instead.
It looks like SQL is returning syntax errors that are in memory.
The msgbox is ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server] Line 1: Incorrect Syntax near 'c03fb'.
c03fb looked liek a memory address so I ran it again and got the same line but with 'b56dc' instead.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you gstieger!
I split points on this because I think Arthur was very close.
gstieger 250
Arthur 50
Thanks again for your help!
I split points on this because I think Arthur was very close.
gstieger 250
Arthur 50
Thanks again for your help!