Archives

 
In this post we share our cursor template. It works better than the default one, and allows BREAK and CONTINUE easily. There is only on FETCH line, so maintenance is much easier

The msdn article on Cast an Convert is perfectly clear when it comes to how Cast and Convert manipulate the date values into text.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

But… it’s just a little bit easier to see it printed out. Here’s a short script that prints out all the different CASTS that are possible and give an example. ‘127’ is in the SQL 2008 R2 documentation but failed against the SQL 200 server I was testing on, so I commented it out.

This post documents the usp_SOPTotal stored procedure. usp_SOPTotal is designed to retotal a SOP document.
It was written agains GP 9, but should work in later versions
This stored procedure will retotal the taxes for a SOP  document. It deletes the lines from SOP10105 and then re-adds them in based on the TAXSCHID in the customer record. Then, it add the summary row in SOP10105
Finally, it retotals the taxes in the SOP10200. usp_SOPTtotal should be called next to retotal the SOP10100

I'm getting this error while trying to develop a VBA mod on a Win2K8 server, GP2010. It happens when I hid ALT+F11 to open the VBA editor
Index to our posts related to Dynamics GP Ingetration Manager

Reading XML Attributes

This article can be found on the Integration Manager Menu

A client recently called with an issue with importing an XML file using Integration Manager (IM). Apparently, IM can't read the attributes in an XML document. After trying for a while to get it to work, we decided to write a small application that would parse the XML file and move the attributes to nodes.

Today’s task has to do with an XML document that has the form:


<OutboundReports>
    <InvoiceNotice>
        <InvoiceNoticeHeader InvoiceDate="20100607" InvoiceType="Invoice" invoiceID="60252726" purpose="PR">
        </InvoiceNoticeHeader>
    </InvoiceNotice>
    <InvoiceNotice>
        <InvoiceNoticeHeader InvoiceDate="20100607" InvoiceType="Invoice" invoiceID="60252727" purpose="PR">
        </InvoiceNoticeHeader>
    </InvoiceNotice>
</OutboundReports>

We need to move the InvoiceDate, InvoiceType, and invoiceID attributes to ‘nodes’, so that our integration program will see them, the current version will not read attributes. Here’s what we want it to look like in the end:

<OutboundReports>
    <InvoiceNotice>
        <InvoiceNoticeHeader InvoiceDate="20100607" InvoiceType="Invoice" invoiceID="60252726" purpose="PR">
            <InvoiceDate>20100607</InvoiceDate>
            <InvoiceType>Invoice</InvoiceType>
            <InvoiceID>60252726</InvoiceID>
        </InvoiceNoticeHeader>
    </InvoiceNotice>
    <InvoiceNotice>
        <InvoiceNoticeHeader InvoiceDate="20100607" InvoiceType="Invoice" invoiceID="60252727" purpose="PR">
            <InvoiceDate>20100607</InvoiceDate>
            <InvoiceType>Invoice</InvoiceType>
            <InvoiceID>60252726</InvoiceID>
        </InvoiceNoticeHeader>
    </InvoiceNotice>
</OutboundReports>

Here’s the code:



This article gives a code example on how to use the eConnect stored procedure taGetPMNextPaymentNumber, which gets the next PM Payment Number

This document, and all our eConnect documentation, is available on the eConnect menu.  

Are you using the DynDeveloper eConnect class? It’s in all of our sample code. The eConnect class is designed to be easily transportable from project to project and contains all the common code that developers need to code with eConnect. All that's left is the code that changes from integration to integration.

This class is called from all the eConnect samples. You'll need this class first in order to run them. This code is for GP2010/2013, and it encapsulates all the commons tasks in eConnect (getting a document number, creating a transaction)

Methods included:

Connection String - returns the connection string used by eConnect

Serialize - serializes (turns to XML) the supplied eConnect object

CreateEntity (overloaded) Accepts either the eConnect object or an XML document

CreateTransactionEntity (overloaded) Accepts either the eConnect object or an XML document

UpdateEntity (overloaded) Accepts either the eConnect object or an XML document

GetNextIVNumber
GetNextGLJournalEntryNumber
GetNextPONumber
GetNextPOPReceiptNumber
GetNextRMNumber
GetPMNextVoucherNumber
GetNextSOPNumber

Edit 1/9/2018 added support for GetEntity

 

This is our code to get the next available RM document number
Getting 'Unable to add user to SQL' message when installing eConnect on Microsoft Dynamics GP 2010 in a Workgroup environment that points to a SQL server on another computer in a domain

http://www.shell-tools.net/index.php?op=xml%5Fformat

This tools works great for parsing an XML file

It is occasionally necessary to find all the occurrences of a field name in a SQL Server database. This script will seacrh the database and return all the tables that contain a certain field name.

Here we show two different ways to get this done quickly

 

Earlier I discussed how to dynamically link to background images in ASP.NET. This is a little different, and repuires a little different techniqe. The issue is that when a master page serves a page in a subdirectory, it can't find the style sheet. Not a problem, really, except for the way that ASP.NET 2.0 development works. In development, the site runs as a virtual site, and the path to the css is different in development than when published.

This code will fix that.

Create the css links in the header section of the master page like this:

This piece of code is worth the price of admission

It happens that I want to move an accounting database from one server to another (we do a lot of Dynamics GP work), and the logins have to be persisted. You can't just recreate them manually because the passwords are encrypted and there are often just too many of them.

 To migrate a SQL box:

  • Run this script on the old server, it will create a script that will recreate the logins.
  • Stop the old server
  • Copy the .mdf files to the new server
  • Attach the databases on the new server

 

These posts aren't going to win any Pulitzers, or even 3rd grade spelling contests. They're just our favorites. In a forum this size, you sometimes want to find a post that isn't otherwize categorized, so...

Branding

Logos through the years

So, you want to sell things on-line...

Things that bug me

 

Welcome to our Web Services index

Web Services 101 - sample code for connecting to Web Services

Here is some starter code for Web services. It's not ground beaking code, it's all in the documentation; but it's a start if you're coding a project

Required subroutines - all the example code below need these subroutines to work

 

PM

Get a list of POs

Get a list of vendors

Get one vendor

RM

Get all customers

 

 

Be sure to see all the other Web Services content on the Web Services for Dynamics GP menu.  

Here is sample code to get all customers in Dynamics Web Services

You'll need to get the common code first, this code will require it

Be sure to see all the other Web Services content on the Web Services for Dynamics GP menu.  

Here is sample code to get a list of Vendors in Dynamics GP Web Services

You'll need to get the common code first, this code will require it

Be sure to see all the other Web Services content on the Web Services for Dynamics GP menu.  

Here is sample code to get a list of POs in Dynamics GP Web Services

You'll need to get the common code first, this code will require it 

Be sure to see all the other Web Services content on the Web Services for Dynamics GP menu.  

These subroutines are required by our sample Web Services code to run.

 

Recently I was trying to troubleshoot an MSIEXEC install (you know, where you see MyInstallName.msi in the folder) that wasn’t really giving an error message, and wasn’t logging anything either.

This post shows a technique to enable verbose logging for MSIEXEC so that you can see more of the issue that that you're having.

This is a copy of a post off of the Microsoft forums that I didn't want to lose.

 

I have the problem about change database name and server name runtime...

I use CR for VS.NET 2005 to make a simple report
Use OLEDB for SQL Server and write SQL Statement in COMMAND OBJECT..
(Error will not occur, if I choose link table, not manually write SQL Statement)
I found that i cannot change DBName and ServerName

I receive error when i call function
Report.Database.Table(i).ApplyLogOnInfo(NewLogOnInfo)
--------------------------------------------
Failed to load database information.
Details: The database DLL 'crdb_query.dll' could not be loaded.
Error in File C:\DOCUME~1\anop3\LOCALS~1\Temp\{68D71403-5484-4650-B960-D55DB2523564}.rpt:
Failed to load database information.
---------------------------------------------

Then, I write code in miss password for bring popup, "Select Database" Window, to show
I found that if I write SQL Statement,
The Combo box of ServerName and Database are disable
I can change only userID and Password

Then I found new function in CR For VS2005
Report.SetDatabaseLogon()
It isn't show error but it not work to bring data from new database

By the way, it correct if i change only userID and Password

So, I back to try with VS2003... It is OK....

I don't sure i found it have hotfix for this problem in Crystal Version 10 but not Fix For "Crystal Report for .NET 2005"....
How can I apply the fix?

Please Help me to find out what should i missing and
what should i do

I think it is the critical function because
When i develop i use Develop Server and
I need to change database to Production Server
and it can done in VS2003

This article, and all the other .NET development articles, can be found on the .NET Development Menu  

Here is the link for Crystal XI Release 2

http://www.businessobjects.com/products/reporting/crystalreports/xi_release_2.asp

Another important link. This one has the server install, and merge modules

http://support.businessobjects.com/downloads/merge_modules.asp#06

 

This post will walk you through creating a VB.NET Deployment Project
Here is a walkthroug for creating a localized site in ASP.NET 2.0

To get a reference to a specific control in a gridview rowcommand event:

 

It ends up being harder than it sounds... the javascript methods that we used before are not as freindly now. Here's an easier method:

 

If you need to format a datagrid template column fields, it can be hard to find the documentation.

 

 

This piece of code will validate an email address.

This short code example show how to set and read a cookie in ASP.NET

This is not a primer on ASP.NET Validation controls, there are plenty of those out there. This is just my handy little piece of cheat code - it's easier to copy it out of here when I need it...

First, place the ValidationSummary control on the page. We'll have the individual controls display an '*' for error, and the Summary control will show the error text

 

How to Create a Default 'Enter' Button

ASP.NET 2.0 introduces the concept of a "default button". The new defaultbutton attribute can be used with a <form> or <asp:panel> control. What button will be "clicked" depends on where the cursor acutally is and what button is choosen as a default button for the form or a panel.

Here is sample HTML code that contains one form and one panel control:

 

Very simple. Here is the code

 

Setting a default value on a dropdownlist seems like a simple thing to do... but I never have the exact code when I need it.

 

 

