Column Data Type. 2

[coorsman]

Hi All,
I'm working on an old MSSQL database and need to add records to it.
I'm not sure what the reference strings are on the first column or how to create them. Will they auto populate when adding new data or do I need to create these for each new row.

C91ED0C6-05D2-4C92-9CB3-00006FB63DA1
DE7A2069-7A9D-494D-9FFA-00013371351C

Cheers,
Coorsman

 

[Andrzejek]

I would check which fields are required in this table, provide some test data, Insert a test record and see if Row field is auto-populated.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dgillz]

Looks like a GUID field to me. Can you confirm?

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Andrzejek]

dgillz, you may be right.
This [tt]Row[/tt] field (an unfortunate name for the field, if you ask me) looks like a GUID data type

Here is some more info about it: Understanding the GUID data type in SQL Server

coorsman, you may verify this by checking how this field has been defined, its Data Type

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[coorsman]

Thanks for all the answers.
Yes, it is a GUID.

Thanks everyone for your input.


Cheers,
Coorsman

 

[dgillz]

Coorsman,

I have an excel VBA function that creates a GUID value. I tested it on 1 million records and it never duplicated. Will that help?

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[coorsman]

Thanks,
I'm using the following and it seems to be working.

Private Sub InsertDataIntoSQL()
		Dim query As String = String.Empty
		query &= "INSERT INTO QCITT.dbo.DailySales (Row,Location,TransactionDate,CheckbookID,Description,DailyTotal,Account,DistributionReference,Debit,Credit,Type,ShiftType,InGP)"
		query &= "VALUES (NEWID(),@Location,@TransactionDate,@CheckbookID,@Description,@DailyTotal,@Account,@DistributionReference,@Debit,@Credit,@Type,@ShiftType,@InGP)"
		Dim consString As String
		If cbTrustedConnection.Checked = True Then
			consString = ("Server=" & tbComputerName.Text & ";Database=" & tbSQLDatabase.Text & ";user=" & tbSQLUser.Text & ";Trusted_Connection=true")
		Else
			consString = ("Server=" & tbSQLServerName.Text & ";Database=" & tbSQLDatabase.Text & ";user=" & tbSQLUser.Text & ";password=" & tbSQLPassword.Text)
		End If
		Using conn As New SqlConnection(consString)
			Using comm As New SqlCommand()
				With comm
					.Connection = conn
					.CommandType = CommandType.Text
					.CommandText = query
					'.Parameters.AddWithValue("@Row", NEWID())
					.Parameters.AddWithValue("@Location", "Test1")
					.Parameters.AddWithValue("@TransactionDate", "01/02/2024")
					.Parameters.AddWithValue("@checkbookID", "Test3")
					.Parameters.AddWithValue("@Description", "Test4")
					.Parameters.AddWithValue("@DailyTotal", "0")
					.Parameters.AddWithValue("@Account", "0")
					.Parameters.AddWithValue("@DistributionReference", "Test5")
					.Parameters.AddWithValue("@Debit", "0")
					.Parameters.AddWithValue("@Credit", "0")
					.Parameters.AddWithValue("@Type", "0")
					.Parameters.AddWithValue("@ShiftType", "0")
					.Parameters.AddWithValue("@InGP", "0")
				End With
				Try
					conn.Open()
					comm.ExecuteNonQuery()
				Catch ex As SqlException
					MsgBox(ex.Message, "Error Message")
				End Try
			End Using
		End Using

	End Sub

Cheers,
Coorsman

 

[Andrzejek]

It would be nice to see the CREATE TABLE statement, because if it looks something like;
[pre]

CREATE TABLE DailySales
(
Row [blue]UNIQUEIDENTIFIER PRIMARY KEY default NEWID()[/blue],
Location VARCHAR (50),
...
[/pre]
you do not need to mention Row field anywhere in your INSERT statement, it will be populated 'auto-magically'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[coorsman]

Hi Andy,
This was an existing database that interfaces with an accounting program and was written by their previous company. They want me to continue using it and update the data daily, I've never seen and wasn't sure what the GUID column was, all of you have helped me get over that hurdle.
Thanks,
KB



Cheers,
Coorsman

 

[Chris Miller]

Coorsman,

you introduced NEWID() in the VALUES of the INSERT statement you execute.

What Andy is stating is that you could not only comment the '.Parameters.AddWithValue("@Row", NEWID()) as you did and put this in directly into the SQL insert, it could simple by an ID field the SQL Server sets when you skip it, i.e. try this:

Private Sub InsertDataIntoSQL()
		Dim query As String = String.Empty
		query &= "INSERT INTO QCITT.dbo.DailySales (Location,TransactionDate,CheckbookID,Description,DailyTotal,Account,DistributionReference,Debit,Credit,Type,ShiftType,InGP)"
		query &= "VALUES (@Location,@TransactionDate,@CheckbookID,@Description,@DailyTotal,@Account,@DistributionReference,@Debit,@Credit,@Type,@ShiftType,@InGP)"
		Dim consString As String
		If cbTrustedConnection.Checked = True Then
			consString = ("Server=" & tbComputerName.Text & ";Database=" & tbSQLDatabase.Text & ";user=" & tbSQLUser.Text & ";Trusted_Connection=true")
		Else
			consString = ("Server=" & tbSQLServerName.Text & ";Database=" & tbSQLDatabase.Text & ";user=" & tbSQLUser.Text & ";password=" & tbSQLPassword.Text)
		End If
		Using conn As New SqlConnection(consString)
			Using comm As New SqlCommand()
				With comm
					.Connection = conn
					.CommandType = CommandType.Text
					.CommandText = query
					.Parameters.AddWithValue("@Location", "Test1")
					.Parameters.AddWithValue("@TransactionDate", "01/02/2024")
					.Parameters.AddWithValue("@checkbookID", "Test3")
					.Parameters.AddWithValue("@Description", "Test4")
					.Parameters.AddWithValue("@DailyTotal", "0")
					.Parameters.AddWithValue("@Account", "0")
					.Parameters.AddWithValue("@DistributionReference", "Test5")
					.Parameters.AddWithValue("@Debit", "0")
					.Parameters.AddWithValue("@Credit", "0")
					.Parameters.AddWithValue("@Type", "0")
					.Parameters.AddWithValue("@ShiftType", "0")
					.Parameters.AddWithValue("@InGP", "0")
				End With
				Try
					conn.Open()
					comm.ExecuteNonQuery()
				Catch ex As SqlException
					MsgBox(ex.Message, "Error Message")
				End Try
			End Using
		End Using

	End Sub

That GUID seems to be the ID of the records and in general IDs, no matter what column data type, are generated by the database server, never by a client, so these fields are skipped in inserts and will still have a value once the record is created by the INSERT sql statement.

You usually will want to know the generated ID value to be able to refer to the new record by its ID, that's the usual problem, not knowing what data type is used for the ID. I guess you'll come to this point quite soon, no matter if you stay with your solution or try my shortened version. You don't know the value generated by NEWID()

Chriss