Answered by:
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-
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 SubMonday, 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