Question Problem with inserting a new row with GUIDs

O

ontrack5

New member
Joined
Jan 11, 2012
Messages
2
Programming Experience
10+
I'm having huge issues adding a new record to SQL server using the following...

The GUID for the vendorID is no problem....however the vendorType doesn't work. I have stepped though the code and know a value is getting assigned...when I do a SQL trace, however, it shows null.....but only for the vendorType....all the other fields are fine. Also, if I do an update to an existing record, it stores the GUIDs fine....just not on the INSERTS.

Any ideas?

Thanks,
Craig

Dim dsNewRow As DataRow
dsNewRow = VendorRecordDS1.tbl_vendors.NewRow

Dim venType As Guid = New Guid(ddType.SelectedValue.ToString)
dsNewRow.Item("vendorType") = venType
dsNewRow.Item("vendorID") = System.Guid.NewGuid.ToString()
dsNewRow.Item("vendorName") = txtName.Text
dsNewRow.Item("vendorFirstName") = txtFirstName.Text
dsNewRow.Item("vendorLastName") = txtLastName.Text
dsNewRow.Item("vendorAddress") = txtAddress1.Text
'additional lines skipped
VendorRecordDS1.tbl_vendors.Rows.Add(dsNewRow)
Tbl_vendorsTableAdapter1.Update(VendorRecordDS1.tbl_vendors)
Me.Tbl_vendorsTableAdapter.Fill(Me.VendorEditDS.tbl_vendors)
 
Sort by date Sort by votes
Here is the insert code

Updating works fine, it's the insert that doesn't.....so I'm posting the insert stuff from the designer.....if you want to see the update, I can post that too....


Me._adapter.InsertCommand.Connection = Me.Connection
Me._adapter.InsertCommand.CommandText = "INSERT INTO [tbl_vendors] ([vendorID], [vendorName], [vendorFirstName], [vendorLa"& _
"stName], [vendorAddress], [vendorAddress2], [vendorCity], [vendorState], [vendor"& _
"Zip], [vendorContact], [vendorPhone], [vendorContactEmail], [vendorContact2], [v"& _
"endorPhone2], [vendorContactEmail2], [vendorMainPhone], [vendorFAX], [vendorWebs"& _
"ite], [vendorWebID], [vendorWebPassword], [vendorAccountNum], [vendorBalance], ["& _
"vendorCreditLimit], [vendorTerms], [vendorType]) VALUES (@vendorID, @vendorName,"& _
" @vendorFirstName, @vendorLastName, @vendorAddress, @vendorAddress2, @vendorCity"& _
", @vendorState, @vendorZip, @vendorContact, @vendorPhone, @vendorContactEmail, @"& _
"vendorContact2, @vendorPhone2, @vendorContactEmail2, @vendorMainPhone, @vendorFA"& _
"X, @vendorWebsite, @vendorWebID, @vendorWebPassword, @vendorAccountNum, @vendorB"& _
"alance, @vendorCreditLimit, @vendorTerms, @vendorType);"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"SELECT vendorID, vendor"& _
"Name, vendorFirstName, vendorLastName, vendorAddress, vendorAddress2, vendorCity"& _
", vendorState, vendorZip, vendorContact, vendorPhone, vendorContactEmail, vendor"& _
"Contact2, vendorPhone2, vendorContactEmail2, vendorMainPhone, vendorFAX, vendorW"& _
"ebsite, vendorWebID, vendorWebPassword, vendorAccountNum, vendorBalance, vendorC"& _
"reditLimit, vendorTerms, vendorType FROM tbl_vendors WHERE (vendorID = @vendorID"& _
")"
Me._adapter.InsertCommand.CommandType = Global.System.Data.CommandType.Text
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorID", Global.System.Data.SqlDbType.UniqueIdentifier, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorID", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorName", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorName", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorFirstName", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorFirstName", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorLastName", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorLastName", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorAddress", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorAddress", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorAddress2", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorAddress2", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorCity", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorCity", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorState", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorState", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorZip", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorZip", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorContact", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorContact", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorPhone", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorPhone", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorContactEmail", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorContactEmail", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorContact2", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorContact2", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorPhone2", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorPhone2", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorContactEmail2", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorContactEmail2", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorMainPhone", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorMainPhone", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorFAX", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorFAX", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorWebsite", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorWebsite", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorWebID", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorWebID", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorWebPassword", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorWebPassword", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorAccountNum", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorAccountNum", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorBalance", Global.System.Data.SqlDbType.Money, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorBalance", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorCreditLimit", Global.System.Data.SqlDbType.Money, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorCreditLimit", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorTerms", Global.System.Data.SqlDbType.UniqueIdentifier, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorTerms", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@vendorType", Global.System.Data.SqlDbType.UniqueIdentifier, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "vendorType", Global.System.Data.DataRowVersion.Current, False, Nothing, "", "", ""))

------------------------------------------------------

<Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
Global.System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0"), _
Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter"), _
Global.System.ComponentModel.DataObjectMethodAttribute(Global.System.ComponentModel.DataObjectMethodType.Insert, true)> _
Public Overloads Overridable Function Insert( _
ByVal vendorID As System.Guid, _
ByVal vendorName As String, _
ByVal vendorFirstName As String, _
ByVal vendorLastName As String, _
ByVal vendorAddress As String, _
ByVal vendorAddress2 As String, _
ByVal vendorCity As String, _
ByVal vendorState As String, _
ByVal vendorZip As String, _
ByVal vendorContact As String, _
ByVal vendorPhone As String, _
ByVal vendorContactEmail As String, _
ByVal vendorContact2 As String, _
ByVal vendorPhone2 As String, _
ByVal vendorContactEmail2 As String, _
ByVal vendorMainPhone As String, _
ByVal vendorFAX As String, _
ByVal vendorWebsite As String, _
ByVal vendorWebID As String, _
ByVal vendorWebPassword As String, _
ByVal vendorAccountNum As String, _
ByVal vendorBalance As Global.System.Nullable(Of Decimal), _
ByVal vendorCreditLimit As Global.System.Nullable(Of Decimal), _
ByVal vendorTerms As Global.System.Nullable(Of Global.System.Guid), _
ByVal vendorType As Global.System.Nullable(Of Global.System.Guid)) As Integer
Me.Adapter.InsertCommand.Parameters(0).Value = CType(vendorID,System.Guid)
If (vendorName Is Nothing) Then
Throw New Global.System.ArgumentNullException("vendorName")
Else
Me.Adapter.InsertCommand.Parameters(1).Value = CType(vendorName,String)
End If
If (vendorFirstName Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(2).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(2).Value = CType(vendorFirstName,String)
End If
If (vendorLastName Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(3).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(3).Value = CType(vendorLastName,String)
End If
If (vendorAddress Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(4).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(4).Value = CType(vendorAddress,String)
End If
If (vendorAddress2 Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(5).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(5).Value = CType(vendorAddress2,String)
End If
If (vendorCity Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(6).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(6).Value = CType(vendorCity,String)
End If
If (vendorState Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(7).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(7).Value = CType(vendorState,String)
End If
If (vendorZip Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(8).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(8).Value = CType(vendorZip,String)
End If
If (vendorContact Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(9).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(9).Value = CType(vendorContact,String)
End If
If (vendorPhone Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(10).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(10).Value = CType(vendorPhone,String)
End If
If (vendorContactEmail Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(11).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(11).Value = CType(vendorContactEmail,String)
End If
If (vendorContact2 Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(12).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(12).Value = CType(vendorContact2,String)
End If
If (vendorPhone2 Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(13).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(13).Value = CType(vendorPhone2,String)
End If
If (vendorContactEmail2 Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(14).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(14).Value = CType(vendorContactEmail2,String)
End If
If (vendorMainPhone Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(15).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(15).Value = CType(vendorMainPhone,String)
End If
If (vendorFAX Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(16).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(16).Value = CType(vendorFAX,String)
End If
If (vendorWebsite Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(17).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(17).Value = CType(vendorWebsite,String)
End If
If (vendorWebID Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(18).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(18).Value = CType(vendorWebID,String)
End If
If (vendorWebPassword Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(19).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(19).Value = CType(vendorWebPassword,String)
End If
If (vendorAccountNum Is Nothing) Then
Me.Adapter.InsertCommand.Parameters(20).Value = Global.System.DBNull.Value
Else
Me.Adapter.InsertCommand.Parameters(20).Value = CType(vendorAccountNum,String)
End If
If (vendorBalance.HasValue = true) Then
Me.Adapter.InsertCommand.Parameters(21).Value = CType(vendorBalance.Value,Decimal)
Else
Me.Adapter.InsertCommand.Parameters(21).Value = Global.System.DBNull.Value
End If
If (vendorCreditLimit.HasValue = true) Then
Me.Adapter.InsertCommand.Parameters(22).Value = CType(vendorCreditLimit.Value,Decimal)
Else
Me.Adapter.InsertCommand.Parameters(22).Value = Global.System.DBNull.Value
End If
If (vendorTerms.HasValue = true) Then
Me.Adapter.InsertCommand.Parameters(23).Value = CType(vendorTerms.Value,System.Guid)
Else
Me.Adapter.InsertCommand.Parameters(23).Value = Global.System.DBNull.Value
End If
If (vendorType.HasValue = true) Then
Me.Adapter.InsertCommand.Parameters(24).Value = CType(vendorType.Value,System.Guid)
Else
Me.Adapter.InsertCommand.Parameters(24).Value = Global.System.DBNull.Value
End If
Dim previousConnectionState As Global.System.Data.ConnectionState = Me.Adapter.InsertCommand.Connection.State
If ((Me.Adapter.InsertCommand.Connection.State And Global.System.Data.ConnectionState.Open) _
<> Global.System.Data.ConnectionState.Open) Then
Me.Adapter.InsertCommand.Connection.Open
End If
Try
Dim returnValue As Integer = Me.Adapter.InsertCommand.ExecuteNonQuery
Return returnValue
Finally
If (previousConnectionState = Global.System.Data.ConnectionState.Closed) Then
Me.Adapter.InsertCommand.Connection.Close
End If
End Try
End Function
 
Upvote 0 Downvote

Similar threads

C
  • Question
Read rows from a SQLite Database
Replies
1
Views
2K
jmcilhinney
jmcilhinney
S
  • Solved
Question how to retrieve row, update and lock from another user?
Replies
1
Views
1K
servox
S
J
  • Question
Question Trying to use the INSERT INTO Query Statement
Replies
6
Views
7K
JimRyanVB
J
W
  • Question
Question Refresh a combobox in a parent tabpage after making an entry in child tabpage
Replies
9
Views
11K
jmcilhinney
jmcilhinney
S
  • Question
Question Update Database and datagridview from textboxes
Replies
1
Views
5K
jmcilhinney
jmcilhinney

Share this page

Latest posts

Back
Top