Type convection error

Asked

Viewed 111 times

0

Trying to record in a sql table an image, its name and description and some foreign keys to allow me to search later.

On execution I get error of string conversion for Int 32. But all fields in the table are correctly defined. What am I doing wrong?

    private void BotaoGravar_Click(object sender, EventArgs e)
    {
        string sql = "select * from Banco_Imagens.tb_imagem";

        MemoryStream memory = new MemoryStream();

        // Verificar se alguma imagem foi seleccionada

        if (bmp == null)
        {
            MessageBox.Show("Por favor seleccione uma imagem, antes de gravar o registo.");
        }
        else
        {
            bmp.Save(memory, ImageFormat.Bmp);
        }

        byte[] foto = memory.ToArray();

        SqlCommand comando = new SqlCommand("insert into tb_imagem (imagem,nome_imagem,descr_imagem,iddiag,idlesao,idlesao1,idlesao2,idlesao3,idlesao4,ND) values (@imagem,@nome_imagem,@descr_imagem,@iddiag,@idlesao,@idlesao1,@idlesao2,@idlesao3,@idlesao4,@ND)", con);
        SqlCommand cmdInteiro = new SqlCommand(sql, con);

        SqlParameter imagem = new SqlParameter("@imagem", SqlDbType.VarBinary);
        SqlParameter nome_imagem = new SqlParameter("@nome_imagem", SqlDbType.VarChar);
        SqlParameter descr_imagem = new SqlParameter("@descr_imagem", SqlDbType.VarChar);
        SqlParameter ND = new SqlParameter("@ND", SqlDbType.Int);
        SqlParameter iddiag = new SqlParameter("@iddiag", SqlDbType.Int);
        SqlParameter idlesao = new SqlParameter("@idlesao", SqlDbType.Int);
        SqlParameter idlesao1 = new SqlParameter("@idlesao1", SqlDbType.Int);
        SqlParameter idlesao2 = new SqlParameter("@idlesao2", SqlDbType.Int);
        SqlParameter idlesao3 = new SqlParameter("@idlesao3", SqlDbType.Int);
        SqlParameter idlesao4 = new SqlParameter("@idlesao4", SqlDbType.Int);

        cmdInteiro.CommandType = CommandType.StoredProcedure;

        comando.Parameters.AddWithValue("@imagem", SqlDbType.VarBinary);
        comando.Parameters["@imagem"].Value = foto;
        comando.Parameters.AddWithValue("@nome_imagem", SqlDbType.VarChar);
        comando.Parameters["@nome_imagem"].Value = TextBoxNomeImagem.Text;
        comando.Parameters.AddWithValue("@descr_imagem", SqlDbType.VarChar);
        comando.Parameters["@descr_imagem"].Value = TextBoxDescrImagem.Text;
        comando.Parameters.AddWithValue ("@iddiag", SqlDbType.Int);
        comando.Parameters["@iddiag"].Value = TextBoxiddiag.Text;
        comando.Parameters.AddWithValue ("@idlesao", SqlDbType.Int);
        comando.Parameters["@idlesao"].Value = TextBoxidlesao.Text;
        comando.Parameters.AddWithValue ("@idlesao1", SqlDbType.Int);
        comando.Parameters["@idlesao1"].Value = TextBoxidlesao1.Text;
        comando.Parameters.AddWithValue ("@idlesao2", SqlDbType.Int);
        comando.Parameters["@idlesao2"].Value = TextBoxidlesao2.Text;
        comando.Parameters.AddWithValue ("@idlesao3", SqlDbType.Int);
        comando.Parameters["@idlesao3"].Value = TextBoxidlesao3.Text;
        comando.Parameters.AddWithValue ("@idlesao4", SqlDbType.Int);
        comando.Parameters["@idlesao4"].Value = TextBoxidlesao4.Text;
        cmdInteiro.Parameters.AddWithValue("@ND", SqlDbType.Int);
        cmdInteiro.Parameters["@ND"].Value = TextBoxND.Text;
        //comando.Parameters.AddWithValue ("@idtipolesao", SqlDbType.Int);
        //cmd.Parameters["@idtipolesao"].Value = TextBoxidtipolesao;

        imagem.Value = foto;
        nome_imagem.Value = TextBoxNomeImagem.Text;
        descr_imagem.Value = TextBoxDescrImagem.Text;
        iddiag.Value = (TextBoxiddiag.Text);
        idlesao.Value = (TextBoxidlesao.Text);
        idlesao1.Value = (TextBoxidlesao1.Text);
        idlesao2.Value = (TextBoxidlesao2.Text);
        idlesao3.Value = (TextBoxidlesao3.Text);
        idlesao4.Value = (TextBoxidlesao4.Text);
        ND.Value = (TextBoxND.Text);
        //idtipolesao.Value = TextBoxidtipolesao.Text;


        // Verificar se o Número de Doente só contém inteiros

        if (Regex.IsMatch(TextBoxND.Text, @"^\D*$"))
        {
            //Show message and clear input.  
            MessageBox.Show("Por favor introduza só algarismos.");
            TextBoxND.Clear();
        }
        else
        {
            comando.Parameters.Add (imagem);
            comando.Parameters.Add(nome_imagem);
            comando.Parameters.Add(descr_imagem);
            comando.Parameters.Add(iddiag);
            comando.Parameters.Add(idlesao);
            comando.Parameters.Add(idlesao1);
            comando.Parameters.Add(idlesao2);
            comando.Parameters.Add(idlesao3);
            comando.Parameters.Add(idlesao4);
            comando.Parameters.Add(ND);
            //comando.Parameters.Add(idtipolesao);
        }
        try
        {
            con.Open();
            comando.ExecuteNonQuery();
            MessageBox.Show("Imagem Gravada!");

            PictureBox1.Image = null;
            TextBoxNomeImagem.Text = "";
            TextBoxDescrImagem.Text = "";
            TextBoxiddiag.Text = "";
            TextBoxDiagnostico.Text = "";
            TextBoxLesao.Text = "";
            TextBoxLesao1.Text = "";
            TextBoxLesao2.Text = "";
            TextBoxLesao3.Text = "";
            TextBoxLesao4.Text = "";
            TextBoxND.Text = "";
            //TextBoxTipoLesao.Text = "";
            //TextBoxiddiag.Text = "";
            //TextBoxidlesao.Text = "";
            //TextBoxidlesao1.Text = "";
            //TextBoxidlesao2.Text = "";
            //TextBoxidlesao3.Text = "";
            //TextBoxidlesao4.Text = "";
            ////TextBoxidtipolesao.Text = "";


        }
        catch (Exception E)
        {
            MessageBox.Show(E.Message);
        }
        finally
        {
            con.Close();
        }
    }
