Value of type 'string cannot be converted to SQL command'

Question

1.00/5 (1 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

SQL

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

VB

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 21-Mar-20 0:04am

Computechsoft

Updated 21-Mar-20 0:38am

phil.o

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2020-03-21T00:31:00

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:

VB

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:

VB

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.

phil.o · Accepted Answer · 2020-03-21T00:35:00

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:

VB

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

Patrice T · Accepted Answer · 2020-03-21T00:39:00

Solution 3

VB

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

Posted 21-Mar-20 0:39am

Patrice T

Value of type 'string cannot be converted to SQL command'

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0