views:

33

answers:

3

I HAVE iNSERT TO TABLE AG_MAS IN fOXPRO FOLLOW:

private void button1_Click(object sender, EventArgs e)
    {
        OleDbConnection connect = new OleDbConnection("Provider=VFPOLEDB.1;Data Source=pl_prov.dbf");
        try
        {
            connect.Open();
            string strSQL = "insert into ag_mas";
            strSQL += "(ag_code,ag_level,ag_group,ag_rcode,ag_status,";
            strSQL += "ag_type,ag_gender,ag_lname,ag_fname,ag_add1,";
            strSQL += "ag_add2,ag_add3,ag_add4,ag_class,ag_vouch,";
            strSQL += "pl_prov,pl_zone,ag_jdate,ag_ddate,ag_dbrith,ag_bank  ,";
            strSQL += "ag_bankno,ag_telno,ag_faxno,ag_email,ag_ppay,";
            strSQL += "ag_contno,ag_dcont,ag_ladate,ag_lpolno,ag_lendno,";
            strSQL += "ag_comm,ag_maxcom,ag_orcomm,ag_om1com,ag_om2com,";
            strSQL += "ag_om3com,ag_oy1com, ag_oy2com,ag_oy3com,ag_network)";
            strSQL += "values (";
            strSQL += "'" + textBox1.Text + "',";//ag_code
            strSQL += "'" + textBox2.Text + "',";//ag_level
            strSQL += "'" + textBox3.Text + "',";//ag_group
            strSQL += "' ' ,";                                //ag_rcode
            strSQL += "'" + textBox4.Text + "',";//ag_status
            strSQL += "'" + textBox22.Text + "',";//ag_type
            strSQL += "'" + textBox5.Text + "',";//ag_gender
            strSQL += "'" + textBox6.Text + "',";//ag_lname
            strSQL += "'" + textBox7.Text + "',";//ag_fname
            strSQL += "'" + textBox8.Text + "',";//ag_add1
            strSQL += "'" + textBox9.Text + "',";//ag_add2
            strSQL += "'" + comboBox1.Text + "',";//ag_add3
            strSQL += "' ' ,";                                   //ag_add4
            strSQL += "'" + textBox10.Text + "',";//ag_class
            strSQL += "'" + textBox11.Text + "',";//ag_vouch
            strSQL += "'" + comboBox1.SelectedValue + "',";//pl_prov
            strSQL += "' ' ,";                                   //pl_zone
            strSQL += "'" + textBox12.Text + "',";  //ag_jdate
            strSQL += "'1987/10/10',";                             //ag_ddate
            strSQL += "'1987/10/10',";                             //ag_dbirth
            strSQL += "' ' ,";                                   //ag_bank
            strSQL += "' ' ,";                                   //ag_bankno
            strSQL += "' ' ,";                                   //ag_telno
            strSQL += "' ' ,";                                   //ag_faxno
            strSQL += "' ' ,";                                   //ag_email
            strSQL += "'" + textBox13.Text + "',"; //ag_ppay
            strSQL += "'" + textBox15.Text + "',";//ag_contno
            strSQL += "'" + textBox14.Text + "',";//ag_dcont
            strSQL += "'1987/10/10', ";               //ag_ladate
            strSQL += "' ' ,";                                  //ag_lpopno
            strSQL += "' ' ,";                                  //ag_lendno
            strSQL += "0,";                                  //ag_comm
            strSQL += "0,";                                  //ag_maxcom
            strSQL += "0,";                                  //ag_orcomm
            strSQL += "0,";                                  //ag_om1com
            strSQL += "0,";                                  //ag_om2com
            strSQL += "0,";                                  //ag_om3comm
            strSQL += "0,";                                  //ag_oy1com
            strSQL += "0,";                                  //ag_oy2Xcom
            strSQL += "0,";                                  //ag_oy3com
            strSQL += "' ')";                                  //ag_network
            OleDbCommand cmd = new OleDbCommand(strSQL, connect);
            cmd.ExecuteNonQuery();
        }
        catch(Exception err)
        {
            MessageBox.Show(err.Message);
        }
    }
}

BUT I CAN'T INSERT. BECAUSE ERROR "DATA MISS MATCH" PLEASE HELP ME.

A: 

A Data Type Mismatch error occurs when you try to insert an inappropriate data type into a field. For example, if you try to store a string into an integer field.

At first glance I think the format of your dates is the problem. Change "'1987/10/10'," to "{^1987-10-10},".

If changing the dates does not correct the problem, it would be helpful if you could show me the result of the strSQL variable and structure of the ag_mas table.

Frank Perez
thanks so much!
A: 

I agree with Frank on the issue with dates... It could also be trying to do a text into a numeric field (or any other such swapping).

However, a bigger issue might be if/when you get into using a true SQL database such as Oracle, SQL-Server, MySQL, etc... (not that I have anything against VFP and have been programming with Foxpro since '85) But doing SQL statements like you have, especially in any web-based system would leave you wide-open to SQL-injection attacks.

Its better to use parameterized query/insert/update/deletes.

DRapp
Thanks so much!
A: 

I agree with Frank in regards to a solution and I believe that DRapp has a valid point about how you are going about your insert statement. To add to DRapp’s suggestion, I’d like to point out that you could use LINQ to VFP to handle writing all your sql commands.

Tom Brothers