Ok, I have an idea how to do this, but I want to make it efficient.
I have four tables in a DB (employees, customer, car, deal)
employees = empId(pk)
car = carID(pk & identity) custID(FK to customers.custID)
customers = custID(pk and identity) empID(FK to employees.empID)
deal = dealID(pk/identity) Also has custID, carID, and empID as FK's
With having the PK's in customers, car and deal as identities, I can have these auto-increase each time something is submitted. How can I have the FK keys update with the same number that is assigned to the PK's(I have separate programs accessing the same database, so having an int in one program will not work). I was thinking having a separate table in the DB and assign a number to it; when the user submits the form, the program will grab this number from the DB, assign it to a local variable, increase by one, then update the number in the DB. Will this work?
When the end-user clicks submit, I want the program to do multiple things:
1) Take the string from where the employees id is entered, match the number with empid in the database, return material and save it to strings in the program.
2) Take the information from the web form and insert into the database.
3) Check to see if the empid matches one in the database, if not stop the submission and inform the user to enter a valid number (no idea how to do this)
Does all this make sense? If not I can try to explain whatever you need to know.
Words surrounded by (()) are omitted code/info from the actual program.
The first If statement is commented out because it would not work?
If Me.rfCurrent.IsValid And Me.rfExp.IsValid And Me.rfUserID.IsValid And Me.rfSrvType.IsValid And Me.rfWarExp.IsValid Then
Dim myReader As SqlDataReader
' If Me.txtCustLast.Text = "" Or Me.txtUserID.Text = "" Or Me.txtVin.Text = "" Or Me.txtWarEnd.Text = "" Or Me.txtExpdate.Text = "" Or Me.txtCustPhone.Text = "" Or Me.txtCustName.Text = "" Or Me.txtCrntMile.Text = "" Or Me.txtComment.Text = "" Then
' Throw New ArgumentException
' End If
Dim mySqlCommand As SqlCommand = New SqlCommand("select * from employees where " _
& "empID = " + Me.txtUserID.Text + "", mysqlconnection)
'Open the connection
myReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
'Loop through the rows returned in the SqlDataReader
empname = (myReader("empName").ToString())
empstore = (myReader("empStore").ToString())
empemail = (myReader("empEmail").ToString())
' Catch argNullExc As ArgumentException
' Me.txtError.Text = "please make sure all required fields are filled out"
Catch ex As Exception
txtError.Text = "error: " + ex.Message
If Not myReader Is Nothing Then
Dim rdl As String = Me.rdlSrvType.SelectedValue.ToString()
obj.SmtpServer = "((server))"
Mailmsg.To = "((email)); " + empemail
Mailmsg.Bcc = "((email))"
Mailmsg.From = empemail
Mailmsg.BodyFormat = MailFormat.Html
Mailmsg.Subject = "((subject)) " + empname
Dim mailCon As String
Mailmsg.Body = ((msg))