asp.net - Incorrect syntax near ',' (dbnull issue) -



asp.net - Incorrect syntax near ',' (dbnull issue) -

it suppose there chart appeared. but, doesnt there problem regarding dbnull issue. happen when either 1 of 3 select statement has no data.

dim user string = session("nomatrik") dim resultid object = session("max") dim idquery = "select max(resultid) id tblresult result_nomatric = @matric , result_quiz_id = 1 union " + "select max(resultid) id tblresult result_nomatric = @matric , result_quiz_id = 2 union " + "select max(resultid) id tblresult result_nomatric = @matric , result_quiz_id = 3" conn.open() dim cmdgetid new sqlcommand(idquery, conn) cmdgetid.parameters.addwithvalue("@matric", user) dim maxids sqldatareader = cmdgetid.executereader dim ids string = "" while maxids.read ids += maxids("id").tostring() + ", " end while maxids.close() ids = ids.substring(0, ids.length - 2) dim cmdstring = "select tblresult.result_quiz_id quiz,count(tblanswer.answertype) answercount , tblanswer.answertype " + "from tblresultdetail inner bring together tblanswer on tblresultdetail.resultdetail_answer_id = tblanswer.answerid " + "inner bring together tblresult on tblresult.resultid = tblresultdetail.resultdetail_result_id " + "where tblresultdetail.resultdetail_result_id in (" + ids + ") " + "group tblanswer.answertype, tblresult.result_quiz_id order tblanswer.answertype" dim cmd new sqlcommand(cmdstring, conn) if isdbnull(resultid) label1.visible = true chrtresult.visible = false else dim dr1 sqldatareader dr1 = cmd.executereader while dr1.read() dim temparr(0) double dim count double = dr1("answercount") temparr(0) = count dim type string = dr1("answertype").tostring() dim level integer = dr1("quiz") chrtresult.series(type).points(level - 1).yvalues = temparr end while end if conn.close() end if end sub

an error, wrong syntax near '(' appear @ line dr1 = cmd.executereader. , how want prepare error?

update first query exclude null values using having clause so:

dim idquery = "select max(resultid) id tblresult " + "where result_nomatric = @matric , result_quiz_id = 1 " + "having max(resultid) not null " + "union " + "select max(resultid) id tblresult " + "where result_nomatric = @matric , result_quiz_id = 2 " + "having max(resultid) not null " + "union " + "select max(resultid) id tblresult " + "where result_nomatric = @matric , result_quiz_id = 3 " + "having max(resultid) not null"

the having max(resultid) not null exclude nulls in union all.

if there no ids returned, need check on before execute next block of code , @dmitrie suggests adding of quotes. reorganise like:

while maxids.read ids += "'" + maxids("id").tostring() + "', " end while if ids = "" label1.visible = true chrtresult.visible = false else ids = ids.substring(0, ids.length - 2) dim cmdstring = "select ....." dim dr1 sqldatareader dr1 = cmd.executereader while dr1.read() ' while loop code here' end while end if

asp.net sql-server vb.net gridview

Comments

Popular posts from this blog

php - Android app custom user registration and login with cookie using facebook sdk -

django - Access session in user model .save() -

php - .htaccess Multiple Rewrite Rules / Prioritizing -