Results 1 to 6 of 6

Thread: Syntax error in FROM clause

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    11

    Syntax error in FROM clause

    Code:
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Private Sub cmdAddItem_Click()
    	AdoStock.Recordset.AddNew
    	AdoStock.Recordset.Fields("Date") = Format(Date, "dd/mm/yyyy")
    	AdoStock.Recordset.Fields("Category") = AdoInventory.Recordset.Fields("Category")
    	AdoStock.Recordset.Fields("Brand") = AdoInventory.Recordset.Fields("Brand")
    	AdoStock.Recordset.Fields("Item Description") = AdoInventory.Recordset.Fields("Item Description")
    	AdoStock.Recordset.Fields("Serial Number") = AdoInventory.Recordset.Fields("Serial Number")
    	AdoStock.Recordset.Fields("Stock In") = AdoInventory.Recordset.Fields("Stock")
    	AdoInventory.Recordset.Update
    	
    End Sub
    
    Private Sub cmdBrand_Click()
    	
    End Sub
    
    Private Sub cmdIn_Click(Index As Integer)
    	Dim StockValue
    	AdoStock.Recordset.Fields("Stock In") = InputBox("Enter Stock In")
    	StockValue = Val(AdoStock.Recordset.Fields("Stock In")) + Val(AdoInventory.Recordset.Fields("Stock"))
    	AdoInventory.Recordset.Fields("Stock") = Str(StockValue)
    	
    	AdoInventory.Recordset.Update
    	AdoStock.Recordset.Update
    	
    End Sub
    
    Private Sub cmdOut_Click(Index As Integer)
    	Dim StockValue As Integer
    	AdoStock.Recordset.Fields("Stock Out") = InputBox("Enter Stock Out")
    	StockValue = Val(AdoInventory.Recordset.Fields("Stock")) - Val(AdoStock.Recordfields("Stock Out"))
    	AdoInventory.Recordset.Fields("Stock In") = (Str(Val(AdoStock.Recordset.Fields("Stock In")) - (Val(AdoStock.Recordset.Fields("Stock Out")))))
    	
    	Do While AdoStock.Recordset.Fields("Stock In") < 0
    		AdoStock.Refresh
    		AdoStock.Recordset.Fields("Stock Out") = InputBox("Enter Stock Out")
    		AdoStock.Recordset.Fields("Stock In") = (Str(Val(AdoStock.Recordset.Fields("Stock In")) - (Val(AdoStock.Recordset.Fields("Stock Out")))))
    		
    	Loop
    	AdoInventory.Recordset.Update
    	AdoStock.Recordset.Update
    	
    End Sub
    
    Private Sub cmdSearch_Click()
    	Dim SeachString1, SearchString2 As String
    	SearchString1 = ComboBrand.Text
    	SearchString2 = ComboCategory.Text
    	
    	If ComboBrand.Text <> "All Brands" And ComboCategory.Text <> "All Categories" Then
    		AdoInventory.RecordSource = "SELECT * FROM Inventory WHERE Brand = '" & SearchString1 & "' and Category='" * SearchString2 & "'"
    		
    	ElseIf ComboBrand.Text <> "All Brands" And ComboCategory.Text <> "All Categories" Then
    		AdoInventory.RecordSource = "SELECT * FROM Inventory WHERE Category = '" & SearchStrings & "'"
    	ElseIf ComboBrand.Text <> "All Brands" And ComboCategory.Text <> "All Categories" Then
    		AdoInventory.RecordSource = "SELECT * FROM Inventory WHERE Brand = '" & SearchString1 & "'"
    	ElseIf ComboBrand.Text = "All Brands" And ComboCategory.Text = "All Categories" Then
    		
    		AdoInventory.RecordSource = "SELECT * FROM Inventory"
    	End If
    	AdoInventory.Refresh
    End Sub
    
    Private Sub cmdViewAll_Click()
    	AdoInventory.RecordSource = "SELECT * FROM Inventory"
    	AdoStock.RecordSource = "SELECT * FROM Stock"
    	AdoInventory.Refresh
    End Sub
    
    
    Private Sub Form_Load()
    	AdoInventory.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Dennis\Desktop\New folder\inventory_br.mdb;Persist Security Info=False"
    	AdoInventory.RecordSource = "SELECT * FROM Inventory"
    	AdoInventory.Refresh
    	Set DataInventory.DataSource = AdoInventory
    	AdoInventory.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Dennis\Desktop\New folder\inventory_br.mdb;Persist Security Info=False"
    	AdoInventory.RecordSource = "SELECT * FROM Stock"
    	AdoInventory.Refresh
    	Set DataInventory.DataSource = AdoInventory
    	
    	Do Until AdoInventory.Recordset.EOF
    		Dim C, B As String
    		
    		C = AdoInventory.Recordset.Fields("Category")
    		B = AdoInventory.Recordset.Fields("Brand")
    		ComboCategory.AddItem C
    		ComboBrand.AddItem B
    		AdoInventory.Recordset.MoveNext
    		
    	Loop
    	AdoInventory.Recordset.Requery
    End Sub
    Last edited by dday9; Apr 27th, 2021 at 08:19 AM. Reason: Formatted code and added code tags

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,043

    Re: Syntax error in FROM clause

    you are in the wrong Forum, you are using VB6
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error in FROM clause

    This is a terrible question for several reasons. Firstly, you have a partial error message in the title and a big wad of unformatted code in the question and nothing else. A good question would start with a full and clear explanation of the problem, which would include a description of what you're trying to achieve, how you're trying to achieve it (including RELEVANT code) and what happens when you try (including error messages AND where they occur). It would then end with a title that summarises that complete explanation. Most importantly though, the error message says that there's an error in the FROM clause of a SQL statement and yet you have posted loads of code that is completely irrelevant as well as numerous SQL statements that contain FROM clauses and with absolutely no indication of which one is the problem. How about you make an effort to help us help you and post JUST the actual SQL statement that causes the error? If you don't know which one that is, debug your code and find out.

    Apart from any of that, if you're going to use .NET then you should use .NET, which means using ADO.NET for data access and not ADODB like you were writing VB6 code.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Syntax error in FROM clause

    Quote Originally Posted by ChrisE View Post
    you are in the wrong Forum, you are using VB6
    Ha! Missed that and just assumed that they were writing VB.NET code like it was VB6. On closer inspection, it definitely looks like VB6 though. That said, the issue is actually a SQL problem and nothing to do with any VB version. The user should have done as I suggested and posted just the relevant SQL code and done so in the Database Development forum.

  5. #5
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Syntax error in FROM clause

    my best guess would be here :
    AdoInventory.RecordSource = "SELECT * FROM Inventory WHERE Brand = '" & SearchString1 & "' and Category='" * SearchString2 & "'"
    you put a * instead of a & but it is just a guess...
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Syntax error in FROM clause

    Moderator Actions: Moved to Database Development Forum
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width