Android Tutorial Connect Android to MS SQL Server Tutorial

MohammadNew · Jun 27, 2016

Erel said:
You will need to modify the ASP.Net script to support commands. Why aren't you using RDC?

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

jsanchezc · Jun 27, 2016

MohammadNew said:
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

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

MohammadNew · Jun 27, 2016

jsanchezc said:
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

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

MohammadNew · Jun 29, 2016

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

and Thanks Erel

Scotter · Nov 26, 2016

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>

JOHN BAUSTIN · Apr 20, 2017

Hello... I used this code . But I m getting error HTTPUTils cannot be resolved to a variable. Can you please help me out..

Erel · Apr 20, 2017

Download the example attached to the first post: https://www.b4x.com/android/forum/threads/connect-android-to-ms-sql-server-tutorial.13166/
The code is older and is based on the deprecated HttpUtils (v1).

Infotech · Apr 21, 2017

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

DonManfred · Apr 21, 2017

Infotech said:
if i connect with ODBC this sql server is conected, why

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

Erel · Apr 21, 2017

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

Infotech · Apr 21, 2017

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?

jatko · Oct 10, 2017

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"))

Scotter said:

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>

DonManfred · Oct 10, 2017

jatko said:
How to use the script with MS SQL instance?

You should create a NEW thread in the questionsforum for EACH question you have.

Android Tutorial Connect Android to MS SQL Server Tutorial

Attachments

MohammadNew

Active Member

jsanchezc

Member

MohammadNew

Active Member

MohammadNew

Active Member

Scotter

Active Member

JOHN BAUSTIN

New Member

Erel

B4X founder

Infotech

Member

DonManfred

Expert

Erel

B4X founder

Infotech

Member

Attachments

jatko

Member

DonManfred

Expert

Similar Threads