• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

How do I check to see if item exists in database

I have a query where I am trying to get the lastname where the lastname and first name equals something, but get errors. I tried executescaler, but I am using too many values in the where clause and get a instance before reference of an object and then I tried execute reader, but get no column in the result error. This is what I am trying to do:

I am trying to check to see if a user is in the username table where the firstname=variable and lastname=variable.If it is in their, move on, but if it's not in the username table, insert it in.
using (OdbcConnection con16 = new OdbcConnection(ConnStr))
                        using (OdbcCommand cmd16 = new OdbcCommand("select LastName from usernames where FirstName='" + firstName + "' and LastName='" + lastName + "'", con16))
                        {
                            con16.Open();
                            OdbcDataReader dr1;
                            //string result = cmd16.ExecuteReader();
                            dr1 = cmd16.ExecuteReader();
 
                            try
                            {
                                if (dr1["LastName"] == Null)
                                {
                                    OdbcConnection con17 = new OdbcConnection(ConnStr);
                                    OdbcCommand cmd17 = new OdbcCommand("insert into usernames (ProjectLeader,CommercialLeader,GroupLeader,Admin,FirstName,LastName) values(ProjectLeader='" + pnewleader + "', CommercialLeader='" + cnewleader + "', GroupLeader='" + gnewleader + "', Admin='" + newadmin + "', FirstName='" + firstName + "', LastName='" + lastName + "')", con17);
                                    con17.Open();
                                    cmd17.ExecuteNonQuery();
                                    con17.Close();
                                    dr1.Close();
                                }
                            }
                            catch (Exception ee)
                                {
                                    OdbcConnection con17 = new OdbcConnection(ConnStr);
                                    OdbcCommand cmd17 = new OdbcCommand("insert into usernames (ProjectLeader,CommercialLeader,GroupLeader,Admin,FirstName,LastName) values(ProjectLeader='" + pnewleader + "', CommercialLeader='" + cnewleader + "', GroupLeader='" + gnewleader + "', Admin='" + newadmin + "', FirstName='" + firstName + "', LastName='" + lastName + "')", con17);
con17.Open();
cmd17.ExecuteNonQuery();
con17.Close();
 }

Open in new window

0
bschave2
Asked:
bschave2
  • 3
  • 2
1 Solution
 
AanvikCommented:
I would suggest go for a Data Adapter and user Data table and check for the row count before you check if the value is null.
0
 
bschave2Author Commented:
how do I do that? Could you give me an example? thanks...newbie to c#.....easy in vb.net!
0
 
AanvikCommented:
Here's the sample code.
OleDbCommand selectCMD = new OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
selectCMD.CommandTimeout = 30;
 
OleDbDataAdapter custDA = new OleDbDataAdapter();
custDA.SelectCommand = selectCMD;
 
DataTable custDS = new DataTable();
custDA.Fill(custDS, "Customers");
 
now check if the DataTable.Rows.Count > 0 then go for a null check... and insert and etc.

Open in new window

0
 
bschave2Author Commented:
i did a dataset like this... Thanks for the help.


OdbcConnection con16 = new OdbcConnection(ConnStr);
                        OdbcCommand cmd16 = new OdbcCommand("SELECT LastName, FirstName FROM usernames where FirstName='" + firstName + "' and LastName='" + lastName + "'", con16);
                        OdbcDataAdapter da16 = new OdbcDataAdapter(cmd16);
                        DataSet ds16 = new DataSet();
                        da16.Fill(ds16, "projectmanager");
 
                        if (ds16.Tables["projectmanager"].Rows.Count > 0)
                        {
                        }
                        else
                        {
                            OdbcConnection con17 = new OdbcConnection(ConnStr);
                            OdbcCommand cmd17 = new OdbcCommand("insert into usernames (ProjectLeader,CommercialLeader,GroupLeader,Admin,FirstName,LastName) values(ProjectLeader='" + pnewleader + "', CommercialLeader='" + cnewleader + "', GroupLeader='" + gnewleader + "', Admin='" + newadmin + "', FirstName='" + firstName + "', LastName='" + lastName + "')", con17);
                            con17.Open();
                            cmd17.ExecuteNonQuery();
                            con17.Close();
                        }
 
                            con16.Close();
                        }

Open in new window

0
 
AanvikCommented:
Perfect.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now