CODEWITHSUNDEEP
vb.net
GAVIOTA
GAVIOTA

Reputation: 7

Crazy randomize MS Access query

I am really exhausted.i have this query it work very very with my Sql server database but when i put it with my MS Access database i have an error ( The index (zero base) must be greater than or equal to zero and less than the size of the argument list ). Sql server query :

Dim sql As String = String.Format(" SELECT TOP {0} ID1,Team1,Team2,Team3 FROM (SELECT ROW_NUMBER() OVER(ORDER BY newid() ASC) AS F1, ID1, Team1, Team2 FROM Table1 ) as t1 INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY newid() ASC) AS F1,Team3 FROM Table2 )  as t2 on t1.f1 = t2.f1", rows)

MS Access query :

Dim sql As String = String.Format(" SELECT TOP (0) ID1,Team1,Team2,Team3 FROM (SELECT ROW_NUMBER() OVER(ORDER BY {" & Guid.NewGuid().ToString & "} ASC) AS F1,ID1,Team1,Team2 from Table1) as t1 INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY {" & Guid.NewGuid().ToString & "} ASC) AS F1, Team3 FROM Table2 ) as t2 on t1.f1 = t2.f1 ", rows)

My code in Form1 Load :

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Conn_Randomize()
    InfoCommand = New OleDbCommand("SELECT * FROM Table1 ORDER BY Team2", Conn_Randomize)
    InfoAdapter = New OleDbDataAdapter()
    InfoAdapter.SelectCommand = InfoCommand
    InfoTable = New DataTable()
    InfoAdapter.Fill(InfoTable)
    DataGridView1.DataSource = InfoTable
End Sub

My code for input number of rows and columns i want to display in DataGridView2 with NumericupDown1 and 2 and randomize

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Conn_Randomize()
    Dim rows As Integer
    If Not Integer.TryParse(NumericUpDown1.Value, rows) Then
        MsgBox("NUMBER NOT AVAILABLE", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error")
        NumericUpDown1.Value = ""
        NumericUpDown1.Focus()
        Exit Sub
    End If
    If NumericUpDown2.Value = 2 Then
        Dim sql As String = String.Format("SELECT Top {0} ID1, Team1, Team2 FROM Table1  ORDER BY RND(-(100000*ID1)*Time())", rows)
        InfoCommand = New OleDbCommand(sql, Conn_Randomize)
        InfoAdapter = New OleDbDataAdapter()
        InfoAdapter.SelectCommand = InfoCommand
        InfoTable = New DataTable()
        InfoAdapter.Fill(InfoTable)
        DataGridView2.DataSource = InfoTable
    End If
    If NumericUpDown2.Value = 3 Then
        Dim sql As String = String.Format(" SELECT TOP {0} ID1,Team1,Team2,Team3 FROM (SELECT ROW_NUMBER() OVER(ORDER BY {" & Guid.NewGuid().ToString & "} ASC) AS F1,ID1,Team1,Team2 from Table1) as t1 INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY {" & Guid.NewGuid().ToString & "} ASC) AS F1, Team3 FROM Table2 ) as t2 on t1.f1 = t2.f1 ", rows)
        InfoCommand = New OleDbCommand(sql, Conn_Randomize)
        InfoAdapter = New OleDbDataAdapter()
        InfoAdapter.SelectCommand = InfoCommand
        InfoTable = New DataTable()
        InfoAdapter.Fill(InfoTable)
        DataGridView2.DataSource = InfoTable
    End If
End Sub

Upvotes: 0

Views: 66

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54457

Did you actually have a look at what sql contains after that second code snippet is executed? Does it make sense?

The first code snippet is using newid, which is a T-SQL function, which means that the database is going to generate a new uniqueidentifier for each row and use them to sort the data. How could the second code snippet do the equivalent? It's calling NewGuid in the VB code once only and concatenating the result into the SQL code. You're lucky that that is even valid syntax but the result is not going to be that each record has a different value to be sorted by.

You need the database to generate a value for each record that it can use as a sort key and the way to generate random numbers in Access SQL is with Rnd. I thought that you were already using that days ago.

Also, while I haven't checked, I'm not sure that ROW_NUMBER is supported in Access either. It wasn't added to SQL Server all that long ago.

Upvotes: 2

Related Questions