[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Visual Studio SQL Best Practices

Posted on 2010-01-05
10
Medium Priority
?
270 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:rwheeler23
  • 5
  • 5
10 Comments
 
LVL 9

Expert Comment

by:magicdlf
ID: 26186998
0
 

Author Comment

by:rwheeler23
ID: 26187068
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
 

Author Comment

by:rwheeler23
ID: 26187277
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 9

Expert Comment

by:magicdlf
ID: 26187646
Can you please attach your code and let me have a look?
0
 

Author Comment

by:rwheeler23
ID: 26189509
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
 
LVL 9

Expert Comment

by:magicdlf
ID: 26189790
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
 

Author Comment

by:rwheeler23
ID: 26190190
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
 
LVL 9

Expert Comment

by:magicdlf
ID: 26190239
Did you get the data from DB successfully?
0
 
LVL 9

Accepted Solution

by:
magicdlf earned 2000 total points
ID: 26190249
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
 

Author Comment

by:rwheeler23
ID: 26190414
You are correct. Moving those lines behind did the trick. Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question