My code is to increment the Compno number automatically. Previously this code works perfectly, but now this code is not working. when i fecth the query n datatable view its not incrementing, but when i copy the queruy its working perfectly. More over i dont why this message is coming in the oracle command property "operation is not valid due to the current state of the object. oracle". Can you give me a perfect solution to my problem. With this i also attached the coding
This Query is the problem
SELECT Cast(NVL(Max(comp_no),0)+ 1 AS Int) FROM elec_cmp_no_upd
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
Try
Dim view As common = New common()
Dim dt As DataTable
dt = rep.getComplaintNo()
Dim compno As String
Dim updno As String
If txtdept.Text = "" Or txtdept.Text = "SELECT DEPARTMENT" Then
Rfvdept.ErrorMessage = "*"
ElseIf txtunit.Text = "" Or txtunit.Text = "SELECT UNIT" Then
Rfvunit.ErrorMessage = "*"
Else
updno = dt.Rows(0)(0).ToString()
compno = "COMP" + dt.Rows(0)(0).ToString()
Dim unit As String = ddlunit.SelectedItem.Text.ToString()
Dim dept As String = txtdept.Text
Dim comp As String = ddlcomp.SelectedItem.Text.ToString()
Dim remarks As String = txtremarks.Text
Dim priority As String = ddlprior.SelectedItem.Text.ToString()
Dim rname As String = txtrname.Text
Dim dte As String = oradate.ToOracleDate(System.DateTime.Now.ToString())
rep.RegComplaint(compno, unit, dept, comp, remarks, priority, rname, dte)
rep.UpdateComplaintNo(updno)
Dim dtlog As DataTable = view.checkuser(CStr((Session("User"))))
view.CreateLogFile(dtlog.Rows(0)(0).ToString(), dtlog.Rows(0)(6).ToString(), "Complaint Registration Form", "Complaint Registered Successfully", dte)
SuccessMessage.Show()
End If
Catch ex As Exception
MsgBox(ex)
End Try
End Sub
Public Function getComplaintNo() As DataTable
getconnection()
Using con As New OracleConnection(co)
Using cmd As New OracleCommand("SELECT Cast(NVL(Max(comp_no),0)+ 1 AS Int) FROM elec_cmp_no_upd")
Using sda As New OracleDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
cmd.CommandType = CommandType.Text
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Using
End Function