c# sql type-conversion

2 answers

0


using System;
using System.Drawing;
using System.Windows.Forms;
using System.IO;
using System.Drawing.Imaging;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;


namespace Banco_de_Imagens_Beatriz
{
    public partial class EcranSeleccaoImagens : Form
    {
        Bitmap bmp;

        SqlConnection con = new SqlConnection("Data Source = localhost\\SQLEXPRESS;Initial Catalog = Banco_Imagens; Persist Security Info=True;User ID = sa; Password=12QWaszx");


        public EcranSeleccaoImagens()
        {
            InitializeComponent();
            TextBoxiddiag.ReadOnly = true;
            TextBoxidlesao.ReadOnly = true;
            TextBoxidlesao1.ReadOnly = true;
            TextBoxidlesao2.ReadOnly = true;
            TextBoxidlesao3.ReadOnly = true;
            TextBoxidlesao4.ReadOnly = true;
            //TextBoxidtipolesao.ReadOnly = true;
            TextBoxDiagnostico.ReadOnly = true;
            TextBoxLesao.ReadOnly = true;
            //TextBoxTipoLesao.ReadOnly =true;
            TextBoxiddiag.BackColor = SystemColors.Window;
            TextBoxidlesao.BackColor = SystemColors.Window;
            TextBoxidlesao1.BackColor = SystemColors.Window;
            TextBoxidlesao2.BackColor = SystemColors.Window;
            TextBoxidlesao3.BackColor = SystemColors.Window;
            TextBoxidlesao4.BackColor = SystemColors.Window;
            //TextBoxidtipolesao.BackColor = SystemColors.Window;
            TextBoxDiagnostico.BackColor = SystemColors.Window;
            TextBoxLesao.BackColor = SystemColors.Window;
            //TextBoxTipoLesao.BackColor = SystemColors.Window;
        }

