Question

Locked

SQL Statement in VB6 Code

By belcredit ·
I thank all of you for your input. Unfortunately no suggestion workes.

I have created a query using VB6 Query builder which works when run from the Query builder but when placed in my code it produce the following error.

"syntax erroe in FROM CLAUSE"

Here is the Select statement which works in the Query builder.

SELECT tblInfo.fldSuit, tblPayments.fldSuit, tblInfo.fldRCR, tblPayments.fldPDate, tblPayments.fldPaid, tblPayments.fldBalance, tblPayments.fldCashier, tblPayments.fldRecNum, tblPayments.fldVchNum
FROM tblInfo INNER JOIN tblPayments ON tblInfo.fldSuit = tblPayments.fldSuit
WHERE tblPayments.fldSuit = Gsuit;

I know that the problem is in declairing "GSuit" as a string in the select statement. The syntax to do this is where I am falling down.


?To fill the Grid with data that matches the Value in cboPayCombo

Dim strSuit As String
strSuit = cboPayCombo.Text
strQuery = "SELECT tblInfo.fldSuit, tblPayments.fldSuit, tblInfo.fldRCR, tblPayments.fldPDate, tblPayments.fldPaid, tblPayments.fldBalance, tblPayments.fldCashier, tblPayments.fldRecNum, tblPayments.fldVchNum
FROM tblInfo INNER JOIN tblPayments ON tblInfo.fldSuit = tblPayments.fldSuit
WHERE tblPayments.fldSuit = Gsuit"

adoGrid.RecordSource = strQuery adoGrid.Refresh
dtgPayment.ReBind


Please if someone can show me the correct syntax I would be very happy. Thank you.

You can contact me direct at belcredit@yahoo.com

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

count your apostrophes

by john.a.wills In reply to SQL Statement in VB6 Code

I suspect it is actually the WHERE clause that is wrong.

Collapse -

How To Diagnose

by Wayne M. In reply to SQL Statement in VB6 Code

Strip your SQL statement down to something that works and add pieces back until it fails. This will allow you to diagnose the problem.

Collapse -

VB Query

by saheer85 In reply to How To Diagnose

hi,

got a problem about creating reports in VB.
i no the 'select * from tablename;'

but i want to display details of only one record. what should be the syntax??

please post a reply because i'm totally bloked

Collapse -

You need a where clause

by rob In reply to VB Query

Select * from tablename will return all records in the table.

"Select * from Tablename WHERE myfield = mycriteria" will return only records which match.

Collapse -

Try an explicit join

by rob In reply to SQL Statement in VB6 Code

i.e.:
From tblInfo INNER JOIN tblPayments ON tblInfo.fldSuit=tblPayments.fldSuit WHERE tblPayments.fldSuit = '" & strSuit & "'"

Collapse -

Types mismatch, I suppose

by msi77 In reply to SQL Statement in VB6 Code

1. What data type of tblPayments.fldSuit is?
2. Try strSuit = cboPayCombo.Value

http://www.sql-ex.ru/

Collapse -

SQL Statement (INNER JOIN)

by hlanai In reply to SQL Statement in VB6 Code

Assuming the link between the two tables tblInfo and tblPayments is fldSuit, you have to use this field to join the two tables on the FROM clause as:

From tblInfo inner join tblPayments on tblInfo.fldSuit = tblPayments.fldSuit

It's only that I am not sure what error message you are getting but try this.

Hope it helps.

Collapse -

Another trick for debugging queries

by navanax In reply to SQL Statement in VB6 Code

When you get done building the query string, display it (or put a breakpoint in and copy it's contents) and check it. It's not only easier to spot problems with quotation marks (unbalanced, missing, mismatched) but you can also paste the string into your favorite query client and "play" with it there until you have the correct query, then go back to make the fixes in your code.

Collapse -

Is this against SQL Server?

by simonmlarsen In reply to SQL Statement in VB6 Code

If not ignore my post.

If it is you are approaching this so wrong it is not funny.

For starters that style of join syntax is deprecated.

Secondly take advantage of the performance sql has rather than messing about with ad hoc statements.

Thirdly don't create strings, security nightmare.

try this.

From query analyser or SSMS (depending on version).

create proc pr_sWhatever @strSuit varchar (8000) = Null
as
set nocount on

SELECT i.fldSuit, i.fldRCR, p.fldPDate, p.fldPaid, p.fldBalance,
p.fldCashier, p.fldRecNum, p.fldVchNum, p.fldSuit
From tblInfo i join
tblPayments p
on i.fldSuit = p.fldSuit
Where p.fldSuit = @strSuit
go

I have put a varchar 8000, check what you actually need there.

then your string is

strQuery = "pr_sWhatever " & strSuit

except of course this is not ado style code.

You probably want to create an ado command object and declare the parameter and set it.

bam! performance and security all in one quick hit.

Collapse -

Deprecated

by kerry.millen In reply to Is this against SQL Serve ...

The style of join syntax simonlarsen is referring to has in fact been deprecated in favor of join syntax. That being said, no DBMS I'm aware of has discontinued support for the old syntax and although the new join syntax promises increase efficiency and speed, this primarily applies to very large data sets. Additionally, the old syntax is more human readable.

Back to Web Development Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Software Forums