• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Problem with sql statements.

Public Function GetUserEntries(ByVal userID As String) As DataSet
        Dim sql As New StringBuilder()
        sql.AppendLine("SELECT t.typeid as AppID, i.interviewid, t.Name AS Application, i.PrimaryUse AS [Primary Use], i.DateLastModified AS [Date Last Modified], ")
        sql.AppendLine(" (SELECT  COUNT(*) FROM   tbl_dotnet_attachments AS a WHERE  (i.InterviewID = InterviewID)) AS Attachements FROM  tbl_dotnet_interviews AS i INNER JOIN ")
        sql.AppendLine(" tbl_dotnet_types AS t ON i.AppID = t.TypeID WHERE(i.szName = " + userID + ")")
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter(sql.ToString(), conn)
        da.Fill(ds, "APPS")
        Return ds
    End Function

            sql.ToString()      
"SELECT t.typeid as AppID, i.interviewid, t.Name AS Application, i.PrimaryUse AS [Primary Use], i.DateLastModified AS [Date Last Modified],
 (SELECT  COUNT(*) FROM   tbl_dotnet_attachments AS a WHERE  (i.InterviewID = InterviewID)) AS Attachements FROM  tbl_dotnet_interviews AS i INNER JOIN
 tbl_dotnet_types AS t ON i.AppID = t.TypeID WHERE(i.szName = matt)
"      String


 exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Invalid column name 'matt'.

the problem is that i.szName is null so I still want the quire to work.
0
mathieu_cupryk
Asked:
mathieu_cupryk
  • 2
  • 2
1 Solution
 
jandromedaCommented:
Your code should change like this.

Public Function GetUserEntries(ByVal userID As String) As DataSet
        Dim sql As New StringBuilder()
        sql.AppendLine("SELECT t.typeid as AppID, i.interviewid, t.Name AS Application, i.PrimaryUse AS [Primary Use], i.DateLastModified AS [Date Last Modified], ")
        sql.AppendLine(" (SELECT  COUNT(*) FROM   tbl_dotnet_attachments AS a WHERE  (i.InterviewID = InterviewID)) AS Attachements FROM  tbl_dotnet_interviews AS i INNER JOIN ")
        sql.AppendLine(" tbl_dotnet_types AS t ON i.AppID = t.TypeID WHERE(i.szName = '" + userID + "')")
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter(sql.ToString(), conn)
        da.Fill(ds, "APPS")
        Return ds
    End Function
0
 
mathieu_cuprykAuthor Commented:
i think i need single quotes?
0
 
jandromedaCommented:
Yeah. Since it is a string.
0
 
mathieu_cuprykAuthor Commented:
cool
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now