There are legions of articles (articli?) out there on how to trap errors in ASP.NET. My purpose here is not to be bigger, better, faster. I'm only trying to blog the code that I use, so that I can use it in the next app that I write. So... keep your expectations low <smiles>

 

I can never remember this piece of code to encode ASP.NET output:

 

On a recent project, I needed to query Active Directory to get a list of people in a specific group.  I didn't find much sample code for this so i thought i'd post what I figured out. 

This function returns the users in the current active directory group.  It does this by getting a list of all the users and checking what group they are in.

The group itself lists what users are in it, but it stores the display name not the user name.

This code may not be scalable, but it worked for us. I read, but haven't tested, that ldap queries limit to 1000 results so this may be a problem if there are more than 1000 users.

 

We have all seen the websites that disable "submit" buttons when you click on them. This is often done to prevent users from clicking the button multiple times.
Normally this is accomplished using an 'onclick' JavaScript event to disable the button. In ASP.NET, each server side item already has a onclick event handler which calls the server back for event processing.

To accomplish the same thing in ASP.NET, you could easily do:

 

Here's a useful tip for adding lines to an html table in code. Normally I'd define and populate a grid, but sometimes that's overkill 

 

Someone wanted to reset their password for their aspnet membership site. Since I didnt have the source code only the database information, I had to create a new project with only a page setup for password changing. Make sure you set the new projects web.config file to match the role provider information for the database currently used.

 

 

This article can be found in the VBA/Dynamics Modifier section of the VBA for Dynamics GP menu

I've avoided blogging this because I always figured that it would just... go away, but I always seem to need something from here.

Here's the standard adovbs.inc file.

The whole thing needs to be wrapped in <%... %> if you really want to use it right... I usually just want a value

 

This blog will detail how to do URL Rewriting.

The idea here is to provide a method to allow us to write URLs like these

http://www.store.com/articles/links
http://www.store.com/articles/aboutus
http://www.store.com/articles/resources

and have them resolve to

http://www.store.com/articles.aspx/links
http://www.store.com/articles.aspx/aboutus
http://www.store.com/articles.aspx/resources

Note that the source URLs don't have any '.aspx' at all.

 

This post shows how to iterate through a ComponentArt grid. In this grid we have a check box column in the grid, and we want to loop through only the selected items.

 

 

DataGrids often have a delete button that removes data from the database.  There isn't, however, an immediately obvious way of making a delete confirmation popup.  By adding a javascript attribute in the datagrid ItemCreated event, we can have the standard javascript confirmation box appear.

 This html side of the page would have a template column defined something like this:

 

When restoring a SQL Server backup, the users that are restored in the database are not the same as the users that were created at the server level, even though they may share the same name.  This creates a problem with websites/users/etc that try and log into these databases.  Because the accounts aren't the same, the login is rejected.

There are several ways to fix this problem. 

 

 

I happens that I need to do a running total in a report, and I can't use the built in 'running total' feature for some reason or another. (the current issue is that I need a running total on a formula field that exists only in a group footer)

The code to do the deed is kind of a pain, but here it is.

 

This code was made with Crystal 11 r2 and Visual Studio 2005.  It may not work correctly with other versions.  It shows the code needed to manually populate a subreport in Crystal.

 

Often a report has so much data, it's hard to follow the line across the page. Customers ask me to 'put lines across the report so I can read it better, like Excel'

I think a more effective technique is to alter the background color of every third line. Here is the code to do that:

 

 

Could not load file or assembly 'CrystalDecisions.ReportAppServer.ClientDoc, Version=11.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

When deploying a Crystal-enabled web app to a new server.

 I solved this, but it was a long road.

From a recent email: 

I am creating a Crystal report where I want a count of invoices that have backorders.  I have tried creating a formula field that returns a 1 or 0 if the sum of quantity backordered for an order is greater than 0.  This works fine but I cannot sum this field for the report to give me a total count.  Can you think of a way to do this?

Answer:

 

In Visual Studio 2008, merge modules are no longer used for crystal reports controls.

 To add the crystal reports files:

 

'CrystalReportViewer' is ambiguous in the namespace 'CrystalDecisions.Web'

Also:

Error 13 'ReportDocument' is ambiguous in the namespace 'CrystalDecisions.CrystalReports.Engine'. C:\projects\Culinart\Culinart\Reports\POSCash.aspx.vb 11 18 C:\...\Culinart\

Error 15 'DiskFileDestinationOptions' is ambiguous in the namespace 'CrystalDecisions.Shared'. C:\projects\Culinart\Culinart\Reports\POSCash.aspx.vb 47 26 C:\...\Culinart\

Error 16 'ExportDestinationType' is ambiguous in the namespace 'CrystalDecisions.Shared'. C:\projects\Culinart\Culinart\Reports\POSCash.aspx.vb 50 57 C:\...\Culinart\

Error 17 'ExportFormatType' is ambiguous in the namespace 'CrystalDecisions.Shared'. C:\projects\Culinart\Culinart\Reports\POSCash.aspx.vb 51 52 C:\...\Culinart\

 

This is a walk through for changing the datasource of a VS 2008 Crystal Report at runtime.

Create a new form in VB, add the Crystal Report Viewer control, dock it. For the sake of simplicity, I've kept the code as concise as possible. The form has two public properties, the report name and the data source. We pass those in before opening the form.

 

Here is the 'starter code' that I use when creating the ItemDataBound event. It is not guaranteed to make sense, but exists so that I have sample code that I can alter to get a faster start.

Here's how to loop through an ASP.NET datagrid

 

KB878449_dex_req

This article, and all the other .NET development articles, can be found on the .NET Development Menu  

A word of introduction to the 'introduction'. This is from the magazine ASP.NET. If you are reading this and you don't have a subscripton, stop and get one now - it's invaluable.

 I only copy the article here because the information is so good - I don't want to have to waste time looking for it or worry that it will get archived.

 Again - get the mag. http://www.aspnetpro.com/ 

Introduction to Regular Expressions: Part I

Creating Expressions

 

 

This two-part article series provides a quick and practical introduction to using regular expressions. Regular expressions can be used for many things; however, they are typically used for input validation or to perform advanced searches on text in supporting applications. This first article will explain how to create a regular expression pattern; the expression defines what is considered a match. The second article will provide details on how to implement regular expressions in .NET applications.

 

