Click here to Skip to main content
15,879,613 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
hi I have a method that fills the combo box (by returning data from my DB)
C#
public void Fillcombo()
        {
          cbocompany.Items.Clear();
           OleDbConnection con = new OleDbConnection(strConn);
            con.Open();
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select Com_Name from [company]";
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            foreach(DataRow dr in dt.Rows)
            {
                cbocompany.Items.Add(dr["Com_Name"].ToString());
                
            }
            con.Close();


But when I want to insert data, I choose the text in the combobox but I can't save it ! I have the error Data type mismatch in criteria expression
and i want return @@identity for user

private void btajouter_Click(object sender, EventArgs e)
try
                  {
                                     con.Open();
                  string query="INSERT INTO [USER] (Use_Sgi, Use_FirstName, Use_LastName, Use_Email, Use_MobilePhone, Use_BusinessPhone, Com_ID, Loc_ID ) values('"+txtsgi.Text+"','"+ txtnom.Text +"','"+txtprenom+"','"+txtemail+"','"+txttelphonefix+"','"+txttelephonemobile+"','"+cbocompany.Text+"','"+cbolocaliter.Text+"',’" "select cast (@@identity() as int)")";
                  OleDbDataAdapter ad = new OleDbDataAdapter(query, con);
                  ad.SelectCommand.ExecuteNonQuery();

                  con.Close();

                  MessageBox.Show("Data Inserted Successfully");

                  }
                  catch (Exception ex)
                  {
                   MessageBox.Show(ex.Message);
                  }

How can I fix this issue ! Thx in advance

What I have tried:

i have a methode return @@identity
C#
private int insertUpdatecnnstr(string accesstring)
        {
            string query2 = "Select @@Identity";
           OleDbConnection con = new OleDbConnection(strConn);
            con.Open();
            OleDbCommand cmd = new OleDbCommand(accesstring,con);
            cmd.ExecuteNonQuery();
            cmd.CommandText = query2;

            int ID = (int)cmd.ExecuteScalar();
            return (ID);
        }
Posted
Updated 30-Jan-19 2:32am
Comments
CHill60 30-Jan-19 7:16am    
Your method insertUpdatecnnstr is just wrong on so many levels - it's not even doing an insert or an update. On top of all that your queries are at risk of SQL Injection attack
MadMyche 30-Jan-19 7:43am    
What type of DB are you using?
lira meh 31-Jan-19 5:22am    
hi
i use Acces 2016
MadMyche 31-Jan-19 6:52am    
Thats what I used when I coded my answer for you

C#
string query="INSERT INTO [USER] (Use_Sgi, Use_FirstName, Use_LastName, Use_Email, Use_MobilePhone, Use_BusinessPhone, Com_ID, Loc_ID ) values('"+txtsgi.Text+"','"+ txtnom.Text +"','"+txtprenom+"','"+txtemail+"','"+txttelphonefix+"','"+txttelephonemobile+"','"+cbocompany.Text+"','"+cbolocaliter.Text+"',’" "select cast (@@identity() as int)")";

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
 
Share this answer
 
Comments
CHill60 30-Jan-19 7:24am    
Better links than mine!
Patrice T 30-Jan-19 8:28am    
Thank you;
Some were found here in answers from others, just collecting them :) .
Feel free to pick the ones you want.
Learn about SQL Injection and change your query to use Parameters - Injection Prevention Cheat Sheet - OWASP[^]

That will probably fix your problem with the Data type mismatch

Whichever database you are using that is not how you return Identity - even if you actually set up the CommandText before executing the query.

If you are using SQL Server then use the OUTPUT Clause (Transact-SQL) - SQL Server | Microsoft Docs[^] If you are using another database then research how to return the last Identity inserted in the appropriate documentation
 
Share this answer
 
1. You've seen this in the other answers- you have SQL Injection Vulnerabilities. You should seriously review the links that have been provided.

2. You are executing your SQL batch (INSERT and SELECT) with the ExecuteNonQuery() function. This will always return an Int64 representing the rows affected of the last command; so pretty much it will always be 1.
What you want to do is to use the ExecuteScalar() function that will return the 1 data element that you want. Furthermore you are returning it and not capturing that value.

3. Your ReturnIdentity function will probably return 0; as it is run as a separate command and the only item in a batch.

I did some quick cleanup of the first 2 items; using Parameters in the query, changing the Execute function, and capturing the Identity within a new variable. I removed the Cast from the TSQL and you can do that within the calling app.
C#
string query = "INSERT INTO [USER] (Use_Sgi, Use_FirstName, Use_LastName, Use_Email, Use_MobilePhone, Use_BusinessPhone, Com_ID, Loc_ID ) values(?, ?, ?, ?, ?, ?, ?, ?); SELECT @@Identity()";

OleDbCommand cmd = new OleDbCommand(query, com);
cmd.Parameters.AddWithValue("@sgi", txtsgi.Text);
cmd.Parameters.AddWithValue("@nom", txtnom.Text);
cmd.Parameters.AddWithValue("@prenom", txtprenom);
cmd.Parameters.AddWithValue("@email", txtemail);
cmd.Parameters.AddWithValue("@telphonefix", txttelphonefix);
cmd.Parameters.AddWithValue("@telphonemobile", txttelephonemobile);
cmd.Parameters.AddWithValue("@company", cbocompany.Text);
cmd.Parameters.AddWithValue("@localiter", cbolocaliter.Text);

con.Open();
var NewIdentity = cmd.ExecuteScalar();
con.Close();


Once you get the basics fixed of C# talking to a database, people will be much more likely to help you out with any specific issues you are having with it
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900