Mr Haugen

Hello. This should be an easy one for you guys.

I finally made a web service that makes the connections between my VB.net plugin and my SQL database. But I have one problem. How to pass variables from the plugin to the Web service Maby some of you can help me.

Tried this code in my plugin:

Code Snippet
Private Sub btn_Test_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_serviceTest.Click
varScalarCount= service.GetElementCount()
MessageBox.Show(varScalarCount)
End Sub


And this code in my web service:

Code Snippet

<WebMethod()> Public Function GetElementCount() As Integer

Return elementCount("SELECT COUNT(*) FROM TrackElement TE INNER JOIN Track T ON (T.TrackNumber = TE.TrackNumber) INNER JOIN Artist A ON (A.ArtistNumber = T.ArtistNumber) WHERE T.TrackName = '" & varLocalTrackNameModified & "' AND A.ArtistName = '" & varLocalArtistNameModified & "'")

End Function

Public Function elementCount(ByVal SQLQuery As String) As Integer

myCommand.Connection.Open()

varScalarCount = Convert.ToInt32(myCommand.ExecuteScalar())

SQLconn.Close()

Return varScalarCount

End Function

I know it works if I leave out the variables in the select query. I have tried with "SELECT COUNT(*) FROM Artist" etc.
Can anyone tell me what I need to do to pass the variables over to the web service I have never done anything like this before. Feel a little lost.

I also have ONE more question I would like to get an answer to. How to set the timeout in a sql connection.

Have tried the following:

Code Snippet

Public SQLquery As String

Public SQLconn As New SqlConnection("Data Source=10.0.0.8;" _

& "Initial Catalog=Mood;" _

& "User Id=moodDBUser;" _

& "Password=testTEST;" _

& "Timeout=5;")

I know it's a bit out of the subject, but if know why it don't work, please let me know.

- Carl



Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

rfreire

Hi Carl,

To pass the parameters you can just change the method signature as any other method:

Public Function GetElementCount(ByVal parameter1 as String, ByVal parameter2 as String) As Integer

Is that what you need, or is it something different

Regarding the connection string you should use, Connection Timeout = value, as documented here:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

Regards,

Rodrigo





Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

Mr Haugen

Yes. That's probably exactly what I need. Didn't know I could use that between apps as vel....

Have a little problem though. I tried the following:

In my Plugin:

Code Snippet

Private Sub bt_serviceTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_serviceTest.Click

varLocalArtistNameModified = "WILD BIRD"

varLocalArtistNameModified = "BIG BANG"

varElementCount = service.GetElementCount(varLocalTrackNameModified, varLocalArtistNameModified)

MessageBox.Show(varElementCount)

End Sub

EDIT: In my web service:

Code Snippet

Inherits System.Web.Services.WebService

' Creating the connection

Private SQLconn As New SqlConnection("Data Source=10.0.0.5;Initial Catalog=Mood;User Id=moodDBUser;Password=testTEST;")

' Common variable for numbers retrieved from the database

Dim varScalarCount As Int32

' Building the SQL Command

Private SQLQuery As String

Private myCommand As New SqlCommand(SQLQuery, SQLconn)

Public Function GetElementCount(ByVal varLocalTrackNameModified As String, ByVal varLocalArtistNameModified As String) As Integer

Return elementCount("SELECT COUNT(*) FROM TrackElement TE INNER JOIN Track T ON (T.TrackNumber = TE.TrackNumber) INNER JOIN Artist A ON (A.ArtistNumber = T.ArtistNumber) WHERE T.TrackName = '" & varLocalTrackNameModified & "' AND A.ArtistName = '" & varLocalArtistNameModified & "'")

End Function

Public Function elementCount(ByVal SQLQuery As String) As Integer

myCommand.Connection.Open()

varScalarCount = Convert.ToInt32(myCommand.ExecuteScalar())

SQLconn.Close()

Return varScalarCount

End Function

There are noe errors in visual studio, but I get the following error from my application/plugin:

Code Snippet

************** Exception Text **************
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> ExecuteScalar: CommandText property has not been initialized
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at MC_VBNETPlugin_Template.refGetArtist.Service.GetElementCount(String varLocalTrackNameModified, String varLocalArtistNameModified) in D:\mc_vbnetplugintemplate\VB_NetPluginSoloution\VB_NetPluginProject\Web References\refGetArtist\Reference.vb:line 118
at MC_VBNETPlugin_Template.MainInterface.bt_serviceTest_Click(Object sender, EventArgs e) in D:\mc_vbnetplugintemplate\VB_NetPluginSoloution\VB_NetPluginProject\MainInterface.vb:line 1404
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I have little clue as to what this can be. It's complaining about CommandText in ExecuteScalar. Can it be some problems between Integer and String If that is the case I have problems seeing how it can be sorted out. Anyone else have some ideas





Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

Binoj Rajan

yes, because your command "myCommand" is nowhere initialized. you should initialize your command with your connection string and sql query.

check this link for details http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

thanks






Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

Mr Haugen

I don't think that's my problem. Sorry. I left this one out from my web service code (have edited my previous post to a complete example):

Code Snippet

Inherits System.Web.Services.WebService

