Android Tutorial Connect Android to MS SQL Server Tutorial

Discussion in 'Tutorials & Examples' started by Erel, Dec 11, 2011.

Thread Status:
Not open for further replies.
  1. Erel

    Erel Administrator Staff Member Licensed User

    This call cannot work. If you are using the emulator you should use the following address:
    10.0.2.2:59459

    With the device you should use the computer local ip address (use ipconfig to find it).

    You will also need to allow incoming connections in Windows firewall in the later case.
     
  2. jalle007

    jalle007 Active Member Licensed User

    Code:
    This call cannot work. If you are using the emulator you should use the following address:
    10.0.2.2:59459

    With the device you should use the computer local ip 
    address (use ipconfig to find it).
    Thx Erel I switch to real site and its working fine. Irealize its hard to set android working with local site. but its not important now.


    I have same problem here.
    database is updated when I send query directly from address bar
    but NOT when I send it from B4A app.


    Changing SPACE with %20 did not .:sign0085:
     
  3. Erel

    Erel Administrator Staff Member Licensed User

    Which error do you get? Seems like you will need to modify the ASP.Net code for INSERT commands.
     
  4. dlcarp

    dlcarp New Member

    Connecting to SQL Server

    Hi Erel,

    Just curious. I recently came across your post on how to connect to an SQL server 2008 database. Since you posted this over a year ago, have you found any more efficient way to connect or is this still about the only (best) way to do a connection?

    Thanks,
    David
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    There is another library in the forum that connects directly to the database. I think that this solution however works better (more reliable).
     
  6. schalkgreyling

    schalkgreyling Member Licensed User

    MS SQL connection problem

    Hi all, i am quite new to B4A and has run into a problem i cannot seem to resolve using the forum here. So i am trying to connect a mobile app to SQL Server using the code provided here but cannot get the connection established. I got the sql to display in my browser using asp.net like

    [{"ID":"1","Name":"Curtis","Surname":"Cleenverck","Cell_num":"082*** 4548","Email_add":"**@gmail.com","SA_ID":"1"}]

    but when running the url in the B4A app im getting an error saying

    Error occurred. Query=
    System.InvalidOperationException: ExecuteReader: CommandText property has not been initialized


    An that the error happend on line 26 which is
    "cn.Open();"


    Now the obvious thing is that the server did not get the query, but i cannot work out why not.

    Here are the asp, and B4A code used.

    [ASP]<script runat="server">
    protected void Page_Load(object sender, EventArgs ec)
    {
    using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection("Data Source=VM-ONE;Database=TheITCrowd; User Id=it_crowd; password=1qaz!QAZ")) //change as needed
    {
    using (System.IO.StreamReader sr = new System.IO.StreamReader(Request.InputStream, Encoding.UTF8))
    {
    Response.ContentType = "text/plain";
    String c = HttpContext.Current.Request.Url.Query;
    c = c.Replace("?query=", "");
    c = c.Replace("%20", " ");

    //c = Request.QueryString[""]; //for debugging with the browser
    //you can set the query by adding the query parameter For ex: <a href="http://127.0.0.1/test.aspx?query=select" target="_blank">http://127.0.0.1/test.aspx?query=select</a> * from table1
    if (c == null)
    c = sr.ReadToEnd();
    try
    {

    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(c, cn);
    cn.Open();
    System.Data.SqlClient.SqlDataReader rdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
    while (rdr.Read())
    {
    Dictionary<string, object> d = new Dictionary<string, object>(rdr.FieldCount);
    for (int i =0;i < rdr.FieldCount;i++)
    {
    d[rdr.GetName(i)] = rdr.GetValue(i);
    }
    list.Add(d);
    }
    System.Web.Script.Serialization.JavaScriptSerializer j = new System.Web.Script.Serialization.JavaScriptSerializer();
    Response.Write(j.Serialize(list.ToArray()));

    } catch (Exception e)
    {
    Response.TrySkipIisCustomErrors = true;
    Response.StatusCode = 500;
    Response.Write("Error occurred. Query=" + c + "\n");
    Response.Write(e.ToString());

    }
    Response.End();
    }
    }
    }[/ASP]







    And the B4A code

    'Activity module
    Sub Process_Globals

    End Sub

    Sub Globals
    Dim ServerUrl As String
    ServerUrl = "http://192.168.1.21:8123/webform1.aspx"
    End Sub

    Sub Activity_Create(FirstTime As Boolean)
    Dim job1 As HttpJob
    job1.Initialize("Job1", Me)

    'Send a GET request
    job1.PostString(ServerUrl, "select col1,col2 from employees")


    End Sub

    Sub JobDone (Job As HttpJob)
    Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
    If Job.Success = True Then
    Select Job.JobName
    Case "Job1", "Job2"
    'print the result to the logs
    Log(Job.GetString)
    Case "Job3"
    'show the downloaded image
    Activity.SetBackgroundImage(Job.GetBitmap)
    End Select
    Else
    Log("Error: " & Job.ErrorMessage)
    ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
    End Sub

    Sub Activity_Resume

    End Sub

    Sub Activity_Pause (UserClosed As Boolean)

    End Sub




    Please help as i am out of ideas what to do.
    Thank you very much for any help:)
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    Which error do you get? Try to open this Url with the device browser. Does it work?
     
  8. schalkgreyling

    schalkgreyling Member Licensed User

    Sorry oky so a bit more info about the problem, i ran it in AVD manager but also tested it on my phone,
    the error i g3t in ADV manager is :

    HTML:
    LogCat connected to: emulator-5554
    ** Activity (main) Create, isFirst = true **
    
    
    Starting Job: Job1
    
    
    ** Activity (main) Resume **
    
    
    ** Service (httputilsservice) Create **
    
    
    ** Service (httputilsservice) Start **
    
    
    Error. Url=http://192.168.1.21:8123/webform1.aspx Message=Internal Server Error
    
    
    Error occurred. Query=
    
    
    System.InvalidOperationException: ExecuteReader: CommandText property has not been initialized
    
       at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
    
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
    
       at ASP.webform1_aspx.Page_Load(Object sender, EventArgs ec) in c:\Users\Smegal\documents\visual studio 2012\projects\webapplication3\webapplication3\WebForm1.aspx:line 27
    ** Service (httputilsservice) Destroy **

    ----------------------------------------------------------------------------------
    Notice that is says "Error occurred. Query="
    Meaning that it does not receive the query i assume ?
    Since the Query is blank..

    When i open my web browser and insert "http://localhost:8123/",
    i get the same error, but when i enter in the URL of the web browser
    "http://192.168.1.21:8123/webform1.aspx?query=select%20*%20from%20employees"
    i get reply

    HTML:
    [{"ID":"1","Name":"Curtis","Surname":"Cleenverck","Cell_num":"082 672 4548","Email_add":"curtiscleenverck@gmail.com","SA_ID":"1"}]
    which is what i need on the mobile side.

    Thank you very much for taking time to help me.

    here are the code for asp.net
    and for B4A
     
    Last edited: May 5, 2013
  9. Erel

    Erel Administrator Staff Member Licensed User

    Why have you changed the script? Try running the script as posted in the tutorial.
     
  10. schalkgreyling

    schalkgreyling Member Licensed User

    =D=D=D
    Oky so after going back to the tutorial script i found my problem and it works now. Feel so dumb right now :sign0104: me :)

    Thank you :icon_clap::icon_clap::icon_clap:
     
  11. altc

    altc New Member Licensed User

    I wonder if this example works only with apache or IIS can use

     
  12. Erel

    Erel Administrator Staff Member Licensed User

    This thread is about MS SQL Server running in IIS server.
     
  13. Peekay

    Peekay Member Licensed User

    VB script

    Your first piece of code (script) is in C#. Can you possibly let me have it in VB.
    Thanks
     
  14. altc

    altc New Member Licensed User

    Error access Sql Anywhere

    I send "http://localhost/dbremoto.asmx?query=select codigo,nome from acadclie", for the browser and the data return ok normally.

    When I try exec the code bellow, the error occurs.

    Error. Url=http://localhost/dbremoto.aspx Message=org.apache.http.conn.HttpHostConnectException: Connection to http://localhost refused

    My code
    #Region Module Attributes
    #FullScreen: False
    #IncludeTitle: True
    #ApplicationLabel: SQL Server
    #VersionCode: 1
    #VersionName:
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
    #End Region

    'Activity module
    Sub Process_Globals
    Dim ServerUrl As String
    ServerUrl = "http://localhost/dbremoto.aspx"
    End Sub

    Sub Globals

    End Sub

    Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
    HttpUtils.CallbackActivity = "Main"
    HttpUtils.CallbackJobDoneSub = "JobDone"
    End If
    HttpUtils.PostString("Job1", ServerUrl, "SELECT Codigo, Nome FROM ACadClie")
    End Sub

    Sub Activity_Resume
    If HttpUtils.Complete = True Then JobDone(HttpUtils.Job)
    End Sub

    Sub Activity_Pause (UserClosed As Boolean)

    End Sub

    Sub JobDone (Job As String)
    If HttpUtils.IsSuccess(ServerUrl) Then ' the error occurs here
    Dim parser As JSONParser
    Dim response As String
    response = HttpUtils.GetString(ServerUrl)
    parser.Initialize(response)
    Dim rows As List
    rows = parser.NextArray

    'work with result
    'rows is a List. Each item is a Map with the columns names as keys and the db values as the values.
    For i = 0 To rows.Size - 1
    Log("Linha #" & i)
    Dim m As Map
    m = rows.Get(i)
    Log("Codigo=" & m.Get("Codigo")) 'log the values of col1 and col2
    Log("Nome=" & m.Get("Nome"))
    Next
    End If
    HttpUtils.Complete = False
    End Sub
     
    Last edited: Jun 27, 2013
  15. Erel

    Erel Administrator Staff Member Licensed User

    localhost points to the current device (or emulator). I guess that you are running it on the emulator.

    You should use the ip address: 10.0.2.2 it is a special address (in the emulator) that points to the desktop localhost.
     
  16. altc

    altc New Member Licensed User

    Funcionou acesso ao SQL anywhere

    Thangs very much, Erel
    I change for 10.0.2.2 then is ok
     
  17. AlteregoHR

    AlteregoHR Member Licensed User


    Hello,
    I have exactly the same problem.
    How did you managed to solve it?
    Thanks
     
  18. Erel

    Erel Administrator Staff Member Licensed User

    Are you sure that the database name, user name and password are correct?
     
  19. AlteregoHR

    AlteregoHR Member Licensed User

    Thank you Erel,
    I was firewall problem.
     
  20. Kwame Twum

    Kwame Twum Active Member Licensed User

    Hi I would like to know if it's possible to execute two queries without invoking "Already working. Request ignored". If so, how do I go about it? I want to refresh a view right after inserting new data.
     
Thread Status:
Not open for further replies.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice