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. MohammadNew

    MohammadNew Active Member Licensed User

    How to modify the ASP.Net script to support commands ?

    and when I use like this

    Code:
    update >
        job.PostString(ServerUrl & 
    "?query=update tbl1 set id='" & EditText1.Text & "',fname='" & EditText2.Text & "',address='" & EditText3.Text & "'" , "")

    delete >
    job.PostString(ServerUrl & 
    "?query=delete from tbl1" , "")
    is working fine but update all records and delete all records too.

    when I put where does not work and no error no anything

    if you want my example to try ok tell me my teacher >

    after finish this tutorial I am going to learn RDC
     
    Last edited: Jun 27, 2016
  2. jsanchezc

    jsanchezc Member Licensed User

    Hello, you did not write "where ..."
    remember place space before where .... and put 'xxxxx' if field is string type

    update >
    job.PostString(ServerUrl & "?query=update tbl1 set id='" & EditText1.Text & "',fname='" & EditText2.Text & "',address='" & EditText3.Text & "'" & " Where fieldname='" & value & "'", "")

    delete >
    job.PostString(ServerUrl & "?query=delete from tbl1 Where fieldname='" & value & "'" , "")

    best practice: tables and fields names between [ & ]:
    (you 'll avoid some headache)

    update >
    job.PostString(ServerUrl & "?query=update [tbl1] set [id]='" & EditText1.Text & "',[fname]='" & EditText2.Text & "',[address]='" & EditText3.Text & "'" & " Where [fieldname]='" & value & "'", "")

    delete >
    job.PostString(ServerUrl & "?query=delete from [tbl1] Where [fieldname]='" & value & "'" , "")

    Hope this help
     
  3. MohammadNew

    MohammadNew Active Member Licensed User

    sadly does not work is the same my code
    no error no delete no update
    Just insert and select work fine without where
     
  4. MohammadNew

    MohammadNew Active Member Licensed User

    Jsanchezc Thanks, the problem was the table the fields was Text when I changed it to varchar work fine.

    and Thanks Erel
     
  5. Scotter

    Scotter Active Member Licensed User

    I got your C#.Net example working to pull data from a database into the browser.
    I even was able to get my B4A app to send SQL statements as querystrings and have that C#.Net page facilitate the transfer.
    Then I decided I wanted to put the SQL statements in the C#.Net page and use a "switch/case" command to pick the SQL to be submitted to the database, based on my B4A application just sending a "command" via querystring.
    Here's where I'm caught up. I'm not a C# programmer. I'm going to post this in a C# forum but also here, because some of you may be trying to do what I'm doing. So ... here's my code in hopes that someone can help me find a way to figure out the following warning that comes up in FireFox when I test this out in the browser:

    "The character encoding of the plain text document was not declared. The document will render with garbled text in some browser configurations if the document contains characters from outside the US-ASCII range. The character encoding of the file needs to be declared in the transfer protocol or file needs to use a byte order mark as an encoding signature."

    Here's the code:
    Code:
    <%@ Page Language="C#" %>
    <%@ Import Namespace=
    "System.Collections.Generic" %>
    <%@ Import Namespace=
    "System.Data" %>
    <%@ Import Namespace=
    "System.Data.SqlClient" %>
    <%@ Import Namespace=
    "System.IO" %>
    <%@ Import Namespace=
    "System.Web.Script.Serialization" %>

    <!DOCTYPE html 
    PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <script runat=
    "server">
        protected void Page_Load(object 
    sender, EventArgs ec)
        {
            //level 
    01
            using (SqlConnection conn = new SqlConnection(
    "Database=NUTSHELL; User Id=Andy; password=xyzxyz"))
            {
                    //level 
    02
                    Response.ContentType = 
    "text/plain";
                    
    string s = "";
                    
    string u = Request.QueryString["u"];
                    
    if (u == "vJ39k84g!7S2r#wV4")
                    {
                        //level 
    04
                        
    string sCmd = Request.QueryString["sCmd"];
                        
    string IDCategory;
                        
    string sCategory;
                        
    switch (sCmd)
                        {
                            
    case "GetCategoryNames":
                                s = 
    "SELECT";
                                //s += 
    " Id";
                                s += 
    " name";
                                //s += 
    ", defaultSort";
                                s += 
    " FROM Category";
                                s += 
    " WHERE";
                                s += 
    " (";
                                s += 
    " ShowOnHomePage=1";
                                s += 
    " AND IncludeInTopMenu=1";
                                s += 
    " AND Published=1";
                                s += 
    " )";
                                s += 
    " ORDER BY name";
                                //s = 
    "SELECT name FROM Category ORDER BY name";
                                break;
                            
    case "GetCategoryId":
                                sCategory = Request.QueryString[
    "sCategory"];
                                s = 
    "SELECT";
                                s += 
    " Id";
                                s += 
    " FROM Category";
                                s += 
    " WHERE";
                                s += 
    " (";
                                s += 
    " [name]='" + sCategory + "'";
                                s += 
    " )";
                                break;
                        
    }

                        try
                        {
                            SqlCommand cmd = new SqlCommand(s, conn);
                            conn.Open();
                            SqlDataReader rdr = cmd.ExecuteReader(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);
                            }
                            JavaScriptSerializer j = new JavaScriptSerializer();
                            Response.Write(j.Serialize(list.ToArray()));
                        }
                        catch (Exception e)
                        {
                            Response.TrySkipIisCustomErrors = true;
                            Response.StatusCode = 500;
                            Response.Write("Error occurred. Query=" + s + "\n");
                            Response.Write(e.ToString());
                        }

                        //level 03
                    }
                    Response.End();
                //level 02
            }
            //level 01
        }
    </script>
     
    Last edited: Nov 26, 2016
  6. JOHN BAUSTIN

    JOHN BAUSTIN New Member

    Hello... I used this code . But I m getting error HTTPUTils cannot be resolved to a variable. Can you please help me out..
     
    Last edited by a moderator: Apr 20, 2017
  7. Erel

    Erel Administrator Staff Member Licensed User

  8. Infotech

    Infotech Member Licensed User

    hi erel why my connection sql server is not found or not acces, if i connect with ODBC this sql server is conected, why why why


    this code asp.net
    <%@ Page Language="C#"%>
    <%@ Import Namespace="System.Collections.Generic" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.IO" %>
    <%@ Import Namespace="System.Web.Script.Serialization" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <script runat="server">
    protected void Page_Load(object sender, EventArgs ec)
    {
    using (SqlConnection cn = new SqlConnection("Database=PASTISSS; User Id=USERSQL; password=")) //change as needed
    {
    using (StreamReader sr = new StreamReader(Request.InputStream, Encoding.UTF8))
    {
    Response.ContentType = "text/plain";
    string c;
    c = Request.QueryString["query"]; //for debugging with the browser
    //you can set the query by adding the query parameter For ex: http://127.0.0.1/test.aspx?query=select * from table1
    if (c == null)
    c = sr.ReadToEnd();
    try
    {
    SqlCommand cmd = new SqlCommand(c, cn);
    cn.Open();
    SqlDataReader rdr = cmd.ExecuteReader(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);
    }
    JavaScriptSerializer j = new 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();
    }
    }
    }
    </script>



    and this result for asp.net

    Error occurred. Query=
    System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
    at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
    at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
    at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
    at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
    at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.SqlClient.SqlConnection.Open()
    at ASP.website1_default_aspx.Page_Load(Object sender, EventArgs ec) in c:\inetpub\wwwroot\WebSite1\Default.aspx:line 25
     
  9. DonManfred

    DonManfred Expert Licensed User

    ODBC Bridge is removed in JAVA 8.
    You should switch to use jRDC
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    Please start a new thread in the questions forum if it is not solved.
     
  11. Infotech

    Infotech Member Licensed User

    I still can not connect, after my computer re-install

    I need a web config on asp.net

    this code maybe not work and why no conection?
     

    Attached Files:

  12. jatko

    jatko Member Licensed User

    How to use the script with MS SQL instance? I have the error in line 12

    12: using (SqlConnection cn = new SqlConnection("Server=localhost\insertgt; Database=something; User Id=someone; password=something"))


     
  13. DonManfred

    DonManfred Expert Licensed User

    You should create a NEW thread in the questionsforum for EACH question you have.
     
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