locked
C# inserting data into the Database, using textbox

    Question

  •  

    Hi, I am learning c# and i have to do it using the layered architure. So far i have been sucessfull in being able to retrieve data out of the database using combo-box.


    Now i need help bascialy when the user types something in the textbox i want that value to be saved in the Database, i will paste roughly 5-6 classes i am working with and from there maybe it will be sufficient enough for you to help me.

    This is my Dto class.

     public class OrganisationDto
     {
     // OrganisationType attribute which has a list of Organisation names. which gets and sets them.
     public List<String> Organisation { get; set; }
    
     //Constructor which is initialised 
    
     public OrganisationDto()
     {
     Organisation = new List<string>();
     }
     }

     

    This is my Organisation Response class.

     public class OrganisationResponse : Response
     {
     // An Attribute called countries which takes OrganisationDto as its type.
     public OrganisationDto Organisation { get; set; }
    
     
     public OrganisationResponse()
     {
     Organisation = new OrganisationDto();
     }
     }

    This is my Interface class

    public interface IOrganisationService
     {
     OrganisationResponse EnterOrgName(OrganisationRequest cr);
     }

     


    This is the OrganisationService

      class which is the most important class which i think i might need help on. This class contains the DB connections and the sql statement required to the form class.

     public class OrganisationService : IOrganisationService
     {
    
     public OrganisationResponse EnterOrgName(MessageBase.OrganisationRequest cr)
     {
    
     var OrgResponse = new OrganisationResponse();
    
     
     //Step 1 - Handshake with the Database
     SqlConnection conn = new SqlConnection();
     conn.ConnectionString = "Data Source=ARSLAN-2011\\SQLEXPRESS2;" +
     "User Instance=true;" +
     "Integrated Security=true;" +
     "AttachDbFilename=C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS2\\MSSQL\\DATA\\UserStoryOne.mdf;";
     conn.Open();
    
     
    
     SqlCommand myCommand = new SqlCommand("INSERT INTO Organisation )", conn);
    
     myCommand.ExecuteNonQuery();
    
     conn.Close();
    
     return OrgResponse;
    
    
     }
     }


    Form class Textbox method, i am sure that i have made a mistake in this please help me on this so that when the user enters something in the textbox it will be saved in the database.


     OrganisationService OrgService = new OrganisationService();
    
     var OrgResponse = OrgService.EnterOrgName(new OrganisationRequest());
    
     //textBoxOrg = Console.ReadLine();
    
     foreach (string org in OrgResponse.Organisation.Organisation)
     {
     MessageBox.Show(org);
     }


    Thanks!
     





    Monday, August 22, 2011 1:53 PM

Answers

  • Never use an empty catch clause. It just *hides* the error but this error still happens.

    What if you try INSERT INTO Organisation (OrganisationName, OrganisationShortName) that is without enclosing column names within single quotes ?

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Arslan_BMCLUB Tuesday, August 23, 2011 11:54 AM
    Tuesday, August 23, 2011 10:48 AM