        private void ComboBox3_SelectedDiagnosticoChanged(object sender, EventArgs e)
        {

        }
        private void ComboBox1_SelectedLesaoChanged(object sender, EventArgs e)
        {

        }

        private void TextBox3_TextChanged(object sender, EventArgs e)
        {

        }

        private void ComboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void PictureBoxSeleccionar_Imagem_Click(object sender, EventArgs e)
        {

        }

        private void TextBox2_TextChanged(object sender, EventArgs e)
        {

        }

        //TextBox Diagnostico
        private void BotaoSeleccDiag_Click(object sender, EventArgs e)
        {
            EcranDiagnostico frm = new EcranDiagnostico();
            frm.Show();
            frm.VisibleChanged += FormVisibleChangedD;
        }
        private void FormVisibleChangedD(object sender, EventArgs e)
        {
            EcranDiagnostico frm = (EcranDiagnostico)sender;
            if (!frm.Visible)
            {
                TextBoxDiagnostico.Text = frm.ReturnText;
                TextBoxiddiag.Text = frm.ReturnId.ToString();
                frm.Dispose();
            }
        }

        private void BotaoSeleccionar_Click(object sender, EventArgs e)
        {
            OpenFileDialog open = new OpenFileDialog();

            if (open.ShowDialog() == DialogResult.OK)
            {
                string nome = open.FileName;
                bmp = new Bitmap(nome);
                PictureBox1.Image = bmp;
            }
        }

