?
Solved

Creating my first Web Service in VB.net / SQL Server Database / Creating + Passing back an Array

Posted on 2005-03-15
9
Medium Priority
?
256 Views
Last Modified: 2012-06-21
Okay I am a bit lost in here...
First off do I have to be creating my web serivce on the server where the web service is going to be hosted ..ie the server.
Next I have to connect to Sql Server via VB.Net.
I have to read and write to this database based on what is coming in from the client side.
Here is what is done now on the client side....
 A row of buttons are created and the user will save a color that they will select which will be the background color of the button...these colors will be saved in the database on the web service and when the user logs on to the client...it has to fire at the web service and the web service is supposed to return the colors from the db for these buttons so on the load the colors are displayed.

Right now I have SQL Server with the database name... Sands  and the table name is ... rec_type where the colors are stored for each button ...for eg: the colors for button1 ( eg ID = 1) is bg_color1 = 255, bg_color2= 236, bg_color3=241).....so basically for EACH button I need to return back to the client 3 color parameters


This what I have done so far for publishing the web service...i have based this on what I can gather on the web...but I am not sure about the database connection (SQL Server...executing query) and how I should be returning back the parameters back to client...right now I have a boolean ...I know this is wrong..I am thinking an array is going to have to go back to the client.

Imports System
Imports System.Data
Imports System.Data.SQLCLient
Imports System.Diagnostics
Imports System.Web
Imports System.Web.Services

Public Class Service1
    Inherits System.Web.Services.WebService
    Dim oSQLConn As SqlConnection = New SqlConnection

    <WebMethod(Description:="Method to retrieve the user preferences.")> _
    Public Function RetrieveFeatures(ByVal ID As Integer) As Boolean
        Try
            Dim CmdCat As SqlCommand = oSQLConn.CreateCommand()
            CmdCat.CommandText = "Select button_color_1,button_color_2,button_color_3 FROM Rec_Types Where ID = '" & ID & "'"


            oSQLConn.ConnectionString = "Data Source =(local):Initial Catalog=Sands:Intergrated Security=SSPI"
            oSQLConn.Open()


        Catch exception As SqlException
            Return (False)
        Finally
            oSQLConn.Close()
        End Try
    End Function

Any insight or help would be greatly appreciated.
0
Comment
Question by:nomar2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 13546327
the function should return an array, not boolean:

Public Function RetrieveFeatures(ByVal ID As Integer) As Array()
0
 

Author Comment

by:nomar2
ID: 13546575
That worked but how do I set-up my code based on that array...so that the 3 color parameters will be returned for each button on the client side.

0
 
LVL 35

Expert Comment

by:YZlat
ID: 13547961
Public Function RetrieveFeatures(ByVal ID As Integer) As Boolean
        Try
           dim
            Dim sql,btn1,btn2,btn3 as string
            sql = "Select button_color_1,button_color_2,button_color_3 FROM Rec_Types Where ID = '" & ID & "'"


            oSQLConn.ConnectionString = "Data Source =(local):Initial Catalog=Sands:Intergrated Security=SSPI"
         
Dim CmdCat As SqlCommand = New SqlCommand(sql, conn)
oSQLConn.Open()
Dim myReader As SqlDataReader = CmdCat.ExecuteReader()
' Process results
While myReader.Read
      btn1=myReader("button_color_1")
      btn2=myReader("button_color_2")
      btn3=myReader("button_color_3")
end while
myArray() as string={btn1,btn2,btn3}
myReader.Close()
oSQLConn.Close()


        Catch exception As SqlException
            Return (False)
        Finally
            oSQLConn.Close()
        End Try
RetrieveFeatures=myArray
    End Function

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:nomar2
ID: 13548532
I have changed this is a bit ...the function will not be accepting a parameter.
Basically the client form loads and fires to hit the web service and the WS returns the 3 color parameters associated with each button..there are about 20 buttons...so the buttonID from the table will have to go back as well.

I inserted your code but I am getting an error on
myArray() as string={btn1,btn2,btn3}...what should I declare this as in light of this new info

