Public Function TaxSaleAdd100(ByRef rows As Integer) As Boolean
Dim bSuccess As Boolean
bSuccess = False
dbconn = New DatabaseConn
Dim sqlConn As SqlConnection = dbconn.GetConnectionStarSource
Dim spProcName As String = "TaxSale31015"
Dim sqlCmd As SqlCommand = New SqlCommand()
SetCommandType(sqlCmd, CommandType.StoredProcedure, spProcName, sqlConn)
Try
sqlConn.Open()
rows = sqlCmd.ExecuteNonQuery()
bSuccess = True
Return bSuccess
Catch ex As Exception
Throw (ex)
End Try
End Function
Public Function ExecuteDataConversion(ByVal Jobname As String, ByVal dbServername As String) As Integer
Dim flag As Integer
dbconn = New DatabaseConn
Dim sqlConnMigrate As SqlConnection = dbconn.GetConnection()
Dim spProcName As String = "sp_start_job"
Dim sqlCmd As SqlCommand = New SqlCommand()
SetCommandType(sqlCmd, CommandType.StoredProcedure, spProcName, sqlConnMigrate)
Try
AddParamToSQLCmd(sqlCmd, "job_name", SqlDbType.VarChar, 50, ParameterDirection.Input, Jobname)
AddParamToSQLCmd(sqlCmd, "server_name", SqlDbType.VarChar, 50, ParameterDirection.Input, dbServername)
sqlConnMigrate.Open()
flag = ExecuteScalar(sqlCmd, sqlConnMigrate)
Return flag
Catch ex As Exception
Throw (ex)
Finally
sqlConnMigrate.Close()
End Try
End Function
Public Sub LoadCtrnlBill(ByRef counttraccno As Object, ByRef paidamt As Object)
dbconn = New DatabaseConn
Dim result As Object = 0.0
Dim sqlConn As SqlConnection = dbconn.GetConnectionStarSource
Try
Dim query1 As String = "select count(TR_ACCT_NO) from cntrbill c join PayRegister p on c.TR_ACCT_NO=p.TR_ACCOUNT_NO and c.tr_year=p.tr_year and c.tr_town=p.tr_town where(c.TR_ACCT_NO = p.TR_ACCOUNT_NO And c.tr_year = p.tr_year And c.tr_town = p.tr_town) and c.tr_dvpaidamt<>p.tr_dv_paidamt or c.tr_acvpaidamt<>p.tr_acv_paidamt"
Dim query2 As String = "select sum(tr_dv_paidamt)+sum(tr_acv_paidamt) from cntrbill c join PayRegister p on c.TR_ACCT_NO=p.TR_ACCOUNT_NO and c.tr_year=p.tr_year and c.tr_town=p.tr_town where(c.TR_ACCT_NO = p.TR_ACCOUNT_NO And c.tr_year = p.tr_year And c.tr_town = p.tr_town) and c.tr_dvpaidamt<>p.tr_dv_paidamt or c.tr_acvpaidamt<>p.tr_acv_paidamt"
Dim sqlCmd1 As New SqlCommand(query1, sqlConn)
Dim sqlCmd2 As New SqlCommand(query2, sqlConn)
sqlConn.Open()
paidamt = sqlCmd2.ExecuteScalar
If Convert.IsDBNull(paidamt) Then
paidamt = 100
End If
counttraccno = CType(sqlCmd1.ExecuteScalar, Object)
Catch ex As Exception
Throw (ex)
Finally
sqlConn.Close()
End Try
End Sub
Public Function LoadCtrnlBillSelect() As DataSet
dbconn = New DatabaseConn
Dim sqlConn As SqlConnection = dbconn.GetConnectionStarSource
Dim query1 As String = "select TR_ACCOUNT_NO from cntrbill c right join PayRegister p on c.TR_ACCT_NO=p.TR_ACCOUNT_NO and c.tr_year=p.tr_year and c.tr_town=p.tr_town where tr_acct_no is null"
Try
Dim sqlCmd1 As New SqlCommand(query1, sqlConn)
Dim ds As New DataSet
sqlConn.Open()
sqlAdapter.SelectCommand = sqlCmd1
sqlAdapter.Fill(ds, "AccountList")
Return ds
Catch ex As Exception
Throw (ex)
End Try
End Function
Public Function LoadCtrnlBillUpdate() As Boolean
Dim bSuccess As Boolean
bSuccess = False
dbconn = New DatabaseConn
Dim sqlConn As SqlConnection = dbconn.GetConnectionStarSource
Dim query1 As String = "update cntrbill set TR_ACV_STATEPAID=p.TR_ACV_STATEPAID,TR_DV_STATEPAID=p.TR_DV_STATEPAID, TR_ACV_PAIDTOAPPL=p.TR_ACV_PAIDTOAPPL,TR_DV_PAIDTOAPPL=p.TR_DV_PAIDTOAPPL, TR_DVPaidAmt=p.TR_DV_PaidAmt, TR_ACVPaidAmt = p.TR_ACV_PaidAmt from cntrbill c join PayRegister p on c.TR_ACCT_NO=p.TR_ACCOUNT_NO and c.tr_year=p.tr_year and c.tr_town=p.tr_town where(c.TR_ACCT_NO = p.TR_ACCOUNT_NO And c.tr_year = p.tr_year And c.tr_town = p.tr_town) and c.tr_dvpaidamt<>p.tr_dv_paidamt or c.tr_acvpaidamt<>p.tr_acv_paidamt"
Dim sqlCmd As SqlCommand = New SqlCommand(query1, sqlConn)
Try
sqlCmd.CommandType = CommandType.Text
sqlConn.Open()
sqlCmd.ExecuteNonQuery()
bSuccess = True
Return bSuccess
Catch ex As Exception
End Try
End Function
Private Sub AddOutputParamToSQLCmd(ByRef sqlCmd As SqlCommand, ByVal paramId As String, ByVal sqlType As SqlDbType, _
ByVal paramSize As Integer, ByVal paramDirection As ParameterDirection, ByVal sqlParam As SqlParameter)
If sqlCmd Is Nothing Then
Throw New ArgumentNullException("sqlCmd")
End If
If paramId = String.Empty Then
Throw New ArgumentOutOfRangeException("paramId")
End If
sqlParam.ParameterName = paramId ' parameter name in Stored Procedure
sqlParam.SqlDbType = sqlType
sqlParam.Direction = paramDirection
If paramSize > 0 Then
sqlParam.Size = paramSize
End If
sqlCmd.Parameters.Add(sqlParam)
End Sub
Private Sub AddParamToSQLCmd(ByRef sqlCmd As SqlCommand, ByVal paramId As String, ByVal sqlType As SqlDbType, _
ByVal paramSize As Integer, ByVal paramDirection As ParameterDirection, _
ByVal paramvalue As Object)
If sqlCmd Is Nothing Then
Throw New ArgumentNullException("sqlCmd")
End If
If paramId = String.Empty Then
Throw New ArgumentOutOfRangeException("paramId")
End If
Dim newSqlParam As SqlParameter = New SqlParameter()
newSqlParam.ParameterName = paramId ' parameter name in Stored Procedure
newSqlParam.SqlDbType = sqlType
newSqlParam.Direction = paramDirection
If paramSize > 0 Then
newSqlParam.Size = paramSize
End If
If Not paramvalue Is Nothing Then
newSqlParam.Value = paramvalue
Else
newSqlParam.Value = 0
End If
sqlCmd.Parameters.Add(newSqlParam)
End Sub
Private Sub SetDataAdapter(ByVal sqlCmd As SqlCommand, ByVal sqlConn As SqlConnection)
sqlAdapter = New SqlDataAdapter
sqlAdapter.SelectCommand = sqlCmd
End Sub
Private Sub SetCommandType(ByRef sqlCmd As SqlCommand, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal Conn As SqlConnection)
sqlCmd.CommandType = cmdType
sqlCmd.CommandText = cmdText
sqlCmd.Connection = Conn
End Sub
Public Sub ExecuteNonQuery(ByVal sqlCmd As SqlCommand, ByVal SqlConn As SqlConnection)
If sqlCmd Is Nothing Then
Throw New ArgumentNullException("sqlCmd")
End If
sqlCmd.Connection = SqlConn
sqlCmd.ExecuteNonQuery()
End Sub
Public Function ExecuteScalar(ByVal sqlCmd As SqlCommand, ByVal sqlConn As SqlConnection) As Object
Dim retval As Object
If (sqlConn Is Nothing) Then Throw New ArgumentNullException("sqlConn")
retval = sqlCmd.ExecuteScalar()
Return retval
End Function
(
geocities.com/lim_abc)