VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,823 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I am not a programmer and won't claim to be one. I am trying to do a query and having a problem.
System.Data.SqlClient.SqlException: 'Incorrect syntax near '1'.'
Public Sub SearchData()
'Populates the Datagrid
Dim id As String = Date1.Value
Dim id2 As String = Date2.Value
Dim id3 As String = namecbx.SelectedValue
Dim sql1 As String = ""
Dim SQLConnectionstring As String = My.Settings.OFIData
sql1 = "SELECT TOP (100) PERCENT FullName, FunctionCode, DateTime FROM dbo.timeclockpunches WHERE (DateTime BETWEEN " & id & " AND " & id2 & ") AND (FullName =" & id3 & ")"
'---Standard Code for database connection
Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection(SQLConnectionstring)
Dim comm As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql1, conn)
Dim dataadapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(comm)
Dim ds As DataSet = New DataSet()
Dim RowIndex As Integer = 0
'---open the connection and fill the dataset---
conn.Open()
'---fill the dataset---
dataadapter.Fill(ds, "FullName") ' Error Happens Here
'---close the connection---
conn.Close()
'---bind to the DataGridView control---
vdgv.DataSource = ds
dataadapter.Dispose()
ds.Dispose()
conn.Dispose()
comm.Dispose()
vdgv.DataMember = "FullName"
If vdgv.RowCount >= 1 Then
'Column Names = "Between Quotes" will disable as column header
vdgv.Columns(0).HeaderCell.Value = "FullName"
vdgv.Columns(1).HeaderCell.Value = "FunctionCode"
vdgv.Columns(2).HeaderCell.Value = "DateTime"
'Column Visible
vdgv.Columns(0).Visible = True
vdgv.Columns(1).Visible = True
vdgv.Columns(2).Visible = True
'Column Width
vdgv.Columns(0).Width = 250
vdgv.Columns(1).Width = 100
vdgv.Columns(2).Width = 100
'Hides the Horizontal Scrollbar
vdgv.ScrollBars = ScrollBars.Vertical
'Does not allow users to add rows
vdgv.AllowUserToAddRows = False
End If
End Sub
Change:
sql1 = "SELECT TOP (100) PERCENT FullName, FunctionCode, DateTime FROM dbo.timeclockpunches WHERE (DateTime BETWEEN " & id & " AND " & id2 & ") AND (FullName =" & id3 & ")"
to:
sql1 = "SELECT TOP (100) PERCENT FullName, FunctionCode, DateTime FROM dbo.timeclockpunches WHERE (DateTime BETWEEN @start AND @end) AND (FullName = @name)"
Then after:
Dim comm As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql1, conn)
Add:
comm.Parameters.Add("@start", SqlDbType.Date).Value = Date1.Value
comm.Parameters.Add("@end", SqlDbType.Date).Value = Date2.Value
comm.Parameters.Add("@name", SqlDbType.NVarChar, 40).Value = namecbx.SelectedValue
Building an SQL string by inlining values as you tried to do is difficult to get right. It is a lot easier to use a parameterised commands. That is, you use these things starting with @ and then you defined with SqlCommand.Parameters.Add.