running sql query in vb6
neha
data base. the sql query is got declarations, select statements and
the displaying the data. but i don't know how to implement it. i have
written some code. now to display each i am using function like that
follows
Public Sub DeleteSubDivSQLQuery()
On Error GoTo Error_Det
sSQL = ""
sConnectionString = "user id=" & Trim(sUserId) & ";password=" &
Trim(sPassword) & ";data source=" & Trim(sServerName) & ";database=" &
Trim(sDBName)
Set com = New ADODB.Command
With com
.ActiveConnection = OpenConnection
.CommandType = adCmdText
.CommandText = sSQL
End With
Set rs = com.Execute
MsgBox "Query Successfully Run!!! "
strChoice = MsgBox("Do you wish to continue with same database and
different query?", vbYesNo, "Confirmation")
If strChoice = vbYes Then
sSQL = ""
Call DeleteSubDivSQLQuery
Else
Call Initialize
End If
Exit Sub
Error_Det:
MsgBox "Query Failed!!!"
strChoice = MsgBox("Do you wish to Try Again!!!", vbYesNo,
"Confirmation")
If strChoice = vbYes Then
Call Initialize
Call DeleteSubDivSQLQuery
Else
Call Initialize
End If
End Sub
wherever there is sSQL the following query is to come.
This code is to delete the datya from table.
the delte query is as follows
delete from ar where resvno='' and resbdno='' and bcode=''
delete from br where svno='' and sbdno='' and bcode=''
delete from bi where svno='' and sbdno='' and bcode=''
delete from tp where svno='' and sbdno='' and bcode=''
delete from mpvreg where newsvno='' and newsbdno='' and bcode=''
delete from pvtemp where newsvno='' and newsbdno='' and bcode=''
Then for displaying the area i have written
Public Sub BlockSQLQuery()
On Error GoTo Error_Det
sSQL = ""
sConnectionString = "user id=" & Trim(sUserId) & ";password=" &
Trim(sPassword) & ";data source=" & Trim(sServerName) & ";database=" &
Trim(sDBName)
Set com = New ADODB.Command
With com
.ActiveConnection = OpenConnection
.CommandType = adCmdText
.CommandText = sSQL
End With
Set rs = com.Execute
MsgBox "Query Successfully Run!!! "
strChoice = MsgBox("Do you wish to continue with same database and
different query?", vbYesNo, "Confirmation")
If strChoice = vbYes Then
sSQL = ""
Call BlockSQLQuery
Else
Call Initialize
End If
Exit Sub
Error_Det:
MsgBox "Query Failed!!!"
strChoice = MsgBox("Do you wish to Try Again!!!", vbYesNo,
"Confirmation")
If strChoice = vbYes Then
Call Initialize
Call BlockSQLQuery
Else
Call Initialize
End If
End Sub
wherever there is sSQL the following query is to come.
The query for this is
"DECLARE @thect integer
declare @tare integer
declare @tsqm decimal(9,2)
set @thect = (select sum(nhect) from ar where bcode ='012')
set @tare = (select sum(nare) from ar where bcode ='012')"
set @tsqm = (select sum(nsqm) from ar where bcode ='012')
if @tsqm > 99 and @thect>0
set @thect=@thect+cast((@tsqm/10000) as integer)
if @tsqm > 99 and @thect=0
set @thect=cast((@tsqm/10000) as integer)
if @tsqm > 99 and @tare >0
set @tare = @tare+cast(right(cast((@tsqm/100) as integer),2)
as integer)
if @tsqm > 99 and @tare =0
set @tare = right(cast((@tsqm/100) as integer),2)
if @tsqm > 99
set @tsqm = right((@tsqm),5)
if @tare > 99" & vbCrLf & _
set @thect=@thect+cast((@tare/100) as integer)
if @tare > 99" & vbCrLf & _
set @tare = right(cast((@tare) as integer),2)
select @thect as Total_Hect, @tare as Total_Ares, @tsqm as Total_Sqm"
I will be a very much thankful if any one may help me. i have been
stuck up for more than a week.Thanking in anticipation
Neha