' Creating the connection

Private SQLconn As New SqlConnection("Data Source=10.0.0.5;Initial Catalog=Mood;User Id=moodDBUser;Password=testTEST;")

' Common variable for numbers retrieved from the database

Dim varScalarCount As Int32

' Building the SQL Command

Private SQLQuery As String

Private myCommand As New SqlCommand(SQLQuery, SQLconn)

So, what else can it be





Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

Binoj Rajan

you have not assigned any query to the string variable "SQLQuery", pls do that, it should work

thanks






Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

Mr Haugen

I allready have it in my Function...

Code Snippet

Public Function GetElementCount(ByVal varLocalTrackNameModified As String, ByVal varLocalArtistNameModified As String) As Integer

Return elementCount("SELECT COUNT(*) FROM TrackElement TE INNER JOIN Track T ON (T.TrackNumber = TE.TrackNumber) INNER JOIN Artist A ON (A.ArtistNumber = T.ArtistNumber) WHERE T.TrackName = '" & varLocalTrackNameModified & "' AND A.ArtistName = '" & varLocalArtistNameModified & "'")

End Function

I also have each of my SQL queries in each query string Just seems a bit overkill Surprise





Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

Binoj Rajan

Hello Haugen,

if you can put a breakpoint in your code and debug then you can easily catch what I am trying to say.

in the above code snippet you are calling the function "elementCount" by passing the sqlquery string. what I say is in your function "elementCount", eventhough you are collecting some values in the variable "SQLQuery", you are nowhere assigning that value inside that function. see the following code snippet.

Code Snippet

Public Function elementCount(ByVal SQLQuery As String) As Integer

myCommand.Connection.Open()

varScalarCount = Convert.ToInt32(myCommand.ExecuteScalar())

SQLconn.Close()

Return varScalarCount

End Function

I believe if you replace the above code as following, it should work.

Code Snippet

Public Function elementCount(ByVal SQLQuery As String) As Integer

myCommand.CommandText = SQLQuery

myCommand.Connection.Open()

varScalarCount = Convert.ToInt32(myCommand.ExecuteScalar())

SQLconn.Close()

Return varScalarCount

End Function

I guess you know that the variable "SQLQuery" declared inside your program is different than the variable "SQLQuery" you pass as the argument for the method elementCount, though the names are same.

hope this helps

thanks






Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

Mr Haugen

Yea!! That worked!

Finally I got all I need to complete this plugin (I hope). I'm not totally sure as to why It works, but hope I will in the end. The ByVal things makes my brain hurt.

Thanks so much for the help!

I'm making a plugin, that's why I have a hard time debugging.





Re: ASMX Web Services and XML Serialization Variable input from VB.Net to Web Service

Mr Haugen

Actually I got ONE more.. Sorry for being this slow.

I have a problem making SQL inserts from my web service.

I tried this code in my plugin:

Code Snippet

service.InsertTrackElement(varNumberOfElements, varPartyBit, varClubBit, varLocalTrackNameModified, varLocalArtistNameModified)

Code in the web service:

Code Snippet

Inherits System.Web.Services.WebService

Private SQLconn As New SqlConnection("Data Source=10.0.0.5;Initial Catalog=Mood;User Id=moodDBUser;Password=testTEST;")

Dim varScalarCount As Int32

Private SQLQuery As String

Private myCommand As New SqlCommand(SQLQuery, SQLconn)

'3. Create the DataAdapter and dataSet

Dim dataAdapter As New SqlDataAdapter()

Dim myDataSet As New DataSet()

<WebMethod()> Public Function InsertTrackElement(ByVal varNumberOfElements As Integer, ByVal varPartyBit As Integer, ByVal varClubBit As Integer, ByVal varLocalTrackNameModified As String, ByVal varLocalArtistNameModified As String) As Integer

InsertTrackElement("INSERT INTO TrackElement (ElementNumber, Party, Club, TrackNumber) SELECT '" & varNumberOfElements + 1 & "', '" & varPartyBit & "', '" & varClubBit & "', T.TrackNumber FROM Track T INNER JOIN Artist A ON (A.ArtistNumber = T.ArtistNumber) WHERE T.TrackName = '" & varLocalTrackNameModified & "' AND A.ArtistName = '" & varLocalArtistNameModified & "'")

End Function

Public Function InsertTrackElement(ByVal SQLQuery As String) As String

myCommand.CommandText = SQLQuery

myCommand.Connection.Open()

varScalarCount = Convert.ToInt32(myCommand.ExecuteScalar())

SQLconn.Close()

Return varScalarCount

End Function

I don't know if this direct translation from the GetArtistCount webmethod with "SELECT * FROM Artist" queries etc, can be done this easily. I can only hope.

My problem is that I get the following error "Conversion from string "" to type 'Integer' is not valid".

I know what it means, but I have a hard time figuring out where the conversion is going wrong. If anyone sees where the conversion error hales from, please let me know.

Don't need to return anything to my plugin here, I know. Just insert data to the database.

Also had a problem inserting "ByVal varPartyBit As Bit" as there was no "bit" to choose from. My value in the database are "bit". Can this fuck up things later on