Solved

How do I check to see if item exists in database

Posted on 2009-05-13
5
176 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Perfect.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now