daveofgv
Well-known member
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 *****
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)
The complete combo box code where the first select statement is for is:
and the form load code:
If anyone can help on the above top select statement for the combox would be great!
Thanks in advanced
daveofgv
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: