PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] INSERT values into table except into the Autoincrement Column.-VBForums
Results 1 to 12 of 12

Thread: [RESOLVED] INSERT values into table except into the Autoincrement Column.

  1. #1

    Thread Starter
    Addicted Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    185

    Resolved [RESOLVED] INSERT values into table except into the Autoincrement Column.

    Hello!

    I'm trying to INSERT a couple of values into my table ("Ventas_del_Dia_Table"), it has the following columns:

    CREATE TABLE "Ventas_del_Dia_Table" (
    "#_de_Cliente" TEXT,
    "Nombre_Cliente" TEXT,
    "Concepto" TEXT,
    "Precio_Unitario" TEXT,
    "Cantidad" TEXT,
    "Descuento" TEXT,
    "Importe" NUMERIC,
    "Fecha" TEXT,
    "Entradas" TEXT,
    "Salidas" TEXT,
    "Motivo_de_la_Salida" TEXT,
    "Realizado_Por" TEXT,
    "Idx" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
    );

    This is my query (it used to work fine, until I added the Column "Idx":

    CommandSQL = New SQLiteCommand("INSERT INTO Ventas_del_Dia_Table (#_de_Cliente, Nombre_Cliente, Concepto, Precio_Unitario, Cantidad, Descuento, Importe, Fecha, Entradas, Salidas, Motivo_de_la_Salida, Realizado_Por) VALUES ('" & "No aplica" & "','" & "No aplica" & "','" & "Depůsto" & "','" & "$0.00" & "','" & "No aplica" & "','" & "No aplica" & "','" & CDbl(Entradas_Form.tb_entrada.Text).ToString & "','" & Today.ToShortDateString & "','" & FormatCurrency(Entradas_Form.tb_entrada.Text).ToString & "','" & "No aplica" & "','" & Entradas_Form.TextBox_comentario.Text & "','" & "No aplica" & "')", ConnectionSQL)

    Now it is giving me this error:
    SQL logic error near "#_de_Cliente": syntax error'
    I know my query is not sending value to the Idx column, since it is the PK and Autoincrement, I guess I shouldn't send any value to it, Should I?
    Last edited by Spybot; Nov 21st, 2019 at 02:24 AM.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,451

    Re: INSERT values into table except into the Autoincrement Column.

    Quote Originally Posted by Spybot View Post
    I guess I shouldn't send any value to it, Should I?
    That's correct. If you want the database to generate a value for a column then don;t provide one yourself.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,451

    Re: INSERT values into table except into the Autoincrement Column.

    Quote Originally Posted by Spybot View Post
    vb.net Code:
    1. CommandSQL = New SQLiteCommand("INSERT INTO Ventas_del_Dia_Table (#_de_Cliente, Nombre_Cliente, Concepto, Precio_Unitario, Cantidad, Descuento, Importe, Fecha, Entradas, Salidas, Motivo_de_la_Salida, Realizado_Por) VALUES ('" & "No aplica" & "','" & "No aplica" & "','" & "Depůsto" & "','" & "$0.00" & "','" & "No aplica" & "','" & "No aplica" & "','" & CDbl(Entradas_Form.tb_entrada.Text).ToString & "','" & Today.ToShortDateString & "','" & FormatCurrency(Entradas_Form.tb_entrada.Text).ToString & "','" & "No aplica" & "','" & Entradas_Form.TextBox_comentario.Text & "','" & "No aplica" & "')", ConnectionSQLy)
    That code is rather terrible. Why would you concatenate all those literal Strings? If you wanted the value '2', would you use '1 + 1' in code? Of course not, so why would you use '"Hello" & " " & "World"' if you wanted the value '"Hello World"'? That code should combine all the literal Strings where possible. That means basically taking out every instance of '" & "' from it.
    vb.net Code:
    1. CommandSQL = New SQLiteCommand("INSERT INTO Ventas_del_Dia_Table (#_de_Cliente, Nombre_Cliente, Concepto, Precio_Unitario, Cantidad, Descuento, Importe, Fecha, Entradas, Salidas, Motivo_de_la_Salida, Realizado_Por) VALUES ('No aplica','No aplica','Depůsto','$0.00','No aplica','No aplica','" &
    2. CDbl(Entradas_Form.tb_entrada.Text).ToString & "','" & Today.ToShortDateString & "','" & FormatCurrency(Entradas_Form.tb_entrada.Text).ToString & "','No aplica','" & Entradas_Form.TextBox_comentario.Text & "','No aplica')", ConnectionSQLy)
    The only way you could ever justify concatenating two literal Strings is in order to break a long String over multiple lines. In the case of SQL code though, you should be using a multiline String literal or an XML literal.

    Even the code I posted above could be cleaned up further in a number of ways, the most significant being that you should never use string concatenation to insert variables into SQL code at all. You should be using parameters for that. I suggest that you follow the Blog link in my signature below and read my post on the subject.

  4. #4

    Thread Starter
    Addicted Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    185

    Re: INSERT values into table except into the Autoincrement Column.

    I'm not providing any, my table has 13 columns and my query is sending only 12 values.
    I don't know why this error is happening!

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,451

    Re: INSERT values into table except into the Autoincrement Column.

    Quote Originally Posted by Spybot View Post
    I'm not providing any, my table has 13 columns and my query is sending only 12 values.
    I don't know why this error is happening!
    You have to provide a value for every column you specify, so if you don't want to provide a value for the Idx column then don;t specify that column. The column list and the value list have to have the same number of items in them.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,451

    Re: INSERT values into table except into the Autoincrement Column.

    Maybe I have misunderstood you. Are you saying that the table originally had 12 columns and your INSERT statement with 12 columns worked, but now you have added a new column to the table and the SQL code doesn't work? I thought you were saying that you added the column to the SQL code, not to the table.

    As for the issue, I think all you need to do is wrap the specified column name in brackets. I doubt starting an identifier with the # character is legal otherwise:
    vb.net Code:
    1. CommandSQL = New SQLiteCommand("INSERT INTO Ventas_del_Dia_Table ([#_de_Cliente], Nombre_Cliente, ...
    Did you perhaps not specify the columns explicitly before?

  7. #7

    Thread Starter
    Addicted Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    185

    Re: INSERT values into table except into the Autoincrement Column.

    I Know Parameters are always better, but this time I'll stick with my query the way it is now. since "The column list and the value list have to have the same number of items in them." What kind of value should I send to my Idx column? I've already tried sending the following vales:
    Nothing
    ' '
    But I'm still getting the error.

  8. #8

    Thread Starter
    Addicted Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    185

    Re: INSERT values into table except into the Autoincrement Column.

    Yes, I added the Idx column to my table.
    Before I added this column, the query used to work fine.
    I've tried adding the brackets and now I'm getting: System.Data.SQLite.SQLiteException: 'SQL logic error
    13 values for 12 columns'


    So, I've added the Idx column to the query code, so I have 13:13 and now I'm getting:
    System.Data.SQLite.SQLiteException: 'datatype mismatch datatype mismatch'

    What king of value should I send to the Idx column?
    Last edited by Spybot; Nov 21st, 2019 at 03:04 AM.

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,369

    Re: INSERT values into table except into the Autoincrement Column.

    So, I've added the Idx column to the query code, so I have 13:13 and now I'm getting:
    Don't. Take idx back out of the column list and out of the value list. You shouldn't supply it in either. In fact, adding that autoincrement should not have affected your insert statement at all.

    Honestly, your code in post 1 and JM's code in post 3 look roughly correct at a glance. Neither references the idx column and both appear to be passing in 12 fields and 12 values so I think the idx field is probably a red herring. However, it's very difficult to read with all your string concatenation. Can you provide us with sql string that's actually being passed to the database? Ie, what is the result of al that concatenation? I suspect the problem is either with a quote being in the wrong place (always a favourite) or an issue with the # as JM identified.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,563

    Re: INSERT values into table except into the Autoincrement Column.

    Quote Originally Posted by Spybot View Post
    Hello!

    I'm trying to INSERT a couple of values into my table ("Ventas_del_Dia_Table"), it has the following columns:

    CREATE TABLE "Ventas_del_Dia_Table" (
    "#_de_Cliente" TEXT,
    "Nombre_Cliente" TEXT,
    "Concepto" TEXT,
    "Precio_Unitario" TEXT,
    "Cantidad" TEXT,
    "Descuento" TEXT,
    "Importe" NUMERIC,
    "Fecha" TEXT,
    "Entradas" TEXT,
    "Salidas" TEXT,
    "Motivo_de_la_Salida" TEXT,
    "Realizado_Por" TEXT,
    "Idx" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
    );

    This is my query (it used to work fine, until I added the Column "Idx":

    CommandSQL = New SQLiteCommand("INSERT INTO Ventas_del_Dia_Table (#_de_Cliente, Nombre_Cliente, Concepto, Precio_Unitario, Cantidad, Descuento, Importe, Fecha, Entradas, Salidas, Motivo_de_la_Salida, Realizado_Por) VALUES ('" & "No aplica" & "','" & "No aplica" & "','" & "Depůsto" & "','" & "$0.00" & "','" & "No aplica" & "','" & "No aplica" & "','" & CDbl(Entradas_Form.tb_entrada.Text).ToString & "','" & Today.ToShortDateString & "','" & FormatCurrency(Entradas_Form.tb_entrada.Text).ToString & "','" & "No aplica" & "','" & Entradas_Form.TextBox_comentario.Text & "','" & "No aplica" & "')", ConnectionSQL)

    Now it is giving me this error:
    SQL logic error near "#_de_Cliente": syntax error'
    I know my query is not sending value to the Idx column, since it is the PK and Autoincrement, I guess I shouldn't send any value to it, Should I?
    No one is paying attention to the error....
    SQL logic error near "#_de_Cliente": syntax error'

    NO WHERE does it say there's a problem with idx... it CLEARLY states the syntax error is near #_de_Cliente .... and YES IT IS! If you LOOK at the SQL above:
    Code:
    CommandSQL = New SQLiteCommand("INSERT INTO Ventas_del_Dia_Table (#_de_Cliente, Nombre_Cliente, Concepto, Precio_Unitario, Cantidad, Descuento, Importe,
    Yeah, I don't care what SQL version or DBS you're using, that syntax right there isn't allowed. # is a special char. Honestly I don't know why they DBMS even allowed you to use that in the column name in the first place, but that's the problem right there. Chang your column name in the table to all text, or use what ever annotation is needed for the DBMS you're using to escape the object ( dfor SQL Server it's [#_de_Cleinte], for mySQL it's `#_de_Cliente` for Access it's '#_de_Cliente' ... etc).

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11

    Thread Starter
    Addicted Member Spybot's Avatar
    Join Date
    Jan 2019
    Posts
    185

    Re: INSERT values into table except into the Autoincrement Column.

    OK, I got it!
    I took idx out of the column list and the value list as FunkyDexter suggested, also I used [#_de_Cleinte] since I'm using an SQLite database.

    vb.net Code:
    1. CommandSQL = New SQLiteCommand("INSERT INTO Ventas_del_Dia_Table ([#_de_Cliente], Nombre_Cliente, Concepto, Precio_Unitario, Cantidad, Descuento, Importe, Fecha, Entradas, Salidas, Motivo_de_la_Salida, Realizado_Por) VALUES ('No aplica','No aplica','Depůsto','$0.00','No aplica','No aplica','" & CDbl(Entradas_Form.tb_entrada.Text).ToString & "','" & Today.ToShortDateString & "','" & FormatCurrency(Entradas_Form.tb_entrada.Text).ToString & "','No aplica','" & Entradas_Form.TextBox_comentario.Text & "','No aplica')", ConnectionSQL)
    .

    Thank you All!

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,451

    Re: INSERT values into table except into the Autoincrement Column.

    Quote Originally Posted by Spybot View Post
    OK, I got it!
    I took idx out of the column list and the value list as FunkyDexter suggested, also I used [#_de_Cleinte] since I'm using an SQLite database.

    vb.net Code:
    1. CommandSQL = New SQLiteCommand("INSERT INTO Ventas_del_Dia_Table ([#_de_Cliente], Nombre_Cliente, Concepto, Precio_Unitario, Cantidad, Descuento, Importe, Fecha, Entradas, Salidas, Motivo_de_la_Salida, Realizado_Por) VALUES ('No aplica','No aplica','Depůsto','$0.00','No aplica','No aplica','" & CDbl(Entradas_Form.tb_entrada.Text).ToString & "','" & Today.ToShortDateString & "','" & FormatCurrency(Entradas_Form.tb_entrada.Text).ToString & "','No aplica','" & Entradas_Form.TextBox_comentario.Text & "','No aplica')", ConnectionSQL)
    .

    Thank you All!
    So exactly what I told you to do in post #5 and #6 then.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width