Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQLCommand Question Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 11:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 23, 2009 12:42 PM
Points: 2, Visits: 19
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

Post #821849
Posted Friday, November 20, 2009 2:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 12:45 PM
Points: 365, Visits: 1,341
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...

Post #822164
Posted Saturday, November 21, 2009 8:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 23, 2009 12:42 PM
Points: 2, Visits: 19
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?
Post #822954
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse