Solved

How do I check to see if item exists in database

Posted on 2009-05-13
5
181 Views
Last Modified: 2013-12-17
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
Comment
Question by:bschave2
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:Aanvik
ID: 24375901
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
 

Author Comment

by:bschave2
ID: 24375916
how do I do that? Could you give me an example? thanks...newbie to c#.....easy in vb.net!
0
 
LVL 5

Accepted Solution

by:
Aanvik earned 500 total points
ID: 24375963
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
 

Author Comment

by:bschave2
ID: 24376143
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
 
LVL 5

Expert Comment

by:Aanvik
ID: 24376172
Perfect.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question