title: How to store a value from a database as a variable with VB.net
body: <p>I am trying to store DoctorId from the patient table as a variable that I can then add to an insert statement (to be inserted into the Order_pres table). I am also trying to do this with PharmacyId from the pharmacy table.</p>
  
  <p>The pharmacy name displays on the dropdown, however I dont know how to store the PharmacyId, as shown below:</p>
  
  <pre><code>&lt;asp:SqlDataSource ID="SqlPharm" runat="server" ConnectionString="&lt;%$ ConnectionStrings:SurgeryConnectionString %&gt;" SelectCommand="SELECT DISTINCT Pharmname FROM Pharmacy "&gt;&lt;/asp:SqlDataSource&gt;
  
  
  &lt;asp:DropDownList ID="DropPharm" runat="server" DataSourceID="SqlPharm" DataTextField="Pharmname" DataValueField="Pharmname"&gt;&lt;/asp:DropDownList&gt;
  </code></pre>
  
  <p>How I am storing my variables and adding Ids to the insert query</p>
  
  <pre><code> Protected Sub btnconfirm_Click(sender As Object, e As EventArgs) Handles btnconfirm.Click
  
      ' Dropdown for pharmacy  someVariable = DropPharm.SelectedItem.Value  
  
      Dim PatientId As Integer = Session("PatientId")
      Dim PharmacyId As Integer = Session("PharmacyId")
      Dim MedicineId As Integer = Session("MedicineID")
      Dim DateOrdered As Date
  
  
      ' Get DoctorId from the patient table
  
      Dim DoctorId As Integer = "SELECT DoctorId FROM Patient  "
  
      '.Value = CInt(Session("DoctorId").ToString()) 
  
  
  
          Dim query As String = String.Empty
          query &amp;= "INSERT INTO Order_pres (PatientId, PharmacyId, "
          query &amp;= "                     DoctorId, [Date Ordered])  "
          query &amp;= "VALUES (@PatientId,@MedicineId, @PharmacyId, @DoctorId, @DateOrdered)"
  
  
  
          Dim sqlCs As String = ConfigurationManager.ConnectionStrings("SurgeryConnectionString").ConnectionString
  
          Using conn As New SqlConnection(sqlCs),
                comm As New SqlCommand(query, conn)
              With comm.Parameters
  
              .Add("@PatientId", SqlDbType.Int).Value = Session("PatientId")
              .Add("@DoctorId", SqlDbType.Int).Value = Session("DoctorId")
              .Add("@MedicineId", SqlDbType.Int).Value = Session("MedicineID")
              .Add("@PharmacyId", SqlDbType.Int).Value = Session("PharmacyId")
              .Add("@DateOrdered", SqlDbType.DateTime).Value = DateTime.Parse(DateOrdered)
  
  
  
              End With
  </code></pre>
  
  <p>I have stored some of the sessions in the login.aspx page i.e when the user logs in:</p>
  
  <pre><code>  Public Function CheckUser(username As String, password As String) As Integer
      Dim cmdstring As String = "SELECT * FROM Patient  Where Username=@USERNAME AND Password=@PASSWORD"
      Dim found = 0
      Using conn As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\surgerydb.mdf;Integrated Security=True;Integrated Security=True;Connect Timeout=30")
  
          Dim cmd = New SqlCommand(cmdstring, conn)
          cmd.Parameters.Add("@USERNAME", SqlDbType.NChar).Value = username
          cmd.Parameters.Add("@PASSWORD", SqlDbType.NChar).Value = password
          conn.Open()
  
  
  
          Dim reader = cmd.ExecuteReader()
  
          While reader.Read()
              Session("PatientId") = CInt(reader.Item("PatientId"))
              Session("Username") = CStr(reader.Item("Username"))
              Session("DoctorId") = CStr(reader.Item("DoctorId"))
              found = CInt(reader.Item("PatientId"))
          End While
  
          reader.Close()
      End Using
      Return (found)
  End Function
  
  enter code here
  </code></pre>
  
  <p>If I have been vague or any more information is needed a for the question is needed please let me know.</p>
  
  <p>Kind regards :)</p>