Inventory management Software for all

Paging for MS SQL query

2012-01-31T07:01:00.000-08:00

It's a usual task get some records from a big table. MySql has a simple operator Limit offset,pagesize
If you want to get 16-20 records from the table Customers you write in MySql
SELECT * FROM Cbstomers LIMIT 15,5

It's a not trivial task for MS SQL query. We should write the next query :

SELECT * FROM (SELECT TOP 5 CustomerID,FullName From (SELECT TOP 20 Customers.CustomerID, Customers.FullName
FROM Customers
ORDER BY Customers.CustomerID ASC) As T2 Order by CustomerID DESC) ORDER BY CustomerID ASC;

page size=5
current page=4
current page x page size=20

Insert XML examples in a blog

2011-07-20T00:02:00.000-07:00

When I wrote the previous blog, I encountered a small problem. How to insert an example of my XML file in the blog. I'm using the Notepad function Edit-Replace. First I've replaced the symbol < on <

Then, I've replaced the symbol > on >.

After that, I've copy pasted the new text in my blog.

HTTPS in Google Application Engine

2011-07-19T23:19:00.000-07:00

Now I'm playing with Google Application Engine.GAE is a terrific tool for learning requires. If you want to work with your application via HTTPS simple add the next lines to web.xml:
<security-constraint>
<web-resource-collection>
<web-resource-name>all</web-resource-name>
<url-pattern>/*</url-pattern>
</web-resource-collection>
<user-data-constraint>
<transport-guarantee>CONFIDENTIAL</transport-guarantee>
</user-data-constraint>
</security-constraint>

You do not need to buy a certificate and not need to configure anything.

MySQL and ADO

2011-04-10T05:36:00.000-07:00

I am getting this error message in VB6 with MySQL

-2147217864 Row cannot be located for updating. Some values may have been changed since it was last read.
I've opened recordset and change some fields . Then I tried to update recordset and had the error message
Set rsCt = New Recordset
rsCt.CursorLocation = adUseClient
rsCt.open SQLstring, mConn, adOpenStatic, adLockOptimistic
rsCt!OrderNumber = pbOrderNumber
rsCt!CustomerID = custID
rsCt.Update

I spent several hours searching for a solution. Changed primary keys in my tables,used different options in rsCt.Open . And eventually found a small message on forum.mysql.com

Open properties fo your DNS (Windows XP: Control Panel->Administrative Tools->Data Sources) and find option Return Matching Rows (turn it on).

Also, you can do this under connection string specity parameter OPTION=2.

This should work...

Osman :D
I added to my connection string option=2

ConnectionString = "driver={MySQL ODBC 5.1 Driver};option=2,server=" & mySqlIP & ";uid=" & usr_id & ";pwd=" & pass & ";database=" & Database

Finally the problem is solved. Thanks Osman!!!

Barcode inventory system

2011-01-28T04:56:00.000-08:00

It is not so difficult to do for your business an inventory system with bar codes. Golden Inventory is a barcode inventory system.The inventory accounting system can help you to improve the manageability of your warehouses and the company.See an example of designing inventory system and create own system for your business. You can make a warehouse inventory system either a point of sales and inventory system .

Your own online inventory system.

2011-01-28T01:35:00.000-08:00

You can create a modern own online inventory system using .net inventory source code You get complete ASP.NET source code of the system for a relatively small fee. You can adapt the inventory system for your business and gain maximum effect with small investments.You read more about online inventory system and make the right decision.

What do we want from inventory software?

2011-01-20T15:18:00.000-08:00

It's a typical request from my client about inventory software
It seems that your basic inventory program will provide all the information needed by my business but the methods of performing all the tasks is beyond my intuitive capacity. My business needs a rather simple point of sale program that would maintain an inventory.

Primary need for business management:

Create Sales Receipt
Create Sales Receipt with Labor being a variable amount.
Create Sales Receipt with taxable and non taxable items. ie. Labor, resale exempt
Create Sales Receipt for a Special Customer Order of a inventory or non inventory items showing the customer’s deposit and balance.
Create Special Order Receipt with customers name and phone number and be able to retain, recall and update the infromation when item is delivered
Create Sales Receipt for items that are not in inventory on the fly.
Create Sales Receipt and automacally adjust inventory quantity.
Create Sales Receipt with several terms of sale printed on it. "3 Day Retutn"
Create Sales Receipt and add a special note or instructions.
Ability to search and review special orders or open sales receipts by customer or invoice number.
Allow for inventory anjustment, quantity and pricing.
Maintain a list of manufacturers or supplies.
Maintain a list of customer information for some, not all sales.
Would like a daily and monthly sales reconcilliation.

I have been working with your inventory software as downloaded and find many things quite easy. The inventory and sale of inventoried items seem just fine. I have had trouble understanding or finding how to preform the following:

Making items taxable or not on a given sale receipt.

How can I make up a labor ticket which allows for non taxable labor and taxable parts?

How can I make up a labor ticket that retains the customer name and phone number and allows for updating with the addition of parts utilized and then provide a sales receipt?

How do I take a deposit on a special order of an inventory or non inventory item, then then update the transaction when the items is picked up and provide a sales receipt?

How do I set up a group of special terms of sale to appear on all sales receipts?
We usually answered that our inventory software can make all these functions and clarify how it works.

You can use our Golden Inventory system. You can use Invoices and
Sales Receipts for tracking you sales. If you sell items for cash you
use sales receipts if you want to track customer payments and balances
you should use invoices. Sales receipts don't change customers
balance.

You can add in the items list inventory and non-inventory part for
labor.Set the option "Non stock" for labor. You can set the option Tax
Invoice for any item.

Create a new invoice and select a cusomer in the drop down list. If
it's the new customer enter his name in the field customer. Click the
button Items and select inventory parts and labor. Click the button
Enter and close the items list. You can change quantity for any item
and change the Taxable. If you don't have any item in the item list
you can enter it in the invoice detail section and program will add
it to the items list after saving your invoice. You can use the button
"Paid" for entering a customers deposit.

The inventory system keeps all customers in the special table.
You can view it in the left menu Lists the Items ,Customers and
Vendors lists. The program has the Adjustment and Report functions.
Golden inventory decrease quantity on hand for items when you create
invoice or sales receipts.

You can use Sales Orders for your labor tickets. Than, you'll be able
to create a new sales receipt or invoice and download all information
from this Sales Order. Use the small button near the field Sales
Order No.

You can use the field Status in invoices and sales receipts. The
inventor system calculates only approved documents.
All documents has the field Group below the field Comments.

Free Excel Invoice template

2011-01-03T05:58:00.000-08:00

If you are just starting your business and do not have enough spare cash to buy any inventory system, you can use free utilities. For example, the free invoice template for MS Excel.Download the free Excel invoice template. Open the invoice template in Microsoft Excel.Unprotect the Invoice worksheet. The Invoice worksheet is protected to avoid accidental damage formulas and fixed cells. To unprotect it, in Excel 2000/XP/2003 click Excel menu Tools -> Protection -> Unprotect Sheet, or in Excel 2007/2010 click Review tab -> Changes group -> Unprotect Sheet. Enter your company name and address. Then protect the sheet again. After that, you'll be able to fill out your invoices and print them for your customers. Excel will automatically calculate line totals,taxes and grand total for each your invoice.

Online inventory systems

2010-12-09T22:23:00.000-08:00

What is an online inventory system? This software is located on a Web server and works with your inventory database.You can connect to the database from any computer using a web browser. You use a personal password and encrypted connection over the Internet.This is a convenient solution for companies with branches in different cities or remote stores and warehouses. You can create a new order in the client's office. You can check the real on hand quantity of goods while you are on the way to the customer.You can quickly organize a point of selling your goods at a remote location. You will need only internet connection. You can use the SilverNet online inventory system.It is an inexpensive and effective solution for creating your own online inventory system for any company

Point of sale function in an inventory system

2010-11-29T02:07:00.000-08:00

If you sale your goods in a retail store the best way to track sold products is using the Point of Sale form. Of course, this is not a complete point of sale station with a cash register and a receipt printer but it is more cost-effective solution when you already have some sort of computer and printer. You need only install software and begin to work with your customers.

Sales orders,Packing slips and Invoices.

2010-11-26T10:51:00.000-08:00

We can use different documents for inventory management.Сonsider how to organize the working process of selling goods from the warehouse.If your client has decided to buy your product you will create for him a Sales Order.You enter in it a clients name , the name of your company and list of items with prices and quantity.Usually inventory software has a special function for this. Then, the customer should pay for these products. After that, you will create invoice with same products for your customer and packing slip with these products for warehouse staff. You have to send the packing slip to warehouse and your personal will ship goods to the customer.Usually you will only create an invoice and print it in the form of packing slip and invoice.Finally, you will send invoice to the customer. If you use any perpetual inventory system you will know anytime how many items you have in the warehouse.

Reorder points in your inventory system

2010-11-24T00:48:00.000-08:00

If you have many products in stock, it is very difficult to keep track of all. Do you have important items in sufficient quantity?Golden inventory system will help you to manage this issue.You'll be able to define a reorder point for each item in your warehouse.
You should avoid reducing of quantity for important in your business goods below a certain level.
You can open open the Items list form and double click on any item. Fill out the Minimal Qty and Target Qty fields. Click the button Save and close.Now your system monitors the critical balance of goods.If the inventory of goods will be less than the minimum level the inventory system will show them at the Overview form in the Inventory to Reorder panel.You should just click on the Create PO button and the inventory system automatically creates a new purchase order with needed items.

Why we use the adjustment orders in inventory systems.

2010-11-22T23:36:00.000-08:00

When we use an inventory system, we mean that we record all operations with the goods by our warehouse. But it is not always true. Sometimes we make mistakes, sometimes we do not receive all ordered goods. Sometimes the goods are stolen or spoiled.Usually we find out the problem after physical counting of the goods in our wrehouses. What should we do in this case? We have to create a new adjustment order and enter in it all problem items. The inventory system will recalculate quantity on hand. And we will have a document showing the gap in the stock. Adjusting is the same transaction as receiving or shipping.Or rather it is sending of goods in nowhere.

Multi location inventory accounting

2010-11-19T04:41:00.000-08:00

.You can store your goods in different locations in warehouses,store rooms and shelves. However, you should always know where they are.Can your inventory system help you? Sure.
First,you have to add storage locations to your inventory system at the Company&Locations form.
Select in the left menu the item Lists-Company&Locations and click on the toolbar button New. Enter a name of your location and click on the OK button.You can add several locations and even add child storage location in the warehouse. You should define such locations as sublocation of the big warehouse.
You can use these locations in item receipts or invoices. If you are receiving goods to the Warehouse#1 you have to select it in the drop down Location list at the Item Receipt Form. If you shipped goods from the Warehouse#2 you have to define this location at the Invoice Edit form. The inventory program wiil increase or decrease quantity on hand in in the corresponding stock.At any point you can open the Item form and click on the On Hand button to get distribution of your product by locations.You can read more about How to create an inventory system here.

Using barcode scanner in an inventory system

2010-11-18T04:19:00.000-08:00

In order to benefit from using of barcode labels you need to buy a barcode scanner.This device converts the image of a barcode to a combination of letters and numbers and transmits them to a computer. How to use the barcode scanner in the Golden Inventory system.First, you should connect the barcode scanner to your computer. The scanner connects to the computer's USB or keyboard ports.Then, click on the button Create New:Invoice and turn on the option Scan bar code. You'll see the small window for entering barcodes. Take any barcode label and scan it by the scanner.The program will searche for the product that will match this barcode and automatically enters it into the invoice. This will speed up the creation of shipping documents in your company a few times.

Bar code labels and inventory system

2010-11-17T14:14:00.000-08:00

Bar code labels are widely used in modern inventory systems. They help reduce the amount of manual data entry and simplify the search for goods in warehouses.
Why do we need a barcode ? It's a combination of numbers and letters uniquely identifies the goods lying in your warehouse.There are many types of barcodes UPC,EAN,Code39,Code93,Code128.
In order for your company to print own UPC barcode symbols, you will need to become a member of GS1 US Partner Connections. Your company will be assigned an identification number -company prefix. You will use this number to create your own UPCs. But you can create own barcodes using Code39 within the company. You can select any system for your barcodes. For example, you have computer hardware and furniture in your warehouse. You can define the 123 prefix for hardware and the 125 prefix for furniture and you can decide to use for numbering of items 6-digit numbers. So you set for a monitor the barcode 123000001 and the barcode 125000001 for a chair.If you receive a keyboard to your warehouse you define it to the code 123000002 and if you get an office table then assign the code 125000002 to it. And so on.
What should we do with these codes? We have to eneter your codes in the field Barcode at the Item Edit form.

How to get started with the inventory systems.

2010-11-16T14:33:00.000-08:00

Usually you already have goods in your warehouse and you should enter your inventory system physical quantity of goods in stock.Any system begins in the initial quantity of goods in stock. You can go to the warehouse and write items names and quantities on paper. Or you can install the inventory program on laptop and enter items directly in computer. In any case, you should create the initial item receipt or receiving order with all items.For example, if you work with Golden Inentory system you should do next:

Click the button Receiving
Enter a name,description,quantity and price of the first item.
Enter a name,description,quantity and price of the second item.
Enter names,descriptions,quantities and prices of other items in warehouse.
Click the button Save and close the Item Receipt form.

After that, the system will add all your goods to the item list. You will use this list in all system's forms.From this point, you have to track in the inventory system any your operation with inventory (receiving,shipping,moving between locations)

How inventory systems track inventory operations

2010-11-16T07:14:00.000-08:00

Usually, an inventory system tracks all receiving and all shipping operations. Then, the system calculates an on hand quantity for each item in your warehouse as difference between received and shipped amounts.
(Qantity On Hand)=(RECEIVED - SHIPPED)
Therefore we should track all receiving and shipping operations and the inventory system will calculate actual on hand quantities for all items.What does it mean? When you receive goods in the warehouse you should enter items in the inventory system.The inventory system uses for this item receipts or receiving orders. When you send goods to customers you should enter all shipped items in the invoices or shipping orders. The system calculates sums for all received and shipped items and calculate quantity on hand for each item.It allows to have real time information about any product in the warehouse.You can print all these documents and you'll be able to send invoices to customers or check accuracy of the inventory system.

How to create a simplest inventory system using only pen and paper

2010-11-16T01:45:00.000-08:00

I read recently in the book "Inventory accounting: a comprehensive guide" by Steven M.Bragg that :"Inventory is a difficult asset to control-it arrives and departs company premises daily, is scattered throughout the warehouse and production areas (and possibly offsite storage locations),may contain obsolete or scrap items ,can involve thousands of part numbers,can include items owned by suppliers or customers, and may be valued using a variety of techniques for both direct and overhead costs."
It's really true.But what should you do if you don't like computers but want to solve problems with you inventory? There's an old and true pen and paper solution.Take a few sheets of paper and draw the table as shown in Figure.

You have to create inventory cards for all items in your warehouse. Put them in a box and begin tracking of your inventory. You should do phisical count of your warehouse and enter current on hand quantity in the column IN for each good. You will enter any shipped quantity in the column OUT and received quantity in the column IN. Then,you should calculate BALANCE in mind or using any hand calculator. You will have a very simple but the real working inventory system. Of course, if you have a computer you can use a more convenient solutions for inventory accounting.

Inventory software for osCommerce web store.

2010-10-10T13:32:00.000-07:00

We've created the special Ecommerce extention for our inventory software . This utility downloads pending orders from any osCommerce web store and adds sales orders in the inventory system. Then, you can check these orders and create invoices in batch mode. The inventory systems will automatically create work orders for all sold assemblies from your web store. This program greatly speeds up the dispatch of goods sold through the online shop.

Inventory software for Ebay vendors.

2010-06-17T15:27:00.000-07:00

Recently,one my friend ask me to help him in tracking Ebay transactions. I've added the new function "Import from EBay csv files" to the Sales Order form of Golden Inventory System. Now, he can download all daily sales orders and then create invoices for any sales order. I've added the special button "Create Invoices from Sales order" at the Sale Order list.Besides, we created a special function for butch creation of item kits. My friend saves a lot of time using the Golden Inventory System. He loads a single mouse click everything Ebay sales in own inventory databaseю Then, he prints the invoices and send all the goods sold. He always sees the stocks at the warehouse and replenishes their шт time.

Wireless Inventory System

2010-05-21T11:58:00.000-07:00

Conventional inventory systems are installed on desktops but the people working in warehouses and selling goods are in constant motion. They should give information about all their operations in the office where the information is entered into the computer. But you can significantly accelerate the integration of all warehousing and sales information if you install wireless inventory system . This system allows to enter information from any mobile device connected to the network via wireless internet or Wi-Fi. Warehouse workers will record the receipt of goods directly to a warehouse using a handheld computer or a wireless scanner. Sales managers will write invoices or orders directly from customers using your cell phone. You can significantly speed up the inventory of your warehouse if your employee will conduct physical accounting using a wireless scanner or PDA.

Web based inventory software. source code

2010-05-10T07:55:00.000-07:00

It's very useful to buy inventory system with full source code. You can save a lot of time if you want to implement inventory management in your company. Use this page with web based inventory software with full source code

Horowitz competition of young pianist

2010-04-28T06:03:00.000-07:00

Last week, I enjoyed watching playing of young pianists in the competition in memory of Vladimir Horowitz There were a lot of beautiful classical piano music.

Classical piano music competition.

2010-04-25T04:47:00.000-07:00

I recently watched broadcast of the competition of young pianists in memory of Vladimir Horowitz. Very talented and interesting guys played with great enthusiasm. I really enjoyed the performance of Mozart's piano sonata by Kyrill Korsunenko .

Classical piano music. Mozart Sonata Horowitz competition

Using a template PHP class for web design

2010-04-03T15:02:00.000-07:00

Sometime, I make a small websites for my friends. Recently, I've opened for myself a very simple and fast way for this. I've created the small PHP class for using templates. And now I take any free template change content on variables like {CONTENT1}{CONTENT2}. After that, I create a small php file for each page and define in these variables text for this page. It is very flexible and convenient way to create small sites. Like this: service company of Sharnoa Machining Centers,repair Tiger CNC controls

Server Error:Operator '<>' is not defined for type 'DBNull' and type 'Integer'.

2010-01-11T08:36:00.000-08:00

Some time I see the Server Error message in our SilverNet web based inventory system
I've investigated this problem and found that in the row:

<asp:Label ID="Label3" runat="server" Text='<%# Eval("Paid", "{0:N}") %>' Visible='<%# Eval("Paid")<>0 %>' ></asp:Label>
The field "Paid" can be DbNull.
First, I tried to check the value Paid before using it in the Eval function. But then,I've found very simple solution. I've edited SQL query in the data source. I've changed the "Select Paid ..." on "Select IsNull(Paid,0) As Paid...."
DbNull is an insidious thing!

How to create clothing set ?

2010-01-10T10:06:00.000-08:00

We will use Victoria's Clothes Organizer for creation clothing sets. First, we should to take pictures of all our warderobe and then we are ready to create our sets.
Select the empty row in the Clothing sets table. Double click on the field "Clothing sets description" and type a name of your new set. For example "Set for restaurant". Then select a destination from the drop down list. You can enter a new destination if don't find it in the destination list. Select or enter the weather and temperature for this set. You should define all clothes for this set in the table "Clothes for set". You can select items from the drop down list or enter a new dress using the right small button "Edit and Add clothes". Select the option at the pop up for Add or Edit. Enter a description and load a photo from an image file. You can enter any category and any location for this item. If you want remove an item from the set you can use the left small button "Remove item". Click on the button "All clothes" and you'll see the list of all your clothes. You can edit,add and delete clothes and use filters by category and by location. You see when and how many times you dressed each item. Click on the button "Select" and add an item to the current clothing set. You can save when you dressed this set. Use the special table "When dressed" . Select an empty row and select a date from the calendar.

Claimed on Technorati

2010-01-09T13:34:00.000-08:00

QVBDM9WHW6ZU
It's a post for verify my blog by Technorati

Create your own outfit

2010-01-09T10:13:00.000-08:00

You can use our Victoria's Clothes Organizer and easy create your own outfit using the clothing sets It has the list of clothing sets, the filter panel, the tables "When dressed" and "Clothes for set", the image of a clothing set, the buttons New Set and Delete Set. Double click on any row for editing. Select the option "---All----" if you want to see all records. Select an appropriate destination, weather and temperature if you want to see limited amount of clothing sets. This function will help you to select an appropriate set of clothes and accessories and create a today's own outfit.

Localization of Victoria's Clothes Organizer

2010-01-03T13:29:00.000-08:00

I've noticed that girls from France often use our softwareVictoria's Clothes Organizer. But I do not know French at all. I've add the special new function "Settings" to the menu File.Now, women can change English captions on captions in their native languages.They can change any program's name for example, Destination on Style or another. I've used google translate and got What should I wear?= Que dois-je porter?. Is it true?

We can answer on qestion: What should I wear?

2009-12-24T14:49:00.000-08:00

I create sometime software for my wife. Usually, it's simple utility for women life.She want to be always dressed immaculately and think about each details in her outfit. But she doesn't have time to think about it every time before leaving home.
We've created a program for manage a warderobe and creating the clothing sets beforehand.Use our Victoria's Clothes Organizer that answers the perennial question: What should I wear?. This program will make you happy!

Remote warehouses and XML orders. in Golden Inventory System

2009-12-21T02:43:00.000-08:00

You can have many storage locations in your company and you may want to automate all. You can use two approaches to this. First, install MS SQL Server and connect to it from all remote locations through the internet. You use one inventory database in this case but you must have a reliable connection to the network in all your branches.Any break down of the internet connection will lead to stop work in your branches.The second approach is that you are installing a separate database for each storage location and synchronize them using XML orders.We consider each location as your customer or reseller. We create invoices when we ship goods to your remote location. We call these invoices as internal invoices and denote their the special group "internal". Then, create copies of these files in XML format. You can download Golden Inventory software for testing this function. You can send these XML invoices via email or on any flash device with a truck driver. Personal of your remote warehouse receive XML invoices and upload them in the local inventory database using the function Add from XML file at a New Item Receipt. You'll have item receipts in remote databases as copies of invoices from the main base. This provides full synchronization of data in the main and remote databases. Golden Inventory has a lot of XML functions and you can create purchase and sales orders in the XML format.This allows you to automate your workflow with remote warehouse.

Do you like classical piano music ?

2009-12-19T14:52:00.000-08:00

Probably, you know that piano music is a huge area of musical culture. Beginning of piano music was laid in second quarter of XVIII century, when was designed a new keyboard-stringed instrument - piano.
The real flowering of piano music associated with the period of romanticism.
Earlier known genres was reinvented, often turning into something else: a prelude becomes an independent poetic miniatures;
romantic content and images are saturated genres of fantasy,
variations, suites, is transformed and a piano sonata concert.
There are new purely romantic genres appear.For example, musical moments and Impromptus, Nocturnes and ballads, transcribed for piano of orchestral works and songs without words.
XXI century has taught piano sound in new ways. The images of piano music appeared sharp, tight rhythms, complex melodies, new piano techniques.

Kirill Korsunenko is a bright representative of the new school of piano play. He is a pianist and composer. A lot of people love his excellent performance of Clair De Lune by Debussy

How to add a meta tag to a "child" ASP.Net page.

2009-12-19T12:49:00.000-08:00

I usually use a master page for creation all pages in my inventory software projects. It's a good way for fast creation web pages but I had only one problem. I could not edit meta tags in project's pages because we don't see the "head" section of child pages. But search engines like to see different "description" tags on all pages. You can add any meta tag on your page using the next code snippet in the Sub Page_Load :
Dim metaTag As New HtmlMeta, metaTagD As New HtmlMeta
metaTag.Name = "keywords"
metaTag.Content = "inventory software,systems"
Header.Controls.Add(metaTag)
metaTagD.Name = "description"
metaTagD.Content = "List Receiving orders or item receipts,inventory software,systems"
Header.Controls.Add(metaTagD)

It is a simple and workable solution.You have to enter an appropriate description and keywords for each your page.

Import data from an Excel file to ASP.Net application

2009-12-18T02:03:00.000-08:00

My customers very often ask how to import their products in our SilverNet inventory system. Usually they have Excel files in various formats. Therefore we are forced to write a new utility for each client. First we create the upload function.Add the the Label, Fileupload and Button controls to our aspx file:

<asp:Label ID="ExceptionDetails" runat="server" CssClass="Warning" EnableViewState="False"
Visible="False" Width="568px"></asp:Label>
<asp:FileUpload ID="FileUpload1" runat="server" Width="500px" />

<asp:Button ID="Button1" runat="server" Text="Upload file" Width="130px" />

Then we have to add the Button click handler to our vb file for checking uploaded files and import data in the inventory database.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

If Not FileUpload1.HasFile Then

'-- Missing file selection

ExceptionDetails.Text = "Please choose a file to upload."

ExceptionDetails.Visible = True

ElseIf InStr(UCase(FileUpload1.FileName), ".XLS") = 0 Then

'-- Selection of non-JPG file

ExceptionDetails.Text = "You can upload only xls files."

ExceptionDetails.Visible = True

ElseIf FileUpload1.PostedFile.ContentLength > 5000000 Then

'-- File too large

ExceptionDetails.Text = "Uploaded file size must be less than 5 MB."

ExceptionDetails.Visible = True

Else

'-- File upload

Dim fileName As String = "products.xls"

FileUpload1.SaveAs(Server.MapPath("~/files/") & fileName)

Now we have uploaded the Excel file on server and we have to open this file and load all records to a dataset. Add the special function ExcelConnection:

Protected Function ExcelConnection() As OleDbCommand

' Connect to the Excel Spreadsheet

Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & Server.MapPath("~/Pictures/products.xls") & ";" & _

"Extended Properties=Excel 8.0;"

' create your excel connection object using the connection string

Dim objXConn As New OleDbConnection(xConnStr)

objXConn.Open()

' use a SQL Select command to retrieve the data from the Excel Spreadsheet

' the "table name" is the name of the worksheet within the spreadsheet

' in this case, the worksheet name is "Sheet1" and is coded as: [Sheet1$]

Dim objCommand As New OleDbCommand("SELECT * FROM [Sheet1$]", objXConn)

Return objCommand

End Function

Using this function we can extend our sub for the Button click handler:

' Create a new Adapter

Dim objDataAdapter As New OleDbDataAdapter()

' retrieve the Select command for the Spreadsheet

objDataAdapter.SelectCommand = ExcelConnection()

' Create a DataSet

Dim objDataSet As New DataSet()

' Populate the DataSet with the spreadsheet worksheet data

objDataAdapter.Fill(objDataSet)

ExceptionDetails.Visible = True

ExceptionDetails.Text = "File Uploaded"

ExceptionDetails.Text &= "File Name: " & FileUpload1.FileName & "<br/>"

ExceptionDetails.Text &= "File Size: " & FileUpload1.PostedFile.ContentLength & _
" bytes<br/>"

Now we have the dataset with data about products from the customer's Excel file but we want save all these records in our sever database. Usually we use MS SQL Server database and have the special table "Product" . Create a new function for inserting one record in the inventory database.

Function Add_ProductFull( ByVal InvDescription As String, ByVal ProductName As String, ByVal Serial As String) As long

Dim DBConnection As SqlConnection

Dim DBCommand As SqlCommand, InsCommand As SqlCommand

Dim SQLString As String, SQLInString As String, pKey As Long

DBConnection = New
SqlConnection(WebConfigurationManager.ConnectionStrings.Item(1).ConnectionString)

DBConnection.Open()

SQLString = "SELECT ProductKey FROM Product WHERE ProductName = @ProductName"

DBCommand = New SqlCommand(SQLString, DBConnection)

DBCommand.Parameters.AddWithValue("@ProductName", ProductName)

pKey = DBCommand.ExecuteScalar

'check the availability of the product in our database

If pKey > 0 Then

Add_ProductFull = pKey

Else

SQLInString = "INSERT INTO Product (ProductName,InvDescription, Serial)VALUES (@ProductName, @InvDescription,@Serial)"

InsCommand = New SqlCommand(SQLInString, DBConnection)

InsCommand.Parameters.AddWithValue("@ProductName", ProductName)

InsCommand.Parameters.AddWithValue("@InvDescription", InvDescription)

InsCommand.Parameters.AddWithValue("@Serial", Serial)

InsCommand.ExecuteNonQuery()

pKey = DBCommand.ExecuteScalar

Add_ProductFull = pKey

End If

DBConnection.Close()

End Function

It's a very simple example with three columns and you can add more fields. Sometime our customers send us file with 20-30 columns.

Finally we can finish our function for our Button handler.

Dim i As Integer = 0

Try

For i = 0 To objDataSet.Tables(0).Rows.Count - 1

Dim InvDescription As String = objDataSet.Tables(0).Rows(i).Item("Item Name").ToString

Dim Serial As String = objDataSet.Tables(0).Rows(i).Item("Serial").ToString

Dim ProductName As String = objDataSet.Tables(0).Rows(i).Item("Product ID").ToString

Add_ProductFull(ProductName, InvDescription, Serial)

Next

Catch ee As System.InvalidCastException

ExceptionDetails.Text &= "Problem with import file " & ee.Message.ToString "

Exit Sub

End Try

ExceptionDetails.Text &= objDataSet.Tables(0).Rows.Count & " records "

End If

End Sub

That is all. I think this is useful information for those who use ASP.NET in their work.

Printing documents with GridView in IE

2009-12-10T17:01:00.000-08:00

It's a good idea using ASP.NET web based systems for inventory and business control. But any browser is not a good place for printing documents.
I had a bit of a problem in printing GridView in Internet Explorer. I needed to print only two rows on one page. The first problem was how to break page. I used the special style
"page-break-before:always;" for the tag "br". The second problem was how to insert this unit before each odd row but exclude the first row. I used the property Container.DataItemIndex And now we have this very simple solution:
<asp:gridview....

<Itemtemplate >

<%#IIf(Container.DataItemIndex Mod 2 = 0 AND Container.DataItemIndex <> 0, "<br style=""page-break-before:always;""> ", "<br> ")%>
<div>..information....</div >
</Itemtemplate >
If you will print this gridview you will have only two rows on each page

Multi location inventory management system.

2009-07-20T01:23:00.000-07:00

Companies very often have several locations for saving their products. They can put one item in two or more warehouses. How will our inventory management system process this item? First, we'll add the item to the Item list. We'll enter the item name and description. If we use bar code labels we'll add value of bar code in the special field. Then we should track receiving of this item on our first warehouse. Create a new receiving order with this item and select location=Warehouse#1. The inventory management system will add this item to the warehouse #1. After that, we have to create a second receiving order with the item and location=warehouse#2. We'll see the summary on hand for our item in the Inventory report and local on hand in the Inventory local report. If we sell product from our warehouse #1 we create a new invoice with this item and select the location=warehouse #1 for this invoice. The inventory system will reduce on hand amount of the item on the warehouse #1.

The inner logical relationship of inventory database

2009-07-18T12:28:00.000-07:00

Typical inventory software has next tables:

Items (Products)
Customers. You can create several Jobs or Contracts for one Customer
Vendors. You can create several Contracts or Branches for one Vendor
Locations -is your storehouse or warehouse with shelfs or office room. You can add a sublocation using the separator ":"
Vendor Types
Customer Types
Job Types
Purchase Orders and POrder Detail for tracking ordered items. When you create a POrder the Inventory software increases "On Order" amount in the Item form . Each record in the table POrder has relation with record in the table Vendor.
Receiving and Receiving Detail for tracking got products. When you create any Item Receipt the inventory system increases "On Hand" amount in the Item form and changes balance of the vendor. Some records in the table Receiving have relation with the table Vendor.
Vendor Payments for tracking payments to your vendors for received products. When you create the Payment the system changes balance of the vendor. All records in this table have relation with the table Vendor.
Estimates and Estimate Detail for tracking estimates or quotes to your customers. Each record from the table Estimate connects to one record in the table Customer.
Sales Orders and Sales Order Detail will help to keep track of items that you want to allocate for customers. When you create Sales Order you increase allocated amount in the Item Form. Each record in the table Sales Orders has relation with one record in the table Customer.
Invoice and Invoice detail . If your customers don't pay you in full at the time you provide your service or product, or if they pay in advance, you need to track how much they owe you. You create invoice if you shipping your products to a customer.The inventory software will decrease the On Hand amount of your items and changes balance of the customer.
Customer Payments for tracking payments from your customers for sold products. When you create the Payment the system changes balance of the customer.
Sales receipt. You use Sales Receipt if your customers pay in full at the time they receive your service or product. When you create a Sales Receipt the inventory system decreases "On Hand" amount but doesn't change the customer balance .
Work Order. You can create inventory assembly or new product using work order. You enter name of the assembly and select components for this assembly. When your create a Work order the inventory system increases the "On Hand" amount of the assembly item and decreases amount of the components. Each work order record connects with one record in the table Receiving Detail and several records in the table Invoice Detail.
Transfers for tracking the moving of products between your storehouse or locations. You can see stocks of the item on the Locations if you click on the button "On Hand" on the Item Form. each record in the table Transfer has connection with several records in the Receiving Detail table and in the Invoice Detail table.
Adjust inventory for physical count of your inventory.

Additional tables using in the transactions:

Payment Terms
Payment Methods
Customer Messages
Tax Code List
Ship Via List

Records from these tables are used in invoices,orders and receipts.

How will software track your inventory.

2009-06-30T02:52:00.000-07:00

Inventory systems use Item receipts for tracking all incoming products and invoices and sales receipts for tracking all outcoming products. These documents have information about vendor or customer and list of items with quantities and prices. You can print these documents and save in your company and send to your partners.

First of all, you create an initial item receipt with all your existed products. You enter in the column Quantity current stocks of your items. The inventory system will show these values in the Item list form in the column On hand. If you have several warehouses you'll create item receipt for each warehouse with defined field Location. When you sell you product you have to create an invoice and save it. After that, the inventory system will subtract shipped amount from received amount and show in the on hand field of the Item list the current on hand amount.

Syatem will recalculate on hand amounts for your products after each new item receipt or invoice. If you want to have accurate information about your inventory you have to create document for each receiving and shipping.

Customer and vendor list in inventory systems

2009-06-28T10:46:00.000-07:00

You have deal with many companies in your business. And you can divide them in customers and vendors. Any inventory system has tables for customers and vendors. You'll define their names, addresses, discounts, contact persons and other information in these tables. Customer and vendor lists allow to find any company and edit it. You'll be use the customer and vendor list for tracking your receiving and shipping operations. You'll enter information about your partner only one time. Then you'll find required company in the customer or vendor list and add it to your invoice , sales order or purchase order.

Item list in an inventory system.

2009-06-27T13:13:00.000-07:00

Any inventory system has two parts. The first one is a program and the second one is a database. The database is a set of tables. Some tables we'll name lists. These tables will keep information about your products,customers, vendors and other constant parameters. The other tables we will name Actions. These tables must include information on the inflow and outflow of inventory items as well as the quantities and prices of items.

Items or products are base of any inventory system. You'll keep information about your items in the item table. This table usually has fields Item Name,Description,Vendor Price, Sales Price,Department, Bar Code. You'll fill out these fields one time when you receive an item in your warehouse. If you receive this item second time you use your first record. You can only change vendor and sales prices. You'll be able to search any item in the item list.

And you'll be able to edit any record in the item list.

The item table doesn't have information about current on hand amount of items. The inventory system calculates this amount as difference between received and shipped quantity each time when your open the item list form.

What is inventory software?

2009-06-26T05:41:00.000-07:00

If you have any business you face with inventory control everyday. Inventory is an asset that must be managed as it has a direct influence on your money. Obviously, you are tracking all your inventory at paper or in MS Excel. Sometime, you think I spend a lot of time calculating real amounts of your items in warehouse but inventory software systems are expensive and complex. But if you want that your business grows you have to improve inventory accounting in your company.
It's not so hard and not so expensive as you thought. And you'll have the next benefits:
Get items on hand when you need them.
Increase customer satisfaction.
Reduce carrying costs.
Improve the ordering process.
Maximize order flow.
Reduce waste.
I'll describe in this blog how inventory systems work.