I want to create a reporting software in which date is retrieved on the date specified by the user through datepicker tool in user form. I want to know how to implement the code in order to get the result.
This is my code... Please help me. I'm new to this
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Private conn As New SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
conn.ConnectionString = "Data Source=ROG\SQLEXPRESS;Initial Catalog=GKEAPL;Integrated Security=True;"
conn.Open()
MsgBox("Connected")
Catch ex As Exception
MsgBox("Could Not connect")
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim officeexcel As New Microsoft.Office.Interop.Excel.Application
officeexcel = CreateObject("Excel.Application")
Dim workbook As Object = officeexcel.Workbooks.Add("D:\GKEAPL\project 1\gk format.xltx")
officeexcel.Visible = True
Dim da As New SqlDataAdapter
Dim ds As New DataSet
da = New SqlDataAdapter("SELECT FeedWaterTankLevelFWST101,
FeedFlowFT101,
ClearWaterTankLevelCWST201,
TMFilPressurePT201,
TMFolPressurePT202,
HPPilPressurePT203,
MembraneilPressurePT204,
MembraneolPressurePT205,
PermeateFlowFT201,
RejectFlowFT202
FROM DATA1 WHERE(DATEnTIME >='2020-12-18 11:06:30.000' AND DATEnTIME <= '2020-12-19 10:07:31.000')", conn)
da.Fill(ds, "DATA1")
For i As Integer = 0 To ds.Tables("DATA1").Rows.Count - 1
With officeexcel
.Range("Sheet2!B" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(0).ToString
.Range("Sheet2!C" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(1).ToString
.Range("Sheet2!D" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(2).ToString
.Range("Sheet2!E" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(3).ToString
.Range("Sheet1!F" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(4).ToString
.Range("Sheet1!G" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(5).ToString
.Range("Sheet1!H" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(6).ToString
.Range("Sheet1!I" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(7).ToString
.Range("Sheet1!J" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(8).ToString
.Range("Sheet1!K" + (i + 7).ToString).Value = ds.Tables("DATA1").Rows(i).Item(9).ToString
End With
Next
officeexcel = Nothing
workbook = Nothing
Catch ex As Exception
End Try
End Sub
Private Sub Report_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DateTimePicker1.CustomFormat = "YYYY-MMMM-DD"
End Sub
End Class
Do not open a connection in Form.Load. Connections are precious resources. Keep them local to the method where they are used. Open just before the .Execute... line and close as soon as possible.
If you look at the SQLConnection class in MS docs https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=dotnet-plat-ext-3.1 you will see a .Dispose method. When you see that, it means it should be called to release unmanaged resources used by the class. Luckily, vb.net provides Using...End Using blocks to handle this for us.
I moved your data access code to a separate function. As much as possible a method should only be doing one thing.
In your sql string WHERE clause I changed your literals to parameters and used BETWEEN.
I think you have problems with the Excel code too but your question was about data access. Briefly, don't Dim workbook as Object, Range is not a member of Excel.Application and turn On Option Strict now and always.
Private Function GetTankData() As DataTable
Dim dt As New DataTable
Dim strSql = "SELECT FeedWaterTankLevelFWST101,
FeedFlowFT101,
ClearWaterTankLevelCWST201,
TMFilPressurePT201,
TMFolPressurePT202,
HPPilPressurePT203,
MembraneilPressurePT204,
MembraneolPressurePT205,
PermeateFlowFT201,
RejectFlowFT202
FROM DATA1 WHERE DATEnTIME BETWEEN @StartDate AND @EndDate"
Using conn As New SqlConnection("Data Source=ROG\SQLEXPRESS;Initial Catalog=GKEAPL;Integrated Security=True;"),
cmd As New SqlCommand(strSql, conn)
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = DateTimePicker1.Value
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DateTimePicker2.Value
conn.Open()
dt.Load(cmd.ExecuteReader)
End Using 'Closes and disposes the connection and command
Return dt
End Function
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim officeexcel As New Microsoft.Office.Interop.Excel.Application
officeexcel = CreateObject("Excel.Application")
Dim workbook As Object = officeexcel.Workbooks.Add("D:\GKEAPL\project 1\gk format.xltx")
Try
officeexcel.Visible = True
Dim dt = GetTankData()
For i As Integer = 0 To dt.Rows.Count - 1
With officeexcel
.Range("Sheet2!B" + (i + 7).ToString).Value = dt.Rows(i).Item(0).ToString
.Range("Sheet2!C" + (i + 7).ToString).Value = dt.Rows(i).Item(1).ToString
.Range("Sheet2!D" + (i + 7).ToString).Value = dt.Rows(i).Item(2).ToString
.Range("Sheet2!E" + (i + 7).ToString).Value = dt.Rows(i).Item(3).ToString
.Range("Sheet1!F" + (i + 7).ToString).Value = dt.Rows(i).Item(4).ToString
.Range("Sheet1!G" + (i + 7).ToString).Value = dt.Rows(i).Item(5).ToString
.Range("Sheet1!H" + (i + 7).ToString).Value = dt.Rows(i).Item(6).ToString
.Range("Sheet1!I" + (i + 7).ToString).Value = dt.Rows(i).Item(7).ToString
.Range("Sheet1!J" + (i + 7).ToString).Value = dt.Rows(i).Item(8).ToString
.Range("Sheet1!K" + (i + 7).ToString).Value = dt.Rows(i).Item(9).ToString
End With
Next
Finally
officeexcel = Nothing
workbook = Nothing
End Try
End Sub