In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.
I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".
What do I need to do to make this work?
All help is greatly appreciated!
Lynnetteasp.net and sql server are two different things...you cannot call a vb/c# function in your sql statement...you can write the function in a UDF in your sql server and call it in your sql statement.|||Thanks for your response. I am brand new to SQL Server, but not so new to asp.net. Any ideas how to modify this vb.net function to a UDF?
Thanks again for all your help!
lds
Function GetWorkStatus(ByVal sU As Object, ByVal sSG As Object) As Integer
Dim dtEdate As String = Calendar1.SelectedDate.ToShortDateString()
Dim mydateMonth As Integer = DatePart("m", dtEdate)
Dim thisDay As String = CStr(DatePart("d", dtEdate))
Dim sDayOfWeek As String = CStr(DatePart("w", dtEdate))
Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
Dim cmd As New SqlCommand("usp_CheckWorkStatus", cnn)
Try
With cmd
.CommandType = CommandType.StoredProcedure
With .Parameters.Add("@.ForThisDate", SqlDbType.DateTime)
.Value = dtEdate
End With
With .Parameters.Add("@.mydateMonth", SqlDbType.Int)
.Value = mydateMonth
End With
With .Parameters.Add("@.thisDay", SqlDbType.VarChar, 2)
.Value = thisDay
End With
With .Parameters.Add("@.theUnion", SqlDbType.NVarChar, 2)
.Value = sU
'Session("sUn") = sU
End With
With .Parameters.Add("@.theSG", SqlDbType.NVarChar, 50)
.Value = sSG
'Session("strSG") = sSG
End With
End With
cnn.Open()
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds, "SGDetails")
'no record in FLSAScheduleGroup
If ds.Tables("SGDetails").Rows.Count = 0 Then
Select Case sSG
Case "08"
If sDayOfWeek = 1 OrElse sDayOfWeek = 7 Then 'Sunday or Saturday
Return 0
Else
Return 1
End If
Case "09"
If sDayOfWeek = 1 OrElse sDayOfWeek = 2 Then 'Sunday or Monday
Return 0
Else
Return 1
End If
Case "12"
If sDayOfWeek = 4 OrElse sDayOfWeek = 5 Then 'Wed or Thurs
Return 0
Else
Return 1
End If
Case "13"
If sDayOfWeek = 5 OrElse sDayOfWeek = 6 Then 'Thurs or Friday
Return 0
Else
Return 1
End If
Case "14"
If sDayOfWeek = 6 OrElse sDayOfWeek = 7 Then 'Friday or Saturday
Return 0
Else
Return 1
End If
Case "31"
If sDayOfWeek = 1 Or 7 Then
Return 0
Else
Return 1
End If
Case Else
Return 1
End Select
ElseIf ds.Tables("SGDetails").Rows.Count = 1 Then 'if there is a row in FLSAScheduleGroup
Return 0
End If
Catch ex As Exception
lblError.Text = ex.ToString
Finally
With cmd.Connection
If .State = ConnectionState.Open Then
.Close()
End If
End With
Session("sUn") = Nothing
Session("strSG") = Nothing
End Try
End Function
|||what you have seems to be fine although it can be fine-tuned a little bit more..i dont see where you are calling a function in a sql stmt here..|||I wrote the function for the vb.net code behind, and called it from the <ItemTemplate Text=> part of the datagrid.
I have no idea how to make it work in SQL - don't know how to write a UDF.
Thanks again for all your help. Sorry the code is so spaced out - don't know what the "POST-ing" did to it.
Lynnette|||ok you got me confused when you said you are trying to call a function in codebehind from SQL.
what you have seems to be a regular vb function that queries a database to fill a dataset. so what are you trying to do now ? does this function not work ? do you get any errors and if so at which line ? and what is the xact error message ?|||Thanks so much for sticking with me here. I'm the only one at my office that does .NET, so I'm kind of an island here.
The vb.net Function "GetWorkStatus" in the post above works fine. I get the error, "not a recognized function name" when I try to call it from another function, that is using it in the SQL statement to identify values for the specified field. Here is the calling function:
Private Sub GetTmpRecs()
'uses the AppSettings table to generate WHERE clause from the filter values Dim dtWdate As String = Calendar1.SelectedDate.ToShortDateString()
Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
Dim wValue As String = Session("svFilterValue")
Dim dtEdate As DateTime = Now()
Dim strUser As String = Session("sUser")
Dim sSQL As String = "INSERT INTO tmpFLSAEmpInfo "
sSQL = sSQL & " (Emp_Number, PT_ID, Name_full, Division, Department, Job_Dept_Code, Dept_Mgr, "
sSQL = sSQL & " DeptInfo, Job_Supervisor, Location, Shift, sUnion, sSG, WrkDate, WrkStatus, HrsWorked, Username, AddDate) SELECT "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Emp_Number, "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[ID], "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Name_Full, "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Division, "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Department,"
sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Job_Dept_Code, "
sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Dept_Mgr, "
sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Department + ' - ' + hcsosharedtables.dbo.employee2.Dept_Mgr + ' - ' + hcsosharedtables.dbo.employee2.job_dept_code, "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Job_Supervisor, "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Loc_Name, "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Shift, "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[Union], "
sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Sched_Group, "
sSQL = sSQL & "'" & dtWdate & "', "
sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS WrkStatus, "
sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS HrsWorked, "
sSQL = sSQL & "'" & strUser & "', "
sSQL = sSQL & "'" & dtEdate & "' "
'sSQL = sSQL & "INTO tmpFLSAEmpInfo "
sSQL = sSQL & "FROM hcsoSharedTables.dbo.Employee2 " & wValue & ""
Dim ds As DataSet = New DataSet
constants.ExecuteSQL(sSQL, constants.SQLConStrFLSA)
End Sub
|||So I got it right..you were indeed trying to call a vb function into an sql query..I can suggest moving the entire code into a stored proc..move all the logit into it..you can get it all done in one trip.
AspUser123 wrote: