C# .NET Database connection help

hibeesmad

hibeesmad

hibeesmad

Associate
Joined
7 Sep 2007
Posts
400
Location
Edinburgh
Hi, I am new to this, does anyone know where I am going wrong?

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 Example
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            System.Data.SqlClient.SqlConnection con;
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            con = new System.Data.SqlClient.SqlConnection();

            con.ConnectionString ="DataSource=.\\SQLEXPRESS; AttachDbFilename =C:\\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            con.Open();

            MessageBox.Show("Database Open");

            con.Close();

            MessageBox.Show("Database Closed");
        }
    }
}
 
Last edited:
I have another small error.

When I click the Save button - btnSave, I get the following error, in yellow.

Any ideas where I am going wrong?

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 Example
{
    public partial class Form1 : Form
    {

        System.Data.SqlClient.SqlConnection con;
        DataSet ds1;
        System.Data.SqlClient.SqlDataAdapter da;

        int MaxRows = 0;
        int inc = 0;

        public Form1()
        {
            InitializeComponent();

            System.Data.SqlClient.SqlConnection con;
            DataSet ds1;
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            con = new System.Data.SqlClient.SqlConnection();
            ds1 = new DataSet();

            con.ConnectionString = "Data Source=.\\SQLEXPRESS; AttachDbFilename =C:\\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            con.Open();

            string sql = "SELECT * FROM tblWorkers";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, con);

            MessageBox.Show("Database Open");

            da.Fill(ds1, "Workers");
            NavigateRecords();
            MaxRows = ds1.Tables["Workers"].Rows.Count;

            con.Close();

            MessageBox.Show("Database Closed");

            con.Dispose();
        }

        private void NavigateRecords()
        {
            DataRow dRow = ds1.Tables["Workers"].Rows[inc];

            textBox1.Text = dRow.ItemArray.GetValue(1).ToString();
            textBox2.Text = dRow.ItemArray.GetValue(2).ToString();
            textBox3.Text = dRow.ItemArray.GetValue(3).ToString();
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (inc != MaxRows - 1)
            {
                inc++;
                NavigateRecords();
            }
            else
            {
                MessageBox.Show("End of Records");
            }
        }

        private void btnPrevious_Click(object sender, EventArgs e)
        {
            if (inc > 0)
            {
                inc--;
                NavigateRecords();
            }
            else
            {
                MessageBox.Show("First Record");
            }
         }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            textBox1.Clear();
            textBox2.Clear();
            textBox3.Clear();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);

            DataRow dRow = ds1.Tables["Workers"].NewRow();

            dRow[1] = textBox1.Text;
            dRow[2] = textBox2.Text;
            dRow[3] = textBox3.Text;

            ds1.Tables["Worker"].Rows.Add(dRow);

            MaxRows = MaxRows + 1;
            inc = MaxRows - 1;

[COLOR="Yellow"]            da.Update(ds1, "Workers");[/COLOR]

            MessageBox.Show("Entry Added");
        }
    }
}
 
Kobrakai said:
You are referring to the table “Workers” in some places and “Worker” in others, what is the name of the table you are using?

Also why are you declaring con and ds1 as both member variables and in the constructor?
Click to expand...

Thanks for replying.

The table is called tblWorkers.

I am following tutorials from here
http://www.homeandlearn.co.uk/csharp/csharp_s12p10.html

If you could look there, and try and figure out what I am doing wrong, I would be very grateful as I would like to complete this.
 
Last edited:
Kobrakai said:
Change:

ds1.Tables["Worker"].Rows.Add(dRow);

To:

ds1.Tables["Workers"].Rows.Add(dRow);

As this is how it is in the example on that site.
Click to expand...

Thanks.

I changed this, but I am still getting the error. It is driving me mad.

Any other suggestions?

I appreciate your help.
 
Inquisitor said:
Give us the stack trace! And again, try removing the code that closes the connection.
Click to expand...

I have just started learning C#, so I am unsure what a stack trace is.

I removed the code which closes the connection and it works.

Thanks for being patient guys, I appreciate all your help.
 
