# Gridview with CRUD Options (MySQL)

**URL:** <https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244>\
**Category:** Dashboard\
**Created:** [13 August 2018 03:00 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244 "2018-08-13T03:00:05Z")\
**Posts on this page:** 9\
**Page:** 2

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [11 September 2018 10:00 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/21 "2018-09-11T10:00:52Z")

</div>

You have an error in eithor your data.txt or the update code you are using. It throws an error: "Error: ER\_TRUNCATED\_WRONG\_VALUE\_FOR\_FIELD: Incorrect integer value: 'Rishan' for column 'lastname' at row 1"

You can't define a colum as 'int' and then expect to insert text into it.

Also, you define several rows as 'int(255)' why? Do you realize that that says you want to store 255 digits?

`INT(size)	-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis`

so the maximum size in parenthesis can only be 10.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [12 September 2018 04:03 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/22 "2018-09-12T04:03:08Z")

</div>

There are two tables called **test** and **lastname**. Table **test** consists of **id** , **name** , **lastname** and **code** columns. Table **lastname** consists of **id** and **lastname** columns. I want to store the data into **test** table, but I want the **lastname** dropdown field options coming up from **lastname** column ( **lastname** table). I just would like to save the **id** of the **lastname** table into **test** table.

That int(255) came automatically once I created the column. If 10 also fine.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [12 September 2018 10:17 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/23 "2018-09-12T10:17:38Z")

</div>

So if I understand you, you want all last names stored in the table called 'lastname' while in the table called 'test' the column you call 'lastname' should actually be 'lastname\_idx' to show that it is just an index into the 'lastname' table. It will only contain the index value (a number) of the name in the 'lastname' table. Is that correct?

If so, you need to correct your update code to move the `id` not the `lastname` feild into the `lastname` of `test`, but then how is your add suppose to work? How will someone add a new last name into the table?

I might suggest you copy this Gridview code out and put it in some straight HTML files and get it to work, before trying to get it to work in Node-RED.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [14 September 2018 07:35 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/24 "2018-09-14T07:35:07Z")

</div>

@zenofmud, I would like to make the **lastname** column data of **lastname** table as the dropdown options on Gridview's **lastname** field. The **id** of **lastname** table should be added/updated to the **lastname** column of **test** table once I perform add/edit on Gridview (according to selected **lastname** ).

It's already too long I created this topic, but no solution for this till date. I'm pretty sure @shrickus able to provide the solution for this. @knolleary, I need your help as well here.

Thanks for helping. 🙂

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 September 2018 08:25 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/25 "2018-09-14T08:25:32Z")

</div>

@rishanaziz Let me pose a scenario for you.

I'm told to add a new person, 'John Smith' with code '888' to the table. I go to the table and press the plus (+) button. I can now enter first name 'John' and the code, but since you want lastname stored in a different table, how do I add 'Smith' to that other table?

This is NOT a Node-RED problem, your underlining code is not working. I suggest you eithor

1. change the requitement and store the actual lastname in the test table or
2. get it working outside of NR so you have an idea of how to get this scenario with Gridview to work.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [1 October 2018 01:00 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/26 "2018-10-01T01:00:06Z")

</div>

Hi all,

I accomplished my objective using jqGrid.

I added the flow to the following link:

[https://flows.nodered.org/flow/9a57908f031def68946f6bdf4cfb94a4](https://flows.nodered.org/flow/9a57908f031def68946f6bdf4cfb94a4)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [2 October 2018 09:20 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/27 "2018-10-02T09:20:52Z")

</div>

It's nice of you to provide a flow, but without the underlining database tables it is not very helpful as an example.

---

<div class="post-metadata">

**Author:** ![thiren](https://avatars.discourse-cdn.com/v4/letter/t/48db29/32.png) [@thiren](https://discourse.nodered.org/u/thiren)\
**Post date:** [2 October 2018 09:43 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/28 "2018-10-02T09:43:06Z")

</div>

> [@rishanaziz](#):
>
> Hi all,
> 
> I accomplished my objective using jqGrid.
> 
> I added the flow to the following link:
> 
> [https://flows.nodered.org/flow/9a57908f031def68946f6bdf4cfb94a4](https://flows.nodered.org/flow/9a57908f031def68946f6bdf4cfb94a4)

Bravo......Very helpful and useful grid.....Thank you.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [2 October 2018 09:43 UTC](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244/29 "2018-10-02T09:43:50Z")

</div>

You're welcome

[Previous page](https://discourse.nodered.org/t/gridview-with-crud-options-mysql/2244.md?page=1)