I'm having trouble inserting data into my table called "client" in my database called "vet".
I'm trying to do the connection manually as opposed to using the database wizard. I want to do it this way so that i can hide the majority of my code in a separate class.
I have a feeling I have the logic for this all wrong as I'm not making use of a data set, I was hoping someone could point me in the right direction.
This is for a school assignment and not for real world use. I have read a number of similar posts but am still unable to come to a solution.
public void CommitToDatabase()
using (var con = new SqlConnection(CLSDatabaseDetails.GlobalConnectionString))
DataTable client = new DataTable();
DataSet ds = new DataSet();
string commandString = "SELECT * FROM client";
SqlCommand cmd = new SqlCommand(commandString, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataRow newClientRow = ds.Tables["client"].NewRow();
newClientRow["ClientID"] = ClientID;
newClientRow["FirstName"] = FirstName;
newClientRow["LastName"] = LastName;
newClientRow["Phone"] = PhoneNumber;
newClientRow["CAddress"] = Address;
newClientRow["Email"] = Email;
newClientRow["CUsername"] = Username;
newClientRow["CPassword"] = Password;
DataSet is a collection of
DataTables. In your example, you're only referring to one table. The
SqlDataAdapter.Fill method will populate either
DataTable. You're calling
da.Fill(client) which is fine - it will fill the DataTable
client. You could also call
da.Fill(ds, "client") which will fill the DataSet
ds. You can then extract your table via
ds.Tables["client"]. Using a
DataSet in this example is overkill, but you can show your tutor how a
You're filling the DataTable but then adding the new row with reference to the DataSet. But in your example, the DataSet
ds has nothing in it, because you filled the DataTable not the DataSet. Try this:
DataSet ds = new DataSet(); string commandString = "SELECT TOP 0 * FROM client"; SqlCommand cmd = new SqlCommand(commandString, con); con.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder builder = new SqlCommandBuilder(da); da.Fill(ds, "client"); DataTable client = ds.Tables["client"]; DataRow newClientRow = client.NewRow(); ... ... ... client.Rows.Add(newClientRow); builder.GetInsertCommand(); da.Update(client);
If you only want to insert a row, you don't need to
SELECT all the rows. If you
SELECT TOP 0 * from client you'll get the column names (which you need) but without the overhead of returning all the rows.