Incorrect syntax near the keyword 'from' ?

[jimneal]

When I create a form using only one table in a database this works fine. However when I create a form that uses at least two tables I am getting an error on my ReceiptsAdapter (or my SQLDataAdapter). It's saying that I am using the incorrect syntax and need the keyword "From." I am assuming that I am getting this error because I am using multiple tables in my SQLCommand statement. Any suggestions on how to remedy this? Thanks

Imports System.Data
Imports System.Data.SqlClient

Public Class Receipts
    Dim ReceiptsConnection As SqlConnection
    Dim ReceiptsCommand As SqlCommand
    Dim ReceiptsAdapter As SqlDataAdapter
    Dim ReceiptsTable As DataTable
    Dim ReceiptsManager As CurrencyManager
    Dim MyState As String, MyBookmark As Integer

    Private Sub Receipts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tabReceipts.Click

        ' connect to database
        ReceiptsConnection = New SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=C:\Program Files\Campaign\ToolsCamp.mdf; Integrated Security=True; Connect Timeout=30; User Instance = True")
        ReceiptsConnection.Open()
        ' establish command object
        ReceiptsCommand = New SqlCommand("select from candidate_nm nm inner join candidate_rc rc on nm.nm_seq = rc.rc_nm_seq Order by nm_lname", ReceiptsConnection)
        'establish data adapter and data tables
        ReceiptsAdapter = New SqlDataAdapter()
        ReceiptsAdapter.SelectCommand = ReceiptsCommand
        ReceiptsTable = New DataTable()
        ReceiptsAdapter.Fill(ReceiptsTable)
        'bind controls to data table
        txtReceiptsID.DataBindings.Add("Text", ReceiptsTable, "ReceiptsID")
        cboContribType.DataBindings.Add("Text", ReceiptsTable, "Contributor Type")
        txtPACInterest.DataBindings.Add("Text", ReceiptsTable, "PAC Interest")
        cboContributionType.DataBindings.Add("Text", ReceiptsTable, "Contribution Type")
        txtOrgname.DataBindings.Add("Text", ReceiptsTable, "Organization Name")
        txtRecDate.DataBindings.Add("Text", ReceiptsTable, "Received Date")
        txtPname.DataBindings.Add("Text", ReceiptsTable, "Prefix")
        txtFname.DataBindings.Add("Text", ReceiptsTable, "First Name")
        txtLname.DataBindings.Add("Text", ReceiptsTable, "Last Name")
        txtMname.DataBindings.Add("Text", ReceiptsTable, "Middle")
        txtSname.DataBindings.Add("Text", ReceiptsTable, "Suffix")
        txtAddress1.DataBindings.Add("text", ReceiptsTable, "Primary Address")
        txtAddress2.DataBindings.Add("text", ReceiptsTable, "Secondary Address")
        txtCity.DataBindings.Add("Text", ReceiptsTable, "City")
        txtState.DataBindings.Add("Text", ReceiptsTable, "State")
        txtZip.DataBindings.Add("Text", ReceiptsTable, "Zip Code")
        txtAmount.DataBindings.Add("Text", ReceiptsTable, "Amount")
        cboMode.DataBindings.Add("Text", ReceiptsTable, "Mode")
        txtInkDesc.DataBindings.Add("Text", ReceiptsTable, "Inkind Description")
        txtOtherDesc.DataBindings.Add("Text", ReceiptsTable, "Other Description")
        txtOccupation.DataBindings.Add("Text", ReceiptsTable, "Occupation")
        txtEmployer.DataBindings.Add("Text", ReceiptsTable, "Employer")
        QtyNum.DataBindings.Add("Text", ReceiptsTable, "Qty")
        txtSpFname.DataBindings.Add("Text", ReceiptsTable, "First Name")
        txtSpLname.DataBindings.Add("Text", ReceiptsTable, "Last Name")
        txtSpPname.DataBindings.Add("Text", ReceiptsTable, "Prefix")
        txtSpMidname.DataBindings.Add("Text", ReceiptsTable, "Middle")
        txtSpOcc.DataBindings.Add("Text", ReceiptsTable, "Occupation")
        txtSpEmployer.DataBindings.Add("Text", ReceiptsTable, "Employer")
        ' establish currency manager
        ReceiptsManager = DirectCast(Me.BindingContext(ReceiptsTable), CurrencyManager)
        Me.Show()
        Call SetState("View")
 
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        ReceiptsManager.Position = 0
    End Sub
    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If ReceiptsManager.Position = 0 Then Console.Beep()
        ReceiptsManager.Position -= 1
    End Sub
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        ReceiptsManager.Position += 1
    End Sub
    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        ReceiptsManager.Position = ReceiptsManager.Count - 1
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If Not (ValidateData()) Then Exit Sub
        Dim SavedName As String = txtLname.Text
        Dim SavedRow As Integer
        Try
            ReceiptsManager.EndCurrentEdit()
            ReceiptsTable.DefaultView.Sort = "txtlName"
            SavedRow = ReceiptsTable.DefaultView.Find(SavedName)
            ReceiptsManager.Position = SavedRow
            MessageBox.Show("Record saved.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Call SetState("View")
        Catch ex As Exception
            MessageBox.Show("Error saving record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub
    Private Function ValidateData() As Boolean
        Dim Message As String = ""
        Dim AllOK As Boolean = True
        Return (AllOK)
    End Function

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
       
        Dim Response As Windows.Forms.DialogResult
        Response = MessageBox.Show("Are you sure you want to delete this record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
        If Response = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If
        Try
            ReceiptsManager.RemoveAt(ReceiptsManager.Position)
        Catch ex As Exception
            MessageBox.Show("Error deleting record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
 
    Private Sub SetState(ByVal AppState As String)
        MyState = AppState
        Select Case AppState
            Case "View"
                txtFname.ReadOnly = True
                txtPname.ReadOnly = True
                txtMname.ReadOnly = True
                txtLname.ReadOnly = True
                txtSname.ReadOnly = True
                txtOrgname.ReadOnly = True
                txtAddress1.ReadOnly = True
                txtAddress2.ReadOnly = True
                txtCity.ReadOnly = True
                txtState.ReadOnly = True
                txtZip.ReadOnly = True
                txtOccupation.ReadOnly = True
                txtEmployer.ReadOnly = True
                txtAmount.ReadOnly = True
                txtInkDesc.ReadOnly = True
                txtOtherDesc.ReadOnly = True
                txtSpEmployer.ReadOnly = True
                txtSpFname.ReadOnly = True
                txtSpLname.ReadOnly = True
                txtSpMidname.ReadOnly = True
                txtSpOcc.ReadOnly = True
                txtSpPname.ReadOnly = True
                btnPrevious.Enabled = True
                btnNext.Enabled = True
                btnAddNew.Enabled = True
                btnSave.Enabled = False
                btnCancel.Enabled = False
                btnEdit.Enabled = True
                btnDelete.Enabled = True
                cboContribType.Focus()
            Case "Add", "Edit"
                txtFname.ReadOnly = False
                txtPname.ReadOnly = False
                txtMname.ReadOnly = False
                txtLname.ReadOnly = False
                txtSname.ReadOnly = False
                txtOrgname.ReadOnly = False
                txtAddress1.ReadOnly = False
                txtAddress2.ReadOnly = False
                txtCity.ReadOnly = False
                txtState.ReadOnly = False
                txtZip.ReadOnly = False
                txtOccupation.ReadOnly = False
                txtEmployer.ReadOnly = False
                txtAmount.ReadOnly = False
                txtInkDesc.ReadOnly = False
                txtOtherDesc.ReadOnly = False
                txtSpEmployer.ReadOnly = False
                txtSpFname.ReadOnly = False
                txtSpLname.ReadOnly = False
                txtSpMidname.ReadOnly = False
                txtSpOcc.ReadOnly = False
                txtSpPname.ReadOnly = False
                btnPrevious.Enabled = False
                btnNext.Enabled = False
                btnAddNew.Enabled = False
                btnSave.Enabled = True
                btnCancel.Enabled = True
                btnEdit.Enabled = False
                btnDelete.Enabled = False
                cboContribType.Focus()
            Case "AddOrganization"
                txtFname.BackColor = Color.Gray
                txtFname.ReadOnly = True
                txtPname.BackColor = Color.Gray
                txtPname.ReadOnly = True
                txtMname.BackColor = Color.Gray
                txtMname.ReadOnly = True
                txtLname.BackColor = Color.Gray
                txtLname.ReadOnly = True
                txtSname.BackColor = Color.Gray
                txtSname.ReadOnly = True
                txtOrgname.ReadOnly = False
                txtAddress1.ReadOnly = False
                txtAddress2.ReadOnly = False
                txtCity.ReadOnly = False
                txtState.ReadOnly = False
                txtZip.ReadOnly = False
                txtAmount.ReadOnly = False
                txtInkDesc.ReadOnly = False
                txtOtherDesc.ReadOnly = False
                txtOccupation.BackColor = Color.Gray
                txtOccupation.ReadOnly = True
                txtEmployer.BackColor = Color.Gray
                txtEmployer.ReadOnly = True
                txtSpEmployer.BackColor = Color.Gray
                txtSpFname.BackColor = Color.Gray
                txtSpLname.BackColor = Color.Gray
                txtSpMidname.BackColor = Color.Gray
                txtSpOcc.BackColor = Color.Gray
                txtSpPname.BackColor = Color.Gray
                txtSpEmployer.ReadOnly = False
                txtSpFname.ReadOnly = False
                txtSpLname.ReadOnly = False
                txtSpMidname.ReadOnly = False
                txtSpOcc.ReadOnly = False
                txtSpPname.ReadOnly = False
                btnPrevious.Enabled = False
                btnNext.Enabled = False
                btnAddNew.Enabled = False
                btnSave.Enabled = True
                btnCancel.Enabled = True
                btnEdit.Enabled = False
                btnDelete.Enabled = False
                cboContribType.Focus()
            Case "AddIndividual"
                txtFname.ReadOnly = False
                txtPname.ReadOnly = False
                txtMname.ReadOnly = False
                txtLname.ReadOnly = False
                txtSname.ReadOnly = False
                txtOrgname.ReadOnly = True
                txtOrgname.BackColor = Color.Gray
                txtAddress1.ReadOnly = False
                txtAddress2.ReadOnly = False
                txtCity.ReadOnly = False
                txtState.ReadOnly = False
                txtZip.ReadOnly = False
                txtOccupation.ReadOnly = False
                txtEmployer.ReadOnly = False
                txtAmount.ReadOnly = False
                txtInkDesc.ReadOnly = False
                txtOtherDesc.ReadOnly = False
                txtSpEmployer.ReadOnly = False
                txtSpFname.ReadOnly = False
                txtSpLname.ReadOnly = False
                txtSpMidname.ReadOnly = False
                txtSpOcc.ReadOnly = False
                txtSpPname.ReadOnly = False
                btnPrevious.Enabled = False
                btnNext.Enabled = False
                btnAddNew.Enabled = False
                btnSave.Enabled = True
                btnCancel.Enabled = True
                btnEdit.Enabled = False
                btnDelete.Enabled = False
                cboContribType.Focus()
        End Select
    End Sub
    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        Try
            MyBookmark = ReceiptsManager.Position
            ReceiptsManager.AddNew()
            Call SetState("Add")
        Catch ex As Exception
            MessageBox.Show("Error adding record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        Call SetState("Add")
    End Sub
    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        Call SetState("Edit")
    End Sub
    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        ReceiptsManager.CancelCurrentEdit()
        If MyState = "Add" Then
            ReceiptsManager.Position = MyBookmark
        End If
        Call SetState("View")
        ReceiptsManager.CancelCurrentEdit()
        Call SetState("View")
    End Sub
    Private Sub frmAuthors_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If MyState = "Edit" Or MyState = "Add" Then
            MessageBox.Show("You must finish the current edit before stopping the application.", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            e.Cancel = True
        Else
            Try
                'save changes to database
                Dim ReceiptsAdapterCommands As New SqlCommandBuilder(ReceiptsAdapter)
                ReceiptsAdapter.Update(ReceiptsTable)
            Catch ex As Exception
                MessageBox.Show("Error saving database to file:" + ControlChars.CrLf + ex.Message, "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
            'close the connection
            ReceiptsConnection.Close()
            'dispose of the objects
            ReceiptsConnection.Dispose()
            ReceiptsCommand.Dispose()
            ReceiptsAdapter.Dispose()
            ReceiptsTable.Dispose()
        End If
 
    End Sub
    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub
End Class

The exception:


System.Data.SqlClient.SqlException was unhandled
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Incorrect syntax near the keyword 'from'."
Number=156
Procedure=""
Server="\\.\pipe\E0A02AA7-78DA-49\tsql\query"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at WindowsApplication1.frmReceipts.frmReceipts_Load(Object sender, EventArgs e) in C:\Documents and Settings\kref\My Documents\Visual Studio 2008\Projects\campaignTool\campaignTool\frmReceipts.vb:line 27
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

 

[Menthos]

So your exception gives you...

Message="Incorrect syntax near the keyword 'from'."

and your query looks like...

ReceiptsCommand = New SqlCommand("select from candidate_nm nm inner join candidate_rc rc on nm.nm_seq = rc.rc_nm_seq Order by nm_lname", ReceiptsConnection)

Can you see anything near the keyword 'from' in your query that might be giving you an error? Perhaps in the field list you've defined between 'select' and 'from'... oh wait....