We help IT Professionals succeed at work.

Insert, Update into SQL using DataAdapter and Textbox

Mapy123
Mapy123 asked
on

Please help me, I am geting this error message:

"Insertion error: the column name or the specific values do not correspond to table definition"

I have some textboxs, what I am trying to do is to take the text the user is typing into my textboxs and insert it into my table, also I have to give the user the option to do "update", please help me, I am having bad time....

The UserID and Titulo columns are VarChar.

This is my code:

    protected void ButtonGuardaCurriculo_Click(object sender, EventArgs e)
    {
        string conString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\empleo.mdf;Integrated Security=True;User Instance=True";
        SqlConnection myConn = new SqlConnection(conString);
        SqlDataAdapter da = new SqlDataAdapter();
        da.InsertCommand = new SqlCommand("INSERT INTO Curriculo VALUES(@UserID, @Titulo)", myConn);
        da.InsertCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = (String)Session["CurrentUser"];
        da.InsertCommand.Parameters.Add("@Titulo", SqlDbType.VarChar).Value = TextTitulo.Text;
        myConn.Open();
        da.InsertCommand.ExecuteNonQuery();
        myConn.Close();
        da.Dispose();
    }
Comment
Watch Question

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
If your Curriculo  table has more than 2 fields (UserID, Titulo) - plus a possible identity column, then you need to specify the fields in the list:

INSERT INTO Curriculo(UserID, Titulo) VALUES(@UserID, @Titulo)
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Here is an example where it will work and not work.

@test3 won't work because there are more fields than supplied in the INSERT.
@test2 works because
DECLARE @test1 table (
  UserID varchar(20),
  Titulo varchar(20)
)  
  
DECLARE @test2 table (
  UserID varchar(20),
  Titulo varchar(20),
  id int identity(1,1)
)  
  
DECLARE @test3 table (
  UserID varchar(20),
  Titulo varchar(20),
  id int -- without identity column
)  

INSERT INTO @test1 values ('1', 'President')
INSERT INTO @test2 values ('1', 'President')
--INSERT INTO @test3 values ('1', 'President')
select * from @test1
select * from @test2
select * from @test3

Open in new window

Commented:
//problem solved;
//just replace mysql with mssql if your using m$sql
//Refer your code to my code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using MySql.Data.MySqlClient;


namespace testcsharpDB
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        string MyConString = "SERVER=localhost;" +
                        "port=3306;" +
                        "database=test;" +
                        "user id=root;" +
                        "password=;";
        MySqlConnection mycon;
        MySqlDataAdapter da = new MySqlDataAdapter();



        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0) return;
            if (textBox2.Text.Length == 0) return;
            string appendstring = "insert into t1(f1,f2)values(@xf1,@xf2)";
            da.InsertCommand = new MySqlCommand(appendstring, mycon);
            da.InsertCommand.Parameters.AddWithValue("@xf1", textBox1.Text);
            da.InsertCommand.Parameters.AddWithValue("@xf2", textBox2.Text);
            da.InsertCommand.ExecuteNonQuery();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            mycon = new MySqlConnection(MyConString);
            mycon.Open();
            mycon.InitializeLifetimeService();
            button1.Text = "Save";
            button2.Text = "Query";
            button3.Text = "Update";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string selectstring = "select * from t1";
            da.SelectCommand = new MySqlCommand(selectstring, mycon);
            DataSet ds = new DataSet();
            da.Fill(ds, "t1");
            dataGridView1.DataSource = ds.Tables["t1"];
            DataRowCollection dc = ds.Tables[0].Rows;
            comboBox1.DisplayMember = "f1";
            comboBox1.DataSource = dc[0].Table;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0) return;
            if (textBox2.Text.Length == 0) return;
            string updatestring = "update t1 set f2=@xf2 where f1=@xf1";
            da.UpdateCommand = new MySqlCommand(updatestring, mycon);
            da.UpdateCommand.Parameters.AddWithValue("@xf1", textBox1.Text);
            da.UpdateCommand.Parameters.AddWithValue("@xf2", textBox2.Text);
            da.UpdateCommand.ExecuteNonQuery();
            button2.PerformClick();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            da.Dispose();
            mycon.Close();
        }

    }
}

Open in new window

Author

Commented:
My table did have more fields... but I still have this an error.. see image.

Thank you in advance.
Regards

Author

Commented:
My table did have more fields... but I still have this an error.. see image.

Thank you in advance.
Regards
error.jpg
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
It can either be that you have the word varchar in your statement (new sqlcommand...) or you have a data type mismatch in the table definition in sql and what you are setting here in code sqldbtype.varchar on a field that is not varchar.

Author

Commented:
I just found what the error is:   .net does not accept those characters " [ ] ", and I did have them in 2 fields.... and important thing is the order of the fields... you most try to insert in the same order you have your fields on your table... here is my code working:

    protected void ButtonGuardaCurriculo_Click(object sender, EventArgs e)
    {
        string conString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\empleo.mdf;Integrated Security=True;User Instance=True";
        SqlConnection myConn = new SqlConnection(conString);
        SqlDataAdapter da = new SqlDataAdapter();
        da.InsertCommand = new SqlCommand("INSERT INTO Curriculo VALUES(@UserID, @Titulo, @Nombre, @ApellidoPaterno, @ApellidoMaterno, @Direccion, @Colonia, @Municipio, @Estado, @Telefono, @Celular, @Objetivo, @Sexo, @Reubicarse, @Disponibilidad, @NivelEstudios, @EstudiosExtranjero, @ExperienciaInternacional, @IdiomaEspañol, @IdiomaIngles, @AreaExperiencia, @TipoContratacion, @AreaInteres, @SueldoMinimo, @SueldoDeseado)", myConn);
        da.InsertCommand.Parameters.Add("@UserID", SqlDbType.VarChar, 10).Value = (String)Session["CurrentUser"];
        da.InsertCommand.Parameters.Add("@Titulo", SqlDbType.VarChar, 100).Value = TextTitulo.Text;
        da.InsertCommand.Parameters.Add("@Nombre", SqlDbType.VarChar, 100).Value = TextTitulo.Text;
        da.InsertCommand.Parameters.Add("@ApellidoPaterno", SqlDbType.VarChar, 10).Value = TextApellidoPaterno.Text;
        da.InsertCommand.Parameters.Add("@ApellidoMaterno", SqlDbType.VarChar, 10).Value = TextApellidoMaterno.Text;
        da.InsertCommand.Parameters.Add("@Direccion", SqlDbType.VarChar, 50).Value = TextDireccion.Text;
        da.InsertCommand.Parameters.Add("@Colonia", SqlDbType.VarChar, 20).Value = TextColonia.Text;
        da.InsertCommand.Parameters.Add("@Municipio", SqlDbType.VarChar, 50).Value = DropMunicipio.Text;
        da.InsertCommand.Parameters.Add("@Estado", SqlDbType.VarChar, 30).Value = DropEstado.Text;
        da.InsertCommand.Parameters.Add("@Telefono", SqlDbType.NVarChar, 12).Value = TextTelefono.Text;
        da.InsertCommand.Parameters.Add("@Celular", SqlDbType.NVarChar, 13).Value = TextCelular.Text;
        da.InsertCommand.Parameters.Add("@Objetivo", SqlDbType.VarChar, 300).Value = TextObjetivo.Text;
        da.InsertCommand.Parameters.Add("@Sexo", SqlDbType.VarChar, 9).Value = DropSexo.Text;
        da.InsertCommand.Parameters.Add("@Reubicarse", SqlDbType.VarChar, 13).Value = DropReubicarse.Text;
        da.InsertCommand.Parameters.Add("@Disponibilidad", SqlDbType.VarChar, 13).Value = TextDisponibilidad.Text;
        da.InsertCommand.Parameters.Add("@NivelEstudios", SqlDbType.VarChar, 15).Value = DropNivelEstudios.Text;
        da.InsertCommand.Parameters.Add("@EstudiosExtranjero", SqlDbType.VarChar, 10).Value = DropEstudiosExtranjero.Text;
        da.InsertCommand.Parameters.Add("@ExperienciaInternacional", SqlDbType.VarChar, 10).Value = DropExperienciaInternacional.Text;
        da.InsertCommand.Parameters.Add("@IdiomaEspañol", SqlDbType.NVarChar, 3).Value = DropIdiomaEspañol.Text;
        da.InsertCommand.Parameters.Add("@IdiomaIngles", SqlDbType.NVarChar, 3).Value = DropIdiomaIngles.Text;
        da.InsertCommand.Parameters.Add("@AreaExperiencia", SqlDbType.VarChar, 50).Value = DropAreaExperiencia.Text;
        da.InsertCommand.Parameters.Add("@TipoContratacion", SqlDbType.VarChar, 20).Value = DropTipoContratacion.Text;
        da.InsertCommand.Parameters.Add("@AreaInteres", SqlDbType.VarChar, 50).Value = DropAreaInteres.Text;
        da.InsertCommand.Parameters.Add("@SueldoMinimo", SqlDbType.NVarChar, 50).Value = DropSueldoMinimo.Text;
        da.InsertCommand.Parameters.Add("@SueldoDeseado", SqlDbType.NVarChar, 50).Value = DropSueldoDeseado.Text;
        myConn.Open();
        da.InsertCommand.ExecuteNonQuery();
        myConn.Close();
        da.Dispose();
    }
C# ASP.NET Developer
Top Expert 2010
Commented:
Actually, the question posted was in regards to:
"Insertion error: the column name or the specific values do not correspond to table definition"

I posted a solution to this in #32957499.

The other error occurs after this was fixed, and is a separate issue.

Author

Commented:
I accept that, thank you for your help.