SQL string concatenate field name

[btran007]

Hello everyone,

I am a newbie. I have tried to google all over the internet but of no avail. Can anyone help me on querying using vb.net SQL string with field name of two or more words, ie "Start Date", "Del Last First Name"

Code:
strSQL = "SELECT startDate as ""Start Date"", delLastFirst as "Del Last First Name"....

rst as ADODB.Recordset = new ADODB.Recordset (strSQL, conn)

rst.Find ("Start Date = '8/16/2006'") 'this is where the problem lies

How do you specify the field name with two or more words. I have tried ""Start Date"", 'Start Date', but incorrect syntax. Can anyone help me?

Thanks
btran

 

[Paszt]

Use brackets to enclose field names that include spaces:

 strSQL = "SELECT startDate as [Start Date], delLastFirst as [Del Last First Name]....

Also I see that you're using Recordsets. If it's true that you are using VS2005 as stated your Primary Platform, you should look into ADO.NET which includes many improved features over plain ADO. The ADO.NET analogy of RecordSets is DataSets.

 

[TechGnome]

"The ADO.NET analogy of RecordSets is DataSets" -- beg to differ.... DataTables are closer.... DataSets contain one or more DataTables... making them one level higher order than the dataTable....

-tg

 

[Paszt]

You're correct TechGnome. I guess the 'sets' in Recordsets made me type DataSets even though I was thinking DataTable.
Thanks for setting me straight.

 

[btran007]

Paszt,

That's very true. I have also tried Dataset but the problem lies at when a view as been created and by using the SELECT statement. How do you reference a field that was created using the SELECT statement, ie. ""Start Date"" or [Start Date]. I know that this works: date = rst.Fields("Start Date").Value. But I am trying to use the Find method of the Recordset, ie. rst.Find([Start Date] = '8/16/2006'). The [Start Date] field cannot be referenced. I hope that clears up the confusion.

 

[btran007]

This is a simple SQL Select query

SELECT startDate as [Start Date]
FROM Table
WHERE [Start Date] = '8/16/2006'

The error is "Invalid column name 'Start Date'". How would you fix that?

 

[Paszt]

Visual Studio .NET has a built in Query Builder that you can use to visually create SQL statements. To use it, add a new dataSet to your solution (in the solution explorer right click the project, add > new item and select DataSet). If the TableAdapter Configuration Wizard doesn't start, right click in the designer and select Add > TableAdapter. Set up your connection, select 'Use SQL statements', then select the 'Query Builder' button.

I did this and this is what it generated:

SELECT startDate AS [Start Date]
FROM Table
WHERE (startDate = CONVERT(DATETIME, '8/16/2006', 102))

As you see, you need to convert the string representation of a date to an actual date. The exact syntax will depend on your database type.

Note: the above is valid only if you're using VS2005 as your primary platform states.

 

[btran007]

Cool. There's something new to try. That solves the Select query, but doesn't the Select query generates a temporary view and bind it to the Recordset, for example. Isn't the field no longer startDate but [Start Date]? If I need to reference [Start Date], I don't think I can go back to 'startDate'. I know I can do this: date = rst.Fields("Start Date").Value. But rst.Find("[Start Date] = ...") is giving me a hard time. Maybe, I should just rename it to Start_Date and that will solves all the problem.

 

[Paszt]

I can't help you with RecordSets. I started using VB after ADO.NET was introduced and know nothing about RecordSets.
I wouldn't suggest using such old technology when there's the much more powerful ADO.NET available.
With ADO.NET you would use the DataTable.Rows.Find(Object) function.

Read about the differences between ADO and ADO.NET, DataViews, and searching a dataset here: ADO.NET DataViews

 

[btran007]

Thanks Paszt. I think I will try that. I think that will solve my problem.
Thank You Much.