Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

Never seen such error:

ERROR [22P02] ERROR: invalid input syntax for integer: ""; Error while executing the query

Can anybody see what may be problem about?

Creating table:

    Public Function PrimkCreate(ByVal myPrimkTable As String, ByVal nCon As OdbcConnection) As Integer
    Dim ans As Integer
    Dim cCommand As OdbcCommand = New OdbcCommand("CREATE TABLE IF NOT EXISTS " + myPrimkTable + "(" & _
            "prm_id int NOT NULL, " & _
            "pkni text, " & _
            "pdatum text, " & _
            "pdatumnaplate text, " & _
            "pdanaodgode int, " & _
            "puldok text, " & _
            "puldokbroj text, " & _
            "pdatumk text, " & _
            "pvrijemek text, " & _
            "pdobid int, " & _
            "pdoboib text, " & _
            "pnabc double precision, " & _
            "purab double precision, " & _
            "ppdv double precision, " & _
            "ppnak double precision, " & _
            "pprodc double precision, " & _
            "pvrstaprimke int, " & _
            "pzapisniktekst text, " & _
            "prez text, " & _
            "CONSTRAINT " & myPrimkTable & "_pkey PRIMARY KEY(prm_id))", nCon)

    ans = cCommand.ExecuteNonQuery()
    cCommand.Dispose()
    Return ans
End Function

Update code:

    Public Function update_LPrimk(ByRef primk As Integer, ByVal mCon As OdbcConnection) As Integer

    Dim retval As Integer
    Dim uCmd As OdbcCommand = New OdbcCommand("UPDATE " & myPrimkTable & " SET " & _
                "prm_id=" & primk & ", " & _
                "pkni='" & prm.pKni & "', " & _
                "pdatum='" & prm.pDatum & "', " & _
                "pdatumnaplate='" & prm.pDatumNaplate & "', " & _
                "pdanaodgode=" & prm.pDanaodgode & ", " & _
                "puldok='" & prm.pUlDok & "', " & _
                "puldokbroj='" & prm.pUlDokBroj & "', " & _
                "pdatumk='" & prm.pDatumk & "', " & _
                "pvrijemek='" & prm.pVrijemek & "', " & _
                "pdobid='" & prm.pDobID & "', " & _
                "pdoboib='" & prm.pDobOib & "', " & _
                "pnabc='" & Replace(prm.pNabc.ToString, ",", ".") & "', " & _
                "purab='" & Replace(prm.pURab.ToString, ",", ".") & "', " & _
                "ppdv='" & Replace(prm.pPdv.ToString, ",", ".") & "', " & _
                "ppnak='" & Replace(prm.pPnak.ToString, ",", ".") & "', " & _
                "pprodc='" & Replace(prm.pProdc.ToString, ",", ".") & "', " & _
                "pvrstaprimke=" & prm.pVrstaPrimke & ", " & _
                "pzapisniktekst='" & prm.pZapisnikTekst & "', " & _
                "prez='" & prm.pRez & "' " & _
                "WHERE prm_id=" + primk.ToString, mCon)

    retval = uCmd.ExecuteNonQuery()
    uCmd.Dispose()
    Return retval
End Function

Query look's exact like this:

UPDATE primke SET prm_id=1, pkni='U', pdatum='07.01.2013', pdatumnaplate='10.01.2013',
pdanaodgode=3, puldok='ghkzug gugug', puldokbroj='jkhk', pdatumk='', pvrijemek='', 
pdobid='', pdoboib='', pnabc='0', purab='0', ppdv='0', ppnak='0', pprodc='0', 
pvrstaprimke=0, pzapisniktekst='', prez='' WHERE prm_id=1

I have many tables with which I work same way but never have such error.

share|improve this question
3  
'0' is not a valid number, it's character literal. 0 is a number. You should also not store dates in text columns. –  a_horse_with_no_name Jan 7 at 18:33
 
Dates and '0' are here because of internationalization issues and VB.NET issues. Those '0' is for double precision numbers stored that way. OK, not proper but I use that in many tables. Here is something specific. –  user973238 Jan 7 at 18:38
 
The '0' is the root of your problem. It is not a number. You have to use 0 for a number value. –  a_horse_with_no_name Jan 7 at 18:44
2  
'' is not a double precision number either. If you mean NULL, use NULL. –  Paul Tomblin Jan 7 at 18:45
1  
As an aside, isn't there something that uses placeholders and bound values in VB.net? There must be a better approach than all this string concatenation. And you should be dealing with L10N and I18N issues at the edges of the application: standardize on input, localize on output. –  mu is too short Jan 7 at 19:11
show 3 more comments

1 Answer

up vote 1 down vote accepted

I would advice to read the chapter Constants in the manual. It's a brief and very informative read.
The cause for the error message is that '' is an empty string that has no representation in a numeric type like integer.

@a_horse_with_no_name: To be precise, '0' is a string constant to PostgreSQL that can be cast to integer just as well as it can be cast to text. Only text is the default for string constants. Consider this demo:

CREATE TEMP TABLE t (i int);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES ('2');
INSERT INTO t VALUES ('3'::int);
INSERT INTO t VALUES (4::bigint);
INSERT INTO t VALUES (5::numeric);
INSERT INTO t VALUES (6);

UPDATE t SET i = '0' WHERE i = '6';
SELECT * FROM t;

-> sqlfiddle

share|improve this answer
 
The SQL standard calls 'foobar' a character string literal :) I tend to avoid the name "string" in combination with SQL as it is not a regular datatype. And I strongly advice against using implicit datatype converstion. That is simply calling for trouble. Better to always use the proper literal (constant) syntax. –  a_horse_with_no_name Jan 7 at 20:22
 
I'm actually surprised that i = '0' works. I thought those implicit casts had been removed with 8.3 –  a_horse_with_no_name Jan 7 at 20:27
 
@a_horse_with_no_name: I think what has been removed with 8.3 is automatic coercion of a numeric constant to a character type. (0 -> '0'::text). But a generic string constant ('0') can be coerced to any type. –  Erwin Brandstetter Jan 7 at 20:40
 
@a_horse_with_no_name: I think "string" is just the right term for a string constant like '0', because "string" is not a regular data type - since the literal has not been cast to any data type yet. –  Erwin Brandstetter Jan 7 at 20:49
1  
@user973238: You can use the function to_number() to convert a string to a numeric type, independent of locale. –  Erwin Brandstetter Jan 7 at 20:59
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.