how to change syntax SQL access to phpmyadmin SQL/SQL server

Question

information error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')-Tanggalpjm)+1 As Lmpinjam, IFF (LMPINJAM>5,(LMPINJAM-5)*500,0) AS DENDA From t' at line 1

this my databse:

how this syntax work: 1. Select the member number in the combobox or type the member number in the Combobox (Book will appear that has been borrowed on the second grid 2. type the code of the Book (eg B001, B002, B003 etc.) on the first grid in the first column, if the file has never been borrowed, a message will appear that the Book has never been borrowed. 3. cancellation of returns on certain Book codes can be done by pressing ESC in the relevant Book row " 4. before being saved, fill in the "payment amount first"

denda = payment

its error on DataGridView1 coz this script use SQL access and my vb use phpmyadmin as database so how to change this syntax to phpmyadmin SQL?

("Select distinct tbBuku.NomorBK,tbdetailpjm.Nomorpjm,Judul,JumlahBK,tanggalpjm, (Date()-Tanggalpjm)+1 As Lmpinjam, IFF (LMPINJAM>5,(LMPINJAM-5)*500,0) AS DENDA From tbAnggota,tbPinjam,tbBuku,tbDetailpjm Where tbBuku.NomorBK=tbDetailpjm.NomorBK And tbPinjam.Nomorpjm=tbDetailpjm.Nomorpjm And tbAnggota.Nomoragt=Pinjam.Nomoragt And tbAnggota.Nomoragt='" & CmbNomoragt.Text & "' AND tbDETAILPJM.NomorBK='" & DgLaporan.Rows(e.RowIndex).Cells(0).Value & "' AND DETAILPJM.JUMLAHBK>0", conn)

full script:

Imports MySql.Data.MySqlClient
Imports System.Math


Private Sub DgLaporan_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DgLaporan.CellEndEdit
    If e.ColumnIndex = 0 Then
        CMD = New MySqlCommand("select NomorBK from tbanggota,tbpinjam,tbdetailpjm where NomorBK ='" & DgLaporan.Rows(e.RowIndex).Cells(0).Value & "' and tbpinjam.nomorpjm=tbdetailpjm.nomorpjm and tbanggota.nomoragt=tbpinjam.nomoragt and tbanggota.nomoragt='" & CmbNomoragt.Text & "' AND tbdetailpjm.JUMLAHBK>0", conn)
        RD = CMD.ExecuteReader
        RD.Read()
        If Not RD.HasRows Then
            MsgBox(" " & LblNamaAgt.Text & " tidak meminjam kode Buku " & DgLaporan.Rows(e.RowIndex).Cells(0).Value & "")
            Call HapusBaris()
            Exit Sub
            RD.Close()
        End If
        RD.Close()
        CMD = New MySqlCommand("Select distinct tbBuku.NomorBK,tbdetailpjm.Nomorpjm,Judul,JumlahBK,tanggalpjm, (Date()-Tanggalpjm)+1 As Lmpinjam, IFF (LMPINJAM>5,(LMPINJAM-5)*500,0) AS DENDA From tbAnggota,tbPinjam,tbBuku,tbDetailpjm Where tbBuku.NomorBK=tbDetailpjm.NomorBK And tbPinjam.Nomorpjm=tbDetailpjm.Nomorpjm And tbAnggota.Nomoragt=Pinjam.Nomoragt And tbAnggota.Nomoragt='" & CmbNomoragt.Text & "' AND tbDETAILPJM.NomorBK='" & DgLaporan.Rows(e.RowIndex).Cells(0).Value & "' AND DETAILPJM.JUMLAHBK>0", conn)
        RD = CMD.ExecuteReader
        RD.Read()
        If RD.HasRows Then
            DgLaporan.Rows(e.RowIndex).Cells(1).Value = RD.Item(1)
            DgLaporan.Rows(e.RowIndex).Cells(2).Value = RD.Item(2)
            DgLaporan.Rows(e.RowIndex).Cells(3).Value = RD.Item(3)
            DgLaporan.Rows(e.RowIndex).Cells(4).Value = RD.Item(4)
            DgLaporan.Rows(e.RowIndex).Cells(5).Value = RD.Item(5)
            DgLaporan.Rows(e.RowIndex).Cells(6).Value = RD.Item(6)
            DgLaporan.CurrentCell = DgLaporan.Rows(e.RowIndex).Cells(0)
            Call TotalKEMBALI()
            Call TotalDENDA()
            Call pembayaran()
        Else
            MsgBox(" " & LblNamaAgt.Text & "  tidak meminjam kode Buku " & DgLaporan.Rows(e.RowIndex).Cells(0).Value & "")
        End If
    End If
    RD.Close()
End Sub

Visual Vincent · Answer 1 · 2018-12-09T09:18:45.717

0

The DATE function is used to extract a date from a string, thus it expects you to pass a parameter to it.

If you want to get today's date use the CURRENT_DATE function instead:

(CURRENT_DATE()-Tanggalpjm)+1

Also, your code is vulnerable to SQL Injection. Using regular string concatenation to build queries has been discouraged for many years. Look into how to use SQL Parameters.

edited Dec 09 '18 at 09:18

answered Dec 09 '18 at 09:13

Visual Vincent

18,045
5
28
75

now error FUNCTION dbperpustakaan.IFF does not exist. then i changed it to IF then have error : Unknown column 'LMPINJAM' in 'field list' – نور أنيساء Dec 09 '18 at 09:42
@نورأنيساء : I have no idea what `LMPINJAM` is supposed to be so I can't help you with that, but based on the images in your question I'd say that the error message is accurate: There is no column called `LMPINJAM`. – Visual Vincent Dec 09 '18 at 12:02

how to change syntax SQL access to phpmyadmin SQL/SQL server

1 Answers1