regarding an sql query error

Question

here is my admin.aspx.vb

Imports System.Data.SqlClient

Partial Class Admin
    Inherits System.Web.UI.Page
    Dim conn As New SqlConnection("Data Source=CHIRAG-PC;Initial Catalog=car;Integrated Security=True")
    Dim cmd As SqlCommand
    Dim drd As SqlDataReader
    Dim adp As SqlDataAdapter
    Dim y As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
        Dim x As Integer


        x = GridView1.SelectedIndex
        y = GridView1.Rows(x).Cells(1).Text





    End Sub

   Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim str As String
        str = "update carHeader set cartype='" + car.Text.ToString() + "',imagefile='" + img.Text + "',capacity=" + cap.Text + "where  id=" + Convert.ToDouble(y)
        conn.Open()
        cmd = New SqlCommand(str, conn)
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub
End Class

and its admin.aspx

<%@ Page Title="" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Admin.aspx.vb" Inherits="Admin" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" Runat="Server">
   <br />
   <br />
</asp:Content>


<asp:Content ID="Content2" runat="server" contentplaceholderid="MainContent">
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="Data Source=CHIRAG-PC;Initial Catalog=car;Integrated Security=True" 
        ProviderName="System.Data.SqlClient" 
        SelectCommand="SELECT * FROM [carHeader] ORDER BY [id]"></asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataSourceID="SqlDataSource1" Height="149px" Width="267px">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
            <asp:BoundField DataField="id" HeaderText="id" 
                SortExpression="id" />
            <asp:BoundField DataField="cartype" HeaderText="cartype" 
                SortExpression="cartype" />
            <asp:BoundField DataField="imagefile" HeaderText="imagefile" 
                SortExpression="imagefile" />
            <asp:BoundField DataField="capacity" HeaderText="capacity" 
                SortExpression="capacity" />
        </Columns>
    </asp:GridView>
    <asp:Panel ID="Panel1" runat="server">
        <asp:Label ID="Label1" runat="server" Text="Image file"></asp:Label>
        <asp:TextBox ID="img" runat="server"></asp:TextBox>

        <asp:Label ID="Label2" runat="server" Text="Car Type"></asp:Label>

        <asp:TextBox ID="car" runat="server"></asp:TextBox>
        <asp:Label ID="Label3" runat="server" Text="capacity"></asp:Label>
        <asp:TextBox ID="cap" runat="server"></asp:TextBox>


        <asp:Button ID="Button1" runat="server" Text="Save" />


    </asp:Panel>
</asp:Content>

getting an error in sql query regarding its update statement

and table contents are of carHeader: cartype varchar ,id int,imagefile varchar,capacity int

its givin me error conversion from update set cartype=....to double type is not valid — user734303
– user734303, Commented May 2, 2011 at 12:24

David · Accepted Answer · 2011-05-02 12:27:28Z

1

... + cap.Text + "where ...

Notice the lack of a space before where. It's probably producing a statement like:

... field = valuewhere ...

This would break the SQL.

Additionally, it's bad practice to use string concatenation in SQL statements like this, both from a security and performance point of view. You'll want to look into using parameterized queries.

Edit: Based on your comment here:

its givin me error conversion from update set cartype=....to double type is not valid

It sounds like the error is referring to this part of the query:

set cartype='" + car.Text.ToString() + "'

What type is cartype? According to the error message, it's a double. But according to your query, you're trying to set it to a string value (by wrapping it in single quotes). If it's a double then it needs to be a numeric value, not a string.

edited May 2, 2011 at 12:27

answered May 2, 2011 at 12:18

David

221k42 gold badges247 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

@user734303: Can you debug into the code and determine specifically what's throwing the error? For example, you're also (in code, not in the database) trying to directly convert the variable y to a double. What value is in y when the error is thrown?

Damien_The_Unbeliever Over a year ago

I'd say your first (pre-edit) answer was the one - it's definitely running the capacity and the keyword "where" together, and as we have to guess the columns in the database, it's the one most likely to be a numeric.

Collectives™ on Stack Overflow

regarding an sql query error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related