        private void BotaoSair_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void BotaoGravar_Click(object sender, EventArgs e)
        {
            string sql = "select * from Banco_Imagens.tb_imagem";

            MemoryStream memory = new MemoryStream();

            // Verificar se alguma imagem foi seleccionada

            if (bmp == null)
            {
                MessageBox.Show("Por favor seleccione uma imagem, antes de gravar o registo.");
            }
            else
            {
                bmp.Save(memory, ImageFormat.Bmp);
            }

            byte[] foto = memory.ToArray();

            SqlCommand comando = new SqlCommand("insert into tb_imagem (imagem,nome_imagem,descr_imagem,iddiag,idlesao,idlesao1,idlesao2,idlesao3,idlesao4,ND) values (@imagem,@nome_imagem,@descr_imagem,@iddiag,@idlesao,@idlesao1,@idlesao2,@idlesao3,@idlesao4,@ND)", con);
            SqlCommand cmdInteiro = new SqlCommand(sql, con);

            SqlParameter imagem = new SqlParameter("@imagem", SqlDbType.VarBinary);
            SqlParameter nome_imagem = new SqlParameter("@nome_imagem", SqlDbType.VarChar);
            SqlParameter descr_imagem = new SqlParameter("@descr_imagem", SqlDbType.VarChar);
            SqlParameter ND = new SqlParameter("@ND", SqlDbType.Int);
            SqlParameter iddiag = new SqlParameter("@iddiag", SqlDbType.Int);
            SqlParameter idlesao = new SqlParameter("@idlesao", SqlDbType.Int);
            SqlParameter idlesao1 = new SqlParameter("@idlesao1", SqlDbType.Int);
            SqlParameter idlesao2 = new SqlParameter("@idlesao2", SqlDbType.Int);
            SqlParameter idlesao3 = new SqlParameter("@idlesao3", SqlDbType.Int);
            SqlParameter idlesao4 = new SqlParameter("@idlesao4", SqlDbType.Int);

            cmdInteiro.CommandType = CommandType.StoredProcedure;

            comando.Parameters.AddWithValue("@imagem", SqlDbType.VarBinary);
            comando.Parameters["@imagem"].Value = foto;
            comando.Parameters.AddWithValue("@nome_imagem", SqlDbType.VarChar);
            comando.Parameters["@nome_imagem"].Value = TextBoxNomeImagem.Text;
            comando.Parameters.AddWithValue("@descr_imagem", SqlDbType.VarChar);
            comando.Parameters["@descr_imagem"].Value = TextBoxDescrImagem.Text;
            comando.Parameters.AddWithValue ("@iddiag", SqlDbType.Int);
            comando.Parameters["@iddiag"].Value = TextBoxiddiag.Text;
            comando.Parameters.AddWithValue ("@idlesao", SqlDbType.Int);
            comando.Parameters["@idlesao"].Value = TextBoxidlesao.Text;
            comando.Parameters.AddWithValue ("@idlesao1", SqlDbType.Int);
            comando.Parameters["@idlesao1"].Value = TextBoxidlesao1.Text;
            comando.Parameters.AddWithValue ("@idlesao2", SqlDbType.Int);
            comando.Parameters["@idlesao2"].Value = TextBoxidlesao2.Text;
            comando.Parameters.AddWithValue ("@idlesao3", SqlDbType.Int);
            comando.Parameters["@idlesao3"].Value = TextBoxidlesao3.Text;
            comando.Parameters.AddWithValue ("@idlesao4", SqlDbType.Int);
            comando.Parameters["@idlesao4"].Value = TextBoxidlesao4.Text;
            comando.Parameters.AddWithValue("@ND", SqlDbType.Int);
            comando.Parameters["@ND"].Value = TextBoxND.Text;
            //cmdInteiro.Parameters.AddWithValue("@ND", SqlDbType.Int);
            //cmdInteiro.Parameters["@ND"].Value = TextBoxND.Text;
            //comando.Parameters.AddWithValue ("@idtipolesao", SqlDbType.Int);
            //cmd.Parameters["@idtipolesao"].Value = TextBoxidtipolesao;

            imagem.Value = foto;
            nome_imagem.Value = TextBoxNomeImagem.Text;
            descr_imagem.Value = TextBoxDescrImagem.Text;
            iddiag.Value = Int32.Parse(TextBoxiddiag.Text);
            idlesao.Value = Int32.Parse(TextBoxidlesao.Text);
            idlesao1.Value = Int32.Parse(TextBoxidlesao1.Text);
            idlesao2.Value = Int32.Parse(TextBoxidlesao2.Text);
            idlesao3.Value = Int32.Parse(TextBoxidlesao3.Text);
            idlesao4.Value = Int32.Parse(TextBoxidlesao4.Text);
            ND.Value = Int32.Parse(TextBoxND.Text);
            //idtipolesao.Value = TextBoxidtipolesao.Text;


            // Verificar se o Número de Doente só contém inteiros

            if (Regex.IsMatch(TextBoxND.Text, @"^\D*$"))
            {
                //Show message and clear input.  
                MessageBox.Show("Por favor introduza só algarismos.");
                TextBoxND.Clear();
            }
            else
            {
                comando.Parameters.Add (imagem);
                comando.Parameters.Add(nome_imagem);
                comando.Parameters.Add(descr_imagem);
                comando.Parameters.Add(iddiag);
                comando.Parameters.Add(idlesao);
                comando.Parameters.Add(idlesao1);
                comando.Parameters.Add(idlesao2);
                comando.Parameters.Add(idlesao3);
                comando.Parameters.Add(idlesao4);
                comando.Parameters.Add(ND);
                //comando.Parameters.Add(idtipolesao);
            }
            try
            {
                con.Open();
                comando.ExecuteNonQuery();
                MessageBox.Show("Imagem Gravada!");

                PictureBox1.Image = null;
                TextBoxNomeImagem.Text = "";
                TextBoxDescrImagem.Text = "";
                TextBoxiddiag.Text = "";
                TextBoxDiagnostico.Text = "";
                TextBoxLesao.Text = "";
                TextBoxLesao1.Text = "";
                TextBoxLesao2.Text = "";
                TextBoxLesao3.Text = "";
                TextBoxLesao4.Text = "";
                TextBoxND.Text = "";
                //TextBoxTipoLesao.Text = "";
                //TextBoxiddiag.Text = "";
                //TextBoxidlesao.Text = "";
                //TextBoxidlesao1.Text = "";
                //TextBoxidlesao2.Text = "";
                //TextBoxidlesao3.Text = "";
                //TextBoxidlesao4.Text = "";
                ////TextBoxidtipolesao.Text = "";


            }
            catch (Exception E)
            {
                MessageBox.Show(E.Message);
            }
            finally
            {
                con.Close();
            }
        }
        private void BotaoApagar_Click(object sender, EventArgs e)
        {
            SqlCommand comando = new SqlCommand("DELETE * FROM tb_imagem", con);
            SqlDataReader myReader;

            try
            {
                con.Open();
                myReader = comando.ExecuteReader();
            }
            catch (SqlException E)
            {
                //Log exception
                //Display Error message
                MessageBox.Show(E.Message);
            }
        }
        private void TextBoxDiagImagem_TextChanged(object sender, EventArgs e)
        {

        }

