SELECT LAST_INSERT_ID() always returns 0

Asked

Viewed 301 times

0

I have the following codes:

    public async Task EnviaMySQL<T>(string sql,bool pass = false, string file = "SqlJson.php")
    {
        if(!pass)
            Utils.Acoes.Load.ShowLoad();
        var result = "";

        var lista = new List<T>();

        //var gifPass = Utils.Acoes.GetConfig<bool> (c, "gif");

        try
        {
            HttpClient client = new HttpClient();
            client.Timeout = TimeSpan.FromSeconds(5);

            using (var content = new MultipartFormDataContent())
            {
                content.Add(new StringContent(sql), "sql");
                if (sql.Contains("multi"))
                {
                    sql = sql.Remove(sql.Length - 5);
                    content.Add(new StringContent("multi"), "multi");
                }

                UriBuilder uriBuilder = new UriBuilder("http://" + (new IRealm()).Infopass().Ip);
                uriBuilder.Path += "Toten/" + file;

                HttpResponseMessage response = await client.PostAsync(new Uri(uriBuilder.Uri.ToString()), content);

                response.EnsureSuccessStatusCode();
                string a = await response.Content.ReadAsStringAsync();

                var array = JArray.Parse(a.Trim('{', '}'));
                if (!pass)
                    Utils.Acoes.Load.HideLoad();
                OnFinish(array.ToObject<List<T>>());

            }
        }

        catch (Exception g)
        {
            if (!pass)
                Utils.Acoes.Load.HideLoad();
            OnErrorNet(EventArgs.Empty);
        }
    }

    public async void InsertMovCaixa(int cd_caixa, string vl, string obs, string cd_cli, string forma, string nm_bandeira)
    {
        var b = new BancoN();
        SetBanco(b);
        await b.EnviaMySQL<Objects.Ficha_Def>($"INSERT INTO mov_caixa(cd_caixa,vl,dt,obs,cd_cli,forma,operacao,nm_bandeira) " +
            $"VALUES({cd_caixa},{vl},NOW(),NULL,NULL,'{forma}','VENDA',NULL) ;");

    }

    public async void SelectLastInsert()
    {
        var b = new BancoN();
        SetBanco(b);
        await b.EnviaMySQL<Item_Ficha_Temp>($"SELECT LAST_INSERT_ID();");
    }

First I do the InsertMovCaixa and then I need to get the SelectLastInsert, however, debugging, I realized that the SELECT LAST_INSERT_ID(); is returning 0 every time.

From what I’ve read, it could be because she’s all done sql a new connection is made in the bank.

How can I make it work?

EDIT:

Doing as Leonardo suggested:

response.Content.ReadAsStringAsync();

Is returning:

"Error selecting: "INSERT INTO mov_box(cd_box,Vl,dt,Obs,cd_cli,shape,operation,nm_flag) VALUES(1,14.4,NOW(),NULL,NULL,'CARTAO','VENDA',NULL); SELECT LAST_INSERT_ID(); "original message:You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'SELECT LAST_INSERT_ID()' at line 1"

I also tried with the InsertMovCaixa thus:

var b = new BancoN();
SetBanco(b);
await b.EnviaMySQL<Objects.Ficha_Def>($"INSERT INTO mov_caixa(cd_caixa,vl,dt,obs,cd_cli,forma,operacao,nm_bandeira) " +
                $"VALUES({cd_caixa},{vl},NOW(),NULL,NULL,'{forma}','VENDA',NULL); ");

await b.EnviaMySQL<Item_Ficha_Temp>($"SELECT LAST_INSERT_ID();");

But also returns 0.

Something is missing in the EnviaMySQL<T>? I can do something different?

c# sql xamarin

  • Most likely you are generating new connections in var b = new BancoN();, where the INSERT ID is only valid for the current session. Normally you should make a connection only with the bank and all the necessary operations on the same connection, even to reuse resources. The exception is in case of time consuming operations outside the DB and need to meet a very high amount of simultaneous clients (the important thing is to understand the advantages and disadvantages of each case).

  • 2

    You use a php code to write to the database SqlJson.php. There is the possibility to show this code because the approach used by op @Leonardo Buta would be enough to lead me to believe that the query within php does not return values or if it returns is a fixed value.

  • 1

    I decided to put the SELECT LAST_INSERT_ID(); right in the SqlJson.php! Thanks @Augustovasques

2 answers

3

You need to execute the command right after Insert, so it will be within the same execution and ensure that it returns the correct ID.

Your code will look like this:

public async void InsertMovCaixa(int cd_caixa, string vl, string obs, string cd_cli, string forma, string nm_bandeira)
    {
        var b = new BancoN();
        SetBanco(b);
        await b.EnviaMySQL<Objects.Ficha_Def>($"INSERT INTO mov_caixa(cd_caixa,vl,dt,obs,cd_cli,forma,operacao,nm_bandeira) " +
            $"VALUES({cd_caixa},{vl},NOW(),NULL,NULL,'{forma}','VENDA',NULL);" +
              "SELECT LAST_INSERT_ID();");

    }

  • It didn’t work out... Doing this the "Sponse.Content.Readasstringasync();" returns: "Error when selecting: "INSERT INTO mov_box(cd_box,Vl,dt,Obs,cd_cli,form,operation,nm_flag) VALUES(1,14.4,NOW(),NULL,'CARTAO','SALE',NULL); SELECT LAST_INSERT_ID();" original message:You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'SELECT LAST_INSERT_ID()' at line 1"

0


The solution was to put the SELECT LAST_INSERT_ID(); right in the SqlJson.php

Browser other questions tagged c# sql xamarin

You are not signed in. Login or sign up in order to post.