• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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

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
nomar2
Asked:
nomar2
  • 6
  • 3
1 Solution
 
YZlatCommented:
the function should return an array, not boolean:

Public Function RetrieveFeatures(ByVal ID As Integer) As Array()
0
 
nomar2Author Commented:
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
 
YZlatCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nomar2Author Commented:
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
 
YZlatCommented:
put keyword Dim in front of myArray() as string = {btnID,btn1,btn2,btn3}:

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

0
 
nomar2Author Commented:
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
 
YZlatCommented:
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
 
YZlatCommented:
actually change this line

Public Function RetrieveFeatures() As Array()

to

Public Function RetrieveFeatures() As String()
0
 
YZlatCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now