dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: C# and SQL problem

  1. #1
    Join Date
    Jul 2008
    Posts
    8

    Question C# and SQL problem

    Hello,

    I am doing a big project for personal goal to get more familiar with C# and SQL, ok the project is a help desk simple and small, i have 3 forms, the first asks you to either go into form 2 which is the profile to have the user enter customer profile information (first name, last name etc) and in form 2 there are 4 buttons, add (add new customer profile), Delete (delete customer profile that is selected), Update( select the customer from the listbox and in the text field change anything and have it take changes fast in SQL database), and of course close,

    MY problem i have the code below, for the add button, when i click on it, it will add a new blank field in the list box which after i click add, i have to click on the list box for a drop down of a blank line and select it to add, is there a way to just click add and it clears all fields for the add new customer profile?

    The Delete button is also a problem, when i select a profile to delete, it delete the entire thing!! not good, and what is weird is it has the same problem as the update, any changes made using the C# to interact with SQL DB, once i close out the program and re-open it, the changes made are gone, it goes back to older.

    Update problem is when i make a change click update, it looks like it works because i go out of form 2 back to form 1, click on form2 again and it took the change, but closing out the program entering it back it will go back to old settings, whats the deal? i have ALL the code listed below PLEASE HELP!! MSDN or other forums no help....

    This is form 2 customer profile:


    Code:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace WindowsFormsApplication1
    {
    public partial class Form2 : Form
    {
    public Form2()
    {
    InitializeComponent();
    }

    private void textBox2_TextChanged(object sender, EventArgs e)
    {

    }

    private void textBox6_TextChanged(object sender, EventArgs e)
    {

    }

    private void button2_Click(object sender, EventArgs e)
    {
    this.Close();
    }

    private void Form2_Load(object sender, EventArgs e)
    {
    // TODO: This line of code loads data into the 'insighthdDataSet.enterinfo' table. You can move, or remove it, as needed.
    this.enterinfoTableAdapter.Fill(this.insighthdDataSet.enterinfo);
    enterinfoTableAdapter.Fill(insighthdDataSet.enterinfo);


    }

    private void button3_Click(object sender, EventArgs e)
    {

    }

    private void button1_Click(object sender, EventArgs e)
    {
    insighthdDataSetBindingSource.EndEdit();
    enterinfoTableAdapter.Update(insighthdDataSet.enterinfo);
    MessageBox.Show("Update Complete");
    }

    private void bindingSource1_CurrentChanged(object sender, EventArgs e)
    {

    }

    private void label24_Click(object sender, EventArgs e)
    {

    }

    private void primarycontactLabel_Click(object sender, EventArgs e)
    {

    }

    private void enterinfoBindingNavigatorSaveItem_Click(object sender, EventArgs e)
    {

    }

    private void enterinfoBindingNavigator_Refre........ems(object sender, EventArgs e)
    {

    }

    private void fillByToolStripButton_Click(object sender, EventArgs e)
    {

    }

    private void customerticketTextBox_TextChanged(object sender, EventArgs e)
    {

    }

    private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    private void enterinfoBindingNavigatorSaveItem_Click_1(object sender, EventArgs e)
    {

    }

    private void integratorcompanyTextBox_TextChanged(object sender, EventArgs e)
    {

    }

    private void enterinfoBindingNavigatorSaveItem_Click_2(object sender, EventArgs e)
    {
    this.Validate();
    this.enterinfoBindingSource.EndEdit();
    this.tableAdapterManager.UpdateAll(this.insighthdDataSet);

    }

    private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e)
    {

    }

    private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
    {

    }

    private void toolStripButton1_Click(object sender, EventArgs e)
    {

    }

    private void fillByToolStripButton_Click_1(object sender, EventArgs e)
    {
    try
    {
    this.enterinfoTableAdapter.FillBy(this.insighthdDataSet.enterinfo);
    }
    catch (System.Exception ex)
    {
    System.Windows.Forms.MessageBox.Show(ex.Message);
    }

    }

    private void bindingNavigatorMoveNextItem_Click(object sender, EventArgs e)
    {

    }

    private void customerlocationTextBox_TextChanged(object sender, EventArgs e)
    {

    }

    private void enterinfoBindingSource_CurrentChanged(object sender, EventArgs e)
    {

    }

    private void button3_Click_1(object sender, EventArgs e)
    {
    }

    private void button3_Click_2(object sender, EventArgs e)
    {
    insighthdDataSet.enterinfoRow row =
    insighthdDataSet.enterinfo.NewenterinfoRow();
    insighthdDataSet.enterinfo.Rows.Add(row);

    }

    private void button4_Click(object sender, EventArgs e)
    {
    insighthdDataSet.enterinfo.Clear();

    }
    }
    }

  2. #2
    Join Date
    Nov 2007
    Location
    .NET 3.5 / VS2008 Developer
    Posts
    624

    Re: C# and SQL problem

    1. Learn to use the "code" tags when posting code.
    2. You have 3 Button Click Events for Button3.
    3. Learn to name your controls to more descriptive names. If Button3 is the "Add" button, rename it to "btnAdd".
    4. In my opinion, if you are communicating with a SQL database, use stored procedures. It is much easier, in my opinion, than all those binding controls.

  3. #3
    Join Date
    Jul 2008
    Posts
    8

    Question Re: C# and SQL problem

    OK my apologies ecplipsed4utoo, if you are recommending to use stored procedures rather than binding, can you tell me how to do that? the click_botton 3 is the add button, button 4 is the delete button, and button 2 is the update button. Sorry yes your right to be more descriptive but i wanted to make it fast and come back later to label the names of the buttons since that is time consuming. So again how do you do the SQL stored procedures using C#?

    Thank you

  4. #4
    Join Date
    Nov 2007
    Location
    .NET 3.5 / VS2008 Developer
    Posts
    624

    Re: C# and SQL problem

    pretty simple really....

    Code:
    	public static string GetEmployeeName(string employeeID)
            {
    	    string firstName = string.Empty;
    	    string lastName = string.Empty;
    
                using (SqlConnection cn = new SqlConnection(yourConnectionString))
                {
                    try
                    {
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = cn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "tsp_GetEmployeeName";
    
                        SqlParameterCollection sqlParams = cmd.Parameters;
                        sqlParams.AddWithValue("@BadgeID", employeeID);
    
                        cn.Open();
                        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                        if (dr.Read())
                        {
    			lastName = dr.GetString(dr.GetOrdinal("NameLast"));
    			firstName = dr.GetString(dr.GetOrdinal("NameFirst"));
                        }
    
                        dr.Close();
                        cn.Close();
                    }
                    catch (Exception ex)
                    {
    		  MessageBox.Show("Error connecting to database: " + ex.Message);
                    }
                }
    
                return firstName + " " + lastName;
            }
    where "@BadgeID" is the parameter for the stored procedure, and "NameFirst" and "NameLast" are the two fields that are being returned with the query
    Last edited by eclipsed4utoo; July 9th, 2008 at 01:36 PM.

  5. #5
    Join Date
    Jul 2008
    Posts
    8

    Talking Re: C# and SQL problem

    Ya simple for you lol, but where do i copy that code, under button for the update? add? delete etc? or in the form? sorry but i only have been studying and trying out C# for almost a month now.

  6. #6
    Join Date
    Nov 2007
    Location
    .NET 3.5 / VS2008 Developer
    Posts
    624

    Re: C# and SQL problem

    you put it where it belongs.

    If you are going to add a customer, then you will need to call a stored procedure(maybe name it "sp_AddCustomer"?) and pass the data as parameters and let the stored procedure do the insert.

    if you are going to update a customer's information, you will need to retrieve the data(similar to what I posted earlier), allow the user to edit it, then call a stored procedure to update the record in the database.

    etc.....etc....

    it is really up to you "where" to put it.

    some people would create a Data Layer project, containing classes with methods to add/update/delete/retrieve data from the database.

    others would create other classes in the current project which contain methods to add/update/delete/retrieve data from the database.

    others would create the methods within the form.

    and there are some that would just write the code within the button clicks.

    some are better than others, but none are wrong.

  7. #7
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: C# and SQL problem

    Take a read of the DW2 link in my sig (or if youre on vs2003, DW1) for an easy to follow set of guides on data access. See "Creating a Simple Data Access Project"
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)