SQL Query

[GaWill]

Hi im currently trying to pull some data out of a few tables and i want to merge 2 of the fields so that they become one in my data grid i.e) Firstname and Lastname to become on field name.

I can pull the data in fine just need to know how to make it one field in the datagrid instead of the 2.

My Query is:

Dim cmd As New SqlClient.SqlCommand("SELECT dbo.CLINIC.CLINICNO, dbo.CLINIC.BOOKED_DATE, dbo.CLINIC.BOOKED_TIME, dbo.STAFF.NAME, dbo.PATIENT.FIRSTNAME, dbo.PATIENT.LASTNAME, dbo.PATIENT.DOB, dbo.PATIENT.PEDNO, dbo.PATIENT.REGNO FROM (dbo.CLINIC INNER JOIN dbo.PATIENT ON dbo.CLINIC.INTID = dbo.PATIENT.INTID) INNER JOIN dbo.STAFF ON dbo.CLINIC.STAFF_CODE_1 = dbo.STAFF.STAFF_CODE WHERE (((dbo.CLINIC.COMPLETE)='booked'AND BOOKED_DATE = @BOOKED_DATE ))", cs)


So i want to merge dbo.PATIENT.FIRSTNAME and dbo.PATIENT.LASTNAME so they both fill one new field called name in my datagrid.

Im using VB 2005 .

 

[Schenz]

Try this:

Dim strSQL As String = [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#800000]"SELECT " & _
   "dbo.CLINIC.CLINICNO, " & _
   "dbo.CLINIC.BOOKED_DATE, " & _
   "dbo.CLINIC.BOOKED_TIME, " & _
   "dbo.STAFF.NAME, " & _
   "dbo.PATIENT.FIRSTNAME + ' ' + dbo.PATIENT.LASTNAME AS name, " & _
   "dbo.PATIENT.DOB, " & _
   "dbo.PATIENT.PEDNO, " & _
   "dbo.PATIENT.REGNO " & _
   "FROM (" & _
   "dbo.CLINIC INNER JOIN dbo.PATIENT ON dbo.CLINIC.INTID = dbo.PATIENT.INTID" & _
   ") INNER JOIN dbo.STAFF ON dbo.CLINIC.STAFF_CODE_1 = dbo.STAFF.STAFF_CODE " & _
   "WHERE (((dbo.CLINIC.COMPLETE)='booked'AND BOOKED_DATE = @BOOKED_DATE ))"[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2][COLOR=#000000] cmd [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2][COLOR=#000000] SqlClient.SqlCommand([/COLOR][/SIZE][SIZE=2][COLOR=#000000]strSQL, cs)
[/COLOR][/SIZE][/COLOR][/SIZE]

It is based on SQL Server, which appears to be the DB you are using.

 

[GaWill]

Excellent thats worked a treat. Thanks alot Mate !!

 

[Schenz]

No problems. It took me a while to figure out SQL concatenations as well. Glad to help.