vvic vvic - 1 year ago 69
Vb.net Question

vbnet select inside a select to a dt

I got this little code where I connect to a db, select the data and write the data to a file.

...

sqlquery = ("select field1 as Asl, field2 as nCuen , field3 as nfac , "" as contr from [2016cl]")

Using connection As SqlConnection = New SqlConnection("server=" & srvSQL & ";database=" & bdSQL & ";uid=" & usrSQL & ";password=" & pswSQL & ";")
connection.Open()
Using comm As SqlCommand = New SqlCommand(sqlquery, connection)
Dim rs As SqlDataReader = comm.ExecuteReader
Dim dt As DataTable = New DataTable
dt.Load(rs)

Call clCreateCSV.CreateCSVFile(dt, strFileNameDiario)
End Using
connection.Close()
End Using


.....

Public Shared Sub CreateCSVFile(dt As DataTable, strFilePath As String)
Dim sw As New StreamWriter(strFilePath, False, Encoding.UTF8)
Dim iColCount As Integer = dt.Columns.Count

For Each dr As DataRow In dt.Rows
For i As Integer = 0 To iColCount - 1
If Not Convert.IsDBNull(dr(i)) Then
sw.Write(dr(i).ToString())
End If
If i < iColCount - 1 Then
sw.Write(";")
End If
Next
sw.Write(sw.NewLine)
Next
sw.Close()
End Sub


I need to fill 4th value on the select ("" as contr) with field from another table which joins to another middle table.

field3 joins another table on GFac.Gfac2
the table Gfac joins the 3rd table on CCli.ccli1 --> this is the one I need on the query

Could just use inner join but, afaik it would only get the fields that validate the join.

how can i do this, writing ALL the data from [2016cl] and the field contr for each one of them if it exists?

Thanks in advance. If you need more info, just ask!

Answer Source

Use LEFT JOIN instead INNER JOIN.

For example: SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON ...

That query selects ALL rows from TABLE1. If a row in TABLE1 doesn't have a match record in TABLE2, then the TABLE2 fields contains NULL.

Hope that helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download