sql-server


I am trying to insert values into a table from multiple tables (table relationships are shown)


I have four tables. The purpose of this code is for user to select a customer from a combobox and the check the shipments going to that customer to put into the FormHeaderTable. Let's pretend two shipments get selected.... forms can have several Shipments. Shipments can have several partnumbers
Table 1: FormHeaderTable
FormID (pk)
OrderKey(pk) (keeps track of how many forms have been made for a specific customer)
CreationDate
CustomerName
Table 2: FormLineTable
LineID (pk)
FormID, (pk, fk) (comes from FormHeaderTable)
OrderKey (pk, fk) (comes from FormHeaderTable)
ShipmentNumber (pk, fk) (comes from ShipmentHeaderTable)
PartNumber
Table 3: ShipmentHeaderTable
ShipmentNumber (pk) (rest of columns not needed for this question, just shipment details)
Table4: ShipmentLineTable
ShipmentLine (PK)
ShipmentNumber(PK, FK) (comes from shipmentHeaderTable)
PartNumber
I need to insert values into my FormLineTable based on the ShipmentNumbers selected from a checklistbox that is populated by a customer combobox's selection.
I tried to do this:
Dim OrderKey As Integer
Dim LastFormID As Integer
Dim NextFormID As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Get FormID and Get OrderKey
Dim MAXOrderKey As String = "SELECT MAX(OrderKey) FROM FormHeaderTable WHERE CustomerName = #CustomerName"
Dim MAXOrderKeyCommand As New SqlCommand(MAXOrderKey, con)
Dim MAXFormID As String = "Select MAX(FormID) FROM FormHeaderTable"
Dim MAXFormIDCommand As New SqlCommand(MAXFormID, con)
MAXOrderKeyCommand.Parameters.Add("#CustomerName", SqlDbType.VarChar).Value = cboCustomer.Text
If con.State = ConnectionState.Closed Then con.Open()
Try
LastFormID = CInt(MAXFormIDCommand.ExecuteScalar)
Catch ex As System.Exception
LastFormID = 0
End Try
Try
OrderKey = CInt(MAXOrderKeyCommand.ExecuteScalar)
Catch ex As System.Exception
OrderKey = 0
End Try
con.Close()
NextFormID = LastFormID + 1
OrderKey = OrderKey + 1
Dim cmd As New SqlCommand("SET IDENTITY_INSERT FormHeadertable ON Insert INTO FormHeaderTable (FormID, CustomerName, CreationDate, OrderKey) Values (#FormID, #CustomerName, #CreationDate, #OrderKey) SET IDENTITY_INSERT FormHeadertable OFF ", con)
cmd.Parameters.Add("#FormID", SqlDbType.Int).Value = NextFormID
cmd.Parameters.Add("#CustomerName", SqlDbType.VarChar).Value = cboCustomer.Text
cmd.Parameters.Add("#CreationDate", SqlDbType.Date).Value = Date.Now()
cmd.Parameters.Add("#OrderKey", SqlDbType.Int).Value = OrderKey
'this is where I need help'
Dim cmd1 As New SqlCommand("Insert into FormLineTable (FormID, OrderKey, LineId, ShipmentNumber, PartNumber) Select FormID, OrderKey, ShipmentLineNumber, ShipmentNumber, PartNumber FROM ShipmentLineTable CROSS JOIN FormHeaderTable", con)
cmd1.Parameters.Add("#FormID", SqlDbType.Int).Value = NextFormID
cmd1.Parameters.Add("#OrderKey", SqlDbType.Int).Value = OrderKey
Dim isFirst As Boolean = True
For i As Integer = 0 To chkLstShipments.Items.Count - 1
If chkLstShipments.GetItemCheckState(i) = CheckState.Checked Then
If isFirst = True Then
cmd1.CommandText += " WHERE ShipmentNumber = #ShipmentNumber" + i.ToString
isFirst = False
Else
cmd1.CommandText += " OR ShipmentNumber = #ShipmentNumber" + i.ToString
End If
cmd1.Parameters.Add("#ShipmentNumber" + i.ToString, SqlDbType.Int).Value = chkLstShipments.Items(i)
End If
Next
If con.State = ConnectionState.Closed Then con.Open()
cmd.ExecuteNonQuery()
cmd1.ExecuteNonQuery()
con.Close()
End Sub
My error
36 duplicate values or so...

Related Links

How do I validate SQL tables data using a schema
C# SQL connection string best practice
SQL Server combine rows into single column
Sum monthly amount and output for last day of month
SSRS referring to Database Project in the solution
Error when trying to execute stored procedure
Get actual database size in SQL Server 2012 in GB?
Simple results list from SQL Server stored procedure using Codefluent
SQL Report Error - [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name
How to get distinct column in SQL Server?
Powershell returns negative exit code while script results are correct
Spark program cannot open SQL Server context?
Sql server error 916 when using a trigger that selects data in other database (outside sql server only)
Convert rows from a NoSQL schema to columns in SQL Server
Unable to pass a variable value to a stored procedure in ssis
Getting value of one column ( price ) for max of another column (date) for a set of columns (make.model)

Categories

HOME
npm
nlp
sed
smartphone
json-ld
memory-leaks
mapbox-gl-js
couchdb-2.0
laravel-4
user
websphere-liberty
contact-form-7
pca
flann
squarespace
bar-chart
rhapsody
quartus
google-people
k2
explode
azure-servicebus-queues
tfs2013
philips-hue
weinre
blackboard
ioc-container
ifstream
modelandview
body-parser
pace
bing-maps-api
pmwiki
autocompletetextview
axis-labels
strstr
mongoexport
nashorn
tripwire
paho
crop
pytest-django
resuming-training
scalafx
context-free-language
rpostgresql
scd
iphone-developer-program
visual-studio-templates
redux-router
bettercms
pspice
ebtables
iostat
mailcatcher
software-product-lines
windows-nt
nssplitview
inotifypropertychanged
spring-lemon
spring-repositories
project-online
django-south
jta
file-diffs
enyo
js-cookie
mogrify
divide-by-zero
criteria-api
sparse-file
bridge
pylearn
scrollspy
virus
adodb
named-ranges
ghostdoc
iostream
dbsetup
svcutil.exe
lov
exiv2
pyjade
gamepad
sslexception
vmware-server
visual-studio-2003
htmltextwriter
abnf
pys60
maven-ear-plugin
legacy-code
driving-directions
viewdidload
hashalgorithm
fileutils
ou
nemerle
authenticode
port-scanning
junit3
clause
svn-hooks
open-graph-beta
trampolines
filetime
hmacsha1

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App