?
Solved

sql/server select statement - Incorrect syntax near 's'. when I change the index on dropdownlist

Posted on 2010-01-10
5
Medium Priority
?
428 Views
Last Modified: 2012-06-21
I am getting this error when when I change the index on the dropdownlist

 Incorrect syntax near 's'.
Unclosed quotation mark after the character string ') Order by e.dtmEvent'.

dropdownlist
==============================================================
Select Year:
<asp:DropDownList ID="ddlYear" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlYear_SelectedIndexChanged">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="2009">2009</asp:ListItem>
<asp:ListItem Value="2010">2010</asp:ListItem>
<asp:ListItem Value="2011">2011</asp:ListItem>
<asp:ListItem Value="2012">2012</asp:ListItem>
<asp:ListItem Value="2013">2013</asp:ListItem>
<asp:ListItem Value="2014">2014</asp:ListItem>
<asp:ListItem Value="2015">2015</asp:ListItem>
</asp:DropDownList>

   Protected Sub ddlYear_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim dtVolumeOrder As New DataTable()
        Dim strSelectCommand As String = "SELECT e.dtmEvent,e.strTitle,r.strName,r.strEmail,u.strRegistrationUserName,u.strBadgeName, r.fltOrderAmount,r.strRegistrationStatusID,r.dtmCreated FROM i2Integration_EventRegv45_Event e,i2Integration_EventRegv45_Registration r, i2Integration_EventRegv45_RegistrationUser u where e.intEventID = r.intEventID and r.intRegistrationID = u.intRegistrationID and r.blnEmailSent=1 and (YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND e.strTitle = '" + DroplistData.SelectedValue + "') Order by e.dtmEvent"
        Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                adapPatientBills.Fill(dtVolumeOrder)
            End Using
        End Using
        'iRowsCount = dtVolumeOrder.Rows.Count - 1
        uxItemDetailGrid.DataSource = dtVolumeOrder
        uxItemDetailGrid.DataBind()
    End Sub
0
Comment
Question by:westdh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Expert Comment

by:myrotarycar
ID: 26279094
Well, syntactically, everything looks alright. Is it possible that one of these
+ DroplistData.SelectedValue +
+ ddlMth.SelectedValue +
+ ddlYear.SelectedValue +
is returning a character such as ' " or something along those lines. Have you checked the query results using the filters causing the exception?

SELECT
      e.dtmEvent,
      e.strTitle,
      r.strName,
      r.strEmail,
      u.strRegistrationUserName,
      u.strBadgeName,
      r.fltOrderAmount,
      r.strRegistrationStatusID,
      r.dtmCreated
FROM
      i2Integration_EventRegv45_Event e,
      i2Integration_EventRegv45_Registration r,
      i2Integration_EventRegv45_RegistrationUser u
where
      e.intEventID = r.intEventID
      and r.intRegistrationID = u.intRegistrationID
      and r.blnEmailSent=1
      AND (
            YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "'
            AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "'
            AND e.strTitle = '" + DroplistData.SelectedValue + "'
            )
Order by
      e.dtmEvent
0
 
LVL 6

Expert Comment

by:Allister_Reid
ID: 26279133
Hi,
Can you post the asp for the DroplistData and ddlMth controls?


Thanks
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 26279139
you probably have a value with 's in your DroplistData

try this:

ddlMth.SelectedValue + "' AND e.strTitle = '" + DroplistData.SelectedValue.Tostring.replace("'","''") + "') Order by e.dtmEvent"

0
 
LVL 10

Expert Comment

by:myrotarycar
ID: 26279181
One more thing you oughta try to apply on your .NET code: try using "&" to concatenate instead of "+"

& will perform an implicity cast/conversion if necessary. See code below.

numResult will return the sum of both variables if numeric (3)
txtResult will return the concatenation of both variables, regardless of data type (21)
dim txtResult as string
dim numResult as integer

dim avalue as integer
dim bvalue as integer

bvalue = 1
avalue = 2

txtResult = avalue&bvalue
numResult = avalue+bvalue

msgbox(numResult)
msgbox(txtResult)

Open in new window

0
 

Author Closing Comment

by:westdh
ID: 31675257
Thanks much , I did not notice or even think about the 's deliminting the string.. Many thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question