VB Function inside an SQL Statement??

.net , sql United States
  • 16 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.


    SOMETHING LIKE



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


    Thanks


  • 16 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.




  • 16 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 "
    MYSELECT = MYSELECT + "WHERE" & MYFUNC(Doc
    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..


  • 16 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" ?





  • 16 years ago

    TlKelley.


    P.S: Here´s my code


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


    MY FUNCTION :


    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).

Contribute

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.

“It is practically impossible to teach good programming style to students that have had prior exposure to BASIC. As potential programmers, they are mentally mutilated beyond hope of regeneration.” - E. W. Dijkstra