Before starting I’d like to point out I have a free regular expression tester available on my Web site (http://www.mitchelsellers.com); you can use this to test the behavior of your regular expressions. During the second article I’ll discuss the specific options available on this test page, as well as how the page was created.

 

Regular expressions have three basic types of symbols that are used: meta characters, escape characters, and character classes. The following table lists the important meta character(s), a short description, and an example of each.

 

Character

Description

Example

Matches

^

Indicates the start of a string; used to match a specific beginning sequence.

^abc

abc, acb123, abcdefg

$

Indicates end of a string; used to match a specific ending sequence.

abc$

123456789abc, 987abc

.

Any character excluding \n (new line).

a.c

abc, aac, a9c

|

Or operator used to specify one criteria or another.

john|jane

jane, john

*

Zero or more of previous expression.

12c*

12, 12c, 12cc

+

One or more of previous expression.

1a+c

1ac, 1aac

?

Zero or one of previous expression.

12?c

1c, 12c

\

Escape character, used to make any of the special characters (^, $, ., |, *, +, ?, (, [, {, etc...) literal for matching. See next chart for other escape characters.

1\*a

1*a

{....}

Explicit quantifier notation; used to indicate _ occurrences of a character or character class. A comma can be added to provide min/max occurrences.

12a{2}

12aa, 12aa3

[....]

Matches a range of characters; you can provide collections of characters (abcdefg), as well as hyphenated ranges of characters for matching (A-Z).

123[abc]

123a, 123b, 123c

(....)

Groups a portion of the expression; used to group sections for display.

(123){2}

123123

Meta Characters

 

The characters in the table below are used to match special characters in regular expressions; we will use some of these later in this article. NOTE: This is a list of commonly used escape characters, not a complete list of escape characters.

 

Character

Matches

\b

Word boundary; indicates a space or other non-word character to signify the end of a word.

\t

Tab character.

\n

New line character (great for multi-line textboxes).

\(any metacharacter)

Matches the entered meta character. (\* matches *, \$ matches $).

Escape Characters

 

Below are character classes that represent different groups of characters to make it easier to match common groups of characters.

 

Character Class

Description

Example

Matches

.

Matches any character except \n. If Single Line option is enabled, it matches ANY character.

a.c

aac, abc, a1c

[rstlne]

Matches any single character in the provided list.

a[rstlne]

ar, as, al

[^aeiou]

Matches any single character NOT in the provided list.

a[^aeiou]

ab, ad, ah

[0-9a-zA-Z]

Matches any single character in the following ranges (0 through 9, A through Z, and a through z). The hyphen indicates a range element.

123[0-9A-F]

123A, 1234

\w

Matches any word character; in ECMAScript mode this matches [0-9A-Za-z].

123\w

123a, 1234

\W

Matches any NON-word character; in ECMAScript mode this is the same as [^0-9A-Za-z].

123\W

123$, 123-

\s

Matches any whitespace character; in ECMAScript mode this matches spaces, tabs, and new lines.

123\sa

123 a

\S

Matches any NON-whitespace character.

1\Sa

14a, 1ba

\d

Matches any digit character; in ECMAScript mode this matches 0-9.

\d2

12, 32

\D

Matches any NON-digit character; in ECMAScript mode this matches anything that is not 0-9

\D2

a2, b2

Character Classes

 

How to Apply this Information

Now that we’ve explained the various characters included in matching regular expressions, let’s walk through some practical examples to illustrate how all these items are pulled together. In the following subsections I’ll walk you through a series of real-world validations and provide examples with detailed information.

 

Before beginning the examples I want to point out that in ALL of my examples the regular expressions created start with the ^ character and end with the $ character. This is done to ensure that the expression matches the entire string. This is done to ensure that the string is that match, and ONLY that match. Otherwise, you can receive matches for strings with more than the included characters. You may play around with this using my expression tester to see the effects of omitting the ^ and $ characters.

 

Postal Code Validation

Postal code validation is a very common user input validation; typically, your postal code will either be five digits or nine digits, with a hyphen after the fifth digit. We can validate this input with the following expression:

 

^\d{5}(-\d{4})?$

 

First we have the “\d{5}” portion of the expression, which indicates that the input must start with five digit characters (0-9). Next the portion of the expression inside the parenthesis, “-\d{4}” indicates a hyphen (-) to be followed by four digit characters. This is grouped within parentheses and has a question mark appended to the end. This question mark indicates that the input should have zero or one of the preceding items, which happens to be the entire expression in the parentheses. Therefore, in the case of zero, the expression would simply be five digit characters; in the case of one, the expression would be five digits, a hyphen, and four more digits.

 

Simple Date Validation

Validation of date input is another very common occurrence, full regular expression date validation is very involved; however, it is very easy to restrict users to a MM/DD/YYYY format with basic checking for incorrect input. Below is a regular expression to validate a date in the MM/DD/YYYY format; I’ve added parenthesis for readability:

 

^([01]\d)/([0-3]\d)/(\d{4})$

 

The first section of this expression “([01]\d)” represents the month portion of our date, because there are only 12 months in the year we restrict the first digit to either a zero or a one, and the second character can be any number 0-9. This is one portion of this example that can be improved upon; you can modify and create regular expressions that are capable of validating that the input is between 1 and 12 (however, this is outside the scope of this article).

 

The second section of this expression “([0-3]\d)” represents the day portion of our date. This is separated from our first part by a / character, which is a literal requirement that the month be separated from the date by a forward slash. The first part of our day check requires that the first digit of the day is a 0, 1, 2, or 3, then the second digit can be any number 0-9. Just as with the month portion, this can be expanded to ensure that the day value is appropriate for the month provided; however, it is outside the scope of this article.

 

The final section of this expression is again separated by a / character, then it allows for four digit characters to be entered. This forms the final portion of the date.

 

Phone Number Validation

Another common input item to validate are phone numbers, including area codes and extensions. Below is a sample regular expression that validates a phone number that meets one of the following formats; (555) 555-1212, 555-555-1212, (555) 555-1212 x1111, or 555-555-1212 x1111. Portions of the expression have been highlighted to illustrate the different sections of logic. These sections will be explained below:

 

^ (\(\d{3}\)\s|\d{3}\s) (\d{3}[\s-]\d{4}) (\sx\d+)?$

 

The yellow portion of this expression validates the area code input. Notice that we have two individual groups separated by the or operator (|). This indicates that one of the two expressions must be true. The first one validates on a left parenthesis (, three digits, a right parenthesis ), and a space; the second option validates on three digits and a space. Therefore, the phone number must begin with either (515) or 515; this validates the area code portion of our phone number.

 

The green portion of this expression validates the remaining portion of the standard phone number. The first part “\d{3}” requires three digits, then the “[\s-]” allows for either a space or a hyphen. This is then followed by the “\d{4}” portion, which indicates that an additional four digits are required. We now have validation for a standard 10-digit phone number with support for multiple formats.

 

The gray portion of this expression validates the optional telephone extension. The expression “\sx\d+” indicates that the input string should have a space, the letter x, and then one or more digits. This is enclosed in parentheses and followed by a question mark to indicate that it is optional. This provides for validation of numbers such as (555) 555-1212 x102.

 

This should provide a helpful overview of regular expressions. Stay tuned for Part II.

 

Mitchel W. Sellers is a Microsoft Certified Professional Developer with multiple specializations. He’s been developing in .NET since shortly after the release of .NET 1.1 He is the Co-Founder of a startup software consulting firm, IowaComputerGurus L.L.P. He is also very active in multiple online communities, including GotDotNet and DotNetNuke. Find out more about him at http://www.mitchelsellers.com or e-mail him at mailto:mitchel.sellers@gmail.com.

 

This article, and all the other .NET development articles, can be found on the .NET Development Menu  

A word of introduction to the 'introduction'. This is from the magazine ASP.NET. If you are reading this and you don't have a subscripton, stop and get one now - it's invaluable.

 I only copy the article here because the information is so good - I don't want to have to waste time looking for it or worry that it will get archived.

 Again - get the mag. http://www.aspnetpro.com/ 

 Introduction to Regular Expressions: Part II

Validating Expressions in .NET

This is the second part of my Regular Expressions overview. In the first article I discussed the basics of creating Regular Expressions and provided a link to test expression patterns using the .NET Framework RegEx classes. In this article I’ll discuss the various actions that can be taken to match values in a string using regular expressions. I’ll also discuss how you can implement a SQL CLR UDF to allow regular expression validation from your database to provide a strong level of input validation at the database level.

I’ll start by discussing the use of the RegEx classes in standard .NET applications. My examples will all be based off a C# .NET console application designed to display the results of expression testing. You can expand these examples to apply to other program types. Please note that for all examples you must add a “using System.Text.RegularExpressions” statement to your code to be granted direct access to the RegEx classes. (See end of article for download details to obtain sample projects for each of my examples.)

Regular Expressions: Standard .NET Application Examples

Using bool Regex.IsMatch(string input, string pattern). The first Regular Expression match option I’ll provide is the static method Regex.IsMatch(string input, string expression). This method provides a quick way to receive a boolean result regarding a match between a regular expression and the input string provided by the user. The method signature in its simplest form is provided above. Using this overload of the “IsMatch” function you use the default regular expression options and receive a boolean value indicating the success or failure of a match.

Below is the code required to receive input from the user, and to test the input value for a match based on the regular expression:

 

//Prompt the user, 2 separate input items (inputRegEx and inputMatchText) 
  
Console.WriteLine("Welcome to the Regular Expression demonstrator!"); 
  
Console.Write("Please enter a regular expression string:"); 
  
string inputRegEx = Console.ReadLine(); 
  
Console.Write("Please enter a test string for matching:"); 
  
string inputMatchText = Console.ReadLine(); 
  
//Perform the match test, then output the result 
  
bool isDirectMatch = Regex.IsMatch(inputMatchText, inputRegEx); 
  
Console.WriteLine("Result of Regex.IsMatch(string input, string expression): " + isDirectMatch.ToString()); 

Using this method I performed a test on the expression “b\w*” (without the quotes); this is to match a string that contains the letter b followed by zero or more word characters:

Value      Result

billy         true

billy777   true

b              true

Billy         false

aaaBill    false

The results reported above are to be expected for a few reasons. Firstly, by default, Regular Expression matches are case sensitive, therefore my above match would ONLY work on an input string that contained a lowercase b. At times you want to validate that a string contains a particular string, but you don’t care if it is an uppercase or a lowercase letter. You could modify your expression to be “[bB]\w*” to allow an uppercase or lowercase letter b; however, this can add an extra level of confusion to your expression. The .NET Framework provides a RegexOptions enumeration you can use to provide additional options when matching expressions. We’ll discuss using this next.

Using bool Regex.IsMatch(string input, string pattern, RegexOptions options). This method allows us to use the RegexOptions enumeration to specify a specific option or bit-switched option set. For the case of this article we’ll only discuss the IgnoreCase RegexOption; however, to perform further research on the available options, please see the following MSDN article: http://msdn2.microsoft.com/en-us/library/system.text.regularexpressions.regexoptions.aspx. This example code performs a case-insensitive match on an expression text:

The change to this example is very simple, yet the effect on the valid matches is great. Below is the truth table for the same examples used in the first test of the article. The same regular expression text was used; the only difference was that RegexOptions.IgnoreCase was provided:

//Perform the match test, using the ignore case option
  
bool isIgnoreCaseMatch = Regex.IsMatch(inputMatchText,
  
inputRegEx, RegexOptions.IgnoreCase); 
  
Console.WriteLine("Result of RegEx.IsMatch(string input,
  
string expression, RegexOptions options): " + isIgnoreCaseMatch);

Value      Result

billy         true

billy777   true

b              true

Billy         true

aaaBill    true

This shows you the power of using the regular expression options; you can provide more flexibility in your validation system. Using the Regex.IsMatch method is a great tool, but it won’t always suit your needs — there are times you need to find how many matches there are for a specified string among other complex validations. We’ll discuss some other options available in the following section.

Using MatchCollection Regex.Matches(string input, string pattern). The Regex.Matches method provides a facility to check a string for multiple matches of an expression within the string. This can be helpful when validating license keys or other types of input that might have multiple occurrences of the same pattern. The inputs for this method are the same as for the IsMatch function; however, this method returns a MatchCollection object to show you the collection of matches. The MatchCollection will contain one Match object for each successful match. The Match object provides methods to receive information about the match. The most helpful methods are Index, Length, and value. Index is the index of the starting match character in the input string; this allows you to extract the match if needed. The Length value is the number of characters contained in the match and the Value is the actual match string.

Below is the code required to attempt a multiple match and then output the results of the match:

Standard Usage Summary. Using the above examples should help you get started with Regular Expression validation in .NET. The .NET Framework provides many methods and classes to validate and work with Regular Expressions and this article has only scratched the surface. Please see the below section to see how to create a CLR UDF to validate Zip Code Input!

//Perform Matches() test
  
MatchCollection oMatches = Regex.Matches(inputMatchText, inputRegEx); 
  
Console.WriteLine("Matches found using Regex.Matches(string input, string pattern): "
  
                   + oMatches.Count.ToString());
  
   
  
Console.WriteLine("Match Detail, if appliciable"); 
  
   
  
//Loop through the collection, this will be skipped if no match
  
foreach (Match oMatch in oMatches) 
  
{
  
   Console.WriteLine("Match Index: " + oMatch.Index.ToString());
  
   Console.WriteLine("Match Length: " + oMatch.Length.ToString());
  
   Console.WriteLine("Match Value: " + oMatch.Value); 
  
   Console.WriteLine(""); 
  
   
  
}

Regular Expressions in SQL CLR User Defined Functions

A place where Regular Expression validation can become very handy is in SQL Server 2005 CLR User Defined Functions and Stored Procedures. Prior to the ability to use CLR functions and procedures in SQL Server it was very cumbersome to implement sophisticated string validation at the database level. Now with the SQL CLR functionality you can quickly build procedures that can be used to validate input on SQL Server. Below is an example of how to create a CLR User Defined Function to validate a zip code based on the expression we created in Part I one of this article series.

First, before building this example function we must ensure that CLR Integration is enabled on your specific database. To validate this you may run the following script to enable CLR Integration:

sp_configure 'clr enabled', 1
  
GO
  
RECONFIGURE
  
GO
  
  

When this has been completed you’ll want to create a new SqlServer project. You can create this project by selecting New Project from the File menu in Visual Studio. You’ll find the SqlServer project type under Visual C# | Database | SqlServer. (NOTE: you may also create the UDF in Visual Basic by selecting the SqlServer project type from the Visual Basic project listing.) When you create the project it will request that you provide it a link to your SQL Server. This is needed for the automatic deployment and configuration of your stored procedure.

 Once your project has been created you’ll want to right-click on the project and select Add | User-Defined Function; you’ll then be asked to give it a name. In our case we’ll call it ValidateZip.cs to keep the name short and simple. Visual Studio will then provide a shell to place the code for your validation method. In our case we’ll want to be sure to set the return type to “bool” as it is simply a yes or no answer. We’ll also want to ensure that an input string value was provided. Because a zip code is an all or nothing validation, we’ll use the Regex.IsMatch method with a specific validation string to provide the result to the calling user. Below is the completed code to validate the zip code (NOTE: all User-Defined functions intended for use in SQL Server must be declared as public and static!):

public static bool ValidateZip(string input) 
  
{
  
   //Declare our expression
  
   string expression = @"^\d{5}(-\d{4})?$"
  
   //Test the input and return the result
  
   return Regex.IsMatch(input, expression); 
  
}

Now that you have the function built you can simply right-click on your project and select Deploy. Visual Studio will then register your function with SQL Server and you can now freely use this validation function in your SQL Queries. Below is a sample SQL Query to retrieve the validation result for a local Des Moines, Iowa zip code. If successful validation occurs, a 1 will be returned, if unsuccessful, a zero is returned.

select dbo.ValidateZip('50320')

Conclusion

This article shows you the basics of Regular Expression validation in .NET, as well as how to incorporate regular expression validation into new SQL Server CLR User Defined Functions. This should serve as a great starting point for understanding the various methods to implement regular expression validation in your new and existing projects. The download file contains two sample projects and the sample code used in this article. Please feel free to review this code and let me know any questions you might have.

In order to loop through (iterate through) the list of available database in a network using SQL SMO:

 

The following code will keep a form from opening twice in an MDI environment:

 

 

Here is a trick I found while trying to delete rows from a DataSet.  I first tried the following code, but it throws an exception because the enumerator has changed.

 

This is a code example for storing image data in SQL server.

Here is a VB.NET code sample that shows how to process arguments for a console app

Create a console app, and in the project properties click on DEBUG, then specify something in the 'Commant Line Arguments' area. For the sample app, you might put 'olap diskspace' and those two items will be processed.

Once you build and deploy the app, you would call the app like this: MYAPP.EXE OLAP DISKSPACE, where OLAP and DISKSPACE are my example arguments

Here a sample of code that will write to the application event log, using VB..NET:

 

 

We want to be able to set this XML file as the datasource for a grid. This is a short code example on how to do it.

Our XML file:

<seriesSermons>
    <series>
        <title>Building</title>
        <desc>As we enter into the construction phase of our building project, Pastor Campbell brings a series on building. Building not only walls and doors, but lives.</desc>
        <author>Dr. Mark Campbell</author>
        <details>
            <date>20080824 Sun AM</date>
            <file>Building with Prayer</file>
            <description>Nehemiah 2:1-11
From old testament and new, Pastor Campbell continues the series on building. This week - building with prayer.
</description>
        </details>
        <details>
            <date>20080817 Sun AM</date>
            <file>Building with Prayer</file>
            <description>
Nehemiah 1:1-11
As our new building is set to begin, Pastor Campbell begins a series in Nehemiah - God’s building story.
</description>
        </details>
    </series>
    <series>
        <title>Acts</title>
        <desc>
Pastor Campbell continues our weekly bible study in Acts
</desc>
        <author>Dr. Mark Campbell</author>
        <details>
            <date>20080720 Wed PM</date>
            <file>Acts 26</file>
            <description>
Pastor Campbell continues our weekly bible study in Acts
</description>
        </details>
        <details>
            <date>20080806 Wed PM</date>
            <file>Acts 26</file>
            <description>
Acts 26:19-20
Pall continues his defense before King Agrippa
</description>
        </details>
    </series>
</seriesSermons>

 

Deleted row information cannot be accessed through the row

Banged my head against this one for about 2 hours. The answer is pretty simple:

Selecting data on a dataTable using the datatable.select method

The following example uses a filter expression to return an array of DataRow objects.

 

When I develop a VB application that calls a web service, I usually am developing the web service in the same solution. That's convenient, because you can step through and debug all the code.

But I'd also like to be able to deploy the web service and have my VB app call the live service. So...

Here's VB code that will call a web service with a dynamic URL. First, the app.config entry that makes it possible:

 

I have a SQL database that uses some 'BYTE' datatypes. I ran into some issues getting it converted into a VB.NET datatype:

Conversion from type 'Byte()' to type 'Boolean' is not valid.

 

This simple piece of code creates a DataTable on the fly.

 

I have an application that is designed as a class library (a .dll) that is called by a WinForms app. The class library is trying to do data access using the Microsoft Data Access Application Block (which I really love, BTW).

Anyway, I was getting 'The requested database xyz is not defined in configuration' when I attempted data access. Grrr 

This is an example of a WinForms error handling class that we insert into every project. It has classes for error handling, writing to the event log, and sending email

 3/21/2012 Added support for lists of exceptions

This is the class that I use in VB Win Forms applications for user data. this class has methods to return the userid, current database and connection string

 

Here are a couple of simple tasks in XML for VB.NET

We cover examples of using oXMLDoc.selectSingleNode and oXMLDoc.selectNodes

Also, searching for a substring in an attribute, a substring in a node value, a value contained in an attribute, and for text contained in a node value 

I got first this error: 

The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.

Followed by this error

The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state.

The solution:

Greetings all.
i have two question, two specific and one general and would appreciate any help and or pointers in the right direction.
     1. Is it possible to get the items that comprise a kit
           a. i get orders from eConnectMethods.GetEntity and when one of the items is a kit i need to get the items themselves but cannot find a document type to pass to my eConnectType
           b. the same for shippingmethods ( table sy03000)
                    i. when I retrieve an order in the same fashion as above I need to get the details of the shipping method

    2. I am fining it fairly difficult to find help regarding howto for econnect ( I would expect to find a list of supported DOCTYPE for example ) where are

the good resources to learn how to interact with GP via econnect and smartconnect.
So far I’ve been living at  msdn and the below
http://community.dynamics.com
http://dyndeveloper.com

regards

Adam

Hi All,

I am getting the error "Sql procedure error codes returned:Error Number = 370
 Stored Procedure taPoHdr  Error Description = Subtotal (SUBTOTAL) does not match the line item totalsNode Identifier Parameters: taPoHdr  
..........

when doing POP transaction into GP. If anyone has seen  this before, I'd greatly appreciate your help.

 

Thank you.

Regards

Rajesh

 

 

 

Hi Team

         

         I am using eConnect.MiscRoutines.dll  version 9.00 to integrate purchase order to GP9 in Visual studio .net 2003. I have already added reference to

eConnect.MiscRoutines.dll and System.Enterprise.Services. There is no GetNextDocNumbers method found in this reference. Instead of GetNextDocNumbers there exists GetSopNumber method.

 

Please help...

Thanks & regards

Rajesh 

 

I used the example I found on line from Microsoft for eConnect Serialization and using Visual Basic. Everything worked fine, I setup breakpoints in the code to step through it to see the data. It looped through the data, created the xml file exactly like the example BUT no data was moved into GP. As a simple test I was moving customers from one GP database to another GP database on the same server. Anyone have any ideas? No Error Messages were generated.

I can econnect sales transctions that do not have tax into GP succesfully. By choosing a specific customer and the tax schedule ID I can manually enter a sales transcation in GP, and GP autmatically calculates tax for me.

However when the sales transactions are brought in through econnect, I either get an error or the transaction gets posted but tax is not reflected in GP.  

Could some one point me to some sample econnect code or xml document that will show me what are the fields that need to be passed and what value needs to be passed. The code I have so far is as follows:

Dim objtaSOPTax(1) As taSopLineIvcTaxInsert_ItemsTaSopLineIvcTaxInsert

Dim objTax As New taSopLineIvcTaxInsert_ItemsTaSopLineIvcTaxInsertobjTax.SOPTYPE = objtaSopHdrIvcInsert.SOPTYPE

objTax.SOPNUMBE = objtaSopHdrIvcInsert.SOPNUMBE

objTax.CUSTNMBR = ConfigurationSettings.AppSettings("GPCustomer546TAX").ToString()

objTax.TAXDTLID = ConfigurationSettings.AppSettings("TAXDTLID546").ToString()

objTax.TDTTXSLS = decTotal - decSalesTax

objTax.TDTTXSLSSpecified = True

objTax.TXDTOTTX = decSalesTax

objTax.TXDTOTTXSpecified = True

objTax.SALESAMT = decTotal - decSalesTax

objTax.STAXAMNT = decSalesTax

objTax.LNITMSEQ = 0

objtaSOPTax(0) = objTax

In the header:

objtaSopHdrIvcInsert.SUBTOTAL = decTotal - decSalesTax

objtaSopHdrIvcInsert.TAXSCHID = ConfigurationSettings.AppSettings(
"TAXDTLID546").ToString()

objtaSopHdrIvcInsert.TAXAMNT = decSalesTax

objtaSopHdrIvcInsert.USINGHEADERLEVELTAXES = 1

objtaSopHdrIvcInsert.CREATETAXES = 0

 

I'm attempting to import a bank transaction via eConnect into the Microsoft GP2010 sample company, but receive the following error:

--------------------------------------------------------

Sql procedure error codes returned:

Error Number = 7604  Stored Procedure= taBRBankTransactionHeader  Error Description = Originating Debits and Credits do not match (ORDBTAMT <> ORCRDAMT in CM20400)
Node Identifier Parameters: taBRBankTransactionHeader
CMTrxNum = Trx003
CHEKBKID = UPTOWN TRUST

<taBRBankTransactionHeader>
  <Option>1</Option>
  <TRXDATE>2017-04-15</TRXDATE>
  <CHEKBKID>UPTOWN TRUST</CHEKBKID>
  <CMTrxNum>Trx003</CMTrxNum>
  <paidtorcvdfrom>Hotel/Client ID</paidtorcvdfrom>
  <DSCRIPTN>Reservation no.</DSCRIPTN>
  <TRXAMNT>2000</TRXAMNT>
  <USERID>sa</USERID>
  <BACHNUMB>RES_2011_07_15</BACHNUMB>
</taBRBankTransactionHeader>
--------------------------------------------------------

This is my XML:

 

<eConnect>
    <BRBankTransactionType>
        <eConnectProcessInfo/>
        <taRequesterTrxDisabler_Items/>
        <taBRBankTransactionDist_Items>
            <taBRBankTransactionDist>
                <Option>1</Option >
                <ACTNUMST>100-5110-00</ACTNUMST>
                <CRDTAMT>2000</CRDTAMT >
                <DistRef>Test</DistRef>
            </taBRBankTransactionDist >
        </taBRBankTransactionDist_Items>
        <taAnalyticsDistribution_Items/>
        <taBRBankTransactionHeader>
            <Option>1</Option >
            <TRXDATE>2017-04-15</TRXDATE>
            <CHEKBKID>UPTOWN TRUST</CHEKBKID >
            <CMTrxNum>Trx003</CMTrxNum>
            <TRXAMNT>2000</TRXAMNT >
            <BACHNUMB>RES_2011_07_15</BACHNUMB>
        </taBRBankTransactionHeader >
    </BRBankTransactionType>
</eConnect>

I've made a point of not including the default distribution which eConnect creates automatically. 
Any ideas??

 

 

Folks,

I am new to eConnect. I have learned a lot all by myself solving lots of puzzles in the process. My work requires me to only know the basics and I almost know what is required of me. However, I have recently encountered a couple of issues which I am having great deal of troubles with. Here is one of them.

I am trying to create a SOP payment in Dynamics using the taCreateSopPaymentInsertRecord node, as per the xml below.

<?xml version="1.0" encoding="utf-8"?>
<eConnect>
   <SOPTransactionType>
      <eConnectProcessInfo />
      <taCreateSopPaymentInsertRecord_Items>
         <taCreateSopPaymentInsertRecord>
            <SOPTYPE>3</SOPTYPE>
            <SOPNUMBE>STDINV2267</SOPNUMBE>
            <CUSTNMBR>KENSINGT0001</CUSTNMBR>
            <DOCDATE>20/06/2011</DOCDATE>                      <!--EURO DATE FORMAT-->
            <DOCAMNT>20.00</DOCAMNT>
            <PYMTTYPE>4</PYMTTYPE>                                  <!--4=CASH, 5=CHECK, 6=CC-->
            <DOCNUMBR>PMT4CASH</DOCNUMBR>
            <ACTION>1</ACTION>                                              <!--1=NEW TRANSACTION, 2=MODIFY EXISTING-->
         </taCreateSopPaymentInsertRecord>
      </taCreateSopPaymentInsertRecord_Items>
   </SOPTransactionType>
</eConnect>

(I am testing this with Fabrikam).

I get the following error: ERROR: Microsoft.Dynamics.GP.eConnect.eConnectException: Node taSopHdrIvcInsert with required fields is required for new SOPTransactionTypes.

I want to add a (cash) payment against an EXISTING invoice. Why does econnect want me to create a new invoice which I don't need?

TIA

Piterrr

When I am trying to sync the data, the new error is coming.

 

Unique MessageId =
Line Number=7610 Version=8.0.4.0
Error Source = Microsoft OLE DB Provider for SQL Server
Error Number = -2147217833  Error Description = Arithmetic overflow error converting expression to data type tinyint.  Node Sequence Number = 0
 Document Node:
<taCreateSopPaymentInsertRecord><SOPTYPE>3</SOPTYPE>

<SOPNUMBE>I273427</SOPNUMBE><CUSTNMBR>63939</CUSTNMBR>
<DOCDATE>8/3/2011</DOCDATE><PYMTTYPE>6</PYMTTYPE>
<CARDNAME>RD-SPAN</CARDNAME><RCTNCCRD>5809</RCTNCCRD>
<AUTHCODE>858</AUTHCODE><EXPNDATE>09/08/2011</EXPNDATE>
<DOCAMNT>200</DOCAMNT></taCreateSopPaymentInsertRecord> @ <1:19:55 PM> * <8/5/2011>
Processing Transaction I273427 @ <1:56:28 PM> * <8/5/2011>

 

Any help will be appreicated.

This piece of code will add an item to a combo box datasource if it is not in the list

 

This code will return the current user in VBA (Access)

Because I code professionally, I'm not a big fan of using and Access query for data access, I'd prefer to code it manually. It's easier to read the code and see what's going on.

So, here's an insert statement using DBEngine

This is my 'isOpen' function for access, it allows me to tell if a form is open. The idea is that you call it from within a report 'onLoad' after opening a dialog that gets parameters. If the dialog is open (but hidden) you continue processing. If it's not open, you cancel the report

I put the following code in all my Access reports. It's not ground breaking code, but I like to have boilerplate code handy to copy and paste into applications

using  

cmd.Parameters.Append cmd.CreateParameter("@dtDate", adDate, adParamInput, 0, "11/13/2008"

it would return this error:

 

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

 Recently I was migrating some tables to SQL from an old Access database. The original programmer had put spaces and other punctuation in the field names that SQL didn't allow so I changed them, but that broke Access when I linked the table back. This code will loop through the querydef collection and find all instances of a table name or field name.

 

Hi,

I need to add 'Item Note' column with the PO Order Line items in the Report 'POP Purchase Order Blank Form'. The text of Item Note is stored in the 'Records Note Master' table 'SY03900' and Note Index Id is stored in column 'POLNEARY_1' in POP10110 table.

Please help or suggest how it can be done.

Thanks  in Advance,

Somesh Sahu

 

My workstation has Crystal Reports 9, 10, and IX installed on it, along with Visual Studio 2003 and 2005. This combination makes for an occasionally confused PC. Specifically, when deploying a VS 2005 VB.NET app.

What ends up happening is that VB deploys the CR 9 files, along with a few CR 10 files. This link will describe how to download the IX merge files needed and how to do the build.

http://support.businessobjects.com/fix/merge_modules.asp?ref=default.asp_wwwjump#07

Specifically, download crXI_net_merge_modules.zip

This is a piece of code that will compact (truncate) SQL logs. It's especially useful when a server has not been backed up for a while and the transaction logs have grown to a dangerous size

 

This script will give you a comma separated (or semicolon separated) list in SQL Server 2005. In other words, if you have a table with data like this:

item1
item2
item3

This code with give you:

item1, item2, item3

Editors Note:

There is a more recent version of this script here: http://dyndeveloper.com/ArticleView.aspx?ArticleID=4015

Auditing SQL tables is a very basic concept. You ocassionally need to save all changes to a table so that you have some history of the changes.

This script creates an audit table and triggers for the specified table.

Run the script against any table and an audit table will be created with the same name as the source table + 'AUDIT'. So auditing 'myTable' creates 'myTableAUDIT'.

We also create insert, update, and delete scripts that will populate the table.

You'll also need this script: http://dyndeveloper.com/ArticleView.aspx?ArticleID=1707

 

 

This article, along with other great articles, can be found on our SSRS General Index Menu.  

There are many tasks that call for writing to a text file. In order to get that done, we usually have to go into the .NET environment to get it done.

Here is a seriously nice piece of code that will allow you to write to a text file using SQL Server 2000. It makes use of the sp_OACreate and sp_OAMethod internal stored procedures to get the job done.

I use this method to write .vb classes, believe it or not. I have the code scan my database and write the data access code for my project. It's a huge time saver. If I write a new stored procedure, I rescan the database and all my data access code is updated, just that easy.

 

 

This piece of code will truncate the log file size for all the databases in a SQL Server. It has been tested against SQL 2000 and 2005

 

This article, along with other great articles, can be found on our SSRS General Index Menu.  

This script will send the contents of all the Stored Procedures, Functions, and Triggers in a database to a text file. In fact, it will send any object that can be found in SELECT * FROM SYSOBJECTS

Why is that useful? For me, it's indispensible. The Dynamics GP database (Microsoft's accounting package) that I work in has north of 34000 objects. If I change something, or need to know where a reference is, or the boss wants to know what stored procedure is deleting the sales orders every night, I have to play hide-and-go-seek through all the scripts.

What if someone proposes changing the name of a database field? Having the text of all my object in one file enables me to find all occurances of a field name in any of my scripts. Woo-Ha! Now I know the name of the stored proc or trigger that I have to change.

In the past I would just right click on the server name in Enterprise Manager, and choose 'Generate Scripts'. That gave me exactly what I needed, but in a database this size it took almost an hour. This script runs in about a minute.

 Additionally, it gives me a history. If I run this periodically, I am able to search past versions quickly and see what I changed.

 I'm not going to go into detail about how it works, but I'd be happy to if anyone shows an interest. Just drop me a line.

Following is a T-SQL script you can use to rename your SQL Server after you have changed the computer name. This script will work if you are connected to the default instance or a named instance. After running the script, you must restart SQL Server for the action to complete.

 

Creating a stored procedure with an OUTPUT parameter is not a difficult task, but I have to do it quite often and it is convenient for me to blog it here, so that I have an easily accessible example

Create the stored prodecure like this:

We run a web hosting company and we use SQL Server, it happens that we put up several new SQL databases a month. Invariably, the developer will forget to put the new database in 'Simple' recovery model. If you leave a database in 'Full' mode and only run regular backups, the transaction logs won't truncate and the drive will fill up. .

That's what happened today (sigh), so I developed this script to fix it. The script will show the recovery model of all the dbs, and and set them to Simple (or Full) if desired

 

 

 

How to Write Text to Files with a StreamWriter in Visual Basic 

This short piece of code shows how to code the System.IO.StreamWriter obect to save a file.

Error message when trying to send SQL 2005 mail:

No global profile is configured. Specify a profile name in the @profile_name parameter.

I did this by accidentally deleting the default profile, it took me a half an hour to figure out how to set it again.

 To fix:

 

This script will create an INSERT statement on the table that is supplied as a parameter

You'll need this script and one other:

f_4P_columnType

 

This article, along with other great articles, can be found on our SSRS General Index Menu.  

Here is a really nice datetime calc from the MAY 2007 edition of SQL Server Magazine (www.sqlmag.com). I reprint it here because it is too good to loose, and too long to remember perfectly.

If you are reading this and don't have a subscription to the magazine, this code alone is worth the subscription. Click on the link above and... just ... do... it <smiles>

 Itzik, you do good work, I love your column.

select dateadd(month,datediff(month,0,getdate()),0)

This will get the first day of the month of the passed in date (we use getdate() here). Quoting Itzik here:

The logic of this technique is a follows: Calculate the difference in terms of months between an anchor date at midnight (in this case, 0, represending the base date January 1, 1900) and the event date. Call this difference diff. Add diff months to the anchor date.

 

 

 

 

 

You heard it here first, folks.

I searched the web for this solution, but could not find anything that worked. After a bit of hacking, I came up with a way to put variables in an ORDER BY clause, and even have it work for GROUP BY queries. Read on.

This statement (written against AdventureWorks) will generate the following error:

declare @col varchar(100)
set @col = 'CustomerID'
select *
 from sales.salesorderHeader
 order by @col

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Fix it like this: 

Here are some useful tips on SQL Server Optimization

If you are experiencing speed issues with SQL Server:

 

Script that will show the space used for all tables in a SQL Server DB

 

Customer has SOP batches that are created periodically without transactions in them and they want to clean them out on a regular basis.  I have the script to do so but now I want to create a scheduled task in SQL Server 2005 to run this during off hours, say, weekly.  What is the best way to accomplish this task?  Customer is now running GP v10 but in this case, I don’t think it matters since table structures have not changed that I know of within this scenario.

 

This function is a helper that is used in our DDL scripts 

From a recent email: 

What, in your opinion, is the best recovery model for a GP database?  Full? Bulk_Logged? Simple?  They all seem to have their pros & cons but I can't find anyplace where someone says this model is better than the other two because of X, Y or Z reasons.
 

 

The following script will create a SELECT statement for the table supplied as a parameter.

** Be sure to get the 'prerequisite' scripts as well

This is a helper function used in DDL scripts 

This tip explains how to read an XML attribute from an XML field in sql server. Or, put another way, if we have this data:

<Receivable documentNumber="PYMNT000000000012    " documentDate="12/13/2007">
  <Type>PAYMENT</Type>
  <CashType>CHECK</CashType>
  <Description />
  <CheckNumber />
  <Amount>3.00000</Amount>
  <AppliedAmount>3.00000</AppliedAmount>
  <UnappliedAmount>0.00000</UnappliedAmount>
  <Items />
</Receivable>

 

We want to use SQL Server 2005 to retrieve the document number (PYMNT000000000012)

The code looks like this:

 

From a recent email:

Isaac,
 
Need to run a query in SQL to find customers who have not purchased a particular product.  In this case “FNADE*”
 
I can easily query customers who have purchased a particular or range of products, but never had to query a list of customers who have not purchased a particular product…
 
Can you get me going in the right direction?
 
Thanks.

Answer:

 

After issuing this command:

DROP index drive.ix_drives

I get this message:

An explicit DROP INDEX is not allowed on index 'drive.ix_drives'. It is being used for UNIQUE KEY constraint enforcement.

Then answer was to issue this command: 

 

From a recent email: 

I am trying to use SmartList Builder to build a query where a calculation is required showing the number of days an A/R transaction is past due.  Obviously, I have the DOCDATE from RM20101 but I need a column that tells me the current date so I can make this comparison…and I am not sure if there is a SQL Server table that I can pull in that shows this information to me.  I am pretty certain that there is not a GP table with current date in it just sitting there by default.

 

Answer:

This is a cursor that will loop through db objects and grant permissions to a user or group.

Not groundbreaking code, but I'm a big fan of keeping boilerplate code around to reuse. Saves tons of time.

 

Code to create an update statement. You'll need to get the prerequisites also

One of the coolest things to come with SQL 2005 is the new 'for XML PATH' syntax. There are a lot of things that can be done with it, I'm going to demonstrate something moderately complex here. The task is to generate an XML file that has nested nodes. In other words, we want the following structure:

 

Sometimes you might need to disable a trigger to keep it from recursing. This is a short code sample that documents how to do that...

 

This script monitors the table space usage using the sp_spaceused stored procedure

This line of code will tell you if a temp table exists in the temp database, so you avoid all those errors..

Tested in SQL 2000 & 2005:

 

Occasionally I'll need to format a string in SQL with 'Proper' casing. In other words, change 'george washington' to 'George Washington'.

Here is a function to do that

 

As a Dynamics developer, I'm frequently asked to divide by the USCATVLS fields in the IV00101 table. Since they're CHAR types, there is often no validation on empty values or numeric values. The funtion below will take two VARCHAR values and divide them, returning '0' if there is some validation issue. The code at the end is a test script.

 

Here is a script that will write code to initialize a Dynamics GP 10.0 database. It depends on a table called _temp that has a list of tables to initialize. This script will scan that table and write insert statements for all the tables in the source table

You would use this script if you had created a new, empty Dynamics company and you wanted to copy setup information from an existing company

I have occasion to look at large servers, and as part of that process I need to look at all the files to see if they're ok, I'm particularly interested in the size of the log files (we might need to shrink them)

 This query will quickly show you where they all are

 

dynamics the database setup has not been completed

 When creating a new company in GP9 I got the error above.

I'm always looking for version info and it takes me forever to find it

 

 

File not found: VBA6.dll

I got this recently on a new GP2010 install when trying to do a VBA modification. I found a post here:

Edit 5/15/2013:

I couldn't get the first fix to work in GP2010, I'm trying the second

 

Applies to: GP9, 10, 2010 

It happens that you need to add a field using modifier to a form, and that field needs to be a required field. There are a host of issues with this, the biggest one is that Dynamics GP doesn't provide you with a hook in the 'save' process to stop a save from happening

The user can save by pressing the Save button, or by navigating using the 'VCR' buttons in the bottom left corner of the form, or by pressing the Print button. Pressing Save will save with no warning, pressing the other 5 buttons will cause a modal dialog to pop up that will prompt the user to Save, Delete, or Cancel.

In order to make a field required, we'll need to do two things - put code in the Save_BeforeUserChanged event, and in the Window_BeforeModalDialog event.

The Save event is simpler. In these examples, our custom field is called Barcode. We're in the Payables Transaction Entry form, so we'll check to see if some of the other required fields are filled in. If they are, we'll stop the save by setting CancelLogic = True

Hi Friends, I had started a visual studio project..I want to move data between my project and GP 10.0...My project is located on the base machine while GP and eConnect is installed on the vpc..I had added namespace references required for econnect in my project..How I can start my coding with econnect? How Authentication is done with GP sql database by providing username,password,server and database name?..pls help me..I am completely new to GP and eConnect..

Whenever we need to update AddIns folder for GP, we need to close GP and then we can replace files. This is time consuming as starting GP takes like30,40 seconds. Is there any shorter way where we can deploy files without closing GP?

Hi,

For one of the project, I have to check a condition while users posting the transaction in Payables Transaction Entry window. If the condition is fulfilled, then only the users should be able to post the transaction, otherwise i should warn the users saying the condition is not fulfilled.

 So i have to register event (focus trigger) trigger on Post button with "ClickBeforeOriginal" option.  In the event - i have written the code to check whether the condition is fulfilled or not.  If condition is not fulfilled i have to stop the Posting event from GP Payables Transaction Entry window. 

So to stop the Post event in Payables transaction entry - please some one provide the equivalent command in c# for "reject script" command in dexterity.

 

 

 

 i've put a breakpoint on load event of a custom form and Dynamics.exe process is attached already. Assembly is digitally signed as well. But whenever i try to open target form, GP crashes automatically. If i try to remvoe debugging and then load the same form, it works fine. Can any1 plz tell, whats wrong with VST debugging?

i want to put some scripts on load of GP (not any transaction/inquiry form, main form of GP)...i couldnt find any window named home etc...Can anyone please in this regard?

I have an external web service that I am calling based on a SAVE event on PMTrxEntry window in GP.  After comiling the code, I see appconfig file with as following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
            <section name="DynamicsGPValidation.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
        </sectionGroup>
    </configSections>
    <applicationSettings>
        <DynamicsGPValidation.Properties.Settings>
            <setting name="DynamicsGPValidation_edmswebdev_APOnBaseServices"
                serializeAs="String">
                <value>http://edmswebdev/onbasewebservices/APOnBaseServices.asmx</value>
            </setting>
        </DynamicsGPValidation.Properties.Settings>
    </applicationSettings>
</configuration>

My quesiton is wether or not I should leave this config file part of the GP\AddIn folder?  or merge this into the Dynamics.config file under the GP\ root folder?

 

Thanks 

 

I am writing this customization in c sharp using developers toolkit and ran into a deadend.  I hope somoen can pitch in and help me out.

I need to know how cancel logic (use to be in VBA) for an event in GP using Visual Studio Developers Toolkit for GP.

I called  PMTRXEntryWindow.SaveButton.ClickBeforeOriginal in my program.  Based on a specified criteria I need to either continue saving the voucher or not save it in GP.  Once the program is in method based on the event handler, how do one cancel the logic of the SaveButton event?  In VBA I remember you simply have CancelLogic which terminates the logic of an event.

Any suggestions?

 

 

Thanks 

 

 

 

I am trying to locate property for PMTransactionEntryWindow where I can select ModalDialog and based my method on SaveButton event.

In VBA I use to specify ModalDialog.Button1 or something of this nature.

The scenario is that I am running a custom logic based on SaveButton.ClickBeforeOriginal.  I have successfully coded the logic based on the SaveButton.  But if the end user clicks on the "x" (upper right hand corner of the window), a Box appears asking if your want to save, delete or cancel.  I am trying to find the event which triggers this Box specifically the Save button on it.

Thanks in advance! 

 

This library of scripts that create other scripts. The idea is that you create a table, then run these scripts as needed agains the table to create the data access scripts that you need.

You might ask 'hasn't this already been done... a lot'?

Sure it has. But we like to have more direct control of the code that we use. And this is way easier to use.

Prerequisites:

f_4P_columnType

f_4P_columnTypeVB 

Scripts:

SELECT

INSERT

UPDATE

Scripts designed to run against Dynamics GP

Copy setup information from an existing company to a new one

Company/System

FP_SY00500_UPD - Retotals the SY00500 batch table

GL

GL Trial Balance Summary

PM

taGetPMNextPaymentNumber- Gets the next available PM Payment Number

RM

AR Historical Aging - Loop through the open receivables only

AR Historical Aging - This report is a modification of the Dynamics script, it fills the Aging Buckets

RM00103_SEL

FP_RMGetNextDocNumber

taGetPaymentNumber - Gets the next RM document number

FP_RMCashReceipt_INS - Inserts an RM Cash Receipt

SOP

FP_SOP10102_UPD - Delete and recreate the distributions on an order

FP_SOPGetNextDocNumber/sopGetIDNumber - Gets the next SOP document number

FP_SOPPayment_INS - Insert a SOP payment

sopGetIDNumber - Gets the next SOP document number

Move a SOP transaction from open to history

Posting Sequences - documents the order that tables are Updated/Inserted on a Dynamics GP Post action

usp_SOPTotal - retotals a SOP document

usp_SOPTotalTaxes - retotals the taxes for a SOP document

TAX

Get one or all Tax Schedules

 

It is installing eConnect it will install on the SQL box fine with the same domain user and sa, but when I try it from another box I get the cannot add a sql user.

 Unable to Add user to SQL

Delegates are a tool to raise an event from one class to another, from one form to another, or any combination.

Let's say that you have a form that shows customers, and a popup dialog that gives you a grid of customers to choose from. We'd like to raise an event in the frmChooseCustomer to let frmCustomerEdit know that a customer has been chosen. So, lets start in frmChooseCustomer:

There are four tasks:

Hi there, I have code to create a PM transaction thru eConnect 2010 and I'm getting the error above. PLEASE HELP!!!! Here is the xml: ...

 

    <PMTRANSACTIONTYPE>
        <ECONNECTPROCESSINFO xsi:nil="true">
        <TAREQUESTERTRXDISABLER_ITEMS xsi:nil="true">
        <TAUPDATECREATEVENDORRCD>
            <VENDORID>ZZ_002165161 </VENDORID>
            <VENDNAME>JOE SMITH</VENDNAME>
            <VENDSTTS>3</VENDSTTS>
            <VNDCLSID>CUSTREFUND</VNDCLSID>
            <VADDCDPR>CISMA76500</VADDCDPR>
            <ADDRESS1>1111 MAIN DRIVE APT 104</ADDRESS1>
            <CITY>MYCITY</CITY>
            <STATE>AK</STATE>
            <ZIPCODE>99999</ZIPCODE>
            <VADCDTRO>CISMA76500</VADCDTRO>
            <USEVENDORCLASS>1</USEVENDORCLASS>
        </TAUPDATECREATEVENDORRCD>
        <TACREATEVENDORADDRESS_ITEMS>
            <TACREATEVENDORADDRESS xsi:nil="true">
            <TACREATEVENDORADDRESS>
                <VENDORID>ZZ_002165161 </VENDORID>
                <ADRSCODE>CISMA76500</ADRSCODE>
                <ADDRESS1>1111 MAIN DRIVE APT 104</ADDRESS1>
                <CITY>MICTY</CITY>
                <STATE>AK</STATE>
                <ZIPCODE>99999</ZIPCODE>
            </TACREATEVENDORADDRESS>
            <TACREATEVENDORADDRESS>
                <VENDORID>ZZ_002165161 </VENDORID>
                <ADRSCODE>CISSA76501</ADRSCODE>
                <ADDRESS1>2222 ALOHA PLACE APT H</ADDRESS1>
                <ADDRESS2>000650030 </ADDRESS2>
                <ADDRESS3>181.50</ADDRESS3>
                <CITY>RICHES </CITY>
                <STATE>AK</STATE>
                <ZIPCODE>99999 </ZIPCODE>
            </TACREATEVENDORADDRESS>
        </TACREATEVENDORADDRESS_ITEMS>
        <TAPMTRANSACTIONTAXINSERT_ITEMS xsi:nil="true">
        <TAPMDISTRIBUTION_ITEMS>
            <TAPMDISTRIBUTION xsi:nil="true">
        </TAPMDISTRIBUTION_ITEMS>
        <TAANALYTICSDISTRIBUTION_ITEMS xsi:nil="true">
        <TAPMTRANSACTIONINSERT>
            <BACHNUMB>CIS20110810</BACHNUMB>
            <VCHNUMWK>CISVCH42414</VCHNUMWK>
            <VENDORID>ZZ_002165161 </VENDORID>
            <DOCNUMBR>000650030_42414</DOCNUMBR>
            <DOCTYPE>1</DOCTYPE>
            <DOCAMNT>181.5</DOCAMNT>
            <DOCDATE>8/10/2011</DOCDATE>
            <PRCHAMNT>181.5</PRCHAMNT>
            <CHRGAMNT>181.5</CHRGAMNT>
            <TRXDSCRN>002165161_000650030</TRXDSCRN>
        </TAPMTRANSACTIONINSERT>
        <TAMDAUPDATE_ITEMS xsi:nil="true">
    </PMTRANSACTIONTYPE>
</ECONNECT>

here is the error message: Dynamics GP Accounts Payable Exception: System.ServiceModel.CommunicationException: There was an error writing to the pipe: The pipe is being closed. (232, 0xe8). ---> System.IO.IOException: The write operation failed, see inner exception. ---> System.ServiceModel.CommunicationException: There was an error writing to the pipe: The pipe is being closed. (232, 0xe8). ---> System.IO.PipeException: There was an error writing to the pipe: The pipe is being closed. (232, 0xe8). at System.ServiceModel.Channels.PipeConnection.StartSyncWrite(Byte[] buffer, Int32 offset, Int32 size, Object& holder) at System.ServiceModel.Channels.PipeConnection.WriteHelper(Byte[] buffer, Int32 offset, Int32 size, Boolean immediate, TimeSpan timeout, Object& holder) --- End of inner exception stack trace --- at System.ServiceModel.Channels.PipeConnection.WriteHelper(Byte[] buffer, Int32 offset, Int32 size, Boolean immediate, TimeSpan timeout, Object& holder) at System.ServiceModel.Channels.PipeConnection.Write(Byte[] buffer, Int32 offset, Int32 size, Boolean immediate, TimeSpan timeout) at System.ServiceModel.Channels.BufferedConnection.WriteNow(Byte[] buffer, Int32 offset, Int32 size, TimeSpan timeout, BufferManager bufferManager) at System.ServiceModel.Channels.BufferedConnection.Write(Byte[] buffer, Int32 offset, Int32 size, Boolean immediate, TimeSpan timeout) at System.ServiceModel.Channels.ConnectionStream.Write(Byte[] buffer, Int32 offset, Int32 count) at System.Net.Security.NegotiateStream.StartWriting(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security.NegotiateStream.ProcessWrite(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) --- End of inner exception stack trace --- at System.Net.Security.NegotiateStream.ProcessWrite(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security.NegotiateStream.Write(Byte[] buffer, Int32 offset, Int32 count) at System.ServiceModel.Channels.StreamConnection.Write(Byte[] buffer, Int32 offset, Int32 size, Boolean immediate, TimeSpan timeout) --- End of inner exception stack trace --- Server stack trace: at System.ServiceModel.Channels.StreamConnection.Write(Byte[] buffer, Int32 offset, Int32 size, Boolean immediate, TimeSpan timeout) at System.ServiceModel.Channels.StreamConnection.Write(Byte[] buffer, Int32 offset, Int32 size, Boolean immediate, TimeSpan timeout, BufferManager bufferManager) at System.ServiceModel.Channels.FramingDuplexSessionChannel.OnSend(Message message, TimeSpan timeout) at System.ServiceModel.Channels.OutputChannel.Send(Message message, TimeSpan timeout) at System.ServiceModel.Dispatcher.DuplexChannelBinder.Request(Message message, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.Dynamics.GP.eConnect.IServiceInterface.CreateTransactionEntity(String connectionString, String xml) at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(String connectionString, String xml) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityImport(String connectionString, String sXML, Boolean isTransaction) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String ConnectionString, String sXML) at CISDynamicsGP.CISInfinity.CISeConnect.AddAPItem(APTransaction CISTrx, APVendor CISVendor). Dynamics GP Accounts Payable Exception: System.Data.SqlClient.SqlException: Procedure or function 'taCreateVendorAddress' expects parameter '@I_vVENDORID', which was not supplied. at Microsoft.Dynamics.GP.eConnect.ServiceProxy.HandleSqlException(FaultException`1 ex) at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(String connectionString, String xml) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityImport(String connectionString, String sXML, Boolean isTransaction) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String ConnectionString, String sXML) at CISDynamicsGP.CISInfinity.CISeConnect.AddAPItem(APTransaction CISTrx, APVendor CISVendor). Thanks!

This post shows how to use the appSettings section of app.config or web.config to store user data

This is another of those cases where the code is pretty simple, but it helps to have a template someplace to copy the code from. So, here’s my template

 If you need something in VB.NET or ASP.NET to be configurable, the easy thing to do is to put it in the .config file and let the user configure it.

 

This class gets inserted into all our web projects as a starter exception handler.

It is called from Global.aspx, and includes methods to log to the event log and send emails

This is the first of two articles covering creating a Windows Service. 

This article will cover:

How to get your Windows Service to run every X seconds/minutes/hours (on a timer)
How to make your Windows Service installable
How to read application settings from a .CONFIG file
How to perform real-time debugging upon your Windows Service 

The second article, located here, will cover the Deployment Project


Adding delete functionality to a Telerik ASP.NET RadGrid is not hard. There are two steps: adding a GridButtonColumn to the grid as shown below; and adding the Delete Command Handler to the code behind. In our example we use a data table stored in the Session as our data source, but this could be easily translated to a stored procedure.

This code also show how to delete from a datatable

 

Our first eConnect installs got done under the user account of the consultant and ... the password go changed. Bright, right?

So, I went in to change the account. I created a domain user account and reassigned the account that the service used to log in. Now I've got this:

Service cannot be started. The handle is invalid

and

HTTP could not register URL http://+:80/Microsoft/Dynamics/GP/eConnect/mex/. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details).

and

Exception message:
The communication object, System.ServiceModel.ServiceHost, cannot be used for communication because it is in the Faulted state.

 

 

 

A large part of my job is taking data in disparate forms and integrating it into Dynamics. The data is frequently in text file - some comma delimited, some fixed length, some 'variable fixed length'. By that I mean that the data has different formats inside the file, the format is usually determined by the first few characters in the line. For instance, we might have a file like this:

OH20110813002351SMITH 0001251
OD00001BIG HAMMER  11.99
OH20110813002351JONES 0001252
OD00001BIG WRENCH  13.99

The lines that start with 'OH' are Order Header lines, the line format would be:

 Character Field Type 
 1-2  Line Type String
 3-10  Date Date
11-16  Customer Number String
17-25 Customer Name String
26-35 Order Number String

 Our OL (Order Line) data is similar:

Character  Field Type
1-2 Line Type String
 2-7 Quantity Int16
 8-20 Item Number String
 21-28 Price Double

 So, how do we read that into VB and get it sent to Dynamics? In the past I've build complicated parsing routines, and used LINQ. I'm not fond of LINQ, nor am I fond of complicated routines. Enter in TextFieldParser

The TextFieldParser class is part of the Microsoft.VisualBasic.FileIO and is designed to handle all three of our file types, but the variable type is by far the harded to deal with. The code below explains how to use this object in your code

This is our primer on the System.Text.Regular expressions namespace and the Regex class

We have sample regular expressions, and sample code that shows how to use it

 

When using the Telerik RadEditor, the toolbar buttons will get the focus as you tab through the form... and there are usually a lot of them. This piece of code will keep that from happening

What is Branding, and why do I need it?

Branding is when you create a certain image for your product. That image may only be font and color, it may include a logo or maybe a slogan. It may include a spokesperson. Your brand represents who you are, what your product is to the world.

You say “We’re a church”, or “We’re a school, why do we need that?” The brand of a church or school is how it is seen by the public. If you think of Florida State, or the University of Florida, what enters your mind? Colors? Mascots?

Branding.

It took me about ten seconds to find an example on the internet: http://visualsystem.fsu.edu/

When I do work for major institutions, they send me reams of branding information that has to be perfect. Colors, logos, fonts are the biggest issues.

recommend that you develop a brand for the school, and repeat it every place. Over and over and over. Everything that you do should have that brand on it.

I find that sometimes Christian organizations (which I do a lot of work for) are reluctant to go to the expense to develop a brand (you should have it done profesisonally) but it’s a one time charge, and it is needed in order to effectively promote the organization. Imagine Coke without a brand, or McDonalds. Hard to print T-Shirts <big smile>

When trying to enter a credit card payment, I'm getting

Error Number = 48 Stored Procedure= taCreateSopPaymentInsertRecord Error Description = Credit card name is invalid.

Can anyone help?

This is a quick code example that shows how to use the DataGridView.UserDeletingRow event to delete rows from the database

A common integrtion task is to scan all the files in a folder and process them.

This code sample shows how to read all the files in a directory and get the file name and the full file path and name. This code uses the System.IO.DirectoryInfo and System.IO.FileInfo objects.

We also get the directory to scan from the app.config file using System.Configuration.ConfigurationManager.AppSettings("directoryName")

This is a short piece of code that will show you how to navigate to a specific line in any Dynamics Grid

 

I'm very unhappy with the tool that Telerik uses to build the fields in its grids (it's about the only thing that I don't like about them, BTW). So, I decided just to build the grids manually in code. this grid class does the work.

 

Here is a short code example of how to only allow selected columns in a Telerik grid to be editable

 

This small piece of code shows how to iterate through a Telerik Winforms RadGridView

 

Telerik - z-order for the RadEditor FormatCodeBlock window

When I drop a RadEditor onto a form, the FormatCodeBlock window is popping up behind the RadMenu. This code snippet will prevent that

The System.IO.Path is an incredibly useful thing. However, every time that I go to do some filename manipulation it takes me just a little longer that I'd like to get it working. The issue is generally figuring out exactly which method to use to best accomplish the task.

So...

This short code snippet will show you how to set a unique primary key on a datatable

 

This short code example shows how to code cell validation in a RadGridView cell

 

Here is a SQL script that will delete and recreate distributions on a SOP document
This code example will show you how to set the default theme for the entire Winforms application
This code example will show you how to set the selected page for a RadPageView control

This document, and all our eConnect documentation, is available on the eConnect menu.  

This is an example eConnect PMTransactionType document that contains distributions

What is the difference between Application.MicrosoftDynamicsGp.ModifiedForms.dll and Application.Dynamics.ModifiedForms.dll

When I ran the following command, it only created Application.MicrosoftDynamicsGp.ModifiedForms.dll

dag.exe 0 "C:\Program Files\Microsoft Dynamics\GP2010\Dynamics.set" /M

One of the more frequent questions that we get here is on Auto Posting. In other words, using eConnect to send a document to Dynamics and then posting it automatically.

This thread is an aggrigation of other threads on the subject. You may add your comments to it, but this is not the thread for questions... just answers

I am trying to add a field in a GP scrolling window using VS tool. Any resources to know how?

I'm trying to send a manual check to GP with the following code. I'm getting a null reference where indicated ******

I would appreciate any help or recommendations.

Thx.

Imports Microsoft.VisualBasic
Imports Microsoft.Dynamics.GP.eConnect.Serialization
Imports Microsoft.Dynamics.GP.eConnect
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
 
Public Class POP
 
    Public Sub eConnectRepaySanAntonio(ByVal strSARepaymentNumber As String, ByVal monSanAntonioRepayment As Decimal)
        Dim mydate As DateTime = Now
        Dim strBatchNumber As String = "eCnctYYYYMMDD"
 
        Dim check As New taPMManualCheck
        With check
            .BACHNUMB = strBatchNumber
            .PMNTNMBR = strSARepaymentNumber
            .VENDORID = "SANANTONIO"
            .DOCNUMBR = strSARepaymentNumber
            .DOCAMNT = monSanAntonioRepayment
            .DOCDATE = mydate
            .PYENTTYP = 0
            'Check
            .CHEKBKID = "CHECKBOOK"
            .CREATEDIST = 0 'Manual
        End With
 
        Dim PMdistItems() As taPMDistribution_ItemsTaPMDistribution
        Dim PMdistCash As New taPMDistribution_ItemsTaPMDistribution
        With PMdistCash
            .DOCTYPE = 6
            'Manual Check
            .VCHRNMBR = check.PMNTNMBR
            .VENDORID = check.VENDORID
            .DISTTYPE = 8
            'Misc
            .ACTINDX = 1 'Cash-Checking
            .CRDTAMNT = check.DOCAMNT
        End With
 
        ReDim Preserve PMdistItems(0)
        PMdistItems(0) = PMdistCash
        Dim PMdistSA As New taPMDistribution_ItemsTaPMDistribution
 
        With PMdistSA
            .DOCTYPE = 6
            'Manual Check
 
            .VCHRNMBR = check.PMNTNMBR
            .VENDORID = check.VENDORID
            .DISTTYPE = 8
            'Misc
            .ACTINDX = 120 'San Antonio Repayments Due
            .DEBITAMT = check.DOCAMNT
        End With
 
        ReDim Preserve PMdistItems(1)
        PMdistItems(1) = PMdistSA
 
        Using e As New eConnectMethods
            Try
                Dim eConnectDoc As New eConnectType
                ReDim Preserve eConnectDoc.PMManualCheckType(0)
                ' **** The following line gives Object reference not set to an instance of an object ****
                eConnectDoc.PMManualCheckType(0).taPMManualCheck = check
                eConnectDoc.PMManualCheckType(0).taPMDistribution_Items = PMdistItems
                'Serialize the request object
 
                Dim memStream As New MemoryStream()
                Dim serializer As New XmlSerializer(GetType(eConnectType))
                serializer.Serialize(memStream, eConnectDoc)
                memStream.Position = 0
 
                'Create and save a copy of the XML to be sent to GP
                Dim myDoc As New XmlDocument
                myDoc.Load(memStream)
 
                'My.Computer.FileSystem.WriteAllText("ManualCheck_" + Trim(strSARepaymentNumber) + ".xml", myDoc.OuterXml.ToString, False)
                Dim sentCheck As String = myDoc.OuterXml
                Dim returnedCheck As String = ""
                returnedCheck = e.CreateTransactionEntity(ConnectionString, sentCheck)
            Catch econnecterror As eConnectException
                Dim errMsg As String = econnecterror.Message
            Catch ex As Exception
                Dim errMsg As String = ex.Message
            End Try
        End Using
    End Sub
End Class

 

 

 

There are two paths to take when adding a field to a grid in Dynamics, depending on the source of the data for the field. If the source is a field that's already in the table it's much quicker. But in these two videos we'll explore both options

 

The code sample below will show you how to create a GL Account in eConnect.

The code sample requires the eConnectHelper class, which can be found on the eConnect Index page

Here we use the GLAccountType and otaUpdateCreateAccountRcd nodes

Edit 1/9/13:

The documentation for this schema says that it will uset update whatever you send and that no parameters are required... but as far as I can tell, that's not true. You have to supply about 7 paramaters or it will error with a very odd error. See this post for details: http://dyndeveloper.com/thread.aspx?Threadid=834

 

 

The XML document below is an example of the XML document sent to Dynamics to create a GL Account. This document uses the GLAccountType and taUpdateCreateAccountRcd nodes.

 

 

This simple library function will strip the unneeded characters from a phone number so it may be stored without format
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3