Code 4 Jay: Basic SQL Statements

Since you already have your "conn" as the SQL connection, below are the 4 basic SQL command that frequently use when building up a system:

SELECT Statement

SELECT statement usually used for displaying records from the DB table on the system to the end user. In order to get this works, you need to have a control in the page to display the extracted records. Since this is just a quick guide, a Label marked as "lblDisplay" will be used as the display control in the page. In your code behind, you should have lines below:

Dim comm1 as SqlDataAdapter = New SqlDataAdapter("SELECT columnname FROM tablename WHERE condition", conn)

Dim ds1 as New DataSet()

comm1.Fill(ds1, "tablename")

lblDisplay.Text = ds1.Tables("tablename").Rows(0).Item("columnname")

By doing so, you should be able to display the FIRST record that you selected from the SQL SELECT statement onto the Label. If you would like to display all of the selected records, you will need to loop through all the "Rows" and it will be better to have a DataGrid as display control instead of a Label, since multiple records may be extracted out.

INSERT Statement

As the name implies, INSERT statement is used to insert new records into a DB table, which usually used in member registration, add new product, and etc. Below are the lines that used for record inserting:

Dim comm2 as SqlCommand = New SqlCommand("INSERT INTO tablename (column1, column2, column3) VALUES ('" & value1 & "', '" & value2 & "', '" & value3 & "')", conn)

conn.Open()

comm2.ExecuteNonQuery()

conn.Close()

Above 4 lines enables you to add new records into the respective table. Do take note that when putting in the values in the statement, you must tag your value with '" & values & "' (single quote follow by double quote, then ampersand. Reverse the sequence while closing the tag), else it will not work :)

DELETE Statement

DELETE statement allows you to remove unwanted records in the DB table permanently:

Dim comm3 as SqlCommand = New SqlCommand("DELETE FROM tablename WHERE condition", conn)

conn.Open()

comm3.ExecuteNonQuery()

conn.Close()

You should always have a condition to define what kind of record to be deleted, else a DELETE statement without condition will remove ALL records in the respective table.

UPDATE Statement

UPDATE statement likely use in modules like edit profile, change password, and etc where it update the column value with the designated new value:

Dim comm4 as SqlCommand = New SqlCommand("UPDATE tablename SET column1 = '" & value1 & "', column2 = '" & value2 & "', column3 = '" & value3 & "' WHERE condition)", conn)

conn.Open()

comm4.ExecuteNonQuery()

conn.Close()

Similarly, UPDATE statement should come with a condition to prevent it updating ALL records in the respective table on the selected columns with the given values.