I wrote a custom web service to submit a form from sharepoint to sql. The form works properly and says the form submitted sucessfully but no data is stored in sql. I am pulling my hair out. Any help would be appreciated. Code is below.

<%@ WebService language="VB" class="Submit" %>

Imports System
Imports System.Web.Services
Imports System.Xml.Serialization

<WebService(Namespace:="http://schemas.xmlsoap.org/wsdl/")> Public Class MyWebService
    ' implementation
End Class

Public Class Submit

    <WebMethod> Public Function Add(iD As String, name As String, ssnumber As String, [date] As String, deptname As String, deptnumber As String, useremail As String, type As String, daysabsent As String, hoursabsent As String, from As String, [to] As String, totalhours As String, otherreason As String, supervisoremail As String, date2 As String,supervisoruse As String) As String
        Return iD + name + ssnumber + [date] + deptname + deptnumber + useremail + type + daysabsent + hoursabsent + from + [to] + totalhours + otherreason + supervisoremail + date2 + supervisoruse

    End Function


    Function MyInsertMethod( _
                ByVal iD As Integer,  _
                ByVal name As String,  _
                ByVal ssnumber As String,  _
                ByVal [date] As String,  _
                ByVal deptname As String,  _
                ByVal deptnumber As String,  _
                ByVal useremail As String,  _
                ByVal type As String,  _
                ByVal daysabsent As String,  _
                ByVal hoursabsent As String,  _
                ByVal from As String,  _
                ByVal [to] As String,  _
                ByVal totalhours As String,  _
                ByVal otherreason As String,  _
                ByVal supervisoremail As String,  _
                ByVal date2 As String,  _
                ByVal supervisoruse As String) As String
        Dim connectionString As String = "server='sharepoint'; user id='****'; password='****'; database='TimeRequest'"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

        Dim queryString As String = "INSERT INTO [Table1] ([ID], [name], [ssnumber], [date], [deptname], [deptnumber],"& _
            " [useremail], [type], [daysabsent], [hoursabsent], [from], [to], [totalhours], ["& _
            "otherreason], [supervisoremail], [date2], [supervisoruse]) VALUES (@ID, @name, @"& _
            "ssnumber, @date, @deptname, @deptnumber, @useremail, @type, @daysabsent, @hoursa"& _
            "bsent, @from, @to, @totalhours, @otherreason, @supervisoremail, @date2, @supervi"& _
            "soruse)"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_iD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_iD.ParameterName = "@ID"
        dbParam_iD.Value = iD
        dbParam_iD.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_iD)
        Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_name.ParameterName = "@name"
        dbParam_name.Value = name
        dbParam_name.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_name)
        Dim dbParam_ssnumber As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_ssnumber.ParameterName = "@ssnumber"
        dbParam_ssnumber.Value = ssnumber
        dbParam_ssnumber.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_ssnumber)
        Dim dbParam_date As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_date.ParameterName = "@date"
        dbParam_date.Value = [date]
        dbParam_date.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_date)
        Dim dbParam_deptname As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_deptname.ParameterName = "@deptname"
        dbParam_deptname.Value = deptname
        dbParam_deptname.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_deptname)
        Dim dbParam_deptnumber As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_deptnumber.ParameterName = "@deptnumber"
        dbParam_deptnumber.Value = deptnumber
        dbParam_deptnumber.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_deptnumber)
        Dim dbParam_useremail As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_useremail.ParameterName = "@useremail"
        dbParam_useremail.Value = useremail
        dbParam_useremail.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_useremail)
        Dim dbParam_type As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_type.ParameterName = "@type"
        dbParam_type.Value = type
        dbParam_type.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_type)
        Dim dbParam_daysabsent As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_daysabsent.ParameterName = "@daysabsent"
        dbParam_daysabsent.Value = daysabsent
        dbParam_daysabsent.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_daysabsent)
        Dim dbParam_hoursabsent As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_hoursabsent.ParameterName = "@hoursabsent"
        dbParam_hoursabsent.Value = hoursabsent
        dbParam_hoursabsent.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_hoursabsent)
        Dim dbParam_from As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_from.ParameterName = "@from"
        dbParam_from.Value = from
        dbParam_from.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_from)
        Dim dbParam_to As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_to.ParameterName = "@to"
        dbParam_to.Value = [to]
        dbParam_to.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_to)
        Dim dbParam_totalhours As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_totalhours.ParameterName = "@totalhours"
        dbParam_totalhours.Value = totalhours
        dbParam_totalhours.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_totalhours)
        Dim dbParam_otherreason As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_otherreason.ParameterName = "@otherreason"
        dbParam_otherreason.Value = otherreason
        dbParam_otherreason.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_otherreason)
        Dim dbParam_supervisoremail As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_supervisoremail.ParameterName = "@supervisoremail"
        dbParam_supervisoremail.Value = supervisoremail
        dbParam_supervisoremail.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_supervisoremail)
        Dim dbParam_date2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_date2.ParameterName = "@date2"
        dbParam_date2.Value = date2
        dbParam_date2.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_date2)
        Dim dbParam_supervisoruse As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        dbParam_supervisoruse.ParameterName = "@supervisoruse"
        dbParam_supervisoruse.Value = supervisoruse
        dbParam_supervisoruse.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_supervisoruse)

        Dim rowsAffected As Integer = 0
        dbConnection.Open
        Try
            rowsAffected = dbCommand.ExecuteNonQuery

        Finally
            dbConnection.Close
        End Try

        Return rowsAffected
    End Function

End Class

would it be because your variables are ByVal and not ByRef?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.