SQL

Spring 2009

DB Roles & Permissions - Marsh

When using SQL, you need to talk with the administrator (Ms. Harris) to change your DB roles and permissions in order to be able to update your database.

Multi user Log in form code from a SQL Database in C# - Michael

Code put in behind your login screen to pull the user name and password from a SQL database.

SqlConnection lgnConn = new SqlConnection(@"Data Source=uam-cis1\sqlexpress;Initial Catalog=Senior_Project_Howard;Integrated Security=SSPI");

            lgnConn.Open();

            SqlCommand cmd = new SqlCommand("SELECT pass_Password FROM Password WHERE pass_Emp_ID = " + txtUsername.Text ,lgnConn);

            SqlDataReader dr;

            try

            {

                dr = cmd.ExecuteReader();

                while (dr.Read())

                {

                    if (dr["pass_Password"].ToString() == txtPassword.Text)

                    {

                        Global.ClockinoutForm();

                        this.Close();

                    }

                    else

                    {

                        errorProvider1.SetError(txtPassword, "Password invalid");

                    }

                }

            }

            catch

            {

                errorProvider1.SetError(txtUsername, "Please Enter Username");

            }

Create a Relationship Diagram with SQL Server Express - Michael

You can create a entity Relation Diagram, or ERD, with in SQL Server Express. When you get it going and you are logged in go to your database and Click on it. In the middle pane it will come up with your database one of them will say “Database Diagrams” Double Click on it. When you do this it will bring up a message box like the one show below and you Click Yes.

After that it will show you your diagram, then just Click Print on the Toolbar and you are done.

Spring 2006

How to import Excel Spreadsheet into SQL – Marquita

1. Select Import and Export Data from SQL menu
2. Select the Microsoft Excel 4.0 from Data Source drop down menu
3. Select File Name click Next
4. Select Destination and Database
5. Click Next
6. Specify Table Copy or Query, Click Next
7. Select Source Table and Views; make sure that your Destination is the name of your Database.
8. Click Next
9. Select Run Immediately
10. Click Next, then click Finish

Create a Crystal Report Using SQL - Marquita

1. Right Click on the Project in Solution Explorer
2. Select Add a New Item
3. Select Crystal Report Template
4. Select Report Document Type from Crystal Report Gallery
5. Click on OLEDB item
6. Select the OLEDB provider for SQL Server
7. Click Next
8. Select your Server and Database also select Integrated Security
9. Click Next, this button moves to the Data Tab
10. Select the table you wish to place in your report.
11. Click Next, this button moves to the Field Tab
12. Select which Fields you want from your table
13. Click Next if you would like to Group, Total or Chart
14. Otherwise Click Finish
15. Attach a Crystal Report Viewer (located on the Toolbox) to a blank Form
16. Set Crystal Report Viewer Display Group Tree Property to False
17. Set Anchor Property to Top,Bottom,Left,Right
18. Set Report Source Property to “your report.rpt”

How To create a table – Shannon

Go to Microsoft SQL Server then click on Enterprise Manager. After you register; you then click on the database folder and find your database. Then right click on on tables under your name. After you right click the table name a pop up menu appears at the top of the menu click on "New Table". Enter your information and then save it.

Relationship diagrams in SQL Server - Jim

To create a relationship diagram in SQL server, open the SQL Enterprise Manager and connect to the SQL server. Expand the databases and then expand your database.

You will see an object called 'Diagrams'. right click on Diagrams and click 'New Database Diagram'. Then select which tables you want to include in the diagram.

It's that easy!

Form_activated Event - Jim

When dealing with datasets and binded textboxes and labels, it is sometimes necessary to 'Refresh' the data so the binding controls will show the most current entries.

I have a datagrid that displays data from a table in SQL server, on the same form with the datagrid I have an 'Add' button that adds a new entry to the database table the datagrid is bound to. After I add a new entry, the datagrid doesn't show the updated entry until it has been refreshed. Because my Sqlconnection and data adapter are not located on the Add form I cannot execute the refresh because it is on a different form. To fix this I use the form_activated event.

The form_activated event is similiar to the form_load event. However, the form_load event will fire only once when the form is first loaded whereas the form_activated event will fire every time the form recieves focus or becomes the active form.

Example:

On my datagrid form 'frmPOS.vb' I create the form_activate event and the commands to run when it fires.

