I am working on an (web)intranet application. I am using vb.net with framework 2.0. I have few excel files which have button click events written using VBA. Now I want to show those excel files in my intranet application. I want to open those excel files in my intranet application where the users can click on the button that is placed in excel file and see the data loaded into excel. These excel files are actually excel reports which get data from sql server. In few files user has to give the date as input and data from sql server will we loaded to excel based on the date parameter. I want to know the ways of doing the above task Regards, Uma

Even we thought the same and written a program in .NET which is working fine. Thank you for all you advices. Regards, Uma

We are using MS Exchange Server ver 10. Has it got something to do with authentication. If so how to set it using CDOObject?

Manually I am sending the similar message from my outlook account and the message is arriving. Sender address exists.

It is not the same message but it is a report in the same format. and to the same address. I just now tried by including my address in BCC but still I did not receive the mail. The mail server has changed 2 months before. Even after changing the mail server the program was sending mails properly and suddenly 1 fine day it stopped sending mails. Subject is same to the mail and also the sender address is same. So if suppose it is identified as SPAM by the server how can we resolve this. As the program is not generating any error logically it seems to be a problem at mail server level. How to resolve this?

this is a code written 8 yrs back by another user. As it was working we did not review it. Now what is the reason for the mails not getting delivered suddenly. till last tuesday it was working fine and suddenly it stopped working.

As I already said I traced the code it is not throwing any exception. The best practice is to write a logfile. I agree with you.

No error message. When I execute the program with break points it is not throwing any error. it is executing successfully. but mails are not received.

Hi, I have a program written in VB 6.0 for sending automated mails using CDO object. Suddenly it stopped working. I am giving the code below. What could be the possible reason for this program to stop working suddenly.

Public Sub SendOutlookMail(Subject As String, Recipient As String, Sender as String, strHTML As String)
Dim iMsg As New CDO.Message

On Error GoTo ErrHdlr
Const cdoSendUsingPort = 2

' Apply the settings to the message.
With iMsg
    .To = Recipient   'Recipient 'Recipient 'ToDo: Enter a valid email address.
    .From = Sender
    .Subject = Subject
    .HTMLBody = strHTML
    .Send
End With

' Clean up variables.
Set iMsg = Nothing
Exit Sub

ErrHdlr:
Exit Sub
End Sub

Regards, Uma

modified on Tuesday, June 7, 2011 8:33 AM

We are using exchange server. thank you for your response. Regards, Uma

Hi, I have developed an email program to send emails to users at end of the daily based on the data I have in database. I want to schedule this program in a PC. When I did a test run from that PC I could not get mails from my program. But it is working fine on another computer. Could anyone let me know what are the system requirements for my email program to work correctly. To my uunderstanding we should have SMTP server. How to install/configure SMTP server on the PC. my email code is as follows Dim iMsg As New CDO.Message Const cdoSendUsingPort = 2 ' Apply the settings to the message. With iMsg .To = Recipient .From = Sender .Subject = Subject ' "This is a test CDOSYS message (Sent via Port 25)" .HTMLBody = strHTML .Send End With Regards, Uma

Dear Senthil, The table structure is as follows [TRXSORCE] [char](13)NOT NULL, [DOCTYPE] [smallint] NOT NULL, [DOCNUMBR] [char](21) NOT NULL, [DOCDATE] [datetime] NOT NULL, [HSTMODUL] [char](3) NOT NULL, [CUSTNMBR] [char](15) NOT NULL, [ITEMNMBR] [char](31) NOT NULL, [LNSEQNBR] [numeric](19, 5) NOT NULL, [UOFM] [char](9) NOT NULL, [TRXQTY] [numeric](19, 5) NOT NULL, [UNITCOST] [numeric](19, 5) NOT NULL, [EXTDCOST] [numeric](19, 5) NOT NULL, [TRXLOCTN] [char](11) NOT NULL, [TRNSTLOC] [char](11) NOT NULL, [TRFQTYTY] [smallint] NOT NULL, [TRTQTYTY] [smallint] NOT NULL, [IVIVINDX] [int] NOT NULL, [IVIVOFIX] [int] NOT NULL, [DECPLCUR] [smallint] NOT NULL, [DECPLQTY] [smallint] NOT NULL, [QTYBSUOM] [numeric](19, 5) NOT NULL, [DEX_ROW_ID] [int] NOT NULL There are no indexes on this table. I cannot create any indexes as it a table used by ERP named Microsoft Dynamics GP

I have to use aggregate function sum() on a table having more than 4 millions of records. It is a history table. Are there any tips to be followed. the queries are a follows

SELECT @TransferIn = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr
and trnstloc = @route and docdate between @FromDate and @ToDate

SELECT @TransferOut = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr
and trxloctn = @route and docdate between @FromDate and @ToDate

SELECT @Sales = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype=6 and itemnmbr = @itemnmbr and
trxloctn = @route

In the above query @route,@FromDate and @ToDate are user inputs

Hi, I am having a problem with the below statements when I give as below I am not getting any records.

declare @list char(100)
set @list = '''wc1'',''wc2'''
select * from iv30300 where trxloctn in (@list)

When I directly give the list I am getting the result.

declare @list char(100)
set @list = '''wc1'',''wc2'''
select * from iv30300 where trxloctn in ('wc1','wc2')

I am working on sql server 2005 version. I am not able to find out the error.

How to split them. The insert or Update statement is not allowed in case statement.

I modified the loop from the application as follows but I have a problem here I cannot give insert/update command in case.Is there any way to solve this. If there exists an alternative solution then it will help a lot. The queries are working correctly I tested them. The following is the code

select
case (select count(*) from test a
where a.trxsorce = case rtrim(b.orgntsrc)
when '' then
b.ortrxsrc
else b.orgntsrc
end
and a.act=b.actindx and a.sopno = b.orctrnum)
when 0 then
insert into test
(gltrxdate,jrnentry,source,glact,glactindex,uswhpstd,ortrxsrc,
orgctrnum,gldr,glcr,gldexrowid,status)
select trxdate,jrnentry,trxsorce,account,actindx,uswhpstd,orgntsrc,
orctrnum,debitamt,crdtamnt,dex_row_id,2
from gl20000 where dex_row_id=b.dex_row_id

when 1 then
update test set
gltrxdate = b.trxdate,jrnentry = b.jrnentry,
source = b.trxsorce,glact = b.account,
glactindex = b.actindx,uswhpstd = b.uswhpstd,
ortrxsrc = b.orgntsrc,orgctrnum = b.orctrnum,
gldr = b.debitamt,
glcr = b.crdtamnt,gldexrowid = b.dex_row_id,status =7
where sopno = b.orctrnum and actindx = b.actindx and
trxsorce = b.trxsorce

else
insert into test
(gltrxdate,jrnentry,source,glact,glactindex,uswhpstd,ortrxsrc,
orgctrnum,gldr,glcr,gldexrowid,status)
select trxdate,jrnentry,trxsorce,account,actindx,uswhpstd,orgntsrc,
orctrnum,debitamt,crdtamnt,dex_row_id,7
from gl20000 where dex_row_id=b.dex_row_id

end
from gl20000 b where
trxdate between convert(datetime,'01/04/2007',103) and
convert(datetime,'30/04/2007',103) and actindx in
(select actindx from gl00100 where actnumbr_6=1300) and
series <> 2 and correspondingunit <> 1

I have to write a insert/update command with in a case. IS it possible? for example select case(select count(*) from table1 a where a.no=b.no and a.act=b.act) when 0 then insert into temp(name,desc,status) values(b.name,b.desc,1) when 1 then update temp set name=b.name,desc = b.desc,status=2 where id=b.id else insert into temp(name,desc,status) values(b.name,b.desc,3) end from table2 b But as case statement accepts only expressions can some one let me know how the above can be modified

I tried moving the entire logic to server side but still did not find any improvement. The problem is with the huge data being handled in a loop. I need a way to eliminate the loop. If I have to redo the loop with a select statement as we did in the previous case I am having some problem. I am taking the value of 2 fields based on the field value. I am giving the code below. Let me know if this can be handled in a select query directly.I think we can use switch case in the select statement but I am not sure.

SELECT JRNENTRY,trxdate,trxsorce,USWHPSTD,ortrxsrc,orgntsrc,orctrnum,debitamt,
crdtamnt,Dex_row_id,actindx,
(select rtrim(actnumbr_1) + '-'+rtrim(actnumbr_2) + '-'+
rtrim(actnumbr_3) + '-' + rtrim(actnumbr_4) + '-' +
rtrim(actnumbr_5) + '-' +
rtrim(actnumbr_6) + '-' + rtrim(actnumbr_7) from gl00100 g1
where g1.actindx = g.actindx) 'account',
origseqnum FROM GL20000 g WHERE trxdate between convert(datetime,'01/04/2007',103) and
convert(datetime,'31/03/2008',103) and
actindx in (4818,25,3189) and series <> 2
and correspondingunit <> 1

