locked
If statements and database queries RRS feed

  • Question

  • User-749682434 posted

    I am trying to create a survey that can be updated if a user comes back after leaving. The page I am working on here is to get back to their survey. The should enter thier programs license number. The page should then look at the DB and see if that license number(DVN) exist with the column isActive = 1. There will be at most 1 instace of this. If the record does not exist, it should go to the insert statement. If the record does exits it should set the Session.

    I'm having trouble seeing if the DB value is null. It keeps going to the ELSE part of the statement. If someone could steer me in the right direction I would much appreciate it. My button click is below. Thanks-

     

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSubmit.Click Dim CheckConn As New System.Data.SqlClient.SqlConnection

    CheckConn.ConnectionString = "MyConnectionString"

    Dim myCommand As New System.Data.SqlClient.SqlCommand

    Dim DVN = CInt(Request.Form("DVN"))

    Dim PersonID = CInt(Request.Form("PersonID"))

    myCommand.CommandText = "SELECT PersonID, DVN, isActive FROM survey.checklist WHERE DVN = " & DVN & " AND isActive = 1"

    myCommand.Connection = CheckConn

    CheckConn.Open()

    myCommand.ExecuteNonQuery()

    CheckConn.Close()

     

    If IsDBNull(DVN) = True Then 'run this becasue there is no active value for this DVN in the DB

    myCommand.CommandText = "INSERT INTO survey.checklist(PersonID, DVN, isActive) values('" & PersonID & "','" & DVN & "','1')"

    myCommand.Connection = CheckConn

    CheckConn.Open()

    myCommand.ExecuteNonQuery()

    CheckConn.Close()

    'Response.Write("If Clause")

    'Response.End()

    Else 'run this because there is a value for DVN in the DB with isActive = 1

    Response.Write("Else Clause")

    Response.End()

    'Session("DVN") = DVN

    End If

    'Work on redirect for if statements

    Response.Redirect("/First/getInfo.aspx")

    End Sub

    Monday, August 25, 2008 12:03 PM

Answers

  • User-125547262 posted

     The problem is that DVN is a variable whose value is being set to Request.Form("DVN") so it will never be a dbnull.

    Even though you are running the query to check if the user exists in the database you are rinning it as ExecuteNonQuery so no value is being returned.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 25, 2008 12:31 PM
  • User-292928630 posted

     You need to use cmd.ExexcuteReader() not cmd.ExecuteNonQuery();

     Dim PersonID = CInt(Request.Form("PersonID"))

    myCommand.CommandText = "SELECT PersonID, DVN, isActive FROM survey.checklist WHERE DVN = " & DVN & " AND isActive = 1"

    myCommand.Connection = CheckConn

    CheckConn.Open()

    Dim  SqlDataReader reader =myCommand.ExecuteReader()

    if(reader.HasRows)

    {

       //this means the record was found in database

    }

    CheckConn.Close()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 25, 2008 12:33 PM

All replies

  • User-125547262 posted

     The problem is that DVN is a variable whose value is being set to Request.Form("DVN") so it will never be a dbnull.

    Even though you are running the query to check if the user exists in the database you are rinning it as ExecuteNonQuery so no value is being returned.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 25, 2008 12:31 PM
  • User-292928630 posted

     You need to use cmd.ExexcuteReader() not cmd.ExecuteNonQuery();

     Dim PersonID = CInt(Request.Form("PersonID"))

    myCommand.CommandText = "SELECT PersonID, DVN, isActive FROM survey.checklist WHERE DVN = " & DVN & " AND isActive = 1"

    myCommand.Connection = CheckConn

    CheckConn.Open()

    Dim  SqlDataReader reader =myCommand.ExecuteReader()

    if(reader.HasRows)

    {

       //this means the record was found in database

    }

    CheckConn.Close()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 25, 2008 12:33 PM