1

sorry guys i am a new programmer of vb.net so i need some help. i am not familiar with sql server, this is my code in inserting employee info. and it works fine, and my question is how to search this record using emp_id only

    Dim mycommand As SqlCommand
    myconnection = New SqlConnection("server=;uid=admin;pwd=;database=payroll")
    myconnection.Open()
    mycommand = New SqlCommand("INSERT INTO employee_info([employee_id],
      [first_name],[last_name],[middle_name],[email],[telephone],
      [gender],[status],[date_birth],[hire_date],[street_add],[city],
      [state_province]) values ('" & Employee_idTextBox.Text & "','" &
      First_nameTextBox.Text & "','" & Last_nameTextBox.Text & "','" &
      Middle_nameTextBox.Text & "','" & EmailTextBox.Text & "','" &
      TelephoneTextBox.Text & "','" & GenderTextBox.Text & "','" & 
      StatusTextBox.Text & "','" & Date_birthDateTimePicker.Value.Date & 
      "','" & Hire_dateDateTimePicker.Value.Date & "','" & 
      Street_addTextBox.Text & "','" & CityTextBox.Text & "','" &
      State_provinceTextBox.Text & "')", myconnection)
    mycommand.ExecuteNonQuery()
    myconnection.Close()
Ňɏssa Pøngjǣrdenlarp
  • 38,411
  • 12
  • 59
  • 178
jepoy
  • 173
  • 2
  • 2
  • 8
  • "SELECT * FROM employee_info WHERE first_name = 'Little Bobby Tables'" see http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Ňɏssa Pøngjǣrdenlarp Jan 25 '14 at 01:31
  • Please read about [Prepare](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare%28v=vs.110%29.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1) commands. – Dour High Arch Jan 25 '14 at 01:34
  • [Little Bobby Tables](http://bobby-tables.com/) should be your *very first stop*. Now, without any delay. Immediately-drop everything else. – Ken White Jan 25 '14 at 01:34
  • 1
    Your next stop should be [SQL Injection](http://msdn.microsoft.com/en-us/library/ms161953%28SQL.105%29.aspx) at MSDN. – Ken White Jan 25 '14 at 01:39

1 Answers1

0

Like others have indicated, you should parameterize your INSERT to avoid SQL Injection vulnerability.

Here's how you can retrieve a newly inserted Employee record by Employee_ID

Dim dbConn as SqlConnection
Dim myCommand As SqlCommand
dbConn = New SqlConnection("server=;uid=admin;pwd=;database=payroll")
dbConn.Open()
myCommand = New SqlCommand("SELECT * FROM employee_info WHERE  employee_id = @EmployeeId", dbConn)
myCommand.Parameters.AddWithValue("@ EmployeeId", employeeId) 
' employeeId in above line is the variable that contains the actual id you want to retrieve

myDataReader = myCommand.ExecuteReader()
' do stuff with the data in myDataReader here
' ...
' .....
myDataReader.Close()
dbConn.Close()
Shiva
  • 20,575
  • 14
  • 82
  • 112
  • question, i want to put all the data i retrieved by this at textbox and should i also use the dataset in textbox? – jepoy Jan 25 '14 at 02:40
  • It depends on your requirements. Here's a nice article on Databinding in VB.Net. It shows you how to bind form controls to datasource. http://msdn.microsoft.com/en-us/magazine/cc164106.aspx – Shiva Jan 25 '14 at 04:59