ShipRush SQL: Getting Data from the Northwind Database

This page is a walk through to set up ShipRush with the Northwind database on SQL Server or MSDE 2000.

 

At the end of these steps, you will have a working ShipRush SQL setup, and can even use a bar code scanner with it.

 

Parts List:

 

 

Have the parts?

 

Let's go:

 

  1. Create a new database in the SQL Server. Name it NorthwindCS

  2. Unzip the example kit, and locate the file "NorthwindCS.SQL"

  3. Log into SQL Server with Query Analyzer or equivalent, and change to the NorthwindCS database

  4. Run the "NorthwindCS.SQL" script in the database. This creates the database and populates the system with data.

  5. Install ShipRush. If running the ShipRush demo, choose "ADO and SQL Databases" when prompted for the host system. If you have a ShipRush license, enter your ShipRush SQL serial number.

  6. ShipRush will go to the System Tray.

  7. Right click on ShipRush, select Settings | Program Settings and then press CM Settings

  8. Press Import Settings From File:

 

 

And navigate to the example script "Northwind_MSSQL.xml" (from the example kit)

 

  1. Now go to the Getting Contact Info and Writing History Info and set the ADO Connection string to connect to your NorthwindCS database:

 

 

  1. If you have a supported bar code scanner, configure it and then check the option and test it in the USB Scanner Setup screen.

  2. Press OK.

  3. ShipRush goes to the system tray.

  4. Continue below

 

 

  1. If you do not have a bar code scanner, Double click on the ShipRush icon in the system tray

  2. The search screen pops up:

 

  1. Enter an order number. Try these examples:

 

10616

10394

10482

 

  1. When ShipRush pops up, either press Ship or Cancel to complete the shipment.

  2. That is it! You are now shipping with ShipRush SQL.

 

 

 

  1. If you have a bar code scanner. Double click on ShipRush to bring up the Search screen.

  2. Scan these bar codes (print them out first -- laser scanners usually cannot scan from the screen):

 

Hint: If ShipRush is in the system tray, scan the code twice to get ShipRush to pop all the way up.

 

 

           Order 10616

 

           Order 10394

 

           Order 10482

 

 

You can see this kit in action in the Northwind example video.

 

 

SQL Discussion

 

The Northwind example is simple. This example:

 

1) Retrieves the customer address based on Orders.OrderID

2) Updates the shipping cost in Orders

 

The 'get' data query is simple:

 

SELECT

  o.OrderID as RecordID,

  o.ShipName as ContactName,

  c.CompanyName as CompanyName,

  o.ShipAddress as Address1,

  o.ShipCity as City,

  o.ShipRegion as State,

  o.ShipPostalCode as ZIP,

  o.ShipCountry as Country,

  c.Phone as Phone

FROM dbo.orders o

  LEFT OUTER JOIN dbo.Customers c ON c.CustomerID = o.CustomerID

WHERE o.OrderID LIKE '%SEARCHPARAM%%'

 

That is it.

 

The write history query is also simple:

 

  update dbo.Orders set Freight = %SHIPPINGCHARGES% where OrderID = %RecordID%

 

 

As you see, the RecordID value is special. It flows through ShipRush, coming into ShipRush in the SELECT, and then being used in the WHERE clause of the UPDATE.

 

You can see this kit in action in the Northwind example video.

 

 

Next: Advanced Shipping Control