November 19, 2009 at 11:48 am
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
Try
' 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
mysqlconnection.Open()
myReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
'Loop through the rows returned in the SqlDataReader
While (myReader.Read())
empname = (myReader("empName").ToString())
empstore = (myReader("empStore").ToString())
empemail = (myReader("empEmail").ToString())
End While
' 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
Finally
If Not myReader Is Nothing Then
myReader.Close()
End If
End Try
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))
obj.Send(Mailmsg)
End If
November 20, 2009 at 2:12 am
I'm not sure what do you want to achieve with the piece of code you've submitted, but first of all please let me remark that:
- the code is prone to SQL injection. Use parameterized commands or, better, stored procedures. There is plenty of material to read about SQL injection, for example see http://blogs.technet.com/ms_schweiz_security_blog/archive/2008/05/28/sql-injection-general-guidance.aspx.
- you can use the .NET validators to make sure that the data you enter through forms is compliant to your requirements. This should replace the commented line.
However, before starting to write code, you should complete the specs of your application - this includes a good database design. If you start coding without having good specs, even the development of a simple application could end up in a mess...
November 21, 2009 at 8:49 pm
So how would I turn the above code into a stored procedure and get the info.
I have looked around several pages on google, but they all want to put the info into a datagrid, which all I want to do is put the info into some text boxes on the web form.
Been looking at everything about stored procedures but I am still a little confused on how they work.
I know I need an input (@empID) and several outputs for the user name, email and store. How do I go about creating the SP with this in mind?
Then how do I assign what was read to text boxes on the form?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply