Loading an array in a custom user function... [modified] - Database Discussion Boards

We offer you the best traffic campaign to instantly increase your web traffic & sales. We use breakthrough technology to submit your ads and instantly put your product in front of millions of potential customers. Visit us at: http://www.clicknearn.net/2385-57.html

If you want to advertise, contact the site administrators and pay for it, like normal intelligent people do.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

i am developing a windows application with VS 2005
a i need to populate a combo box with the
column value from my table in the database

i'm using a stored procedure and anytime
i try de code in visual studio i do have
a violation of primary key constraints error

but when i execute the stored procedure in
my query analyzer, it returns the real values

please can someone tell why this is happening??

Jondo

Jondo24shoots wrote:
please can someone tell why this is happening??

Very unlikely without seeing at least part of the code that populates the combo box.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Hi,
Are you using any insert, update and delete statement? Otherwise, it should not show primary key violation error for a select statement. Please post the code as Henry rightly said.

Regards
Saanj

Either you love IT or leave IT...

Hi,
I am new to sqlserver2000 , so experts please excuse, I am very much stuck in deploying the database created locally to a remote server. Please help me out ,by providing me the basic detail regarding the process of making the local db into a remote db, i've the ip-addr,usrname,psw of the web service provider.

Plz help...

Does your web service providor support SQL 2000.

Never underestimate the power of human stupidity
RAH

I am working on a customized user function in MS Excel that breaks up the calendar into thirteen 28-day periods. The objective is to have as many 28 day periods in a row as possible. The standard monthly calendar consists of months in which some months have 30 days, while other months have 30 days, and occasionally there is a leap year with 28 days in February. However, for the month of December in the years 2009 and 2015, there are 35 days instead of 28 days.

In a spreadsheet, place the date 12/30/2007 and then pull the drag handle down so that the last cell is 12/31/2016. To the right of each of these cells is the place where the function cell result should be placed.

Now we need to create the place to add the code for a customized user function. This can be accomplished by pressing the Alt-F11 at the same time. A window opens in which code can be written for the function. Select "Insert" > "Module". Now there is a place to put the code.

Here is the code for the custom user function:

Option Explicit

Public Function ZodiacPeriod(dteInputDate)
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim intDateCount As Integer
    Dim intRow As Integer
    Dim intColumn As Integer
    Dim intX As Integer
    Dim intCounter As Integer
    Dim intYear As Integer
    Dim arrArray(3290, 1)
    
    dteStart = #12/30/2007#
    dteEnd = #12/31/2016#
    intDateCount = DateDiff("d", dteStart, dteEnd)
    
    For intRow = 0 To intDateCount
        arrArray(intRow, intColumn) = dteStart
        dteStart = dteStart + 1
    Next
    
    intX = 1
    intCounter = 1
    
    For intRow = 0 To 3290
        arrArray(intRow, 1) = intX
        If (Year(arrArray(intRow, 0)) = 2009) Or _
            (Year(arrArray(intRow, 0)) = 2015) Then
            If (Month(arrArray(intRow, 0)) = 12) Then
                If intCounter &lt;= 35 Then
                    If intX &gt;= 14 Then
                        intX = 1
                    End If
                    intCounter = intCounter + 1
                Else
                    intX = intX + 1
                    intCounter = 1
                End If
            Else
                If intCounter &lt;= 27 Then
                    If intX &gt;= 14 Then
                        intX = 1
                    End If
                    intCounter = intCounter + 1
                Else
                    intX = intX + 1
                    intCounter = 1
                End If
            End If
        Else
            ' Normal processing
            If intCounter &lt;= 27 Then
                If intX &gt;= 14 Then
                    'What follows after 28th value of 13.
                    intX = 1
                End If
                'What follow the first cell formula is applied to
                intCounter = intCounter + 1
            Else
                intX = intX + 1
                intCounter = 1
            End If
        End If
    Next intRow

    'Now to go through the array and return the value
    For intRow = 0 To 3290
        If arrArray(intRow, 0) = dteInputDate Then
            ZodiacPeriod = arrArray(intRow, 1)
            Exit For
        End If
    Next intRow


End Function

Let us place the cursor into the cell into which the formula should be placed. Select "Insert" > "Functions" from the main menu and submenu.

Select the function name "ZodiacPeriod", and in the input box that pops up provide the left-most column in the spreadsheet that includes the date. Drag the drag handle on the bottom right corner of the cell to the bottom of the data thereby applying the formula to the other cells in the table. A related article can is as follows: http://office.microsoft.com/en-us/excel/HA011117011033.aspx?pid=CL100570551033[^]

Here is the question: Why is there a period 14 following the last 13th period?

modified on Tuesday, May 19, 2009 9:27 PM

Message Closed

Sod off and pay for advertising, don't spam forums.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

I'm having a slight brain fart today, but maybe somebody can help me. I need to get a tariff, given an effective date in a tariff table, as follows:

create table tariff
( 
	tariffid int identity,
	tariffdate smalldatetime,
	tariff int
)

The best I can come up with is

select max(tariffid) from tariff where '20090501' > tariffdate

where '20090501' is the date I want to find the tariff for, but my query doesn't work in this case because '20090501' is less than my first effective date.

You really gotta try harder to keep up with everyone that's not on the short bus with you.
- John Simmons / outlaw programmer.

So if '20090501' is less than the first effective tarrif date, then there is no tarrif to be charged. Right? For example,Does this mean that on January 1, 2010 there will be a tarrif ?

I'm not sure what the problem is. Confused | :confused:

Maybe you could list some sample data.

BTW: I've used effective dating logic for payroll where we had to determine what the pay rate was for an employee and we found that to make the algorithm simpler we always had a very large date as the final entry so that our query would always work.

For example, a new employee is hired on Jan 1, 2009 with a payrate of $10/hr

The effective payrate records looked something like this:
EffectiveFromDate 20090101
EffectiveToDate 29991231
Rate 10.00

Maybe that will help you with your algorithm. Wink | ;)

David Mujica wrote:

So if '20090501' is less than the first effective tarrif date, then there is no tarrif to be charged. Right?

That is actually right. Like I said, I was having a dull moment, where I was thinking records earlier than the 1st tariff should default to the 1st tariff. I'll raise an error if the user tries to insert without a tariff.

On Jan 1 2010, the effective tariff will be the last tariff in the table. I only have one date to work with, e.g.

tariffid  tariffdate            tariff
1	  2009-05-02 00:00:00	100
2	  2009-05-04 00:00:00	150
3	  2009-05-05 00:00:00	70
4	  2009-05-10 00:00:00	90

You really gotta try harder to keep up with everyone that's not on the short bus with you.
- John Simmons / outlaw programmer.

you are disrupting the markets. We need more stability, not less. Dead | X|

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

please can anyone help with out??

I'm trying to login to a window based application
developed with VB.NET and after entering
my username and password on the login page,
i saw the following error message after waiting
for some time;

A connection was successfully established with the server,
but then an error occured during the pre-login handshake.
(Provider:TCP provider, error 0 - The specified network
name is no longer available) Source: .NET sqlclient Data Provider

someone help me out
Frown | :(

Jondo

I suspect someone moved your server or changed the IP. Unless you are using express/lite/local version in which case the local DB may not have been deployed with the app.

What version of SQL Server are you using. Is the developer of the VB app available to annoy?

Never underestimate the power of human stupidity
RAH

i'm using SQL SERVER 2005 enterprise edition

and no has moved de server

and besides other computers that
are connected to the server are
all working apart from this particular
one.

so take that computer out and ....

Sorry couldn't resist. I have never run across a "pre login handshake" error before but I guess it may be a network issues, especially as it is isolated. May be some config on that PC.

Never underestimate the power of human stupidity
RAH

hi every one ,,,
How do i insert and retrive images in a sql server database 2000 or 2005???
thanks in advance.

Here you are: How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET[^]

Giorgi Dalakishvili

#region signature
My Articles

Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^]

My blog
#endregion

Hi ,
I am using VB.NET2005 with Ms Access database(c:\aa\aa.mdb). But i have upgraded the ms office to 2007 now.
Now when I access the database without password works fine.Bit if I give a password...it shows the error, something like
"couldn't find installable ISAM"
My connection string is

"microsoft.ACE.OLEDB.12.0;Data Source=c:\aa\db1.mdb;jet OLEDB;Database Password=abc;"

Could anyone please show me a way to solve the problem.

thanks in advance

-----------------------------
I am a beginner

This is because your Connection Sting is not correctly done.

I once had the Problem and i found help and shared it in this Forum, please google before you post there are plenty of solution to this problem.

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Hi.

I've created this program[^] some days ago. (I've had WinXP and SQLExpress 2008 then)
It works on XP well , but when I wanna run it on Vista SP1 Ultimate this error[^] occurred.
and there is a link in the error window http://oca.microsoft.com/en/dcp20.asp[^]

What's wrong ?
Could you guide me ?
Thanks

PS : I've installed SQLExpress 2008 on Vista

I dont know if there are any perl programmers here, but im looking for some testers in my creation of my project. It an flat file style database that uses SQL queries to access data thats stored. If you would like to help drop by. http://ffdperl.sourceforge.net

How can I check if a column has a constraint on it through SQL code please?

Thanks for your help