Help!!! Can't call the SQL Stored Procedure from code

[AirDuster101]

Hi,

I am new in InfoPath. I am using InfoPath 2007 and SQL Server 2005 Express
on XP SP2 The production platform will be 2003 Server, SQL Server 2005,
Sharepoint 2007.

All I wanted to do is to capture the result of the form values and pass them
as parameters to call a SQL Stored Procedure. No value needs to return to
the form at this point.

I am hard-coding the parameter values right now as you will see in below
code.

Problem -1 : I don't get any error message when I click on the button to
call the code, but the stored procedure is not fired either.

Problem -2: I also can't use msgbox function either, I can't find
System.Windows class to import in VSTA. The system says MsgBox is not
declared.

Please help!!!

====================================================
Imports Microsoft.Office.InfoPath
Imports System
Imports System.Xml
Imports System.Xml.XPath
Imports System.Data
Imports System.Data.SqlClient

Namespace SpecFileWizard
Public Class FormCode
' Member variables are not supported in browser-enabled forms.
' Instead, write and read these values from the FormState
' dictionary using code such as the following:
'
' Private Property _memberVariable() As Object
' Get
' _memberVariable = FormState("_memberVariable")
' End Get
' Set
' FormState("_memberVariable") = value
' End Set
' End Property

' NOTE: The following procedure is required by Microsoft Office
InfoPath.
' It can be modified using Microsoft Office InfoPath.
Private Sub InternalStartup(ByVal sender As Object, ByVal e As
EventArgs) Handles Me.Startup
AddHandler DirectCast(EventManager.ControlEvents("CTRL53_5"),
ButtonEvent).Clicked, AddressOf CTRL53_5_Clicked
End Sub

Public Sub CTRL53_5_Clicked(ByVal sender As Object, ByVal e As
ClickedEventArgs)

Dim cn As New SqlClient.SqlConnection()
Dim cm As New SqlClient.SqlCommand()

With cn
.ConnectionString=
"Server=SonyBRIAN\SQLExpress;Database=PR_SRDB1;Trusted_Connection=Yes;"

.Open()
End With

With cm
.CommandText = "GetDocs"
.CommandType = CommandType.StoredProcedure
.Connection = cn
Dim parameterSQL1 As New SqlParameter("@p1",
SqlDbType.VarChar,4)
parameterSQL1.Value = "S"
Dim parameterSQL2 As New SqlParameter("@p2",
SqlDbType.VarChar,4)
parameterSQL1.Value = "2Y"
Dim parameterSQL3 As New SqlParameter("@p3",
SqlDbType.VarChar, 4)
parameterSQL1.Value = "3Y"
.Parameters.Add(parameterSQL1)
.Parameters.Add(parameterSQL2)
.Parameters.Add(parameterSQL3)


End With

Try
cm.ExecuteNonQuery()

Catch exp As SqlClient.SqlException
Dim sText As String

sText = exp.Message


'MsgBox(exp.Message) Can't get the MsgBox ????
End Try

End Sub
End Class
End Namespace

 

[JL]

Hi,

I am new inInfoPath. I am usingInfoPath2007and SQL Server 2005 Express
on XP SP2 The production platform will be 2003 Server, SQL Server 2005,
Sharepoint2007.

All I wanted to do is to capture the result of the form values and pass them
as parameters to call a SQL Stored Procedure. No value needs to return to
the form at this point.

I am hard-coding the parameter values right now as you will see in below
code.

Problem -1 : I don't get any error message when Iclickon the button to
call the code, but the stored procedure is not fired either.

Problem -2: I also can't use msgbox function either, I can't find
System.Windows class to import in VSTA. The system says MsgBox is not
declared.

Please help!!!

====================================================
Imports Microsoft.Office.InfoPath
Imports System
Imports System.Xml
Imports System.Xml.XPath
Imports System.Data
Imports System.Data.SqlClient

Namespace SpecFileWizard
Public Class FormCode
' Member variables are not supported in browser-enabled forms.
' Instead, write and read these values from the FormState
' dictionary using code such as the following:
'
' Private Property _memberVariable() As Object
' Get
' _memberVariable = FormState("_memberVariable")
' End Get
' Set
' FormState("_memberVariable") = value
' End Set
' End Property

' NOTE: The following procedure is required by Microsoft OfficeInfoPath.
' It can be modified using Microsoft OfficeInfoPath.
Private Sub InternalStartup(ByVal sender As Object, ByVal e As
EventArgs) Handles Me.Startup
AddHandler DirectCast(EventManager.ControlEvents("CTRL53_5"),
ButtonEvent).Clicked, AddressOf CTRL53_5_Clicked
End Sub

Public Sub CTRL53_5_Clicked(ByVal sender As Object, ByVal e As
ClickedEventArgs)

Dim cn As New SqlClient.SqlConnection()
Dim cm As New SqlClient.SqlCommand()

With cn
.ConnectionString=
"Server=SonyBRIAN\SQLExpress;Database=PR_SRDB1;Trusted_Connection=Yes;"

.Open()
End With

With cm
.CommandText = "GetDocs"
.CommandType = CommandType.StoredProcedure
.Connection = cn
Dim parameterSQL1 As New SqlParameter("@p1",
SqlDbType.VarChar,4)
parameterSQL1.Value = "S"
Dim parameterSQL2 As New SqlParameter("@p2",
SqlDbType.VarChar,4)
parameterSQL1.Value = "2Y"
Dim parameterSQL3 As New SqlParameter("@p3",
SqlDbType.VarChar, 4)
parameterSQL1.Value = "3Y"
.Parameters.Add(parameterSQL1)
.Parameters.Add(parameterSQL2)
.Parameters.Add(parameterSQL3)

End With

Try
cm.ExecuteNonQuery()

Catch exp As SqlClient.SqlException
Dim sText As String

sText = exp.Message

'MsgBox(exp.Message) Can't get the MsgBox ????
End Try

End Sub
End Class
End Namespace

Hi,

I can't tell for your first problem, maybe check for server side logs
or in event viewer.
But for the message box, try the following, it worked for me in
InfoPath 2007 using C# :

System.Windows.Forms.MessageBox.Show(message);

I'm not sure if it work with a browser form using InfoPath Form
Services.

hope that help.

 

[AirDuster101]

Thanks for the suggestion. Unfortunately, messagebox is not supported in
browser form. However, I use different form view to display message instead.

Thanks,
AD