Private Sub frmPOS_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Activated

'when the form becomes the active form, refresh the datagrid

DsTransact21.Clear() ' Remove the old data

daTransact.Fill(DsTransact21) ' Reload the data.

dgTransAct.ResetBindings() ' Redisplay the data.

End Sub

Now when I open frmPOS, this event is fired, then when I open the 'Add' form to add a new entry the 'frmPOS.vb' form loses focus (becomes inactive) and the Add form becomes active. It adds a new entry to the database table and exits. When the Add form exits, the 'frmPOS' form becomes active again and the load_activate fires again, and the datagrid is updated with the new information.

SQL Stored procedures - Jim

Stored procedures are a must when manipulating data with a database. I used several stored procedures in my project to do certain tasks that the SQL server can accomplish. (Less VB coding) I use SQL stored procedures to validate users when they log into the system, to clear all data in a table, to delete a record in a table, and to total a column in a table and return that value to a label in VB.

To create a stored procedure in SQL Server, Open the Query Analyzer and connect to the SQL server. Click on the Query menu and change database to your database.Enter the following to create a stored procedure that deletes all data in a table.

Create procedure dbo.ClearTax

as

BEGIN

Delete from tax

END

You must change the database because you cannot put 'USE DataBaseName' before the Create command. The create command must be the first line when creating a stored procedure.

To execute the stored procedure in VB I use the following:

'Fisrt Create a new sql connection

Dim Conn As New SqlConnection("server=JmeServer; database=JMEPos;Trusted_Connection = True")

' Next, Create a new sql command and define the procedure and connection to use

Dim myCmd As New SqlCommand("ClearTransactTable", Conn)

' Next, open the new connection

Conn.Open()

' Next, define the type of command it is

myCmd.CommandType = CommandType.StoredProcedure

' Next, Run the query

myCmd.ExecuteNonQuery()

' Then close the sql connection

Conn.Close()

This procedure simply goes to the SQL server and deletes everything in the tax table then finishes. It returns nothing back.

In some cases you need to get the return value from the stored procedure.I have a transact table that holds the (product number, description, quantity, price,and total) of products purchased by a customer. When I'm finished adding new products, I need to total the 'total' column to get a subtotal, and return this subtotal back to VB and store the value in a label or textbox.

To create the stored procedure I use:

Create procedure dbo.TotalColumn

as

BEGIN

select '$' + convert(char(10), SUM(total), 1) "Subtotal"

from transact

END

Again, this goes into the SQL Query Analyzer to create the procedure. In VB I have a button called 'Total' that totals all the purchases. It calls the stored procedure and captures the return value and stores the value in a label. The code for the 'Total' Button is as follows:

Private Sub btnTotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotal.Click

Try

' Calculate subtotal

'Create a new SQL connection

Dim Conn As New SqlConnection("server=JmeServer; database=JMEPos;Trusted_Connection = True")

' Next, Create a new sql command and define the procedure and connection to use

Dim myCmd As New SqlCommand("totalColumn", Conn)

' Next, add a parameter that returns a value

myCmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

' Open the connection

Conn.Open()

' Next, define the type of command it is

myCmd.CommandType = CommandType.StoredProcedure

' Run the Query

myCmd.ExecuteNonQuery()

' Capture the return value and put in a label

lblSubTotal.Text = myCmd.ExecuteScalar()

' Close the connection

Conn.Close()

' This line simply formats the label to show dollar sign and decimal point

lblSubTotal.Text = (lblSubTotal.Text * 1).ToString("$##0.00")

Catch ex As Exception

MessageBox.Show("Error Totaling Columns", "transact")

End Try

End Sub

Note: These connections are done on my home computer where I have SQL server and VB and the same computer, therefore I don't need to specify a userid or password when connecting to the SQL server.

I simply use Trusted_Connection = True

If you want to connect to the schools SQL server, (which is located on a different computer) you need to specify the userid and password in the connection string.

For Example:

Dim Conn As New SqlConnection("server=CIS; database=NorthWind; user id=LoginName; password=pass")

Note: Stored procedures can also be used with Microsoft Access

Note: Stored procedures can be tested in both SQL Query Analyzer and VB.NET. To test a Stored Procedure in VB,

open the server explorer and go to your database and expand it, then expand store procedures, find your

procedure and right click, and click 'Run Stored Procedure'. Same in the Query Analyzer.