Click here to Skip to main content
14,486,715 members
Rate this:
Please Sign up or sign in to vote.
See more:
This Query command execute in SQL Server no issue successfully run no error
when i am applying in vb.net this error shows.
(Value of type 'string cannot be converted to SQL Command') ?

What I have tried:

SQL SERVER

SELECT V_Date, V_Time,(SELECT  Max(ptd.Active) 
FROM Master AS ptd WHERE   ptd.Invoice_No = pt.Invoice_No) AS "Activate" 
FROM P_Detail AS pt where pt.V_Date = '2020-03-20' and pt.Invoice_No = '100'


VB.NET

Dim da = New SqlDataAdapter("SELECT V_Date, V_Time,("Select  Max(ptd.Active), FROM Master As ptd WHERE   ptd.Invoice_No = pt.Invoice_No) As "Activate" FROM P_Detail As pt where pt.V_Date = '" & PDate & "' and pt.Invoice_No = '" & MNumber & "'", SQLCon)
Posted
Updated 21-Mar-20 0:38am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Well no. What did you expect?
That isn't a valid string, or even close - if you take out the DataAdapter related bits you can see it more easily:
Dim s As String = "SELECT V_Date, V_Time,("Select  Max(ptd.Active), FROM Master As ptd WHERE   ptd.Invoice_No = pt.Invoice_No) As "Activate" FROM P_Detail As pt where pt.V_Date = '" & PDate & "' and pt.Invoice_No = '" & MNumber & "'"

Try cutting down your use of double quotes:
Dim sql As String = "SELECT V_Date, " &
                           "V_Time, " &
                           "(SELECT  Max(ptd.Active), " &
                            "FROM Master As ptd " &
                            "WHERE ptd.Invoice_No = pt.Invoice_No) As [Activate] " &
                    "FROM P_Detail As pt " &
                    "WHERE pt.V_Date = '" & pDate & "' " &
                      "AND pt.Invoice_No = '" & MNumber & "'"
And use that instead.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

There are several syntax errors in your VB code, and the query can be optimized by joining tables. Moreover, you should not build your SQL queries by concatenating strings, because it leaves your code open to SQL injection attacks; better use parameterized queries instead. With an adapter, it is probably better to first build the command object, and use it to build the adapter:
Using cmd As New SqlCommand("SELECT pt.V_Date, pt.V_Time, Max(ptd.Active) As Activate FROM P_Detail As pt INNER JOIN Master As ptd ON ptd.Invoice_No = pt.Invoice_No WHERE pt.V_Date = @pdate and pt.Invoice_No = @invoiceno", SQLCon)

   cmd.Parameters.Add("@pdate", DbType.DateTime).Value = PDate
   cmd.Parameters.Add("@invoiceno", DbType.String).Value = MNumber

   Using da As New SqlDataAdapter(cmd)

      '' ...

   End Using

End Using
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

Dim da = New SqlDataAdapter("SELECT V_Date, V_Time,("Select  Max(ptd.Active), ...
'              The error is the double quote here   ^
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100