Last edited:
Inquisitor said:
When the exception box pops up in the debugger, click 'View exception details' (or words to that effect), and it should have a load of info there, including the stack trace.

The stack trace essentially shows you the hierarchy of method calls up to the method that generated the error.
Click to expand...

Thanks for your help, I will keep that in mind for any future problems I may have.
 
This loads up in a web browser without any errors as a .asp page, however when I press the Next Record button - btnNext or Previous Record button - btnPrevious, only 2 Records are displayed, when there are 5 in the database.
Also, when I press the Add Record button - btnAddNew, I enter new values into textBox1, textBox2, and textBox3, but they are not displayed after I press the Save Button - btnSave in the database.

Have I missed anything out?

Code: 
public partial class _Default : System.Web.UI.Page 
{
    System.Data.SqlClient.SqlConnection con;
    DataSet ds1;
    System.Data.SqlClient.SqlDataAdapter da;

    int MaxRows = 0;
    int inc = 0;

    protected void Page_Load(object sender, EventArgs e)
    {
        con = new System.Data.SqlClient.SqlConnection();
        ds1 = new DataSet();

        con.ConnectionString = "Data Source=.\\SQLEXPRESS; AttachDbFilename =C:\\MyWorkers1.0.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

        con.Open();

        string sql = "SELECT * FROM tblWorkers";
        da = new System.Data.SqlClient.SqlDataAdapter(sql, con);

        da.Fill(ds1, "Workers");
        NavigateRecords();
        MaxRows = ds1.Tables["Workers"].Rows.Count;

    }
    
    protected void NavigateRecords()
    {
        DataRow dRow = ds1.Tables["Workers"].Rows[inc];

        TextBox1.Text = dRow.ItemArray.GetValue(1).ToString();
        TextBox2.Text = dRow.ItemArray.GetValue(2).ToString();
        TextBox3.Text = dRow.ItemArray.GetValue(3).ToString();
    }

    protected void btnPrevious_Click(object sender, EventArgs e)
    {
        if (inc > 0)
        {
            inc--;
            NavigateRecords();
        }
    }

    protected void btnNext_Click(object sender, EventArgs e)
    {
        if (inc != MaxRows - 1)
        {
            inc++;
            NavigateRecords();
        }
    }

    protected void btnAddNew_Click(object sender, EventArgs e)
    {
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        System.Data.SqlClient.SqlCommandBuilder cb;
        cb = new System.Data.SqlClient.SqlCommandBuilder(da);

        DataRow dRow = ds1.Tables["Workers"].NewRow();

        dRow[1] = TextBox1.Text;
        dRow[2] = TextBox2.Text;
        dRow[3] = TextBox3.Text;

        ds1.Tables["Workers"].Rows.Add(dRow);

        MaxRows = MaxRows + 1;
        inc = MaxRows - 1;

        da.Update(ds1, "Workers");
    }

I also tried using MessageBox.Show("First Record");, in the IF statement but it didn't seem to like it. The same went for textBox1.Clear();, instead I had to use textBox1.Text = "";
 
Last edited:
PsychoDuck said:
You are going to need to understand how the page events fire and in which sequence. Have a read here.



This is beacuse on each button click it causes the page to be reloaded, resetting your inc variable to 0 so you will never have a higher index than 1 as it is incremented in the btnNext_Click method which will get fired after the Page_Load event.


This is the same issue again, the btnSave_Click method gets called after the page_load event, where you have already re-set textboxes 1-3 with data from the first row of your dataset. What will actually happen is that you will be adding additional rows to the database with the same data as the first row.

What you need to do is preserve a pointer to your current record across page loads (happens each time you click a button (unless you are implementing the code client side rather than server side)).
Click to expand...

Thanks for your reply.

I have just started learning C# and .asp, and I have read the link you gave me, but I am still a little confused.

Should I leave Page_Load empty and move that code into a load button, or is it just a case of moving each method around, NavigateRecords, btnSave etc in different orders?

Thanks again.
 
You must log in or register to reply here.
Back
Top Bottom