How to insert information into a SQL Server Database with C#?

Question

I'm creating a form program that allows me to insert information about mail(physical), that is received in a Department. I'm a beginner and I'm having problems with the code.

I use this code:

conn = new SqlConnection(conexionString);
conn.Open();
comando = new SqlCommand(insertarBD, conn);
comando.CommandText = (@"INSERT INTO correspondencia_FFAA (no_reg, fecha_cre, hora_crea, corres_tipo, \n" +
                     "num_corres, dfecha, origen, enviar_a, estado_corres, asunto_corres, mens_corres, usuario, recibido) " +
                     "values(\n" +
                     "  @registro,\n" +
                     "  @fecha_creacion,\n" +
                     "  @hora_creacion,\n" +
                     "  @tipo_corres,\n" +
                     "  @numero,\n" +
                     "  @dfecha,\n" +
                     "  @origen,\n" +
                     "  @enviar_a, \n" +
                     "  @estado, \n" +
                     "  @asunto, \n" +
                     "  @mensaje, \n" +
                     "  @usuario, \n" +
                     "  @recibo)");

comando.Parameters.Add("@registro",SqlDbType.BigInt).Value = txtNoReg.Text;
comando.Parameters.Add("@fecha_creacion", SqlDbType.Date).Value = txtFechCrea.Text;
comando.Parameters.Add("@hora_creacion", SqlDbType.Time).Value = txtHorCrea.Text;
comando.Parameters.Add("@tipo_corres", SqlDbType.VarChar, 30).Value = cbbTipo.Text;
comando.Parameters.Add("@numero", SqlDbType.BigInt).Value = txtNo.Text;
comando.Parameters.Add("@dfecha", SqlDbType.DateTime).Value = txtDF.Text;
comando.Parameters.Add("@origen", SqlDbType.VarChar, 35).Value = txtOrigen.Text;
comando.Parameters.Add("@enviar_a", SqlDbType.VarChar, 35).Value = txtDestino.Text;
comando.Parameters.Add("@estado", SqlDbType.VarChar, 20).Value = cbbEstado.Text;
comando.Parameters.Add("@asunto", SqlDbType.VarChar, 100).Value = txtAsunto.Text;
comando.Parameters.Add("@mensaje", SqlDbType.VarChar, 500).Value = rtxtMensaje.Text;
comando.Parameters.Add("@usuario", SqlDbType.VarChar, 40).Value = txtUsuario.Text;
comando.Parameters.Add("@recibo", SqlDbType.VarChar, 40).Value = txtRecibido.Text;

But when I try to run the ExecuteNonQuery command it shows the next error:

System.FormatException: The Input string is not the correct format.

Any help would be greatly appreciated.

1) Are you tied to, or constrained by, a certain technology? | 2) Please put the full error code and stack trace if you can, it may provide more information. — Yves Schelpe
– Yves Schelpe, Commented Jan 27, 2014 at 13:35
Are you doing anything to check to make sure your DateTime values (coming from textboxes) are valid dates/times and your BitInts (from textboxes) are integers? — tgolisch
– tgolisch, Commented Jan 27, 2014 at 13:42
add a breakpoint and get the contents of your command - then see if you can run this in sql server management studio ... — SteveB
– SteveB, Commented Jan 27, 2014 at 14:14
You're specifying you insert as a verbatim string literal (@"..."). The only valid escape sequence in such literals is, I believe, "" to insert a single " character. So \n goes through as \n. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 27, 2014 at 14:39

Mohit S · Accepted Answer · 2015-10-05 04:39:54Z

Try this

You can't convert a null value to a meaningful value through Convert. Your best option is to check for null first and then assign a value of 0 (or whatever) to the result (whereever Convert was sending its results too).

int result=0;
DateTime val;
TimeSpan Timeval;

String str ="INSERT INTO correspondencia_FFAA (no_reg, fecha_cre, hora_crea, corres_tipo,num_corres, dfecha, origen, enviar_a, estado_corres, asunto_corres, mens_corres, usuario,recibido)values(@registro,@fecha_creacion,@hora_creacion,@tipo_corres,@numero,@dfecha, @origen,@enviar_a,@estado,@asunto,@mensaje,@usuario,@recibo)";

comando.CommandText= str;
if(int.TryParse(txtNoReg.Text,out result))
{
    comando.Parameters.Add("@registro",SqlDbType.BigInt).Value = txtNoReg.Text;
}
if(DateTime.TryParse(txtFechCrea.Text,out val))
{
    comando.Parameters.Add("@fecha_creacion", SqlDbType.Date).Value = txtFechCrea.Text;
}
if(TimeSpan.TryParse(txtHorCrea.Text,out TimeVal))
{
    comando.Parameters.Add("@hora_creacion", SqlDbType.Time).Value = txtHorCrea.Text;
}
if(int.TryParse(cbbTipo.Text,out result))
{
    comando.Parameters.Add("@tipo_corres", SqlDbType.VarChar, 30).Value = cbbTipo.Text;
}
if(int.TryParse(txtNo.Text,out result))
{
    comando.Parameters.Add("@numero", SqlDbType.BigInt).Value = txtNo.Text;
}
if(DateTime.TryParse(txtDF.Text,out val))
{
    comando.Parameters.Add("@dfecha", SqlDbType.DateTime).Value = txtDF.Text;
}
comando.Parameters.Add("@origen", SqlDbType.VarChar, 35).Value = txtOrigen.Text;
comando.Parameters.Add("@enviar_a", SqlDbType.VarChar, 35).Value = txtDestino.Text;
comando.Parameters.Add("@estado", SqlDbType.VarChar, 20).Value = cbbEstado.Text;
comando.Parameters.Add("@asunto", SqlDbType.VarChar, 100).Value = txtAsunto.Text;
comando.Parameters.Add("@mensaje", SqlDbType.VarChar, 500).Value = rtxtMensaje.Text;
comando.Parameters.Add("@usuario", SqlDbType.VarChar, 40).Value = txtUsuario.Text;
comando.Parameters.Add("@recibo", SqlDbType.VarChar, 40).Value = txtRecibido.Text;

Mohit S · Accepted Answer · 2015-10-05 04:38:56Z

0

The problem you are having is that, you have to convert integer to int in txtNoReg.Text and so on.

Please try Convert.ToDateTime(txtDate.text) and Convert.ToInt32(txtNo.Text)

edited Oct 5, 2015 at 4:38

Mohit S

14.1k6 gold badges36 silver badges73 bronze badges

answered Jan 27, 2014 at 17:17

Firdavs Kurbonov

1,2624 gold badges17 silver badges43 bronze badges

Collectives™ on Stack Overflow

How to insert information into a SQL Server Database with C#?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related