        private void TextBoxFKdiag_TextChanged(object sender, EventArgs e)
        {

        }

        private void TextBoxFKlesao_TextChanged(object sender, EventArgs e)
        {

        }

        private void TextBoxFKtipolesao_TextChanged(object sender, EventArgs e)
        {

        }

        private void TextBoxNomeImagem_TextChanged(object sender, EventArgs e)
        {

        }

        private void EcranSeleccaoImagens_Load(object sender, EventArgs e)
        {

        }

        private void Diagnostico_Text_Box_TextChanged(object sender, EventArgs e)
        {

        }

        private void Lesao_Text_Box_TextChanged(object sender, EventArgs e)
        {

        }

        private void Tipo_Lesao_Text_Box_TextChanged(object sender, EventArgs e)
        {

        }

        private void TextBoxND_TextChanged(object sender, EventArgs e)
        {

        }

        // TextBox Lesao
        private void BotaoSeleccLesao_Click(object sender, EventArgs e)
        {
            EcranLesao frm = new EcranLesao();
            frm.Show();
            frm.VisibleChanged += FormVisibleChangedL;
        }
        private void FormVisibleChangedL(object sender, EventArgs e)
        {
            EcranLesao frm = (EcranLesao)sender;
            if (!frm.Visible)
            {
                TextBoxLesao.Text = frm.ReturnText;
                TextBoxidlesao.Text = frm.ReturnId.ToString();
                frm.Dispose();
            }
        }
        private void BotaoSeleccLesao1_Click(object sender, EventArgs e)
        {
            EcranLesao frm = new EcranLesao();
            frm.Show();
            frm.VisibleChanged += FormVisibleChangedL1;
        }
        private void FormVisibleChangedL1(object sender, EventArgs e)
        {
            EcranLesao frm = (EcranLesao)sender;
            if (!frm.Visible)
            {
                TextBoxLesao1.Text = frm.ReturnText;
                TextBoxidlesao1.Text = frm.ReturnId.ToString();
                frm.Dispose();
            }
        }

