When i run the program i always get this error. Can somebody help me why im getting this? I am transfering data from table1 to table2

Question

Syntax error (missing operator) in query expression 'SeriesNo (between 1and5'

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click        
    If rsa.State = 1 Then rsa.Close()

    rsa.Open("select * from Records where SeriesNo between" & Me.txtseriesfrom.Text & "and" & Me.txtseriesto.Text & "", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

    TextBox5.Text = txtseriesto.Text
    TextBox3.Text = txtseriesfrom.Text
    txtseriesto.Text = txtseriesto.Text + 1



    Do Until Val(txtseriesfrom.Text) >= Val(txtseriesto.Text)

        If rs.State = 1 Then rs.Close()
        rs.Open("select * from Transfer", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
        rs.AddNew()
        rs.Fields("SeriesNo").Value = txtseriesfrom.Text
        rs.Fields("Brand").Value = cbobrand.Text
        rs.Fields("Denomination").Value = cbodenomination.Text
        rs.Fields("Store").Value = cbostore.Text
        rs.Fields("User").Value = txtuser.Text
        rs.Fields("Date").Value = txtdate.Text
        rs.Update()

        txtseriesfrom.Text = txtseriesfrom.Text + 1
    Loop

    txtseriesto.Text = txtseriesto.Text - 1
    txtseriesfrom.Text = Val(TextBox3.Text)
    TextBox4.Text = TextBox3.Text

    TextBox5.Text = TextBox5.Text + 1
    Do Until Val(TextBox4.Text) >= Val(TextBox5.Text)
        rsa.Delete()
        rsa.MoveNext()
        TextBox4.Text = TextBox4.Text + 1
    Loop
    TextBox5.Text = TextBox5.Text - 1
    TextBox4.Text = Val(TextBox3.Text)

    MsgBox("Successfully Transfered!", vbInformation, "SAVED")


End Sub

Add spaces for "between" arguments: `Dim query = $"select * from Records where SeriesNo between {Me.txtseriesfrom.Text} and {Me.txtseriesto.Text}"`. — Fabio, May 11 '17 at 14:06
does `Series (between 1and5` look like valid SQL syntax to you? Is it what you were trying to glue together? — Ňɏssa Pøngjǣrdenlarp, May 11 '17 at 14:06
If you want us to help you, you need to expose more clearly what you want to do and how the program works. — Mederic, May 11 '17 at 14:59
Use parameters to avoid sql injection and to solve your error. — LarsTech, May 11 '17 at 15:40

Juanche · Answer 1 · 2017-05-11T15:06:22.473

I recommend you to save in integer variables the values of Me.txtseriesfrom.Text and Me.txtseriesto.Text then call it in the query. An example:

Dim txt_from as integer = Me.txtseriesfrom.Text

Dim txt_to as integer = Me.txtseriesto.Text

rsa.Open("select * from Records where SeriesNo between " & txt_from & " and " & txt_to & "", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

Or if the datatype of SeriesNo in your Database is Varchar:

You are missing the ' ' for texts values. The correct way should be:

rsa.Open("select * from Records where SeriesNo between '" & Me.txtseriesfrom.Text & "' and '" & Me.txtseriesto.Text & "'", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

Also replace all txtseriesfrom.Text and txtseriesto.Text to txt_from and txt_to variable respectively.

When i run the program i always get this error. Can somebody help me why im getting this? I am transfering data from table1 to table2

1 Answers1