nuBuilder Forum

3 minutes later: Aha! Got it... two of those field names contain spaces!!! Yes, the tutorials warned about that! Any way I can squash this thread or might it be useful for other newbs?


Hi, having got a certain way with the video tutorials I am now going back over them with my "real" data, migrated into MySQL.

The Forms Wizards seems persistently to have problems with one of my tables. I'm assuming this is sthg to do with one or more of the fields.

I did a "structure dump" for the table (see below) and just wonder if someone can say which field looks odd?

I also got (once) an intelligble error alert:

===USER==========

globeadmin

===PDO MESSAGE===

SQLSTATE[42000]: Syntax error or access violation: 1583 Incorrect parameters in the call to native function 'IFNULL'

===SQL===========

SELECT InvoiceNo,IFNULL(Date,''),IFNULL(Issued,''),IFNULL(Paid,''),IFNULL(REPLACE(FORMAT(VATRate,4), ",", ""),''),IFNULL(Notes,''),IFNULL(Currency,''),IFNULL(How paid,''),IFNULL(When cleared,''),IFNULL(REPLACE(FORMAT(Outstanding,4), ",", ""),'') from tblinvoices

===BACK TRACE====

D:\Apache root\nuBuilderPro-kernel\nuapi.php - line 2024 (nuRunQuery)

D:\Apache root\nuBuilderPro-kernel\nuapi.php - line 1700 (nuGetBrowseRecords)

D:\Apache root\nuBuilderPro-kernel\nuapi.php - line 193 (nuGetBrowseForm)

... and this is the structure dump:

-- phpMyAdmin SQL Dump
-- version 4.2.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 29, 2015 at 07:03 PM
-- Server version: 10.0.12-MariaDB
-- PHP Version: 5.5.14

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `kernel3data`
--

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

--
-- Table structure for table `tblinvoices`
--

CREATE TABLE IF NOT EXISTS `tblinvoices` (
`ClientID` varchar(3) DEFAULT NULL,
`InvoiceNo` smallint(5) NOT NULL,
`Date` datetime DEFAULT NULL,
`Issued` tinyint(1) NOT NULL,
`Paid` datetime DEFAULT NULL,
`VATRate` decimal(18,4) DEFAULT NULL,
`Notes` varchar(255) DEFAULT NULL,
`Currency` varchar(5) DEFAULT NULL,
`How paid` varchar(50) DEFAULT NULL,
`When cleared` datetime DEFAULT NULL,
`Outstanding` decimal(19,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblinvoices`
--
ALTER TABLE `tblinvoices`
ADD PRIMARY KEY (`InvoiceNo`), ADD KEY `How paid` (`How paid`), ADD KEY `NUMBER` (`InvoiceNo`), ADD KEY `PAID` (`Paid`), ADD KEY `tblContactstblInvoices` (`ClientID`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblinvoices`
--
ALTER TABLE `tblinvoices`
ADD CONSTRAINT `tblContactstblInvoices` FOREIGN KEY (`ClientID`) REFERENCES `tblcontacts` (`Code`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

... by the way, the two "DECIMAL" fields looked like particularly likely culprits, so I tried creating a form without them but with all other fields... Still couldn't create the form. I'm now ploughing through all fields one by one to find out when I can and when I can't then create a form...

mrodent,

You make your job a lot harder when you have spaces in field names, in fact as you see nuBuilder won't cope with it in many cases, you should replace the space with an underscore

If you try to use spaces IFNULL(How paid,'') needs to be IFNULL(`How paid`,'')

Steven