        private void BotaoSeleccLesao2_Click(object sender, EventArgs e)
        {
            EcranLesao frm = new EcranLesao();
            frm.Show();
            frm.VisibleChanged += FormVisibleChangedL2;
        }
        private void FormVisibleChangedL2(object sender, EventArgs e)
        {
            EcranLesao frm = (EcranLesao)sender;
            if (!frm.Visible)
            {
                TextBoxLesao2.Text = frm.ReturnText;
                TextBoxidlesao2.Text = frm.ReturnId.ToString();
                frm.Dispose();
            }
        }
        private void BotaoSeleccLesao3_Click(object sender, EventArgs e)
        {
            EcranLesao frm = new EcranLesao();
            frm.Show();
            frm.VisibleChanged += FormVisibleChangedL3;
        }
        private void FormVisibleChangedL3(object sender, EventArgs e)
        {
            EcranLesao frm = (EcranLesao)sender;
            if (!frm.Visible)
            {
                TextBoxLesao3.Text = frm.ReturnText;
                TextBoxidlesao3.Text = frm.ReturnId.ToString();
                frm.Dispose();
            }
        }
        private void BotaoSeleccLesao4_Click(object sender, EventArgs e)
        {
            EcranLesao frm = new EcranLesao();
            frm.Show();
            frm.VisibleChanged += FormVisibleChangedL4;
        }
        private void FormVisibleChangedL4(object sender, EventArgs e)
        {
            EcranLesao frm = (EcranLesao)sender;
            if (!frm.Visible)
            {
                TextBoxLesao4.Text = frm.ReturnText;
                TextBoxidlesao4.Text = frm.ReturnId.ToString();
                frm.Dispose();
            }
        }
    }
}

0

I think the problem is related to the kind of equalization you make. For example:

command. Parameters["@idlesao4"]. Value = Textboxidlesao4.Text;

If ["@idlesao4"] is type Int the problem may be here.

Try to: command. Parameters["@idlesao4"]. Value = Int32.Parse(Textboxidlesao4.Text);

Take this example:

using(SqlConnection connection = new SqlConnection(_connectionString) 
{
   String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) 
   VALUES (@id,@username,@password, @email)";

using(SqlCommand command = new SqlCommand(query, connection))
{
    command.Parameters.AddWithValue("@id", "abc");
    command.Parameters.AddWithValue("@username", "abc");
    command.Parameters.AddWithValue("@password", "abc");
    command.Parameters.AddWithValue("@email", "abc");

    connection.Open();
    int result = command.ExecuteNonQuery();

    // Check Error
    if(result < 0)
        Console.WriteLine("Error inserting data into Database!");
}

Withdrawn from here

  • After the change, you have given the following error: System.Formatexception: 'Input string with incorrect format.'

  • I’ve been going over your script a little bit, and I think I’m mixing things up. First, you have an Insert command and then you say sqlcommand type is Stored Procedure. Then I see that you are assigning values to the command parameters and last to cmdInteiro. command.Parameters["@idlesao4"]. Value = Textboxidlesao4.Text; cmdInteiro.Parameters.Addwithvalue("@ND", Sqldbtype.Int); cmdInteiro.Parameters["@ND"]. Value = Textboxnd.Text;

  • Thanks for your time, Sandro.

  • I am certainly mixing things up otherwise the "thing" would work. Can you enlighten me how I should then code? I have two problems: 1º if I leave any of the fields idle or idle1-4 blank, I get an error in the attempt to write to the SQL table (type error). The second is an error of " The variable name '@image' has already been declared. Variable Names must be Unique Within a query batch or stored Procedure. Must declare the scalar variable "@ND". How should I do?

  • @Adriano, sorry I didn’t answer before. For idle fields force a fixed value. It probably gives error because the table must have indicated that these fields cannot be null. While the second problem for "Must declare the scalar variable "@ND"" It seems that the problem is here: cmdInteiro.Parameters.Addwithvalue("@ND", Sqldbtype.Int); cmdInteiro.Parameters["@ND"]. Value = Textboxnd.Text; Should be: command.Parameters.Addwithvalue("@ND", Sqldbtype.Int); command.Parameters["@ND"]. Value = Textboxnd.Text; O of the image I’m not seeing anything. Send the latest version of the code.

  • tb_image table has all Foreign Keys defined as (FK, int, null). I don’t think that’s what it indicates, but I may be wrong.

  • If the idle field is a FK then that could be the problem. Null is not an accepted value. As I said, try to force a value that exists in these fields and run the code again. Have you modified what I mentioned in the "@ND" field? If possible send the latest version of the code.

  • Hi Sandro. I posted the code. You got to see it?

Show 3 more comments

Browser other questions tagged c# sql type-conversion

You are not signed in. Login or sign up in order to post.