February 28, 2021
Hot Topics:

.NET Data Secrets, Part 1

  • By Karl Moore
  • Send Email »
  • More Articles »

Cheating with SQL

So, you're developing all those SQL Server applications—and keep forgetting the difference between an inner join and an outer join? Unsure whether you should "Order By", "Sort By", or "Group By"? Then maybe it's about time you started to cheat.

You see, there's an easy way to write SQL—visually! Simply use the Server Explorer to open up your SQL Server, then right-click on the Views node. Select 'New View,' add the tables you wish to use in your query—and begin designing, specifying any sorting or other criteria. Right-click a blank area of the table view and select Property Pages to specify further criteria.

Also, the designer is particularly useful when dealing with date and time fields because it incorporates any required SQL Server functions for you, automatically. Just write the query as you would in, say, Microsoft Access.

Top Tip: The designer may be cool—but don't trust the relationships it automatically adds for you. Hover your mouse over a relationship to view its details. If it's incorrect, select with your mouse and press Delete. To create a relationship, drag-and-drop one field onto the other. Right-click and select Property Pages if you want to alter its details.

When you've finished, click Query > Run to test that it produces the results you require. When you're happy, simply take text from the SQL pane and remove all instances of the useless term "dbo." (this is added by SQL Server for the view and will ruin your statement when used in data access code)—and that's it!

You now can put your SQL statement to work—and have saved yourself an hour in writing and debugging in the process. Not a bad two minutes' work.

Putting together our SQL statement the easy way

Finding the Last Identity Number Added

Everyone has this problem: you've just added a record to your SQL Server database and need to figure out what the automatically generated identity number was, so you can use the value as a foreign key in some child table.

It took me a good while to figure this one out. You simply need to issue the "select @@identity" command to SQL Server and it'll return a one-field response that contains the last identity number added during your connection.

Let's look at a commented example:

    ' Variable to hold the identity value of our record
    Dim MyIdentityValue As Integer
    ' Set up sample connection and command
    Dim objConnection As New SqlClient.SqlConnection( _
        "server=NEMEAN;database=MYDATABASE;" & _
        "user ID=USERID;password=PWD")
    Dim objCommand As New SqlClient.SqlCommand( _
        "INSERT INTO author (authorname) " & _
        "VALUES('Karl Moore')")
    ' Open connection and execute INSERT command
    objCommand.Connection = objConnection
    ' Execute and check minimum of one record affected...
    If objCommand.ExecuteNonQuery > 0 = True Then
       ' Set up separate command to retrieve identity value
       Dim objIdentifier As New _
          SqlClient.SqlCommand("Select @@Identity", objConnection)
          ' Return value of field
          MyIdentityValue = objIdentifier.ExecuteScalar
          MyIdentityValue = 0
       End Try
    End If
    ' Close connection

Page 2 of 3

This article was originally published on May 5, 2003

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date