MSSQL query error - Invalid column name 'IdCliente'

Question

I'm trying to INSERT a column into a table, but there is an error that I can't figure out how to fix it. My Code:

<?php
//connect to sql

    // Dados do banco
    $dbhost   = "db-gadel-campinas.lexos.com.br:1500";   #Nome do host
    $db       = "LexManager_GadelCampinas";   #Nome do banco de dados
    $user     = "user"; #Nome do usuário
    $password = "pass";   #Senha do usuário

        // Dados da tabela
    $tabela = "Clt_cadPontosFidelidade";    #Nome da tabela
    $cliente = "IdCliente"; #id do cliente
    $voucher = "IdPontos";
    $cliente2 = "9662";


    @mssql_connect($dbhost,$user,$password) or die("Não foi possível a conexão com o servidor!");
    @mssql_select_db("$db") or die("Não foi possível selecionar o banco de dados!");


 // Input into staff database
$query = "INSERT INTO $tabela (IdCliente, Pontos, Ativado, Data, IdPov)
          VALUES ($cliente2, -10, 1, GETDATE(), 10)              
          ";

$result = mssql_query($query) or die('Error querying MSSQL database');

//close to sql
//mssql_close($dbc);


echo $result['IdCliente'] . '- Código do Cliente<br />';
echo $result['IdPontos'] . '- Número do voucher do cliente<br />';
echo 'Thank you <br />';
echo 'Ponte Store';
?>

Error I've got:

Warning: mssql_query() [function.mssql-query]: message: Invalid column name 'IdCliente'. (severity 16) in /home/storage/c/60/21/allantoledo/public_html/NovoApp/resgatar.php on line 27

Warning: mssql_query() [function.mssql-query]: General SQL Server error: Check messages from the SQL Server (severity 16) in /home/storage/c/60/21/allantoledo/public_html/NovoApp/resgatar.php on line 27

Warning: mssql_query() [function.mssql-query]: Query failed in /home/storage/c/60/21/allantoledo/public_html/NovoApp/resgatar.php on line 27 Error querying MSSQL database

Thank you!

Allan

[When to use single quotes, double quotes, and backticks in MySQL](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) which also applies to SQL-server. — Funk Forty Niner, Feb 10 '17 at 18:28
What is `IdCliente` representing in the values statement: `VALUES (IdCliente, -10, 1, GETDATE(), 10)` - its not a variable and a column name here is not valid, is it supposed to be a string? — Alex K., Feb 10 '17 at 18:28
@AlexK. SELECT was just the last shot. It means nothing, I think. — Állan Fidelis Toledo, Feb 10 '17 at 18:31
You have the equivalent to `insert T(a,b) values (a, 'hello')` where the column `T.a` is included in `values()` - this is not valid. — Alex K., Feb 10 '17 at 18:32
@AlexK. It worked, thank you. Now, I can't get the values of $result array. Any idea? I will uptate the code with the new one. — Állan Fidelis Toledo, Feb 10 '17 at 18:49

score 1 · Accepted Answer · answered Feb 10 '17 at 18:49

1

It is expecting a literal value not a column name. For example, this would be fine (assuming IDCliente is not a numeric field):

INSERT INTO $tabela (IdCliente, $pontos, Ativado, Data, IdPov)
VALUES ('IdCliente', -10, 1, GETDATE(), 10)

You need to pass in an actual value for IDCliente rather than the name of the column. If you need to get the value for IDCliente from some other table then your query would look something like:

INSERT INTO $tabela (IdCliente, $pontos, Ativado, Data, IdPov)
SELECT IdCliente, -10, 1, GETDATE(), 10
FROM SomeTableName

answered Feb 10 '17 at 18:49

David Cram

768
7
11

It worked. But now, I can't get the values of my array $result, any idea what is happen? – Állan Fidelis Toledo Feb 10 '17 at 18:54
Sorry, I know SQL but I don't really know PHP. However, it looks to me like you are expecting some kind of result set with values for each field? However, for an insert I think you aren't really going to get a result for individual fields but just a true/false result about whether the query ran successfully or not. If you want individual field values you maybe need a select query. – David Cram Feb 10 '17 at 19:04
Now I get it! Thanks! – Állan Fidelis Toledo Feb 10 '17 at 19:05

MSSQL query error - Invalid column name 'IdCliente'

1 Answers1