Member Avatar for Ycefire

Hello,
I'm having problem with a query which is based on fixed-part and all I do is that I'm modifying part after WHERE something.value='... .
This whole text is a string which I need to be connected to my query.
'113') AND (reklamace.kod_reklamace = '932' '

Error message:
Conversion failed when converting the nvarchar value ''113').......' to data type int.

Could any of you help me with this query? Thank you

PS: I forgot to write that its a MS SQL 2008 Express server

Edited by Ycefire because: n/a
  • 2 Contributors
  • 5 Replies
  • 108 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by Ycefire
Member Avatar for pritaeas

From the part you posted you may have too many single quotes. Note that an integer column does not need to have single quotes around the value. Can't say if there is anything else, unless you post your entire query.

Member Avatar for Ycefire

Heres the entire code: (with ASP.NET)

Label called lblDotaz is changed by code, and query is completed by its value "text"

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DbConnStr %>" 
        SelectCommand="SELECT reklamace.kod_reklamace, reklamace.cislo_rekl_zakazky, reklamace.kod_vyrobku, vyrobky.nazev, reklamace.stav, reklamace.reklamovane_mnozstvi, reklamace.jednotka, reklamace.pr, reklamace.tp, reklamace.tot, reklamace.r, reklamace.datum_pr_reklamace, reklamace.datum_pr_vzor, reklamace.no_odeslano_dne, reklamace.datum_uz_reklamace FROM reklamace INNER JOIN vyrobky ON vyrobky.kod_vyrobku = reklamace.kod_vyrobku WHERE (reklamace.cislo_rekl_zakazky = @cislo_rekl_zakazky)">
        <SelectParameters>
            <asp:ControlParameter ControlID="lblDotaz" Name="cislo_rekl_zakazky" 
                PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

Heres the button which causes Searching

Protected 
Sub cmdVyhledat_Click() Handles cmdVyhledat.Click
        Dim retezec As String
        If (txtCisloReklamace.Text <> "") And (txtNazevVyrobku.Text <> "") Then
            lblDotaz.Visible = True
            lblDotaz.Text = "Chyba, není možné vyhledávat pomocí spojeného výrazu"
        ElseIf (txtCisloReklamace.Text <> "") And (txtNazevVyrobku.Text = "") Then
            retezec = "'" & Session("jmeno") & "')" & " AND (reklamace.kod_reklamace = '" & txtCisloReklamace.Text & "'"
            'Session.Add("hledani", retezec)
            lblDotaz.Text = retezec

        Else
           ' retezec = Session("jmeno") & " AND (vyrobek.nazev = " & txtNazevVyrobku.Text 
         '   Session.Add("hledani", retezec)

        End If

        'Response.Redirect("Zakaznik.aspx")

        GridView1.DataBind()
    End Sub

So lets break it: Theres a fix part SELECT * FROM table INNER JOIN another_table ON .... WHERE table.something = MY DYNAMIC PART )

When "MY DYNAMIC PART" is equal just to one int number query works fine, but when its more complex its not working with cannot convert nvarchar to int

Edited by Ycefire because: n/a
Member Avatar for pritaeas

Echo the query that is generated before you use it (including the dynamic part). I'm sure it is a quoting issue.

retezec = "'" // why start with a single quote ?
  & Session("jmeno")  // what is in here ?
  & "')" // why a single quote AND a parenthesis ?
  & " AND (reklamace.kod_reklamace = '" 
  & txtCisloReklamace.Text 
  & "'"
Member Avatar for Ycefire 
retezec = "'" // why start with a single quote ?
  & Session("jmeno")  // what is in here ?
  & "')" // why a single quote AND a parenthesis ?
  & " AND (reklamace.kod_reklamace = '" 
  & txtCisloReklamace.Text 
  & "'"

AD start with single quote: sorry that was my fault, I was so down with code not working I've tried all I could imagine.

Session("jmeno") contains integer number

Single quote and then parenthesis same as start with a single quote, my fault

The problem is that query won't even be created.
This is what I get as an error: Conversion failed when converting the nvarchar value '113) AND (reklamace.kod_reklamace = 932 ' to data type int.

Member Avatar for Ycefire

I'd like to ask mods to delete this thread, since I discovered mine query is correct, only ASP.NET can't handle mine data type conversions (still don't know why)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.