SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLCommand Question


SQLCommand Question

Author
Message
romallon
romallon
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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


dmoldovan
dmoldovan
SSC Eights!
SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)

Group: General Forum Members
Points: 844 Visits: 1469
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...
romallon
romallon
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search