Need help with this type or error: Incorrect syntax near ')'.

[binici]

Hello:

Normally when you received this message is it coming from the db, or the code itself?

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ')'.

Source Error:

Line 278: sqlConn.Open()
Line 279:
Line 280: dbread = sqlCmd.ExecuteReader()
Line 281:
Line 282: Do While dbread.Read()

Source File: E:\PwrDev\_calendars\calendar.aspx.vb Line: 280

Codebehind:

Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As DayRenderEventArgs)

'Dim calid As Integer = CInt(Page.Request.QueryString("calid"))
Dim calid As String = 0

If Page.IsPostBack Then
calid = CalendarType.Value.ToString
'CalendarType.SelectedValue = calid
Else
calid = Request.QueryString("calid")
End If

'If calendartype.Value = "" Then
'If Not Page.Request.QueryString("calid") = "" Then
'calid = Page.Request.QueryString("calid")
'Else
'calid = CalendarType.Value
'End If
'Else
'calid = CalendarType.Value
'End If

calid_value.Text = calid
calid_value_select.Text = CalendarType.Value.ToString


Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim strConnection As String
Dim dbread As SqlDataReader
Dim fieldcount As Integer
Dim sql
strConnection = ConfigurationManager.AppSettings("connectionString")
sqlConn = New SqlConnection(strConnection)

Dim d As CalendarDay
Dim c As TableCell
Dim dbcomm
Dim DayData
Dim Color
d = e.Day
c = e.Cell
Dim TheDate = d.Date.ToShortDateString
current_date = TheDate

If calid = "0" Then
sql = "SELECT * FROM Calendar_Events WHERE ((Calendar_Events.StartDate <= '" & TheDate & "') AND (Calendar_Events.EndDate >= '" & TheDate & "')) ORDER BY Calendar_Events.StartDate, Calendar_Events.StartTime"
sql_lbl.text = sql
Else
sql = "SELECT * FROM Calendar_Events WHERE (((Calendar_Events.StartDate <= '" & TheDate & "') AND (Calendar_Events.EndDate >= '" & TheDate & "')) AND (Calendar_Events.CalendarID=" & calid & ")) ORDER BY Calendar_Events.StartDate, Calendar_Events.StartTime"
sql_lbl.text = sql
End If

sqlCmd = New SqlCommand(sql)
sqlCmd.Connection = sqlConn
sqlConn.Open()

dbread = sqlCmd.ExecuteReader()

Do While dbread.Read()
DayData = Left(dbread("Event"), 19) + "<br>"

If IsMemberLoggedOn <> 1 Then

If d.IsOtherMonth Then
c.Controls.Clear()
Else

'Color = dbread("Color")
c.Controls.Add(New LiteralControl("<br><a href=calendar.aspx?EventID=" & dbread("EventID") & "&calid=" & calid & ">" & DayData & "</a>"))
'c.BackColor=System.Drawing.Color.FromName(Color)
End If

Else
'Color = dbread("Color")
c.Controls.Add(New LiteralControl("<br><a href=calendar.aspx?EventID=" & dbread("EventID") & "&calid=" & calid & "&member_id=" & MemberId & "&IMS_Login=" & IMSLogin & "&IMS_Password=" & PrivateId & "&IsMemberLoggedOn=" & IsMemberLoggedOn & ">" & DayData & "</a>"))
'c.BackColor=System.Drawing.Color.FromName(Color)
End If

Loop
dbread.Close()
sqlConn.Close()

End Sub

The page consists of a calendar control and a combobox. when the user click on the item, they will see the event info on the side and there is a textbox where they can enter their e-mail to be reminded of the event. This should just postback to the same page and show the calendar again.

Any ideas would be great.

Thanks!
Robert

[lmf232s]

Maybe try


SELECT *
FROM Calendar_Events
WHERE Calendar_Events.StartDate BETWEEN '" & TheDate & "' AND '" & TheDate & "'
ORDER BY Calendar_Events.StartDate, Calendar_Events.StartTime"

SELECT *
FROM Calendar_Events
WHERE Calendar_Events.StartDate BETWEEN '" & TheDate & "' AND '" & TheDate & "'
AND Calendar_Events.CalendarID = " & calid & "
ORDER BY Calendar_Events.StartDate, Calendar_Events.StartTime

[cemaksoy]

this syntax reminds me old asp codes,
you can use ado.net more efficiently with command parameters...


If calid = "0" Then
sql = "SELECT * FROM Calendar_Events WHERE ((Calendar_Events.StartDate <= @StartDate) AND (Calendar_Events.EndDate >= @EndDate)) ORDER BY Calendar_Events.StartDate, Calendar_Events.StartTime"
sql_lbl.text = sql

Else
sql = "SELECT * FROM Calendar_Events WHERE (((Calendar_Events.StartDate <= @StartDate) AND (Calendar_Events.EndDate >= @EndDate)) AND (Calendar_Events.CalendarID=@calid)) ORDER BY Calendar_Events.StartDate, Calendar_Events.StartTime"
sql_lbl.text = sql
End If

sqlCmd = New SqlCommand(sql)

cmd.parameters.add("@StartDate",cdate(e.day))
cmd.parameters.add("@EndDate",cdate(e.day))

'in your code start and end date same it should be different

If calid <> "0" Then
cmd.parameters.add("@calid",calid)
end if
sqlCmd.Connection = sqlConn
sqlConn.Open()
dbread = sqlCmd.ExecuteReader()



this code runs smoothly