Alpha Video Training
Results 1 to 7 of 7

Thread: Xbasic SQL INSERT and Null field issue

  1. #1
    Member
    Real Name
    David Fricke
    Join Date
    Sep 2011
    Location
    Kenosha, WI
    Posts
    398

    Default Xbasic SQL INSERT and Null field issue

    I have an Xbasic function in a UX component that writes a record to a table using SQL INSERT. What I can't figure out is this...

    For example. The COST field is a decimal(15,2) field. If I leave the field blank, the script will not write anything. If I put a number in it, it works fine and writes the record. Same goes for anything with the numeric or decimal field type. (using numeric from this point on).

    Why is that?

    Here's the script.

    Code:
    function checkinv as c (e as p)
    	'debug(1)
    	dim cn as sql::Connection
    	dim result as l = .f.
    	dim args as sql::Arguments
    	dim rs as sql::ResultSet
    	dim sqlstm as c 
    	dim sku as n
    	dim location as n
    	dim timeadded as t
    	dim dateadded as d
    	dim store as c
    	dim isDelete as l
    	dim Inventory as l
    	location = "1001"
    	isBarcode = "1"
    	timeadded = now()
    	dateadded = date()
    	store = "102"
    	isDelete = "0"
    	Inventory = "1"
    	
    	result = cn.open("::Name::SQL")
    	if result then
    			sqlstm = "SELECT MAX(SKU) FROM Inventory_NEW WHERE isBarcode = :isBarcode"
    			args.Set("loc",location)
    			args.Set("isBarcode",isBarcode)
    			result = cn.execute(sqlstm,args)
    				if result then 
    				rs = cn.ResultSet
    				sku = rs.data(1)
    				sku = sku+1
    				e._set.skuresult.value = sku
    				
    			sqlTruncateStatement = "TRUNCATE table Inventory_TEMP"
    			cn.Execute(sqlTruncateStatement)
    			sqlInsertStatement = "INSERT into Inventory_TEMP (Quantity,SKU) values (:qty, :BarCodeValue)"
    			args.Set("BarCodeValue",sku)
    			for i = 1 to val(e.dataSubmitted.copies)
    				args.Set("qty",i)
    				cn.Execute(sqlInsertStatement,args)
    			next i
    			
    			args.set("NEWUSED",e.dataSubmitted.NEWUSED)
    			args.set("CATEGORY",e.dataSubmitted.CATEGORY)
    			args.set("ITEM",e.dataSubmitted.ITEM)
    			args.set("MODEL",e.dataSubmitted.VENDORMODEL)
    			args.set("SIZE",e.dataSubmitted.SIZE)
    			args.set("PRICE",e.dataSubmitted.PRICE)
    			args.set("ACTIVE",e.dataSubmitted.ACTIVE)
    			args.set("DATEADDED",dateadded)
    			args.set("TIMEADDED",timeadded)
    			args.set("QUANTITY",e.dataSubmitted.QUANTITY)
    			args.set("ISBARCODE",isBarcode)
    			args.set("SKURESULT",sku)
    			args.set("TAX1",e.dataSubmitted.TAX1)
    			args.set("ADDITIONALINFO",e.dataSubmitted.ADDITIONALINFO)
    			args.set("STOREGEN",store)
    			args.set("COPIES",e.dataSubmitted.COPIES)
    			args.set("TAXEXEMPT",e.dataSubmitted.TAXEXEMPT)
    			args.set("INVENTORY",e.dataSubmitted.INVENTORY)
    			args.set("SIZE_GROUP",e.dataSubmitted.SIZE_GROUP)
    			args.set("SUPPLIER",e.dataSubmitted.SUPPLIER)
    			args.set("MANUFACTURER",e.dataSubmitted.MANUFACTURER)
    			args.set("DESCRIPTION",e.dataSubmitted.DESCRIPTION)
    			args.set("LOCATION",e.dataSubmitted.LOCATION)
    			args.set("REORDERLEVEL",e.dataSubmitted.REORDERLEVEL)
    			args.set("FINANCEPRICE",e.dataSubmitted.FINANCEPRICE)
    			args.set("COST",e.dataSubmitted.COST)
    			args.set("PRICEMULTIPLE",e.dataSubmitted.PRICEMULTIPLE)
    			args.set("FINANCEMULTIPLE",e.dataSubmitted.FINANCEMULTIPLE)
    			args.set("PRODUCTID",e.dataSubmitted.PRODUCTID)
    			args.set("PRICESHEETHEADER",e.dataSubmitted.PRICESHEETHEADER)
    			args.set("PRICESHEETDESCRIPTION",e.dataSubmitted.PRICESHEETDESCRIPTION)
    			args.set("PRODUCTVARIABLE",e.dataSubmitted.PRODUCTVARIABLE)
    			
    			
    			dim sqlInsertInventory as c 
    			sqlInsertInventory = "INSERT INTO Inventory_New (new_used, category, item_name, Size, price, Active, DateAdded, TimeAdded, quantity, isBarcode, SKU, tax1, additionalinfo, StoreGen, Copies, TaxExempt, cost, VendorModel, Inventory, Size_Group, supplier, manufacturer, description, location, reorderlevel, FinancePrice,PriceMultiple, FinanceMultiple, ProductID, ProductVariable, PriceSheetHeader, PriceSheetDescription) VALUES (:NEWUSED, :CATEGORY, :ITEM, :SIZE, :PRICE, :ACTIVE, :DATEADDED, :TIMEADDED, :QUANTITY, :ISBARCODE, :SKURESULT, :TAX1, :ADDITIONALINFO, :STOREGEN, :COPIES, :TAXEXEMPT, :COST, :MODEL, :INVENTORY, :SIZE_GROUP, :SUPPLIER, :MANUFACTURER, :DESCRIPTION, :LOCATION, :REORDERLEVEL, :FINANCEPRICE, :PRICEMULTIPLE, :FINANCEMULTIPLE, :PRODUCTID, :PRODUCTVARIABLE, :PRICESHEETHEADER, :PRICESHEETDESCRIPTION )"
    			cn.Execute(sqlInsertInventory,args)
    			
    	end if
    	cn.close()
    end if
    end function

    AND... see the image attached. I get the squiggly red line under end function with this warning message. Script works just fine except for the numeric/decimal fields when blank.

    GRAPHICS-0016.png

  2. #2
    Member
    Real Name
    Alex
    Join Date
    Oct 2015
    Location
    Perth, WA Australia
    Posts
    141

    Default Re: Xbasic SQL INSERT and Null field issue

    Must likely the COST field is trying to be inserted as Character if blank.

    I would test
    if e.dataSubmitted.COST = "" then
    e.dataSubmitted.COST = null_value()
    end if

    AND... see the image attached. I get the squiggly red line under end function with this warning message. Script works just fine except for the numeric/decimal fields when blank.
    The squiggly red line is just indicating that you have not returned anything back to the client.

    You can use "return" key word or checkinv = "" or have it equal some js like checkinv = "alert('Invoice Checked');"
    Alex Collier

    "The spread of computers and the Internet will put jobs in two categories. People who tell computers what to do, and people who are told by computers what to do"

    AA Builds from 5221_5152 to Pre-releases >> Deploying to IIS in AWS

  3. #3
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,546

    Default Re: Xbasic SQL INSERT and Null field issue

    Quote Originally Posted by dfricke10 View Post
    AND... see the image attached. I get the squiggly red line under end function with this warning message. Script works just fine except for the numeric/decimal fields when blank.
    This was a change made in Pre-release build 5675... http://aadocuments.s3.amazonaws.com/...easeNotes.Html
    Xbasic Editors - Functions

  4. #4
    Member
    Real Name
    David Fricke
    Join Date
    Sep 2011
    Location
    Kenosha, WI
    Posts
    398

    Default Re: Xbasic SQL INSERT and Null field issue

    Thanks, Alex. That led me in the right direction.

    As for the squiggly line, understand now. But, oddly the function still works? Is it causing some other problem by not having the return function?

  5. #5
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,546

    Default Re: Xbasic SQL INSERT and Null field issue

    It's just a warning... a reminder... to return something from the function... otherwise no ill effects.

  6. #6
    Member
    Real Name
    David Fricke
    Join Date
    Sep 2011
    Location
    Kenosha, WI
    Posts
    398

    Default Re: Xbasic SQL INSERT and Null field issue

    Since we're on this topic of this script, I added an ImageUpload component to it. running in Desktop, using a Character image field. This script above writes the record. I've added the argument for the field and the script fails when an image has been selected. If I do not specify an image, it saves the "null_1x1.png.a5image" to the field. My assumption is, since it's an Xbasic script to begin with, I need to use the FileUpload User Defined and integrate it into my current function?

  7. #7
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,546

    Default Re: Xbasic SQL INSERT and Null field issue

    How are you getting the image data? Is it base64 data? What are the table field properties? What is the error message?

Similar Threads

  1. How to insert multiple records(rows) with Insert statement in xbasic ?
    By satyanarayana in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 02-02-2012, 11:06 AM
  2. how to insert null in mysql
    By shood38 in forum Application Server Version 10 - Web/Browser Applications
    Replies: 6
    Last Post: 08-01-2011, 07:35 AM
  3. Force null on numeric SQL insert
    By jim_s in forum Alpha Five Version 10 - Desktop Applications
    Replies: 4
    Last Post: 04-24-2010, 11:33 PM
  4. Insert (Null)
    By JohnFabrega in forum Alpha Five Version 9 - Desktop Applications
    Replies: 10
    Last Post: 12-05-2008, 12:33 PM
  5. Xbasic explorer, field insert
    By kfuss in forum Alpha Five Version 7
    Replies: 2
    Last Post: 10-16-2005, 01:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •