VB Function inside an SQL Statement??

.net , sql United States
  • 17 years ago

    What I tought is that exists a way to "embed" a Function inside an SQL Statment so a could first convert in same data type, data from DataBase and data typed from user in a TextBox, and so I can compare then both acording to my sql condiction ( < , <=, ....).

    MYFUNC is a Function that convert alpha-numeric data in numeric equivalent.
    Doc_Number is a Database field.


    "SELECT MYFUNC(DocNumber) FROM Documents "
    Number) >= '" & MYFUNC(txtNumDoc.Text)) & "%" & "'"


  • 17 years ago

    Hi Mell,
    You can't run a VB function inside of an SQL Statement except maybe in SQL Server 2000 with the new .NET implemented.  I haven't done it before, but I've read that you can use C# and VB in SQL statements on the server.  You may want to place your post in the .NET section.
    Let me know what you find out.

  • 17 years ago

    Oh, wait, are you doing this in ASP?  If so, then you can do this.  Your syntax is a little wrong.  Try this:

    MYSELECT = "SELECT" & MYFUNC(DocNumber) & "FROM Documents "
    Number) & " >= '" & MYFUNC(txtNumDoc.Text)) & "%" & "'"

    Then execute MYSELECT as you normally do.  I'm not sure if the SELECT will work though, because you may want the DocNumber returned based on the WHERE clause.  That leads me to question the: "SELECT" & MYFUNC(DocNumber) & "FROM Documents " section

    Hope this helps.  I was thinking stored procedure in the previous post....just the DBA in me.. hehe..

  • 17 years ago

    Hi tlkelley

    I've tried what u've sugested, but returns me a compile error like :

      ByRef argument type mismatch

    Isn't it becase "Doc_Number is a field of Database" ?

  • 17 years ago


    P.S: Here´s my code

    Note: Normaly My docs nr are like : 21D20001 --> TypeDoc 21 , Dec, 2002, Sequential Nr 0001.


    Public Function MyFunc(DocNum As String) As String
    Dim Month As String

    Select Case Mid(DocNum, 3, 1)

    Case 1 To 9   ' <-----Means the Month digit is numeric
         MyFunc = Mid(DocNum, 1, 2) & "0" & Mid(DocNum, 3, Len(DocNum))
    Case "O"
      Month = 10
      MyFunc = Mid(DocNum, 1, 2) & Month & Mid(DocNum, 4, Len(DocNum))

    Case "N"
      Month = 11
      MyFunc = Mid(DocNum, 1, 2) & Month & Mid(DocNum, 4, Len(DocNum))

    Case "D"
      Month = 12
      MyFunc = Mid(DocNum, 1, 2) & Month & Mid(DocNum, 4, Len(DocNum))

    End Select

    End Function


    I've tested the function with things writed on a textbox ( simulating argument) and it works, but not with my Doc_Number Database field argument, like you've told me !!! ????

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“In order to understand recursion, one must first understand recursion.”