ComboBox not pulling SQL Statement

daveofgv

Well-known member
Joined
Sep 17, 2008
Messages
218
Location
Dallas, TX
Programming Experience
1-3
Hello Everyone -

I have several types of SQL statements in my vb.net application that work perfectly, however, one does not. I have three tables that I am trying to pull from and retrieve data into one datagrid view. Can anyone look at the below code and see if there are any issues on why I cannot pull data from the SQL database and view it into the datagrid?

***** This is the only code I need help with *****
VB.NET:
  da = New OleDbDataAdapter("Select pvb.BATCHID AS [BATCH ID], pvb.NAME, pvb.JOBSTART AS [JOB START], pvb.SIZE, pvb.DOCUMENTCOUNT AS [DOCUMENT COUNT], pvb.PAGECOUNT AS [PAGE COUNT], pvb.IMAGECOUNT AS [IMAGE COUNT], pvj.JOBID AS [JOB ID], SUM(pvs.VALUE) AS [Character Count] 
            FROM PVCAP_BATCH_1 pvb 
            INNER JOIN PVCAP_JOB_1 pvj ON PVCAP_BATCH_1.JOBID = PVCAP_JOB_1.JOBID 
            INNER JOIN PVCAP_BATCHSTATISTIC_1 pvs ON PVCAP_BATCH_1.BATCHID = PVCAP_BATCHSTATISTIC_1.BATCHID 
            WHERE PVCAP_BATCH_1.ISDELETED = 'false' AND PVCAP_BATCHSTATISTIC_1.STATISTICTYPE = 'PVCAP_CharactersSaved_NoMM' AND PVCAP_JOB_1.NAME like '%" & ComboBoxEx1.SelectedItem & "%' GROUP BY pvb.BATCHID, pvb.NAME, pvb.JOBSTART, pvb.SIZE, pvb.DOCUMENTCOUNT, pvb.PAGECOUNT, pvb.IMAGECOUNT, pvj.JOBID", con)


This is starting to be a nightmare on why I cannot get the above statement to work :(

The below code is where I have a value from a textbox and pull the same data - and it works, but the above code does not (which really is the same code)

VB.NET:
       da = New OleDbDataAdapter("Select pvb.BATCHID AS [BATCH ID], pvb.NAME, pvb.JOBSTART AS [JOB START], pvb.SIZE, pvb.DOCUMENTCOUNT AS [DOCUMENT COUNT], pvb.PAGECOUNT AS [PAGE COUNT], pvb.IMAGECOUNT AS [IMAGE COUNT], pvj.JOBID AS [JOB ID], SUM(pvs.VALUE) AS [Character Count] 
            FROM PVCAP_BATCH_1 pvb 
            INNER JOIN PVCAP_JOB_1 pvj ON pvb.JOBID = pvj.JOBID 
            INNER JOIN PVCAP_BATCHSTATISTIC_1 pvs ON pvb.BATCHID = pvs.BATCHID 
            WHERE pvb.ISDELETED = 'false' AND pvs.STATISTICTYPE = 'PVCAP_CharactersSaved_NoMM' AND pvs.VALUE <> 0 AND pvb.BATCHID like '%" & TextBoxX1.Text & "%' GROUP BY pvb.BATCHID, pvb.NAME, pvb.JOBSTART, pvb.SIZE, pvb.DOCUMENTCOUNT, pvb.PAGECOUNT, pvb.IMAGECOUNT, pvj.JOBID", con)

The complete combo box code where the first select statement is for is:

VB.NET:
Try


            con.Open()


            Dim dt As New DataTable
            Dim ds As New DataSet
            ds.Tables.Add(dt)
            Dim da As New OleDbDataAdapter
            ' Try
            'da = New OleDbDataAdapter("Select PVCAP_BATCH_1.BATCHID, PVCAP_BATCH_1.NAME, PVCAP_BATCH_1.JOBSTART, PVCAP_BATCH_1.SIZE, PVCAP_BATCH_1.DOCUMENTCOUNT, PVCAP_BATCH_1.PAGECOUNT, PVCAP_BATCH_1.IMAGECOUNT, PVCAP_BATCH_1.JOBID, PVCAP_BATCHSTATISTIC_1.VALUE,  FROM PVCAP_BATCH_1 INNER JOIN PVCAP_JOB_1 ON PVCAP_BATCH_1.JOBID = PVCAP_JOB_1.JOBID INNER JOIN PVCAP_BATCHSTATISTIC_1 ON PVCAP_BATCH_1.BATCHID = PVCAP_BATCHSTATISTIC_1.BATCHID WHERE PVCAP_BATCH_1.ISDELETED = 'false' AND PVC_BATCHSTATISTIC_1.STATISTICTYPE = 'PVCAP_CharactersSaved' AND PVCAP_BATCH_1.BATCHID LIKE '%" & TextBox1.Text & "%'", con)
            ' da = New OleDbDataAdapter("Select pv.BATCHID, pv.NAME, pv.JOBSTART, pv.SIZE, pv.DOCUMENTCOUNT, pv.PAGECOUNT, pv.IMAGECOUNT, pv.JOBID, pv.VALUE,  FROM PVCAP_BATCH_1 pv INNER JOIN PVCAP_JOB_1 ON PVCAP_BATCH_1.JOBID = PVCAP_JOB_1.JOBID INNER JOIN PVCAP_BATCHSTATISTIC_1 ON PVCAP_BATCH_1.BATCHID = PVCAP_BATCHSTATISTIC_1.BATCHID WHERE PVCAP_BATCH_1.ISDELETED = 'false' AND PVC_BATCHSTATISTIC_1.STATISTICTYPE = 'PVCAP_CharactersSaved' AND PVCAP_BATCH_1.BATCHID LIKE '%" & TextBox1.Text & "%'", con)

            ''''da = New OleDbDataAdapter("Select PVCAP_BATCH_1.BATCHID, PVCAP_BATCH_1.NAME, PVCAP_BATCH_1.JOBSTART, PVCAP_BATCH_1.SIZE, PVCAP_BATCH_1.DOCUMENTCOUNT, PVCAP_BATCH_1.PAGECOUNT, PVCAP_BATCH_1.IMAGECOUNT, PVCAP_BATCH_1.JOBID, PVCAP_BATCHSTATISTIC_1.VALUE FROM PVCAP_BATCH_1 INNER JOIN PVCAP_JOB_1 ON PVCAP_BATCH_1.JOBID = PVCAP_JOB_1.JOBID INNER JOIN PVCAP_BATCHSTATISTIC_1 ON PVCAP_BATCH_1.JOBID = PVCAP_BATCHSTATISTIC_1.JOBID WHERE PVCAP_BATCH_1.ISDELETED = 'false' and PVCAP_BATCHSTATISTIC_1.STATISTICTYPE = 'PVCAP_CharactersSaved' AND BATCHID like '%" & TextBox1.Text & "%'", con)
            ''''da = New OleDbDataAdapter("Select pvb.BATCHID, pvb.NAME, pvb.JOBSTART, pvb.SIZE, pvb.DOCUMENTCOUNT, pvb.PAGECOUNT, pvb.IMAGECOUNT, pvj.JOBID, pvs.STATISTICTYPE, pvs.VALUE AS CharCount FROM PVCAP_BATCH_1 pvb INNER JOIN PVCAP_JOB_1 pvj ON pvb.JOBID = pvj.JOBID INNER JOIN PVCAP_BATCHSTATISTIC_1 pvs ON pvb.JOBID = pvs.JOBID WHERE pvb.ISDELETED = 'false' AND pvs.STATISTICTYPE = 'PVCAP_CharactersSaved_NoMM' AND pvb.BATCHID like '%" & TextBox1.Text & "%'", con)
            '''' go back to da = New OleDbDataAdapter("Select pvb.BATCHID AS [BATCH ID], pvb.NAME, pvb.JOBSTART AS [JOB START], pvb.SIZE, pvb.DOCUMENTCOUNT AS [DOCUMENT COUNT], pvb.PAGECOUNT AS [PAGE COUNT], pvb.IMAGECOUNT AS [IMAGE COUNT], pvj.JOBID AS [JOB ID], pvs.STATISTICTYPE AS [STATISTIC TYPE], SUM(pvs.VALUE) AS [Character Count] FROM PVCAP_BATCH_1 pvb INNER JOIN PVCAP_JOB_1 pvj ON pvb.JOBID = pvj.JOBID INNER JOIN PVCAP_BATCHSTATISTIC_1 pvs ON pvb.BATCHID = pvs.BATCHID WHERE pvb.ISDELETED = 'false' AND pvs.STATISTICTYPE = 'PVCAP_CharactersSaved_NoMM' AND pvs.VALUE <> 0 AND pvb.BATCHID like '%" & TextBox1.Text & "%'", con)

            ' da = New OleDbDataAdapter("Select pvb.BATCHID AS [BATCH ID], pvb.NAME, pvb.JOBSTART AS [JOB START], pvb.SIZE, pvb.DOCUMENTCOUNT AS [DOCUMENT COUNT], pvb.PAGECOUNT AS [PAGE COUNT], pvb.IMAGECOUNT AS [IMAGE COUNT], pvj.JOBID AS [JOB ID], pvs.STATISTICTYPE AS [STATISTIC TYPE], pvs.VALUE AS [Character Count] 
            '                        FROM PVCAP_BATCH_1 pvb INNER JOIN PVCAP_JOB_1 pvj ON pvb.JOBID = pvj.JOBID INNER JOIN PVCAP_BATCHSTATISTIC_1 pvs ON pvb.BATCHID = pvs.BATCHID 
            '                       WHERE pvb.ISDELETED = 'false' AND pvs.STATISTICTYPE = 'PVCAP_CharactersSaved_NoMM' AND pvs.VALUE <> 0 AND pvb.BATCHID like '%" & TextBox1.Text & "%'", con)
            da = New OleDbDataAdapter("Select pvb.BATCHID AS [BATCH ID], pvb.NAME, pvb.JOBSTART AS [JOB START], pvb.SIZE, pvb.DOCUMENTCOUNT AS [DOCUMENT COUNT], pvb.PAGECOUNT AS [PAGE COUNT], pvb.IMAGECOUNT AS [IMAGE COUNT], pvj.JOBID AS [JOB ID], SUM(pvs.VALUE) AS [Character Count] 
            FROM PVCAP_BATCH_1 pvb 
            INNER JOIN PVCAP_JOB_1 pvj ON pvb.JOBID = pvj.JOBID 
            INNER JOIN PVCAP_BATCHSTATISTIC_1 pvs ON pvb.BATCHID = pvs.BATCHID 
            WHERE pvb.ISDELETED = 'false' AND pvs.STATISTICTYPE = 'PVCAP_CharactersSaved_NoMM' AND pvs.VALUE <> 0 AND pvb.BATCHID like '%" & TextBoxX1.Text & "%' GROUP BY pvb.BATCHID, pvb.NAME, pvb.JOBSTART, pvb.SIZE, pvb.DOCUMENTCOUNT, pvb.PAGECOUNT, pvb.IMAGECOUNT, pvj.JOBID", con)

            da.Fill(dt)

            GridControl1.DataSource = dt.DefaultView


        Catch ex As Exception
            MessageBox.Show("Cannot find your search results. Either the file is not in the database or your connection string / credentials are not correct!")
        End Try


        con.Close()

        ToolStripStatusLabel2.Text = DataGridViewX1.Rows.Count.ToString()

and the form load code:

VB.NET:
 con.ConnectionString = "Provider=SQLOLEDB;Data Source=" & My.Settings.servername & ";Persist Security Info=True;User ID=" & My.Settings.databaseuser & ";Password=" & My.Settings.databasepass & ";Initial Catalog=" & My.Settings.databasename & ""
        '''''con.ConnectionString = String.Format("Provider=SQLOLEDB;Data Source={0};Persist Security Info=True;User ID={1};Password={2};Initial Catalog={3}", My.Settings.servername, My.Settings.databaseuser, My.Settings.databasepass, My.Settings.databasename)


        LabelX3.Text = My.User.Name
        Try
            con.Open()


            Dim dt As New DataTable
            Dim ds As New DataSet
            ds.Tables.Add(dt)
            Dim da As New OleDbDataAdapter

          da = New OleDbDataAdapter("Select NAME, JOBID from PVCAP_JOB_1", con)
            da.Fill(dt)

            'ComboBoxEx1.DataSource = dt.DefaultView
            For i As Integer = 0 To dt.Rows.Count - 1


                ComboBoxEx1.Items.Add(dt.Rows(i).Item("NAME").ToString)

            Next

            con.Close()

            'ToolStripStatusLabel2.Text = DataGridViewX2.Rows.Count.ToString()


        Catch ex As Exception
            'MessageBox.Show("Cannot find your search results. Either the file is not in the database or your connection string / credentials are not correct!")
        End Try

If anyone can help on the above top select statement for the combox would be great!

Thanks in advanced

daveofgv
 
Last edited:

daveofgv

Well-known member
Joined
Sep 17, 2008
Messages
218
Location
Dallas, TX
Programming Experience
1-3
Well... It retrieves no results even though it should. I have the same code in button clicks associated with text boxes and it works fine, so I know the connection is valid.

I am figuring the combo box has to have another piece that I am missing.

Any thoughts?

Thanks in advanced
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,400
Location
Sydney, Australia
Programming Experience
10+
The first thing to do would be to test the value returned by your Fill call. If it's zero then that means that there are no records that match your query. If it's not zero then your query is retrieving records so the issue is elsewhere so you need to look for it elsewhere.
 

daveofgv

Well-known member
Joined
Sep 17, 2008
Messages
218
Location
Dallas, TX
Programming Experience
1-3
Here is where I am:

VB.NET:
  da = New OleDbDataAdapter("Select pvb.BATCHID AS [BATCH ID], pvb.NAME, pvb.JOBSTART AS [JOB START], pvb.SIZE, pvb.DOCUMENTCOUNT AS [DOCUMENT COUNT], pvb.PAGECOUNT AS [PAGE COUNT], pvb.IMAGECOUNT AS [IMAGE COUNT], pvj.JOBID AS [JOB ID], SUM(pvs.VALUE) AS [Character Count] 
            FROM PVCAP_BATCH_1 pvb 
            WHERE PVCAP_BATCH_1.ISDELETED = 'false' AND PVCAP_BATCHSTATISTIC_1.STATISTICTYPE = 'PVCAP_CharactersSaved_NoMM' AND PVCAP_JOB_1.NAME like '%" & ComboBoxEx1.SelectedItem & "%'", con)

The above code appears to work - so the problem looks like it is in the INNER JOINS / GROUP sections..... Like I said previously - the INNER JOINS and GROUP work with text boxes, but not the combo box.

Any suggestions?
 

daveofgv

Well-known member
Joined
Sep 17, 2008
Messages
218
Location
Dallas, TX
Programming Experience
1-3
Looks like I have the original select statement as .SELECTEDITEM and .SELECTEDTEXT might be the issue.

Thanks for your help so far and I will test this out more... :)
 

daveofgv

Well-known member
Joined
Sep 17, 2008
Messages
218
Location
Dallas, TX
Programming Experience
1-3
looks like this is working on my work laptop - but thought it didn't work on my home PC:

VB.NET:
 da = New OleDbDataAdapter("Select pvb.BATCHID AS [BATCH ID], pvb.NAME, pvb.JOBSTART AS [JOB START], pvb.SIZE, pvb.DOCUMENTCOUNT AS [DOCUMENT COUNT], pvb.PAGECOUNT AS [PAGE COUNT], pvb.IMAGECOUNT AS [IMAGE COUNT], pvj.JOBID AS [JOB ID], SUM(pvs.VALUE) AS [Character Count] 
                            FROM PVCAP_BATCH_1 AS pvb 
		            INNER JOIN PVCAP_JOB_1 pvj ON pvb.JOBID = pvj.JOBID 
			    INNER JOIN PVCAP_BATCHSTATISTIC_1 pvs ON pvb.BATCHID = pvs.BATCHID 
			    WHERE pvb.ISDELETED = 'false' AND pvs.STATISTICTYPE = 'PVCAP_CharactersSaved_NoMM' AND pvs.VALUE <> 0 AND pvb.JOBID like '%" & ComboBoxEx1.SelectedText & "%' 
                            GROUP BY pvb.BATCHID, pvb.NAME, pvb.JOBSTART, pvb.SIZE, pvb.DOCUMENTCOUNT, pvb.PAGECOUNT, pvb.IMAGECOUNT, pvj.JOBID", con)

Not sure why one computer will work and not the other, however, at home I have DevExpress and I am using the Gridcontrol instead of Datagridview... wonder if there may be a setting in the properties that is causing the failure :/
 
Top Bottom