Visual Studio SQL Best Practices

I am once again trying to learn Visual Studio and I am looking for sample code that will show me how to connect to a sql database, plus how to create a basic data entry form where there is header information on top and line detail in the body. Let's say I have a DSN called MyConnection, a database called MYDataBase, userid is MyUser and the password is password. In that database are two tables, one called Header and the other called Detail. Here are the fields:
Header.OrderNumber
Header.CustomerNumber
Header.CustomerName
Header.OrderDate
in the Detail table are:
Detail.OrderNumber
Detail.LineNumber
Detail.ItemNumber
Detail.ItemDescription

So what I am looking for is a sample VS 2008 project that would show me how to bring up the form, establish the connection, on the top of the screen display the order header information and in the body display the order detail information. This would be a multi-line display.

As I search for solutions I am finding conflicting methods. I am an old C programmer and now I am trying to use VS 2008 C#.
If anyone can provide a basic example for this project that would be sufficient to get me started. Once I see the concepts actually in code I can take it from there.
LVL 1
rwheeler23Asked:
Who is Participating?
 
magicdlfCommented:
You must put the code behind this:
      DisplayOrderDataGrid.DataSource = myDataSet.Tables["Orders"].DefaultView;
//MUST after this line
         DisplayOrderDataGrid.Columns[0].HeaderText = "Your Text";
         DisplayOrderDataGrid.Columns[0].Width = 500;
0
 
rwheeler23Author Commented:
These examples were great but I am left with two questions. I successfully have data appearing in a datagridview.

1) How do I set the columns names to be more user friendly as opposed to the sql field names?
2) How do I define the datagridview to be read-only? Right now it displays the new record row at the bottom and it let's you change values in the grid although it will not save the changes.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
rwheeler23Author Commented:
I was also trying to set the column widths without any success. I tried things like

DataViewGrid.columns[0].width=40 but when I would type in the period I did not get the drop down to be able to select width.
0
 
magicdlfCommented:
Can you please attach your code and let me have a look?
0
 
rwheeler23Author Commented:
I tried zipping up the project but their filter is rejecting the files in the zip file. I have pasted the code below. The odd thing is a tried it in VS Studio 2005 and it works. It jsut does not work in VS 2008.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Sample
{
    public partial class DisplayOrder : Form
    {
        private System.Data.SqlClient.SqlConnection myConnection;
        private System.Data.DataSet myDataSet;
        private System.Data.SqlClient.SqlCommand myCommand;
        private System.Data.SqlClient.SqlDataAdapter DataAdapter;

        public DisplayOrder()
        {
            InitializeComponent();
            string connectionString = "Data Source=W7HOME;Initial Catalog=ACCESS;User Id=sa;Password=223";
            myConnection = new System.Data.SqlClient.SqlConnection(connectionString);
            myConnection.Open();

            myDataSet = new System.Data.DataSet();
            myDataSet.CaseSensitive = false;

            myCommand = new System.Data.SqlClient.SqlCommand();
            myCommand.Connection = myConnection;
            myCommand.CommandText = "Select SOPNUMBE ,CUSTNMBR as [Customer Number],CUSTNAME as [Customer Name],SUBTOTAL as [Sub Total],FRGHTAMT as [Freight Amount],DOCTOTAL as [Order Total] from SOP10100";

            DataAdapter = new System.Data.SqlClient.SqlDataAdapter();
            DataAdapter.SelectCommand = myCommand;
            DataAdapter.TableMappings.Add("Table", "Orders");

            DataAdapter.Fill(myDataSet);

/* Here is where I am trying to insert column widths and headings */
            DisplayOrderDataGrid.ReadOnly = true;
            DisplayOrderDataGrid.DataSource = myDataSet.Tables["Orders"].DefaultView;
            try
            {
                myConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        private void buttonExit_Click(object sender, EventArgs e)
        {
            this.Hide();
            this.Dispose();
        }
    }
}

Open in new window

0
 
magicdlfCommented:
Works for me:
         /* Here is where I am trying to insert column widths and headings */
         DisplayOrderDataGrid.ReadOnly = true;
         DisplayOrderDataGrid.DataSource = myDataSet.Tables["Orders"].DefaultView;
//here you go
         DisplayOrderDataGrid.Columns[0].HeaderText = "Your Text";
         DisplayOrderDataGrid.Columns[0].Width = 500;
0
 
rwheeler23Author Commented:
I applied SP1 to VS 2008 and now I get the options for Width and Header Text, but when I run the program and it hits this grid I get an error message:

Index out of range. Must be non-negative and lesss than the size of the collection
 DataAdapter.Fill(myDataSet);

            DisplayOrderDataGrid.Columns[0].Width = 20;
            DisplayOrderDataGrid.Columns[0].HeaderText="Order Number";
            DisplayOrderDataGrid.Columns[1].Width = 20;
            DisplayOrderDataGrid.Columns[1].HeaderText = "Customer Number";
            DisplayOrderDataGrid.Columns[2].Width = 20;
            DisplayOrderDataGrid.Columns[2].HeaderText = "Customer Name";
            DisplayOrderDataGrid.Columns[3].Width = 20;
            DisplayOrderDataGrid.Columns[3].HeaderText = "Sub Total";
            DisplayOrderDataGrid.Columns[4].Width = 20;
            DisplayOrderDataGrid.Columns[4].HeaderText = "Frieght Amount";
            DisplayOrderDataGrid.Columns[5].Width = 20;
            DisplayOrderDataGrid.Columns[5].HeaderText = "Order Total";

Open in new window

0
 
magicdlfCommented:
Did you get the data from DB successfully?
0
 
rwheeler23Author Commented:
You are correct. Moving those lines behind did the trick. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.