the above query gets me a record set. in the loop for the above record set i am taking value of few fields for insertion or updation based on the value of the field. the logic is as below

for (int i = 0; i < ds.Tables["InGL"].Rows.Count; i++)
{
strOrgnTrxSorce = ds.Tables["InGL"].Rows[i]["orgntsrc"].ToString();
strOrgnDocNo = ds.Tables["InGL"].Rows[i]["orctrnum"].ToString();
strOrTrxSrc = ds.Tables["InGL"].Rows[i]["ortrxsrc"].ToString();
strSource = strOrgnTrxSorce.Trim();
if (strOrgnTrxSorce.Trim() == "")
strSource = strOrTrxSrc;
select count(*) from gl_sl_cmpr where sopno='" + strOrgnDocNo + "'
and actindex ='" + strActIndex + "' and trxsorce='" + strSource + "'"

}

based on the "select count(*) ... " query count I will either insert or update a table. Now my problem is do I have any way of including the above logic in a single sql statement. Also you suggested me to use loops if necessary with in stored procedure. I tried using cursor in stored procedure by moving the logic from .net but still it is taking the same time.

modified on Tuesday, December 23, 2008 12:48 AM

Hi Mika Wendelius, The alternate update statements given by you helped me a lot in speeding up the process. I have few more areas where I need to re-design the logic. I am giving the code below. Please suggest alternative for this. roughly the loop is going to run for 100,000 to 300,000 rows.

        for (int i = 0; i < ds.Tables\["InGL"\].Rows.Count; i++)
                {
                    iJrnEntry = Convert.ToInt32(ds.Tables\["InGL"\].Rows\[i\]\["JRNENTRY"\]);
                    dtTrxDate = Convert.ToDateTime(ds.Tables\["InGL"\].Rows\[i\]\["trxdate"\]);
                    strTrxSorce = ds.Tables\["InGL"\].Rows\[i\]\["trxsorce"\].ToString();
                    strActIndex = ds.Tables\["InGL"\].Rows\[i\]\["account"\].ToString();
                    strGLUser = ds.Tables\["InGL"\].Rows\[i\]\["USWHPSTD"\].ToString();
                    strOrgnTrxSorce = ds.Tables\["InGL"\].Rows\[i\]\["orgntsrc"\].ToString();
                    strOrgnDocNo = ds.Tables\["InGL"\].Rows\[i\]\["orctrnum"\].ToString();
                    strOrTrxSrc = ds.Tables\["InGL"\].Rows\[i\]\["ortrxsrc"\].ToString();
                    dclGLDr = Convert.ToDecimal(ds.Tables\["InGL"\].Rows\[i\]\["debitamt"\]);
                    dclGLCr = Convert.ToDecimal(ds.Tables\["InGL"\].Rows\[i\]\["crdtamnt"\]);
                    dexrowid = Convert.ToInt32(ds.Tables\["InGL"\].Rows\[i\]\["Dex\_row\_id"\]);
                    iGLACT = Convert.ToInt32(ds.Tables\["InGL"\].Rows\[i\]\["actindx"\]);
                    iGlSeqNo = Convert.ToInt32(ds.Tables\["InGL"\].Rows\[i\]\["origseqnum"\]);
                    strOrgnDocNo = strOrgnDocNo.Replace("'", "''");
                    strSource = strOrgnTrxSorce.Trim();
                    if (strOrgnTrxSorce.Trim() == "")
                        strSource = strOrTrxSrc;
                    cmd.CommandText = "select count(\*) from gl\_sl\_cmpr where sopno='" + strOrgnDocNo + "' and actindex ='" + strActIndex + "' and trxsorce='" + strSource + "'";
                    cmd.ExecuteNonQuery();
                    if (ds.Tables.Contains("matchcount"))
                        ds.Tables.Remove("matchcount");
                    da.Fill(ds, "matchcount");
                    
                    
                    reccnt = Convert.ToInt32(ds.Tables\["matchcount"\].Rows\[0\]\[0\]);

                    if (reccnt == 0)
                    {
                        cmd.CommandText = " insert into gl\_sl\_cmpr(jrnentry,gltrxdate,SOURCE,GLACTINDEX,

Thank you for the suggestion and uopdate query. Need to work it out and check.The query seems to be similar to what I am looking for.