If statements and database queries

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

Answer 1

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.

Answer 2

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()