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