CODEWITHSUNDEEP
sqlasp.netsql-server
Faisal
Faisal

Reputation: 179

How to solve this error of conversion failed?

I am getting this error

Conversion failed when converting the varchar value 'Thowheed' to data type int.

I visited and checked in stack overflow, but I couldn't find answer

I added values in drop down list, once I select and click ok button it has to show me the record from database.

This is my code

string cs = ConfigurationManager.ConnectionStrings["Nibrass_DBConnectionString"].ConnectionString;

using (SqlConnection con = new SqlConnection(cs))
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;

    cmd.CommandText = "SELECT Date as Date,Credit, Debit_From as Received_From, Credit_Amount as amount, Reason From Transaction_Credit where Credit = " + DropDownListSelectAccount.SelectedValue+ " or cast(Debit_From as varchar) = " + DropDownListSelectAccount.SelectedValue + " ORDER BY  Date DESC";

    con.Open();
    SqlDataReader rd = cmd.ExecuteReader();

    while(rd.Read())
    {
        DateTime dt = Convert.ToDateTime(rd[0]);
        string receivedFrom = rd[1].ToString();
        int amount = Convert.ToInt32(rd[2]);
    }

    con.Close();
}

My database table definition is

CREATE TABLE [dbo].[Transaction_Credit] 
(
    [Date]          DATE          NOT NULL,
    [Credit]        VARCHAR (50)  NOT NULL,
    [Debit_From]    VARCHAR (50)  NOT NULL,
    [Reason]        VARCHAR (100) NULL,
    [Credit_Amount] INT           NOT NULL,
    [Balance]       INT           NULL
);

Upvotes: 0

Views: 85

Answers (2)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

You should not be concatenating your string. This is a bad practice and your code becomes vulnerable to SQL Injection.

You should use parameters instead:

cmd.CommandText = @"
    SELECT Date
      , Credit
      , Debit_From AS Received_From
      , Credit_Amount AS Amount
      , Reason
    FROM Transaction_Credit
    WHERE Credit = @DropDownListSelectAccount
      OR Debit_From = @DropDownListSelectAccount
    ORDER BY Date DESC";

cmd.Parameters.Add("@DropDownListSelectAccount", SqlDbType.VarChar, 50). Value) = DropDownListSelectAccount.SelectedValue;

By the way, you don't need to cast Debit_From as a varchar, since it's already like that in your database.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is your query:

select Date as Date, Credit, Debit_From as Received_From,
       Credit_Amount as amount, Reason
from Transaction_Credit 
where Credit = " + DropDownListSelectAccount.SelectedValue+ " or
      cast(Debit_From as varchar) = " + DropDownListSelectAccount.SelectedValue + "
order by Date DESC;

The code reading this is:

int amount = Convert.ToInt32(rd[2]);

But the 3rd column is Received_From, not amount. That is probably your problem.

Also, cast(Debit_From as varchar) is very dangerous. When you don't include a length for varchar(), SQL Server inserts a length depending on the context. Just don't do a conversion where you don't need one.

Upvotes: 1

Related Questions