none
how to fix error when use with statement sql server in vb.net ?

    Question

  • show error when connection :

     my code

    Try
              
                Dim connectionString As String = "Data Source=PC;Password=12345;Persist Security Info=True;User ID=sa;Initial Catalog=Data;Data Source=PC"
                Dim connection1 As New SqlConnection(connectionString)
                Dim sql1 As String = " with cte as (select * from ........) select [address]  , sum(CNT) as total from cte group by [address]
                Dim dataadapter1 As New SqlDataAdapter(sql1, connection1)
                Dim ds1 As New DataSet()
                connection1.Open()
                dataadapter1.Fill(ds1, "*")
                connection1.Close()
                Form1.DataGridView1.DataSource = ds1
                Form1.DataGridView1.DataMember = "*"
                Form1.Label2.Text = (ds1.Tables(0).Rows.Count)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

    Wednesday, March 29, 2017 11:04 AM

Answers

  • It should work but if not then create a stored procedure in the database and call it from your code.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 29, 2017 12:28 PM
    Moderator

All replies

  • That's because sql1 does not contain a valid SQL query; the error will be fixed when you provide a valid SQL query string.  Your question is one of SQL syntax, not VB language.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, March 29, 2017 11:37 AM
    Moderator
  • Your best option is to test the query in SQL-Server Management Studio, if you don't have Management studio then create a .sql file in your project (create a new text file, change the extension to .sql)

    Once you have done that, connect with the second box, run with the first box. Find the issue, fix it then use it in your code.

    Screenshot is within Visual Studio project.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 29, 2017 11:43 AM
    Moderator
  • thank you  but I'm use the query in sqlserver no problem and working

    when remove [with cte as (   ) ] then working statement in vb.net

    • Edited by srajmuneer Wednesday, March 29, 2017 11:53 AM
    Wednesday, March 29, 2017 11:51 AM
  • It should work but if not then create a stored procedure in the database and call it from your code.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 29, 2017 12:28 PM
    Moderator
  • Not that it is different but when you want to catch seperately the SQLException you can nest your catch, (with as last the general exception)

    Everything about it is on this page. 

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception(v=vs.110).aspx

     

    Success
    Cor

    Wednesday, March 29, 2017 12:36 PM
  • I would highly recommend putting your CTE query in a stored procedure and calling the stored procedure, instead of the SQL statement, from your VB/ADO.NET code.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, March 29, 2017 2:11 PM