Is this SQL command executable?

[ernestwan]

Hi all,
Anyone can tell me is this coding in vb coding to call the SQL query is correct? Actually I am doing ASP.net, but i pass to backend to do th logic coding using vb.net. Here is the code,


Dim myConnection As OleDbConnection = New OleDbConnection(ConfigurationSettings.GetConfig("MyAppSettings")("connString"))
Dim sqlStr As String
Dim cm As OleDbCommand

Private Function mediaid() As Integer
Dim myDate As Date
Dim ds As OleDbDataReader
myDate = System.DateTime.Today.Date
cm = myConnection.CreateCommand
cm.CommandType = CommandType.Text

sqlStr = "Select * from mediaitem "
sqlStr = "if (select overrideind from webstation where webstationid=3) = 'y' "
sqlStr = sqlStr & "begin() "
sqlStr = sqlStr & "Select ms.setid,ms.setseq,ms.itemid,ms.menuid,mi.filename,mi.url "
sqlStr = sqlStr & "from mediamenuset ms inner join mediaitem mi on ms.itemid = mi.itemid "
sqlStr = sqlStr & "where ms.menuid = (select menuid from webstation where webstationid=3) "
sqlStr = sqlStr & "End "
sqlStr = sqlStr & "ELSE "
sqlStr = sqlStr & "BEGIN select ms.setid,ms.setseq,ms.itemid,ms.menuid,mi.filename,mi.url "
sqlStr = sqlStr & "from mediamenuset ms inner join mediaitem mi on ms.itemid = mi.itemid "
sqlStr = sqlStr & "where ms.menuid = (select wgrp.menuid from webstation web "
sqlStr = sqlStr & "inner join webstationgrp wgrp "
sqlStr = sqlStr & "on web.gid = wgrp.gid where web.webstationid = 3) End "
cm.CommandText = sqlStr
OpenDB()
ds = cm.ExecuteReader(CommandBehavior.CloseConnection)
CloseDB()
End Function


It return me the error message:

System.Data.OleDb.OleDbException: Line 1: Incorrect syntax near ')'.

Source errror:
Line 175: ds = cm.ExecuteReader(CommandBehavior.CloseConnection)


Can I anyway how to execute the sql statement in vb. The sql query i have tested in the query analyst in SQL server it is correct.

Thank you

Ernestwan

[Cakkie]

Note that you did not use any crlf in the sql statement, which is required for commands like begin and end.

Aside that, I'm not sure if it is possible to execute it like that. If not, try pouring it into a stored procedure (from VB), execute the procedure and drop it again.