Update query

cilla · November 9th, 2004, 03:09 AM

Hi, I have a update query thats works fine when I use characters value for POSTCODE. But in this query I wont to use a Integer value. The user update the value (POSTCODE) in a input box. The error message is like this

A value is not compatible with the data type of its assignment target. Target name is "POSTCODE". SQLSTATE=42821

A bit of my code is like this

vPOSTCODEn= "POSTCODE" & myRs.Fields("SAID")
vPOSTCODE= CInt(request.form("vPOSTCODEn"))

SQL="UPDATE TEST.Supplier SET SALECOMPANY= '" & vSALECOMPANY &"', SALECOMPANYNUMBER='" & vSALECOMPANYNUMBER & "', ADRESS='" & vADRESS & "', POSTCODE = " & vPOSTCODE & " , CITY = '" & vCITY & "',COUNTRY = '" & vCOUNTRY & "' WHERE SAID=" & vSAID

Jonax · November 9th, 2004, 03:21 AM

If what you are trying to do is to Request the contents of a dynamically named form-field, then you might want to remove the quotes in the second line, like this:

Code:

vPOSTCODE= CInt(request.form(vPOSTCODEn))

cilla · November 9th, 2004, 03:29 AM

Sorry, I wrote wrong in my last topic.

The code IÂ´am using is
vPOSTCODE= CInt(request.form(vPOSTCODEn))

I think that IÂ´am doing wrong when I put it in the SQL query? A integer dont need any ' ' so I took them out.

Jonax · November 9th, 2004, 03:36 AM

Try checking what the SQL-string contains before executing it...

Code:

[...]
SQL="UPDATE TEST.Supplier SET SALECOMPANY= '" & vSALECOMPANY &"', SALECOMPANYNUMBER='" & vSALECOMPANYNUMBER & "', ADRESS='" & vADRESS & "', POSTCODE = " & vPOSTCODE & " , CITY = '" & vCITY & "',COUNTRY = '" & vCOUNTRY & "' WHERE SAID=" & vSAID 

Response.Write SQL
Response.End
[...]

Jonax · November 9th, 2004, 03:57 AM

[quote]
...when I use characters value for POSTCODE...
[quote]

If the datatype of the field in the db is a string-type - you should pass it numeric postcodes as sting as well.
Try removing the cint() and (re)inserting the ''.

cilla · November 9th, 2004, 04:04 AM

I tried to write it out but still got the error message and nothing writes out on the screen.

In the error message I get this values
SAID1=1&SALECOMPANY1=a&SALECOMPANYNUMBER1=a&ADRESS 1=a&POSTCODE1=1&CITY1=a&COUNTRY1=a

I just put an a in all boxes that wants a charater and 1 for POSTCODE that wants a Interger to test. So the value comes with me but itÂ´s the wrong datatype.

Error message
A value is not compatible with the data type of its assignment target. Target name is "POSTCODE".

cilla · November 9th, 2004, 04:10 AM

I got the same error message when I remove CInt() and put back the ''

gokul_blr · November 9th, 2004, 06:10 AM

Can you check once with the table fields in the order from top whether you have given it in sequence.

or any mismatch between the query statement fields and table structure (position).

Gokulan Ethiraj

happygv · November 9th, 2004, 06:32 AM

Cilla,

Quote:

quote: I tried to write it out but still got the error message and nothing writes out on the screen.

That shouldn't be returning error unless you try to execute that on SQL server from your asp page. Did you try that exactly as Jonax has suggested? As Response.End stops executing the page at that point, you should be able to see how the UPDATE statement is constructed and also the values of each being passed to it.

It is better you post information on what is the datatype of POSTCODE in the table and what is the value you are passing for that.

This should let us understand better on what you are missing there.

Cheers!

_________________________
- Vijay G
Strive for Perfection

cilla · November 9th, 2004, 07:38 AM

Hi, yes I did As Jonax wrote
The datatype of POSTCODE in the table is Integer.
I have just for a test, made it to Char in the database and then
the code is working fine. But no I have put it back to an Integer.

the part of my code thats include the Case Update.

Code:

Case "Uppdatera"

        Set myConn = Server.CreateObject("ADODB.Connection")
        myConn.Open "DSN=CPRCT;UID=" & session("anvid") & ";PWD=" & session("password") & ""

        Set myRs = Server.CreateObject("ADODB.Recordset")
        SQL = "SELECT * FROM TEST.Supplier"

         myRs.Open SQL,myConn,3,3 


    if len(Request.Form("btnval")) > 0 then
         While not myRs.EOF

         vSAIDn= "SAID" & myRs.Fields("SAID")
         vSAID= CInt(request.form(vSAIDn))

         vSALECOMPANYn= "SALECOMPANY" & myRs.Fields("SAID")
         vSALECOMPANY= CStr(request.form(vSALECOMPANYn))

         vSALECOMPANYNUMBERn="SALECOMPANYNUMBER" & myRs.Fields("SAID")
         vSALECOMPANYNUMBER= CStr(request.form(vSALECOMPANYNUMBERn))

         vADRESSn= "ADRESS" & myRs.Fields("SAID")
         vADRESS= CStr(request.form(vADRESSn))

         vCITYn="CITY" & myRs.Fields("SAID")
         vCITY= CStr(request.form(vCITYn))

         vCOUNTRYn="COUNTRY" & myRs.Fields("SAID")
         vCOUNTRY= CStr(request.form(vCOUNTRYn))

         vPOSTCODEn="POSTCODE" & myRs.Fields("SAID")
         vPOSTCODE= CInt(request.form(vPOSTCODEn))

        SQL="UPDATE TEST.Supplier SET SALECOMPANY= '" & vSALECOMPANY &"', SALECOMPANYNUMBER='" & vSALECOMPANYNUMBER & "', ADRESS='" & vADRESS & "', POSTCODE = "& vPOSTCODE &", CITY = '" & vCITY & "',COUNTRY = '" & vCOUNTRY & "' WHERE SAID=" & vSAID 
        Response.Write SQL
        Response.End

        myConn.Execute(SQL)
        myRs.MoveNext

         wend
    End if

             myRs.Close
             SQL = "SELECT * FROM TEST.Supplier"
             myRs.Open SQL,myConn,3,3



        %>
        <Form Method="Post" Action="Supplier.asp">
        <Table><TR><TD><B>LeverantÃ¶rs id</b></TD><TD><b>LeverantÃ¶rs namn</b></TD><TD><b>LeverantÃ¶rs nummer</b></TD>
        <TD><b>Adress</b></TD><TD><b>Postnummer</b></TD><TD><b>Stad</b></TD><TD><b>Land</b></TD>
        <TD><b>Ta bort?</B></TD></TR>
        <%
         While not myRs.EOF
            %>
            <TR><TD><INPUT TYPE="text" name="SAID<%=myRs.Fields("SAID") %>" value="<%=myRs.Fields("SAID")%>" style="background:white;color:BLACK;font-family:arial, verdana; font-size:15px; height:25px;font-color:#ffff00;"
                                size="20"></TD>
            <TD><INPUT TYPE="text" name="SALECOMPANY<%=myRs.Fields("SAID") %>" value ="<%=myRs.Fields("SALECOMPANY")%>" style="background:white;color:BLACK;font-family:arial, verdana; height:25px;font-size:15px; font-color:#ffff00;"
                                size="20"></TD>
            <TD><INPUT TYPE="text" name="SALECOMPANYNUMBER<%=myRs.Fields("SAID") %>" value = "<%=myRs.Fields("SALECOMPANYNUMBER")%>" style="background:white;color:BLACK;font-family:arial, verdana; font-size:15px; height:25px;font-color:#ffff00;"
                                size="20"></B></TD>
            <TD><INPUT TYPE="text" name="ADRESS<%=myRs.Fields("SAID") %>" value="<%=myRs.Fields("ADRESS")%>" style="background:white;color:BLACK;font-family:arial, verdana; font-size:15px; height:25px;font-color:#ffff00;"
                                size="20"></TD>
            <TD><INPUT TYPE="text" name="POSTCODE<%=myRs.Fields("SAID") %>" value ="<%=myRs.Fields("POSTCODE")%>" style="background:white;color:BLACK;font-family:arial, verdana; height:25px;font-size:15px; font-color:#ffff00;"
                                size="20"></TD>
            <TD><INPUT TYPE="text" name="CITY<%=myRs.Fields("SAID") %>" value = "<%=myRs.Fields("CITY")%>" style="background:white;color:BLACK;font-family:arial, verdana; font-size:15px; height:25px;font-color:#ffff00;"
                                size="20"></B></TD>
            <TD><INPUT TYPE="text" name="COUNTRY<%=myRs.Fields("SAID") %>" value ="<%=myRs.Fields("COUNTRY")%>" style="background:white;color:BLACK;font-family:arial, verdana; height:25px;font-size:15px; font-color:#ffff00;"
                                size="20"></TD>
            <TD><INPUT TYPE="checkbox" name="check" value ="<%=myRs.Fields("SAID")%>" style="background:white;color:BLACK;font-family:arial, verdana; font-size:10px; font-color:#ffff00;"
                                size="5"></TD></TR>
            <%
        myRs.MoveNext

       wend
        %>
        <TR><TD COLSPAN=3><INPUT TYPE="submit" name="btnval" value="Uppdatera" style="font-family:arial, verdana; font-size:13px; font-color:black;width=70px;color=BLACK;height:25px;background-color: #D3D3D3">
        <INPUT TYPE="submit" name="btnval" value="Ta bort rad" style="font-family:arial, verdana; font-size:13px; font-color:black;width=70px;color=BLACK;height:25px;background-color: #D3D3D3"></TD></TR></Form></Table>
        <%

        Set myRs = Nothing
        Set myConn = Nothing