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
Post a Comment