Public Function RetrieveFeatures() As Array()

        Dim sql, btn1, btn2, btn3 As String
        Dim btnID as Integer
        sql = "Select rowid, button_color_1,button_color_2,button_color_3 FROM Rec_Types"
        oSQLConn.ConnectionString = "Data Source =(local):Initial Catalog=Sands:Intergrated Security=SSPI"

        Dim CmdCat As SqlCommand = New SqlCommand(sql, oSQLConn)
        oSQLConn.Open()
        Dim myReader As SqlDataReader = CmdCat.ExecuteReader()

        While myReader.Read
            btnID = myReader("rowid")
            btn1 = myReader("button_color_1")
            btn2 = myReader("button_color_2")
            btn3 = myReader("button_color_3")
        End While
        myArray() as string = {btnID,btn1,btn2,btn3}
        myReader.Close()
        oSQLConn.Close()

        RetrieveFeatures = myArray

Your help is much apppreciated..it is a steep learning curve sometimes with asp.net
0
 
LVL 35

Expert Comment

by:YZlat
ID: 13548953
put keyword Dim in front of myArray() as string = {btnID,btn1,btn2,btn3}:

Dim myArray() as string = {btnID,btn1,btn2,btn3}

0
 

Author Comment

by:nomar2
ID: 13554747
I changed the code to reflect what you said

While myReader.Read
            btnID = myReader("rowid")
            btn1 = myReader("button_color_1")
            btn2 = myReader("button_color_2")
            btn3 = myReader("button_color_3")
            Dim myArray() as string = {btnID,btn1,btn2,btn3}
End While
       
        myReader.Close()
        oSQLConn.Close()

        RetrieveFeatures = myArray ***

*** I am getting an error right here that says 'myArray not declared' what should I declare this as...remember this variable is being returned back to the client

You have helped immensely..thanks!!
0
 
LVL 35

Expert Comment

by:YZlat
ID: 13554839
then try declaring it outside the while loop:

Public Function RetrieveFeatures() As Array()

        Dim sql, btn1, btn2, btn3 As String
        Dim btnID as Integer
      dim myArray() as string
        sql = "Select rowid, button_color_1,button_color_2,button_color_3 FROM Rec_Types"
        oSQLConn.ConnectionString = "Data Source =(local):Initial Catalog=Sands:Intergrated Security=SSPI"

        Dim CmdCat As SqlCommand = New SqlCommand(sql, oSQLConn)
        oSQLConn.Open()
        Dim myReader As SqlDataReader = CmdCat.ExecuteReader()

        While myReader.Read
            btnID = myReader("rowid")
            btn1 = myReader("button_color_1")
            btn2 = myReader("button_color_2")
            btn3 = myReader("button_color_3")
        End While
        myArray() = {btnID,btn1,btn2,btn3}
        myReader.Close()
        oSQLConn.Close()

        RetrieveFeatures = myArray
end function
0
 
LVL 35

Expert Comment

by:YZlat
ID: 13554950
actually change this line

Public Function RetrieveFeatures() As Array()

to

Public Function RetrieveFeatures() As String()
0
 
LVL 35

Accepted Solution

by:
YZlat earned 820 total points
ID: 13555043
Public Function RetrieveFeatures() As String()

        Dim sql, btn1, btn2, btn3 As String
        Dim btnID As Integer
        Dim myArray(4) As String
        sql = "Select rowid, button_color_1,button_color_2,button_color_3 FROM Rec_Types"
        oSQLConn.ConnectionString = "Data Source =(local):Initial Catalog=Sands:Intergrated Security=SSPI"

        Dim CmdCat As SqlCommand = New SqlCommand(sql, oSQLConn)
        oSQLConn.Open()
        Dim myReader As SqlDataReader = CmdCat.ExecuteReader()

        While myReader.Read
            btnID = myReader("rowid")
            btn1 = myReader("button_color_1")
            btn2 = myReader("button_color_2")
            btn3 = myReader("button_color_3")
        End While
        myArray(0) = btnID
        myArray(1) = btn1
        myArray(2) = btn2
        myArray(3) = btn3
        myReader.Close()
        oSQLConn.Close()

        RetrieveFeatures = myArray
    End Function
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question