All replies

  • Hi,

    And what happens currently is ? Make sure you don't hide exception. It seems to me you should have a runtime error when running your code (or tell us what it is if you have one)...

    Your EnterOrgName methods looks weird (the SQL INSERT statement is not correct, missing the VALUES clause) and it doesn't read anything unlike what the "retrieve country" comment tells (copy/pasted code from your country retrieval code ?) Also it accepts an unused parameter and doesn't pass the organisation name.

    My understanding for now is that you planned to call EnterOrgName to execute an INSERT statement agains the DB ? For now it seems to me you tried to copy/paste what you used for selecting data to insert new data (some comments seems outdated compared with the code).

    At some point you might be interested in http://msdn.microsoft.com/en-us/data/aa937709 (ADO.NET Entity Framework at a Glance). It allows to expose db data as objects to your code rather than creating explicitely SQL statements. Or perhaps right now if this is acceptable with your requirements...

    As a side note you may want to wait perhaps a bit more before bumping your thread...

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Monday, August 22, 2011 3:20 PM
  • Firstly Thank for you for being blunt and straight to the point.

    Firstly yes it does retrieve data however it is not in this class that is another class of which i have followed the layered architure or 3 tier as you say. Now what i wanted from you guys is bascialy each time a user types something in the textbox in the windows form that data gets saved into the database.
    ---------------------------------------------------------------------------------------------------------

    Now the reason the insert statement is incomplete is because i wasnt sure what to put in the values 
    i know the format of the insert statement
    insert into (table)
    values etc etc.......

    The reason it is empty i am not sure what to put there because if the user types something into the textbox i want that data to be saved into the DB not the predefined data i have given in that class. if you know what i mean..

    ---------------------------------------------------------------------------------------- --------

    I will edit the comments as im sure they will distract other ppl too but they are not an immediate concern. 

    ----------------------------------------------------------------------------------------------

    Currently i have 4 combo-box recieving data from database and i have this textbox which i want to insert data to.
    the text box is visible in the windows form but is not doing anything much

    hope that gives you more insight into this...... 

    ----------------------------------------------------------

    Next about the Bump if you had seen the thread before it was a mistake..
    so to hide that i just wrote bump and i didnt look to see you could delete thread as i am new didnt really pay attention.........

    Again i hope this helps you. 

    Monday, August 22, 2011 3:37 PM
  • I wonder if you shouldn't check EF right now. It allows to write thing such as http://msdn.microsoft.com/en-us/library/bb386870.aspx. The sample code retrieves an order as an object, updates some of its properties, delete one of it details line, adds a new order detail line. When SaveChanges is called, it creates the corresponding actual SQL statements needed to perform these changes agains the db. You can always add other layers as needed but here you go with with your Data Access Layer.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Monday, August 22, 2011 6:43 PM
  • Thanks il have a look at it !
    Tuesday, August 23, 2011 7:52 AM
  • And http://msdn.microsoft.com/en-us/magazine/ee236639.aspx#id0400059 could be of some inspiration if you goes this way.

    Feel free to mark whatever helps as an answer. Else if you want to stay on your current path, drop a note and I'll try to start from your architecture (perhaps somewhat a bit modified) to try to show a small sample of how I would see things without using EF. Note also that the truth IMHO is that the important thing is not to use best practices or using layers but rather knowing what is your *goal* when using them ;-) i.e. there is no "best" architecture for everyone, the "best" is the one that better fits YOUR needs.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    Tuesday, August 23, 2011 8:38 AM
  • hi i have made abit of progress but i am getting abit of an error maybe someone could help me. il try to explain bascialy i have a class which connects to the Db and performs the Sql statement. Right i have a textbox and a button, when i enter into the textbox the textbox takes that value and then when i press that button it should save the data into the DB table.

    Right i am able to type the data, however as soon as i press the button i get some sort of exception error. il show where below.

    This class performs the DB Connection and Insert Statement

     public class OrganisationService : IOrganisationService
     {
    
     public OrganisationResponse EnterOrgName(OrganisationRequest cr)
     {
     var OrgResponse = new OrganisationResponse();
     
     SqlConnection conn = new SqlConnection();
     conn.ConnectionString = "Data Source=ARSLAN-2011\\SQLEXPRESS2;" +
      "User Instance=true;" +
      "Integrated Security=true;" +
      "AttachDbFilename=C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS2\\MSSQL\\DATA\\UserStoryOne.mdf;";
     conn.Open();
    
     SqlCommand myCommand = new SqlCommand();
     myCommand.Connection = conn;
     foreach (string organisationName in cr.OrganisationDetails.Organisation)
     {
     try
     {
      string commandText = @"INSERT INTO Organisation ('OrganisationName', 'OrganisationShortName') 
       VALUES ('" + organisationName + "', 'test')";
      myCommand.CommandText = commandText;
      myCommand.ExecuteNonQuery();
     }
    
     catch (Exception)
     {
      throw;
     }
     }
    
     conn.Close();
     return OrgResponse;
     }




    When i get the error it goes directly to the

    catch(Exception) { throw; }

    The error i get is  as follows.
    sqlException was unhandled
    invalid column name 'OrganisationName' 'OrganisationShortName' 
    i have double checked the names and they are indeed correct



    This class has the button this is in the form class.


     private void button1_Click(object sender, EventArgs e)
     {
     if (!string.IsNullOrEmpty(textBoxOrg.Text))
     {
     var request = new OrganisationRequest(textBoxOrg.Text);
     var OrgService = new OrganisationService();
     var OrgResponse = OrgService.EnterOrgName(request);
     MessageBox.Show("Entered " + textBoxOrg.Text + " into the database!");
     }
     }

    Organisation Request Class
     public class OrganisationRequest
     {
      public OrganisationDto OrganisationDetails { get; set; }
    
      public OrganisationRequest(string organisationName)
      {
       OrganisationDetails = new OrganisationDto();
       OrganisationDetails.Organisation.Add(organisationName);
      }
     }





    Tuesday, August 23, 2011 10:08 AM
  • The things i have tried  i have commented out the 

    catch (exception)
    {
    //throw;

    }

    This time when i execute i still get no errors, i am able to type data into the textbox and press the button and i get no error. I then get a messagebox which displays what i typed.
    I check the Db in case something has been inserted and no nothing gets inserted.

    I am working on microsoft visual C# 2010 Express. Sometimes i hear it can be funny. So i closed the Database connection and went to the SQL Server Management Studio attached the database there and checked if it had been inserted and with no luck no it didnt.

    [P.S] i have to close the connection in the C# Express for it to be attached in Management studio im guessing this is due to it being Express edition but i dnt think it should be a major problem. only mentioning this as a side note.


    If you need any other info please ask il try to reply asap
    Tuesday, August 23, 2011 10:12 AM
  • Never use an empty catch clause. It just *hides* the error but this error still happens.

    What if you try INSERT INTO Organisation (OrganisationName, OrganisationShortName) that is without enclosing column names within single quotes ?

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Arslan_BMCLUB Tuesday, August 23, 2011 11:54 AM
    Tuesday, August 23, 2011 10:48 AM
  • Problem has been fixed the problem was my insert statement single quotes on the Organisation. I think let me explore little more 
    Tuesday, August 23, 2011 10:56 AM