I had a quick question that I cant seem to find an answer to. On my WordPress site, I am using WPDataTables to connect to an existing MSSQL Database. I am able to Retrieve, Update, & Delete records with no issues, but for some reason I am unable to Create entries. After filling out the form to create a new entry and submitting, I get "Success" notification, but I cant seem to find my new entry in my external database, or my WordPress database anywhere. So basically, records are saying that they were submitted successfully, when in reality they aren't. Is adding records not possible? Or could this be a potential bug? Thanks in advance!
On a side note: My filtering doesnt seem to be working with any of my SQL based tables either. Even when I try to search a column for a value that I know exists, my table returns 0 entries as soon as I type my first character into the filter.
Please check what the ID column for editing is in the "Editing" tab above the table:
That column needs to be an auto-increment unique-value column in the database, so the plugin can assign a new value to the column when you enter a new row.
As for filtering. If you have a query with JOIN, CONCAT, UNION and other statements, most likely that filtering is not going to work for server-side tables.
To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.
For the table that isnt filtering, the query is just about as simple as it can get... It looks like
SELECT * FROM Billing.Unit
The only thing I really need to be able to do is enter a deviceID to search the table for, which will be different every time the table is being used, so I really need to be able to search the table each time.
As far as adding a new row goes, my table already has an auto-increment ID field which I confirmed is the ID for column editing. I have two other tables, with an equal or greater amount of data in them that seem to work just fine while using an almost identical SELECT * statement. So I guess I was wondering why those two would be working and this one not be.
EDIT: Creating the view appears to have solved the filtering issue, havent got around to testing the add new again... Will update again as soon as I know something.
I'm glad to hear creating a view solved the issue with filtering. However, please note that views can only be editable for one underlying table, if you pass the proper ID.
As for the original query - if this is being pulled from the same database, I believe the issue is the point in Billing.Unit
Points are being considered delimiters between database and the table, or table and a column. If you have a point in the name, that can cause the issue. Changing the database name to "BillingUnit", for example, will problably solve the issue.
I had a quick question that I cant seem to find an answer to. On my WordPress site, I am using WPDataTables to connect to an existing MSSQL Database. I am able to Retrieve, Update, & Delete records with no issues, but for some reason I am unable to Create entries. After filling out the form to create a new entry and submitting, I get "Success" notification, but I cant seem to find my new entry in my external database, or my WordPress database anywhere. So basically, records are saying that they were submitted successfully, when in reality they aren't. Is adding records not possible? Or could this be a potential bug? Thanks in advance!
On a side note: My filtering doesnt seem to be working with any of my SQL based tables either. Even when I try to search a column for a value that I know exists, my table returns 0 entries as soon as I type my first character into the filter.
Hello Dalton
Please check what the ID column for editing is in the "Editing" tab above the table:
That column needs to be an auto-increment unique-value column in the database, so the plugin can assign a new value to the column when you enter a new row.
As for filtering. If you have a query with JOIN, CONCAT, UNION and other statements, most likely that filtering is not going to work for server-side tables.
To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
For the table that isnt filtering, the query is just about as simple as it can get... It looks like
SELECT * FROM Billing.Unit
The only thing I really need to be able to do is enter a deviceID to search the table for, which will be different every time the table is being used, so I really need to be able to search the table each time.
As far as adding a new row goes, my table already has an auto-increment ID field which I confirmed is the ID for column editing. I have two other tables, with an equal or greater amount of data in them that seem to work just fine while using an almost identical SELECT * statement. So I guess I was wondering why those two would be working and this one not be.
EDIT: Creating the view appears to have solved the filtering issue, havent got around to testing the add new again... Will update again as soon as I know something.
Hi Dalton
I'm glad to hear creating a view solved the issue with filtering. However, please note that views can only be editable for one underlying table, if you pass the proper ID.
As for the original query - if this is being pulled from the same database, I believe the issue is the point in Billing.Unit
Points are being considered delimiters between database and the table, or table and a column. If you have a point in the name, that can cause the issue. Changing the database name to "BillingUnit", for example, will problably solve the issue.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables