Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2000 uniqueidentifier field and using VB .NET

Posted on 2006-06-14
12
Medium Priority
?
1,036 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:Belificus
[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
12 Comments
 
LVL 3

Expert Comment

by:Titan522
ID: 16906009
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.
0
 

Author Comment

by:Belificus
ID: 16906277
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
0
 

Author Comment

by:Belificus
ID: 16906353
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.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16906392
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
0
 

Author Comment

by:Belificus
ID: 16906498
Arthur

I get errors as follows.

Cannot insert the value NULL into column 'fldUniqueID', table 'Members.dbo.tblMembers';coumn 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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16910367
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
0
 

Author Comment

by:Belificus
ID: 16910905
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\MemberTrakker\MemberTrakker\ucAddMembers.vb      27      38      MemberTrakker
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 200 total points
ID: 16911721
ok, this should to it:

 cmdMembers.CommandText = "INSERT INTO tblMembers VALUES(" & UniqueID.ToString() & ",'" & txtFName.Text & "','" & txtLName.Text & "'" & ");"
0
 

Author Comment

by:Belificus
ID: 16911919
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.
0
 
LVL 2

Accepted Solution

by:
gstieger earned 1000 total points
ID: 16930321
The error is probably as a result of the GUID missing the surrounding single quotes. The differences in error is because a different GUID containing e.g. 'c03fb' or 'b56dc' is getting generated on each run.

;-)

Try changing to:

cmdMembers.CommandText = "INSERT INTO tblMembers VALUES('" & UniqueID.ToString() & "','" & txtFName.Text & "','" & txtLName.Text & "'" & ");"

(single quotes added around GUID)
0
 

Author Comment

by:Belificus
ID: 16935885
Thank you gstieger!

I split points on this because I think Arthur was very close.

gstieger 250
Arthur 50

Thanks again for your help!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

636 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