SQL error in VB.net

Question

Papa_Don 31 Posting Pro in Training

5 Years Ago

Hi group,

I'm attempting to populate a listview with data from my test database created via Visual Studio 2019. I'm getting a syntax error that says, "Incorrect syntax near the keyword 'FROM'." It's not clear to me what the error is. The code I've written is as follows:

Dim strQ As String = String.Empty
Dim datasource As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True"
conn = New SqlConnection(datasource)
Dim searchID As String = tbxCompanyName.Text
 strQ = "SEARCH CUST_ACCT_NO, CUST_COMPANY_NAME, CUST_FIRST_NAME, CUST_MIDDLE_INITIAL, CUST_LAST_NAME 
            FROM CUSTREC 
            WHERE CUST_COMPANY_NAME LIKE '" & searchID & "%'"
cmd = New SqlCommand(strQ, conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "CUSTREC")

The error is displayed via the last line above (I'm not sure why, but that's a question for later). I've tried to read through the details of the error message, but most of this doesn't mean anything to me yet.

If you will, could you look through my code and the details of the error message to see where my syntax error truly is. Any "schooling" you can offer will be appreciated.

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Incorrect syntax near the keyword 'FROM'.
Source=.Net SqlClient Data Provider
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
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(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at SalesForm.CustomerInfo.tbxCompanyName_Leave(Object sender, EventArgs e) in C:\Users\Don\Documents\Visual Studio 2019\SalesForm\CustomerInfo.vb:line 189
at System.Windows.Forms.Control.OnLeave(EventArgs e)
at System.Windows.Forms.Control.NotifyLeave()
at System.Windows.Forms.ContainerControl.UpdateFocusedControl()
at System.Windows.Forms.ContainerControl.AssignActiveControlInternal(Control value)
at System.Windows.Forms.ContainerControl.ActivateControlInternal(Control control, Boolean originator)
at System.Windows.Forms.ContainerControl.SetActiveControlInternal(Control value)
at System.Windows.Forms.ContainerControl.set_ActiveControl(Control value)
at System.Windows.Forms.Control.Select(Boolean directed, Boolean forward)
at System.Windows.Forms.Control.SelectNextControl(Control ctl, Boolean forward, Boolean tabStopOnly, Boolean nested, Boolean wrap)
at System.Windows.Forms.Form.ProcessTabKey(Boolean forward)
at System.Windows.Forms.ContainerControl.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.TextBoxBase.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)
at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)
at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at SalesForm.My.MyApplication.Main(String[] Args) in :line 81

sql vb.net

3 Contributors
6 Replies
3K Views
3 Weeks Discussion Span
Latest Post 5 Years Ago Latest Post by RuhiAngel

rproffitt 2,662 "Nothing to see here."

5 Years Ago

In line 5, can you share documention for the SEARCH keyword? https://www.w3schools.com/sql/ doesn't show this.

Edited 5 Years Ago by rproffitt because: Corrected which line number I called out.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Papa_Don 31 Posting Pro in Training · Answer 1 · 2019-04-17T00:46:44+00:00

@rproffitt,

Duh..... I see the error - "SEARCH". It should have been "SELECT". I'm not sure why I wrote it that way. I'll change it and test it. I'll let you know what happens.

Thanks for you good pair of eyes!

Don

Papa_Don 31 Posting Pro in Training · Answer 2 · 2019-04-17T00:50:03+00:00

@rproffitt,

That was the fix. It worked as it should have.

Again, thanks for the second pair of eyes.

Don

rproffitt 2,662 "Nothing to see here." Moderator · Answer 3 · 2019-04-17T00:50:37+00:00

Off topic reply. Hmm, Starwood... Same one that has hotels in Canada?

Papa_Don 31 Posting Pro in Training · Answer 4 · 2019-04-18T20:22:47+00:00

@rproffitt,

Yes, the same one. I'm no longer with them as the company was purchased by Marriott and the end of 2016. My position ended in April of 2017. I'm now trying to get back to some similar position with another company but I haven't written the first line of code (until now) since that time.

Changes are often and fast today. Good luck and good hunting.

RuhiAngel 6 Newbie Poster · Answer 5 · 2019-05-07T10:12:16+00:00

Comments. This class is made at whatever point the .NET Framework Data Provider for SQL Server experiences a blunder created from the server. (Customer side mistakes are tossed as standard normal language runtime exemptions.) SqlException dependably contains no less than one example of SqlError.