Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[MrHaugen]

Hello

I finally made a web service that makes all the connections between the plugin and my 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 have this code in my plugin:

Code: [Select]

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

And this code in my web service:

Code: [Select]

<WebMethod()> Public Function GetElementCount() As Integer
        Return elementCount([color=red]"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 & "'"[/color])
End Function


  Public Function artistCount(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 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.

[src666]

The forum isn't letting me respond to your PM, so I'm putting it here.

Sorry, I haven't really had a chance to look at this problem, and probably won't for another 2 weeks (business, family and such). Wish I could be more help, but at least didn't want you to think you are being ignored.

Hope you can work this one out, and if not give me a shout mid-june and I will try to figure it out.

Aside for the MC Forum Staff: Any particular reason that you ask for the CAPTCHA twice, and it doesn't send the message no matter what combination of CAPTCHA's I fill in (1st, 2nd, both), and doesn't give an error message telling me what's wrong? Just wondering.

[MrHaugen]

Hope you can work this one out, and if not give me a shout mid-june and I will try to figure it out.

Here's the shout

I actually made it, but unfortunately have encountered a much bigger problem. The variables I am using are transformed to the lowest possible number the database tables can deal with (0 or 1 depending on "Allow Null" or not in the DB). NO mather what I set the variables to!
I have used messageboxes to check what is passed on to the Web Service, so im about 100% sure the right value goes into the WS. But from there, I have NO idea. I have looked and tried alot of different things in this code and spent abut 10 hours staring at it. But to no luck.
Have tried about 5 forums to ask for help, but no one answers. I'm really about to give ut the project I have used months on.

Hope you src666, or someone else can help me out!


Code in my plugin:

Code: [Select]

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim varPartyBit As Boolean = True
Dim varClubBit As Integer = 1
Dim varNumberOfElements As Integer = 2

varLocalTrackNameModified = "WILD BIRD"
varLocalArtistNameModified = "BIG BANG"

MessageBox.Show(varNumberOfElements & varPartyBit & varClubBit & varLocalTrackNameModified & varLocalArtistNameModified)

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

As you can see, I have tried both with Integer and Boolean as the Bit variable values. The messagebox prints exactly what I have set the variables as.
It reads just as expected in the MessageBox. The right values for sure. But when passed over to the Web Service only 0 is set for the variable bit values.
I tried with both Integer and Boolean for the Bit variables. Both gives the same result: "0". And the variable varNumberOfElements always result in 1. No mather what I set it as.
The only thing actualy doing as supposed to is the String values and the varNumberOfElements!

Code in my Web Service:

Code: [Select]

<WebMethod()> Public Function InsertTrackElement(ByVal varNumberOfElements As Integer, ByVal varPartyBit As Boolean, ByVal varClubBit As Integer, ByVal varLocalTrackNameModified As String, ByVal varLocalArtistNameModified As String) As Integer
   trackElement("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 trackElement(ByVal SQLQuery As String) As String
   myCommand.CommandText = SQLQuery
   myCommand.Connection.Open()
   varScalarCount = Convert.ToInt32(myCommand.ExecuteScalar())

   SQLconn.Close()
   Return varScalarCount
End Function

For the ones of you that's not familiar with web services, there is not much to it. I'll try to explain my example.
The webmethod are called from the plugin. The plugin passes the variables over to the web service and the web service inserts the variables into the SQL query in the Web Method. The complete SQL query are then passed over to the trackElement function (set in front of the query in the web method). This function opens the connection and executes the SQL query/insert.

I THINK this is how it SHOULD have worked, but along the way the variables are screwed up.

Would be eternaly greatfull for any help and advice!

- Carl

[Mr ChriZ]

OK lets break this down abit.
First I need to be sure I understand what you're doing.
You've got your plugin which talks to your Webservice. (Is that IIS hosted out of curiosity?)

Your Webservice then talks to your SQL Server database.

You're saying that when you're plugin tries to save integers they aren't making
it as far as the database.  They get 0'd or something.
However Strings make it to the database no problem.
How am I doing?

[Mr ChriZ]

Presumably you've tried just putting some static integers
in and seeing what happens to them?

[src666]

OK, here's where I would start. First off, change all of your web service's interfaces to use INTs instead of BOOLs. Use explicit 1's and 0's instead of the VB TRUE/FALSE anywhere you will be dealing with SQL BIT values/fields.

I believe that you are running into a conversion issue with the BOOL values not translating correctly into SQL BIT values - especially the TRUE/FALSE values, which can be a bit tricky. Look into using the SqlBoolean type conversion function in the SYSTEM.DATA.SqlTypes branch that will convert to the SQLBIT type. Use that conversion function when passing data to the database. If you can't find it, just make sure that you only pass a 1 or a 0 - it shouldn't have any trouble doing an implicit conversion from those.

I can't recall, but are you using VB 2005/.NET 2.0?

[MrHaugen]

Thanks for trying guys!

OK lets break this down abit.
First I need to be sure I understand what you're doing.
You've got your plugin which talks to your Webservice. (Is that IIS hosted out of curiosity?)

Your Webservice then talks to your SQL Server database.

You're saying that when you're plugin tries to save integers they aren't making
it as far as the database.  They get 0'd or something.
However Strings make it to the database no problem.
How am I doing?

You're doing well. The web service are IIS 6.0 hosted, yes. Strings works like expected but the integers values wich represent my bit values allways gets 0'd in my database. And the number of elements value allways end up as 1. Since I don't allow nulls for this one. Don't know if this happens in the web service or when passed to the database or in the database it self.

Presumably you've tried just putting some static integers
in and seeing what happens to them?

I have tried with values of 0 to 3 or 4. Also tried vith boolean valuse to represent the bit values. It allways end up with the lowest possible value. Is there a way to print out the variables in different places of the web service? Messagebox do not work well I think 

OK, here's where I would start. First off, change all of your web service's interfaces to use INTs instead of BOOLs. Use explicit 1's and 0's instead of the VB TRUE/FALSE anywhere you will be dealing with SQL BIT values/fields.

I believe that you are running into a conversion issue with the BOOL values not translating correctly into SQL BIT values - especially the TRUE/FALSE values, which can be a bit tricky. Look into using the SqlBoolean type conversion function in the SYSTEM.DATA.SqlTypes branch that will convert to the SQLBIT type. Use that conversion function when passing data to the database. If you can't find it, just make sure that you only pass a 1 or a 0 - it shouldn't have any trouble doing an implicit conversion from those.

I can't recall, but are you using VB 2005/.NET 2.0?

I'm using VB 2005/.NET 2.0 with SQL 2005.
I'll try the conversion you mentioned later when I have sorted out my new IIS setup. But I really don't think that will mather much as I also have problem with the varchar. I just tested out the ElementNumber table with Integer as well. NO difference.

I have tried to gather the most important server data in this picture:
www.kirkegata.com\div\MoodPluginProblems.JPG

Maby you can see something wrong there. As you can se I have not filled all the tables with data, but that should not be necessary.
Just to make it clear; I have trouble with the ElementNumber (varchar), Party and Club (bit) values.

[Mr ChriZ]

so your database fields, that these integers are supposed to be going into,
presumably you've changed them to Integers?
If you have them as bit fields they'll only be able
to contain 1 or 0?

In other words you are aware that the term bit refers to
something that can either be 1 or 0.

[MrHaugen]

so your database fields, that these integers are supposed to be going into,
presumably you've changed them to Integers?
If you have them as bit fields they'll only be able
to contain 1 or 0?

In other words you are aware that the term bit refers to
something that can either be 1 or 0.

I know my bit and bytes well. The field should ONLY be 1 and 0. On and off. That's why I use bit. The whole ide as to limit the possible input data to prevent any attempts of sql injections and stuff.
Have tried the following:

"Party" and "Club" field: Bit and integer as DB data. Input values of integer and boolean. True, false and 0 to 4.
"ElementNumber"  field: Varchar(1) and integer as DB data. Input values of 0 to 4.

Varchar(1) is supposed to limit it to one byte, as I only need a short range of numbers ranging from 0 to 5.

As you can see I have tried to put most things into my database, and have changed the data of the database field it self. To no use. It ALLWAYS ends up at the lowest possible number. I can't even get an error message when I pass along numbers that should not have been excepted. That is trange

[MrHaugen]

I also tried:

Imports System.Data.SqlTypes

        Dim varPartyBit2 As SqlBoolean
        varPartyBit2 = SqlBoolean.True


That didn't do any difference either.

[Mr ChriZ]

OK I  think I'm still confused.
What do you mean by lowest possible number?
Give me a field, what data type it is all down the line,
what goes in and what comes out the other end.

Also using SQLServer Management studio have you tried running a query
directly on the database, exactly the same as the SQL you're running in Code?

As a side note if you want to avoid SQL Injections take a look at Stored Procedures,
however get this working first.

[MrHaugen]

Ok. I'll take this screen shot as an example (hope it works). It shows most of my fields in the trouble table:

www.kirkegata.com\div\MoodPluginProblems.JPG

There are 3 fields in the DB and 3 variables I have a problem with:

1. varPartyBit (column called Party in DB)
2. varClubBit (column called Club in DB)
3. varNumberOfElements (column called ElementNumber in DB).

The first two are of bit value (allow null) and the ElementNumber are an Integer in the DB (NOT null).
So when I say the lowest possible number in the database that will imply 0 for the bit values, as they can be 0 and 1 (allow 0's). The lowest possible value for the ElementNumber column (integer) is 1 as I have set the column as NOT to allow 0's. Agian, look at the screen shot if you want.

And as I have said, I have tried boolean, bit, integer for the variables and bit, varchar(1) and integer in the DB columns.

I have tried to run a query directly. As you can see on the screen shot there are actually one time I have gotten 1's into the bit values and that is when I tried to replace the variables in the query with static data, and ran it in the management studio.

If I ever make this work I do not think I want to get any more functions. I'm allready way over my head.
I'll go to sleep and dream about dissappearing variables now.

[Mr ChriZ]

OK so when you run the SQL statement within management studio,
with static data it works ok.

If you take the exactly the same SQL string and run it from your WebService code,
with the static data exactly the same, does that work?

Apologies if I'm getting you to repeat stuff here, I'm just trying
to get whats happening into my head.

[MrHaugen]

Yea. From the SQL management it works all right.

It's no problem man! You are the ones trying to help me...
I have actually not give static data directly from my web service. Good idea. I'll try that when I get home. Party right after work, so it might have to wait until tomorrow.

[MrHaugen]

If you take the exactly the same SQL string and run it from your WebService code,
with the static data exactly the same, does that work?

To hung over yesterday. Did'nt have the strength to test it before now. Static data in the web service works great!
So we have eliminated the possibility that the database it self actually does something to the data. Or require something strange to set the correct data.
- Bit values only except 0 and 1's. Great.
- Integers set as "Not Null" accepts 0. Strange, but not a priority to fix.
- String values have allways worked.

This means that the integer and bit values either gets screwed up from the plugin to the web service, wich I wery much doubt, as I have used about the same 2 line code on a number of other web service methods, and they all worked great.
The second possibility are my code in the web service. Belive this is the bad seed. I belive that there are a problem with values being converted or something. But I can't find anything wrong in my WS code.

[Mr ChriZ]

OK with the dynamic SQL string back in place,
could you get the webservice to return the string
to your plugin and so see that the variables have not been
inserted correctly?

[Mr ChriZ]

Just a thought but where you are inserting your integers into the SQL String
try using .ToString()
so varPartyBit.ToString()

My VB isn't that strong but that's what I would need to do in C#

[Mr ChriZ]

trackElement("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 & "'")

Not sure this actually makes sense:
I've thrown some variables into your SQL and here's what I see:

Insert Into TrackElement(ElementNumber, Party, Club, TrackNumber) SELECT 4,1,0, T.TrackNumber FROM TRACK T INNER JOIN ARTIST A ON (A.ArtistNumber = T.ArtistNumber) WHERE T.TrackName = 'TrackName' AND A.ArtistName = 'ArtistName' 

Does that Select Statement work?  I don't know what happens when you put numbers into a select statement like that.

[Mr ChriZ]

Oh I see that just creates empty columns with those rows in, ok thats fine I think.

[MrHaugen]

As a last solution I added a few more bit variables, and POFF! It works!

I can't be sure, but I suspect the solution was to update the web reference in the plugin. Guess I might have moved my variables around since the last time I updated it. Might also be because I have changed the intereger, bit and varchar variables. The plugin have not been updated and thus sending the data in wrong format, or to the vrong variables.

This have been keeping me at it for the last weeks! So incredibly irritating.
Never updated the web service reference that much and therefor totally forgot about it. Learning the hard way.

Insert Into TrackElement(ElementNumber, Party, Club, TrackNumber) SELECT 4,1,0, T.TrackNumber FROM TRACK T INNER JOIN ARTIST A ON (A.ArtistNumber = T.ArtistNumber) WHERE T.TrackName = 'TrackName' AND A.ArtistName = 'ArtistName' 

Does that Select Statement work?  I don't know what happens when you put numbers into a select statement like that.

It works great! Trust me. scr666 helped me put that one together. It actually makes you insert the data from the SELECT statement you have bolded out there, with reference to the Tracknumber from the Track of the Artist in the WHERE statement.


Thanks again Chriz and src666! Though you did not exactly find the solution this time, you helped me push forward and keep looking. Probably prevented me from abandoning this project in lack of hope as well!

- Carl