SQL Query Question

[neha2007]

I am trying to use a MS Access Database in an vb.aspx page.

I have written the following two queries.. but they are giving me errors which i am unable to fix.ANy help in this regard would be highly appreciated .

Query 1: Dim sSQL As String = "UPDATE AuthorTable" & _
" SET ID ='" & sID & "',Name = '" & sName & "',BookID ='" & sID & "',Date ='" & sDate & ""

It gives me the error: Syntax error in string in query expression '''.
I understand that there is probably a problem with the way I am ending the query. But I do not understand what would be the correct syntax. Please Help.

Query 2:
Dim sSQL As String = "SELECT COUNT(*)" & _
"FROM (" & _
"SELECT DISTINCT LastName" & _
" FROM Authors" & _
"WHERE Month = '" & sMonth & "' AND ID = '" & sID & "')"

Error: Operator '&' is not defined for string "SELECT COUNT(*)FROM (SELECT DIST" and type 'ListItem'.

Would really appreciate a quick response.

Thanks

[MMcCarthy]

Query 1:

Firstly you need to leave a space after each comma. Also protected words like Name and Date should be enclosed in square brackets to indicate that they are field names ([Name] and [Date]).

Next check the datatype of your fields. Using the single quote indicates that they are all strings. Assuming ID and BookID are numbers you need to remove the single quotes and assuming that [Date] is in the date/time format you need to replace the single quotes with # as that is the delimiter for dates.

It is also not normal practice to put the statement on the same line as the variable declaration. If my assumptions regarding datatypes are correct then the following should work.

[CODE=vb]
Dim sSQL As String

sSQL = "UPDATE AuthorTable" & _
" SET ID=" & sID & ", [Name]='" & sName & "', BookID =" & sID & ", [Date]=#" & sDate & "#"
[/CODE]


Query 2:

You need to leave a space after COUNT(*) and before FROM. You don't need the & after the bracket opening the subquery. You also need an alias for the subquery. Assuming ID is a number datatype then the single quotes need to be removed. I would suggest the following.

[CODE=vb]
Dim sSQL As String

sSQL = "SELECT COUNT(*) " & _
"FROM (SELECT DISTINCT LastName " & _
"FROM Authors " & _
"WHERE Month='" & sMonth & "' AND ID=" & sID & ") As Qry1"
[/CODE]