I’m having trouble with a SQL query in my VB.NET project. The query works fine in SQL Server Management Studio but fails in Visual Studio. Here’s what I’m trying to do:
Dim query As String = "SELECT u.UserID AS 'ID', u.FirstName + u.LastName AS 'FullName',
FORMAT(u.BirthDate, 'dd/MM/yyyy') AS 'DOB', u.Gender,
d.DepartmentName AS 'Department'
FROM Users u
JOIN Departments d ON u.DepartmentID = d.DepartmentID"
Dim cmd As New SqlCommand(query, connection)
When I run this, I get an error saying ‘Incorrect syntax near the keyword ON’. The query works without the JOIN part, so I think I’m formatting it wrong for VB.NET. Any ideas what I’m doing wrong here? How can I fix this JOIN syntax in my VB.NET code?
The issue you’re experiencing might be related to how VB.NET handles string concatenation in SQL queries. Try using string interpolation or concatenation operators to build your query string. Here’s a modified version that might work:
Dim query As String = $"SELECT u.UserID AS 'ID', u.FirstName + ' ' + u.LastName AS 'FullName',
FORMAT(u.BirthDate, 'dd/MM/yyyy') AS 'DOB', u.Gender,
d.DepartmentName AS 'Department'
FROM Users u
JOIN Departments d ON u.DepartmentID = d.DepartmentID"
Dim cmd As New SqlCommand(query, connection)
Notice the addition of a space between FirstName and LastName. Also, ensure your connection object is properly initialized and opened before executing the command. If the problem persists, consider using a parameterized query for better security and reliability.
hey max, sounds like ur havin a rough time with that query. have u tried using INNER JOIN instead of just JOIN? sometimes vb.net can be picky bout that. also, double-check ur connection string - might be connecting to a different db version that dont like that syntax. good luck mate!