• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

INSERT Command in VB6 to a Table with RowGUID

Ok

I'm doing a simple insert from a VB6 application and the INSERT command gives me the error:

Cannot insert the vaule NULL into column 'rowguid' table.  bla-bla-bla.  Column doesn't allow NULLS.  

How do I get around this?  Is there a way I can generate the ROWGUID, put it into a variable, then insert it?  I'm using an ADO connection created within the code.

Chris, Baltimore.  
0
clebo99
Asked:
clebo99
  • 5
  • 5
  • 3
  • +2
1 Solution
 
eheimerCommented:
What is the SQL command you are using to INSERT?  Use 'debug.print' to get the exact statement before you send it to the database.
0
 
clebo99Author Commented:
I'm using a general INSERT command.  

INSERT into TABLENAME (info1, info2, info3.....) values (var1, var2, var3....) using the "','" and such.   Even if I go into SQL Query Analyzer and try to insert a record I get the same error so it's that ROWGUID column.

Chris
0
 
eheimerCommented:
If you have a field in the table defined as the primary key, you must provide a value for that field when you do the insert.  The alternative is to define that field as "Identity", which would have MSSQL automatically insert an auto-incrementing value without the need to specify it in the INSERT command.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
eheimerCommented:
To clarify, the problem is not with VB6, or your INSERT command, it's probably on the MSSQL side with the definition of your table and specifically the primary key.  Hope that helps.
0
 
clebo99Author Commented:
Unfortunately, I can't change the DB........There has to be a way to insert in this scenario.  
0
 
DonKylesCommented:
If you cannot change anyting on the DB side, you need to get the latest ROWGUID by using select command and plus 1 (see the sql statement below)

SELECT MAX(ROWGUID) + 1 AS 'NewRowGUID'
FROM TableName

now you can use the new ROWGUID to insert the new record
0
 
clebo99Author Commented:
Perfect...i'll give that a try...

Thanks!!!
0
 
amiableansariCommented:
Hi

ROWGUID is autogenerated unique id. check in table design mode that whether its default value has the value (newid()) and IsRowGUID is set to yes. and in the insert statement you dont need to provide it as parameter and value; the statement should be without rowguid.
0
 
clebo99Author Commented:
DonKyles,

I tried your solution just within Query Analyzer and got the following error message:

Server: Msg 409, Level 16, State 2, Line 1
The maximum aggregate operation cannot take a uniqueidentifier data type as an argument.
Server: Msg 206, Level 16, State 1, Line 1
Operand type clash: uniqueidentifier is incompatible with int

I tried to take out the "+1" and got the MSG 409 error only.  Any thoughts?  Also, can you provide the code where I would actually get this information into a VB6 variable called GUID?

Chris
0
 
clebo99Author Commented:
What about just using a randomizer or something?  Do we think that would work?

Chris
0
 
chrsmrtnCommented:
If your rowguid is not compatible with an int, then "auto incrementing" it from the program side will be very difficult.  Does anyone know what data type this "uniqueidentifier" data type is?  I've never heard of it.  What database are you working with?

Also keep in mind that using a program created incremented key is not a good idea.  It's possible to get two of the same numbers.  Basically if two people try to start your insert process at about the same time, both of them will get the same rowguid from your step 1 and when they both try to save their data in step 2 only one of them will make it into the database.

I'd suggest getting access to the database, either physically or through the DB admin.

Overall i think we need to know the database that you are working with, MYSQL, MSSQL, Oracle, etc.
0
 
eheimerCommented:
This article should shed some light on the difference between uniqueidentifier and identity fields: http://www.sqlteam.com/item.asp?ItemID=283

From that article:

Now, unlike an IDENTITY column, a uniqueidentifier column doesn't automagically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()), or do something like the following:

DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()
INSERT Item VALUES (@GUID,'Yak Hoof')

Hope that helps.
0
 
eheimerCommented:
Here's a link to some VB code that will create a GUID for you:

http://www.freevbcode.com/ShowCode.Asp?ID=21
0
 
DonKylesCommented:
What is the variable type of the GUID?

From what I understand your GUID type is not an integer you may need to convert ROWGUID to int by the following code.

SELECT MAX(CONVERT(INT,ROWGUID)) + 1 AS 'NewRowGUID'
FROM TableName

You may need to give me more information about how did you create the table.
0
 
DonKylesCommented:
Oh I got your problem sorry for the privious post

eheimer is correct

DECLARE @ROWGUID uniqueidentifier
SET @ROWGUID = NEWID()
INSERT TableName VALUES (@ROWGUID, 'Data for column1', 'Data for column2', ...)

is your solution.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now