Use sql query to populate a form

[MICKI0220]

First let me say, I know barely enough vb.net to open the program. I have used the data connection wizard to create the data connection to my sql 2008 backend database. Now I want to bind the table to a multi-table query so I can add and update the data. When I put the sql statement in the forms open event sub procedure it acts like it is a select case statement instead of a query. How can I code this correctly for it to populate.


SELECT tblAccounting.PaymentTerms, tblAccounting.[FIELD PAYROLLS], tblAccounting.[TAX EXEMPT], tblAccounting.[TAX APPLIES], tblCustomer.*, tblEngineerCompany.*,
tblJobDetails.*, tblJobReqs.*, tblSubcontractDetails.*, tblDatesHours.*
FROM tblJobCustomerID INNER JOIN
tblCustomer ON tblJobCustomerID.CustomerID = tblCustomer.CustomerID INNER JOIN
tblJobDetails ON tblJobCustomerID.JobNoCus = tblJobDetails.JobNo INNER JOIN
tblDatesHours ON tblJobDetails.JobNo = tblDatesHours.JobNoDH INNER JOIN
tblAccounting ON tblJobDetails.JobNo = tblAccounting.JobNoAcct INNER JOIN
tblJobEngineerID ON tblJobDetails.JobNo = tblJobEngineerID.JobNoEng INNER JOIN
tblEngineerCompany ON tblJobEngineerID.EngineerCompanyID = tblEngineerCompany.EngineerCompanyID INNER JOIN
tblJobReqs ON tblJobDetails.JobNo = tblJobReqs.JobNoReq CROSS JOIN
tblSubcontractDetails()

 

[mansii]

Hi.
The question is not clear enough for me. I believe that you didn't find any trouble in populating the query result to the form. So, I assume that the problem is how to update the edited data.
But first, you forgot to mention how you updated the database.
According to what I have experienced in the past (using the MS Access), we cannot update the database using the DataAdapter object for complex (joined) queries. Not sure about MySQL.

Regards.

 

[MICKI0220]

Actually I am having problem populating the fields and that is what my question is. I put this code in the form open event and wanted it to populate my form however it doesn.t I created different code....Below. and I run into syntax error. The question is what is wrong with my code in attempting to populate the form. Or can I run it and get some sort of text display to show that it does work and that the problem may be on my form end.

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim CN As New SqlConnection
CN.ConnectionString = "DATA SOURCE = 172.16.1.145; INITIAL CATALOG = JOBS; Integrated Security = True"
Dim CNstring As String = _
"DATA SOURCE = \\CTIDATA; INITIAL CATALOG = JOBS; " & _
"Integrated Security = True"



Dim Cmd As New SqlCommand
Cmd.Connection = CN
Cmd.CommandText = _
"Select JobMaster.JobNo, JobMaster.JobName, Jobmaster.Jobstate " & _
"From JobMaster "



CN.Open()
Dim count As Integer
Dim Reader As SqlDataReader
Reader = Cmd.ExecuteReader
While Reader.Read
Str &= "JobNo" & vbTab
(JobName) & vbTab
(JobState) & vbTab
& vbCrLf
Count += 1


End While
Debug.WriteLine(vbCrLf & vbCrLf & "read" & count.ToString & _
"rows:" & vbCrLf & vbCrLf)
If CN.State = ConnectionState.Open Then
CN.Close()
End If

End Sub
End Class





It errors in the while reader read area.

 

[Christineeve]

Hi Micki,
I know there are a lot of ways to accomplish things. I'm sure the better developers here will comment about the code I'm about to post but this is a somewhat clear example of what I did to populate a form (actually a panel on a tab, but I think close enough).

This is done with VB.Net 2008/2010 with an M.S. Access 2007 back-end.

I used [ code ] (no spaces) to create a code window.

 Public Shared Sub PopulateTaxes()
                Dim cmdPopTaxes As New OleDbCommand (replace this for MySQL)
        Dim daPopTaxes As New OleDbDataAdapter
        Dim dsPopTaxes As New DataSet
        Dim dtPopTaxes As New DataTable
        Dim m_rowPosition As Integer = 0
       
  Try
            Using oConn As New OleDbConnection
                Dim DataSource As String = frmAhmed.gMyDataSource

                'create a connection string
                oConn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataSource & ";User ID=Admin;Password=;")
                oConn.Open()

                If oConn.State = ConnectionState.Open Then
                    Dim sSQL = "SELECT * FROM tblFederalTaxes Where fk_EmpInfoID =" & "('" & Trim(frmAhmed.txtEmpID.Text) & "')"
                    'Select all from the database and determine if the txtEmplyNo exists.
                    cmdPopTaxes = oConn.CreateCommand
                    cmdPopTaxes.CommandText = sSQL
                    daPopTaxes.SelectCommand = cmdPopTaxes
                    daPopTaxes.Fill(dsPopTaxes, "PopTaxes")
                    dtPopTaxes = dsPopTaxes.Tables("PopTaxes")


                    If dtPopTaxes.Rows.Count > 0 Then
                        frmAhmed.txtExemptions.Text = dtPopTaxes.Rows(m_rowPosition)("empExemps").ToString
                        frmAhmed.cboFileStatus.Text = dtPopTaxes.Rows(m_rowPosition)("empFileStat").ToString
                        frmAhmed.txtAddAmtHold.Text = dtPopTaxes.Rows(m_rowPosition)("empaddAmount").ToString
                        frmAhmed.cboAddPercentHold.Text = dtPopTaxes.Rows(m_rowPosition)("empaddPercent").ToString
                        frmAhmed.ckFICAExempt.Checked = dtPopTaxes.Rows(m_rowPosition)("empFICAExemp").ToString
                        frmAhmed.ckTaxExempt.Checked = dtPopTaxes.Rows(m_rowPosition)("empFICAExemp").ToString
                        frmAhmed.cboEIC.Text = dtPopTaxes.Rows(m_rowPosition)("empEIC").ToString
                        frmAhmed.mtxtEffDate.Text = dtPopTaxes.Rows(m_rowPosition)("emptaxEffDate").ToString
                    Else
                                                Exit Sub
                        oConn.Close()

                    End If
                Else
                    MsgBox("oledbconnection failed to open")
                    MsgBox("connection state = " & oConn.State)
                End If

                oConn.Close()
                oConn.Dispose()
            End Using
            Exit Sub

        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Error!")
        End Try

    End Sub

(To close your code window you'd type [ / code ] no spaces)

So notice after I fill my data I have to tell the code what fields on the form to populate with what corresponding fields/columns from the data source.

I'm far from an expert but this template has worked well for me.
I hope this helps.