Select error Syntax

Question

User319366782 posted

<mce:script language=mce-VB runat="server">Hi im having a sql select syntax error i can add just one AND in my select query right now its looks like this...</mce:script>

<mce:script language=mce-VB runat="server"></mce:script> 

<mce:script language=mce-VB runat="server">

<script language="VB" runat="server">
    
    Dim dr As OleDbDataReader
    Dim myConnection As OleDbConnection
    Dim myCommand As OleDbCommand
    Dim strSQL As String

   
    Sub Page_Load(ByVal Src As Object, ByVal E As EventArgs)
        Dim surplace, dateCreation, tempsHeuresDebut, tempsMinsDebut, tempsHeuresFin, tempsMinsFin, taches, nomClient, nomContact, nomRepresentant, heuresFacturables, deplacements As String
        Dim bDSID, billetDeServicesID, surP As Integer
        
        billetDeServicesID = Request.QueryString("BilletDeServicesID")
        
        bDSID = CInt(billetDeServicesID)


        strSQL = "SELECT dateCreation,surplace,tempsHeuresDebut,tempsMinsDebut,tempsHeuresFin,tempsMinsFin,taches,HeuresFacturables,Deplacements,NomRepresentant FROM BilletDeServices,Representants,Clients,Contacts WHERE BilletDeServicesID = " & bDSID & "AND BilletDeServices.RepresentantID = Representants.RepresentantIDD" & ""
        myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
         & "Data Source=" & Server.MapPath("login.mdb") & ";")
        
        myCommand = New OleDbCommand(strSQL, myConnection)
        
        myConnection.Open()
        
        dr = myCommand.ExecuteReader
        While dr.Read()
            dateCreation = dr(0)
            surplace = dr(1) 
            tempsHeuresDebut = dr(2)
            tempsMinsDebut = dr(3)
            tempsHeuresFin = dr(4)
            tempsMinsFin = dr(5)
            taches = dr(6)
            surP = CInt(surplace)
            heuresFacturables = dr(7)
            deplacements = dr(8)
            nomRepresentant = dr(9)
        End While
        
      
        If surP = 1 Then
            chkSurplace.Checked = True
        Else
            chkSurplace.Checked = False
        End If
        
        lblnomRepresentant.Text = nomClient
        lbldateCreation.Text = surplace   
        lblTempsHeureDebut.Text = tempsHeuresDebut
        lblTempsMinsDebut.Text = tempsMinsDebut
        txtTempsHeuresFin.Text = tempsHeuresFin
        txtTempsMinsFin.Text = tempsMinsFin
        txtTaches.Text = taches
        txtHeuresFacturables.Text = heuresFacturables
        txtDeplacements.Text = deplacements
        
       
        myConnection.Close()
    End Sub
</script>

but when i try to add an another AND its gives me a error syntax ... and my query cant no more be execute plz help ! i search on the net and there is no good option available right now

 

here is the  example

 

</mce:script> 

<script language="VB" runat="server">
    
    Dim dr As OleDbDataReader
    Dim myConnection As OleDbConnection
    Dim myCommand As OleDbCommand
    Dim strSQL As String

   
    Sub Page_Load(ByVal Src As Object, ByVal E As EventArgs)
        Dim surplace, dateCreation, tempsHeuresDebut, tempsMinsDebut, tempsHeuresFin, tempsMinsFin, taches, nomClient, nomContact, nomRepresentant, heuresFacturables, deplacements As String
        Dim bDSID, billetDeServicesID, surP As Integer
        
        billetDeServicesID = Request.QueryString("BilletDeServicesID")
        
        bDSID = CInt(billetDeServicesID)


        strSQL = "SELECT dateCreation,surplace,tempsHeuresDebut,tempsMinsDebut,tempsHeuresFin,tempsMinsFin,taches,HeuresFacturables,Deplacements,NomRepresentant,NomClient FROM BilletDeServices,Representants,Clients,Contacts WHERE BilletDeServicesID = " & bDSID & "AND BilletDeServices.RepresentantID = Representants.RepresentantIDD" & "AND BilletDeServices.ClientID = Clients.ClientIDD" & ""
        myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
         & "Data Source=" & Server.MapPath("login.mdb") & ";")
        
        myCommand = New OleDbCommand(strSQL, myConnection)
        
        myConnection.Open()
        
        dr = myCommand.ExecuteReader
        While dr.Read()
            dateCreation = dr(0)
            surplace = dr(1) 
            tempsHeuresDebut = dr(2)
            tempsMinsDebut = dr(3)
            tempsHeuresFin = dr(4)
            tempsMinsFin = dr(5)
            taches = dr(6)
            surP = CInt(surplace)
            heuresFacturables = dr(7)
            deplacements = dr(8)
            nomRepresentant = dr(9)
            nomClient = dr(10)
        End While
        
      
        If surP = 1 Then
            chkSurplace.Checked = True
        Else
            chkSurplace.Checked = False
        End If
        
        lblnomRepresentant.Text = nomClient
        lbldateCreation.Text = surplace   
        lblTempsHeureDebut.Text = tempsHeuresDebut
        lblTempsMinsDebut.Text = tempsMinsDebut
        txtTempsHeuresFin.Text = tempsHeuresFin
        txtTempsMinsFin.Text = tempsMinsFin
        txtTaches.Text = taches
        txtHeuresFacturables.Text = heuresFacturables
        txtDeplacements.Text = deplacements
        
       
        myConnection.Close()
    End Sub
</script>

please help !

 

Answer 1

User-342606376 posted

you need to do Join in your select , SQL will not understand your query automatically.

review this for 5 minutes and you will understand.

http://www.tizag.com/sqlTutorial/sqljoin.php

Answer 2

User1867929564 posted

 SELECT dateCreation,surplace,tempsHeuresDebut,tempsMinsDebut,tempsHeuresFin,tempsMinsFin,taches,HeuresFacturables,Deplacements,NomRepresentant,NomClient FROM BilletDeServices,Representants,Clients,Contacts WHERE  BilletDeServices.RepresentantID = Representants.RepresentantID  AND BilletDeServices.ClientID = Clients.ClientIDD And BilletDeServices.BilletDeServicesID = " & bDSID & ""

Check what changes I hv done.Put break point in ur prog. copy the query and run in mdb query window.Check what is the problem