Error tying datagridview to dataset 1

[cdck]

Using Visual Studio 2008

I have an unbound datagridview on my form (QCmastInspectList)

In the code, I am attempting to select summed/grouped data from a view in the database, and then set the datasource for the datagridview to that recordset. Here is the associated code (The line that throws the error is marked with >>>>):

   Private Sub QCmaster_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim constr
        constr = My.Settings.M2MAydinOtherConnectionString

        Dim SQLselect As String
        SQLselect = "SELECT fpartno AS Part, fcudrev AS Rev, SUM(QTY) AS QTY, SUM(fqtyrecv) AS QtyRec FROM(vwRIIpartsForInspect) GROUP BY fpartno, fcudrev ORDER BY fpartno"

        Dim con As System.Data.SqlClient.SqlConnection
        Dim da As System.Data.SqlClient.SqlDataAdapter
        Dim InspParts As DataTable
        con = New System.Data.SqlClient.SqlConnection(constr)
        da = New System.Data.SqlClient.SqlDataAdapter(SQLselect, con)
        InspParts = New System.Data.DataTable()
>>>>    da.Fill(InspParts)

        If InspParts.Rows.Count = 0 Then
            MsgBox("The system does not show any items ready for inspection at this time.  Please speak to receiving and/or IT to correct this.")
        Else
            QCmastInspectList.AutoGenerateColumns = False
            QCmastInspectList.DataSource = InspParts
        End If
        con.Close()
        con.Dispose()


    End Sub

When this code executes in debug, I get this exception:
[tt]
System.Data.SqlClient.SqlException was unhandled
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Line 1: Incorrect syntax near ')'."
Number=170
Procedure=""
Server="AD4"
Source=".Net SqlClient Data Provider"
State=1
[/tt]

Essentially this same code is used at the login stage of the application, and has no error - the only difference being that the dataset is not used for a datagridview, but instead is used to process the login.

           some variable declarations/processing left out here.
 
            Dim constr
            constr = My.Settings.M2MAydinOtherConnectionString

            Dim SQLselect As String
            SQLselect = "SELECT Password FROM vwUserDBaccess WHERE UserName = '" & UserName & "' AND DBid = 46"

            Dim con As System.Data.SqlClient.SqlConnection
            Dim da As System.Data.SqlClient.SqlDataAdapter
            Dim ds As DataTable
            con = New System.Data.SqlClient.SqlConnection(constr)
            da = New System.Data.SqlClient.SqlDataAdapter(SQLselect, con)
            ds = New System.Data.DataTable()
            da.Fill(ds)

            If ds.Rows.Count = 0 Then
                Proceed = 0
                MsgBox("The username is not found in the system.  Please try again or contact IT for assistance.", MsgBoxStyle.OkOnly, "Login Failed.")
            Else
                Dim d_UserName As DataRow = ds.Rows(0)

                pullPass = d_UserName("Password")
                If Password <> pullPass Then
                    MsgBox("The password does not match the one in the system for User " & UserName & ".  Please contact IT for assistance.")
                Else
                    QualityControl.LoginStat.Text = "Logged in under user name: " & UserName
                    QualityControl.LoginStat.TextAlign = HorizontalAlignment.Right
                    QualityControl.LoginStat.Visible = True
                    QCmaster.MdiParent = QualityControl
                    QCmaster.Show()
                    Me.Close()
                End If

            End If
            con.Close()
            con.Dispose()

What have I missed here?


Cheryl dc Kern

 

[ousoonerjoe]

You have "( )" around the view in your FROM. Removed them:

SQLselect = "SELECT fpartno AS Part, fcudrev AS Rev, SUM(QTY) AS QTY, SUM(fqtyrecv) AS QtyRec FROM(vwRIIpartsForInspect) GROUP BY fpartno, fcudrev ORDER BY fpartno"

Should be:

        SQLselect = "SELECT fpartno AS Part, fcudrev AS Rev, SUM(QTY) AS QTY, SUM(fqtyrecv) AS QtyRec FROM [b]vwRIIpartsForInspect[/b] GROUP BY fpartno, fcudrev ORDER BY fpartno"

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[cdck]

THANK you! I was stuck looking at the ")" on the line the debugger highlighted. I have no idea where those parenthesis came form when I copied the query in, but they're gone now, and it works fine.

Cheryl dc Kern