locked
SIGNATURE CANNOT SAVE INTO DATABASE RRS feed

  • Question

  • User2132754812 posted

    Hi all,

    I am not manage to save signature into database. I already convert the signature into byte but error during executenonquery. 

    This is my view:

    @using (Html.BeginForm("Signature", "ServiceReport", FormMethod.Post))

    {

    @Html.AntiForgeryToken()
    <div class="row">
    <div class="col-12 col-md-12 col-lg-9">
    <div class="card">
    <div class="card-header-pills col-md-6">
    <h2 class="section-title">Signature</h2>
    </div>
    <div class="card-body">
    @Html.HiddenFor(model => model.servID)
    <canvas id="canvas" style="border: 0.5px solid" width="800" height="400"></canvas>
    <div class="card-footer">
    <div class="text-right">
    <div class="form-group text-left">
    <input id="signature" type="submit"class="btn btn-primary" value="Save Siganture">
    <button id="clear" class="btn btn-danger">Clear</button>
    <button id="undo" class="btn btn-warning">Undo</button>
    <input type="submit" onclick="location.href='@Url.Action("Report", "ServiceReport", new { servID = Model.servID })'" class="btn btn-info float-right" value="Generate Report" />
    <input type="submit" onclick="location.href='@Url.Action("SendServiceReport", "ServiceReport", new { servID = Model.servID })'" class="btn btn-yahoo float-right" value="Generate Report" />
    </div>
    </div>
    </div>

    </div>
    </div>
    </div>
    </div>
    }

    <script src="https://cdn.jsdelivr.net/npm/signature_pad@2.3.2/dist/signature_pad.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function () {
    //var canvas = document.getElementById("sig-canvas");
    //var ctx = canvas.getContext("2d");
    debugger;
    var canvas = document.getElementById("canvas");
    var signaturePad = new SignaturePad(canvas);

    $("#clear").click(function () {
    signaturePad.clear();
    });

    $("#undo").click(function () {
    var data = signaturePad.toData();

    if (data) {
    data.pop();
    signaturePad.fromData(data);
    }
    });

    $("#signature").click(function () {
    var isSignatureSaved = false;
    var image = document.getElementById("canvas").toDataURL("image/png");
    image = image.replace('data:image/png;base64,', '');
    $.ajax({
    type: 'POST',
    url: "@Url.Action("Signature","ServiceReport")",
    data: '{ "imageData" : "' + image + '"}',
    contentType: 'application/json; charset=utf-8',
    dataType: 'json',
    success: function (data) {
    isSignatureSaved = true;
    swal.fire({
    title: "Message!",
    text: "Signature successfully save",
    type: "sucess"
    });
    }
    })
    });
    });

    </script>

    This is the model:

    public byte[] servImage { get; set; }

    This is the controller:

    [HttpPost]
    public ActionResult Signature(String imageData, ServiceReport OSRModel)
    {

    ServiceReportHandle OSRDB = new ServiceReportHandle();
    //if (TempData["ServiceReportData"] != null)
    //{
    //var ServiceReportData = TempData["ServiceReportData"] as ServiceReport;
    if (imageData != null)
    {

    string fileNameWitPath = "/Signature/" + "_" + OSRModel.servNum + DateTime.Now.ToString().Replace("/", "-").Replace(" ", "- ").Replace(":", "") + ".png";


    using (FileStream fs = new FileStream(HostingEnvironment.MapPath(fileNameWitPath), FileMode.Create))
    {


    using (StreamWriter writer = new StreamWriter(fs))
    {

    byte[] data = Convert.FromBase64String(imageData);
    //string servImage = Convert.ToBase64String(data);
    //var imgSrc = String.Format("data:image/png;base64,{0}", data);
    //System.IO.File.WriteAllBytes(fileNameWitPath, data);
    var stream = new MemoryStream();
    writer.Write(data);
    writer.Flush();
    stream.Position = 0;
    //System.Drawing.Image image;
    //using (MemoryStream ms = new MemoryStream(data))
    //{
    // image = System.Drawing.Image.FromStream(ms);
    //}
    //bw.Write(data, 0, data.Length);
    //bw.Close();
    fs.Close();

    OSRModel.servImage = data;
    bool result = OSRDB.InsertOSR(OSRModel);
    if (result)
    {
    TempData["ServiceReportSignature"] = OSRModel;
    ViewBag.Message = "The Signature is save Successfully";
    return RedirectToAction("Signature");
    }
    else
    {
    ViewBag.Message = "Error in saving data";
    return RedirectToAction("Signature");
    }
    }

    }

    }
    //}
    return View(OSRModel);
    }

    This is the stored procedure:

    public bool InsertOSR(ServiceReport OSRModel)
    {
    connection();
    SqlCommand command = new SqlCommand("MasterOSR", con);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@StatementType", SqlDbType.NVarChar, 20).Value = "Insert";
    var serv_numb = DateTime.Now.ToString("yyyyMMddss");
    var serv_created = DateTime.Now.ToString();

    command.Parameters.Add("@servno", SqlDbType.NVarChar, 50).Value = serv_numb;
    command.Parameters.AddWithValue("@custid", OSRModel.custID);
    command.Parameters.AddWithValue("@branchid", OSRModel.branchID);
    command.Parameters.AddWithValue("@userid", OSRModel.userID);
    command.Parameters.AddWithValue("@servcontact", OSRModel.servContactPerson);
    command.Parameters.AddWithValue("@servphone", OSRModel.servPhone);
    command.Parameters.AddWithValue("@servemail", OSRModel.servEmail);
    command.Parameters.AddWithValue("@servreport", OSRModel.servDateReported);
    command.Parameters.AddWithValue("@servarrival", OSRModel.servDateArrival);
    command.Parameters.AddWithValue("@servcompleted", OSRModel.servDateCompletion);
    command.Parameters.AddWithValue("@servdesc", OSRModel.servDescComplain);
    command.Parameters.AddWithValue("@actiontaken", OSRModel.actionID);
    command.Parameters.AddWithValue("@actiondiagno", OSRModel.actionDiagnoID);
    command.Parameters.AddWithValue("@connection", OSRModel.connectionType);
    command.Parameters.AddWithValue("@servhostname", OSRModel.servPCHost);
    command.Parameters.AddWithValue("@servipadd", OSRModel.servIPAdd);
    command.Parameters.AddWithValue("@servsubnet", OSRModel.servSubnet);
    command.Parameters.AddWithValue("@servgateway", OSRModel.servGateway);
    command.Parameters.AddWithValue("@servversion", OSRModel.servVersion);
    command.Parameters.AddWithValue("@servpack", OSRModel.servpack);
    command.Parameters.AddWithValue("@servfeedback", OSRModel.servFeedback);
    command.Parameters.AddWithValue("@jobstatusid", OSRModel.jobStatusID);
    command.Parameters.AddWithValue("@statusid", OSRModel.statusID);
    command.Parameters.Add("@servcreated", SqlDbType.DateTime).Value = serv_created;
    command.Parameters.AddWithValue("@servcreatedBy", OSRModel.servCreatedBy);
    command.Parameters.AddWithValue("@ImageName", OSRModel.servImage);
    command.Parameters.AddWithValue("@servfile", OSRModel.servfile);


    con.Open();
    int i = command.ExecuteNonQuery();  //error here
    con.Close();

    if (i >= 1)
    return true;
    else
    return false;
    }

    Error: executenonquery() is return 0 even the data is there. 

    please tell me where I'm wrong. I already google and not manage to get the answer. 

    Thursday, August 15, 2019 4:44 AM

All replies

  • User665608656 posted

    Hi jyehahaha,

    According to your code, I need to confirm with you that when you run the code, do it throw any exception warning?

    If so, please provide this error detailed information.

    And are you performing data insertion? Or is it just updating statements?

    Since I don't know the content of your stored procedure and the structure of your data table, I hope you can provide more detailed information so that we can find out the cause of the issue.

    The following links may help you:

    SqlCommand.ExecuteNonQuery Method

    ExecuteNonQuery() returns 0 in update or insert always i need to display an alert

    Best Regards,

    YongQing.

    Thursday, August 15, 2019 7:22 AM
  • User2132754812 posted

    Hi yongqing,

    Thanks for reply. This code is for insert statement.

    There is no exception warning when i run the code. The data just not insert to database and error at  int i = command.ExecuteNonQuery(); . I try to insert other data which contains textbox and dropdown and its run successfully without error. So I really don't know what is the problem to save the signature. 

    Thanks

    Thursday, August 15, 2019 7:41 AM
  • User665608656 posted

    Hi jyehahaha,

    There is no exception warning when i run the code. The data just not insert to database and error at  int i = command.ExecuteNonQuery(); . I try to insert other data which contains textbox and dropdown and its run successfully without error. So I really don't know what is the problem to save the signature. 

    According to your description, I hope you can provide us with your detailed stored procedure in your database, this will help us sovle your issue more easily.

    Best Regards,

    YongQing.

    Thursday, August 15, 2019 8:06 AM
  • User2132754812 posted

    Hi yongqing,

    Sure. I am used stored procedure for my database

    This is my database structure:-

    @servid INT = NULL,
    @servno VARCHAR(50) = NULL,
    @custid INT = NULL,
    @branchid INT = NULL,
    @userid INT = NULL,
    @servcontact VARCHAR(50) = NULL,
    @servphone VARCHAR(50) = NULL,
    @servemail VARCHAR(100) = NULL,
    @servreport DATETIME = NULL,
    @servarrival DATETIME = NULL,
    @servcompleted DATETIME = NULL,
    @servdesc VARCHAR(200) = NULL,
    @actiontaken INT = NULL,
    @actiondiagno INT = NULL,
    @connection NVARCHAR(MAX) = NULL,
    @servhostname VARCHAR(50) = NULL,
    @servipadd VARCHAR(50) = NULL,
    @servsubnet VARCHAR(50) = NULL,
    @servgateway VARCHAR(50) = NULL,
    @servversion VARCHAR(50) = NULL,
    @servpack VARCHAR(50) = NULL,
    @servfeedback VARCHAR(200) = NULL,
    @jobstatusid INT = NULL,
    @statusid INT = NULL,
    @servcreated DATETIME = NULL,
    @servcreatedBy VARCHAR(50) = NULL,
    @servupdated DATETIME = NULL,
    @servupdatedBy VARCHAR(50) = NULL,
    @statementType nvarchar(20)= NULL,
    @ImageName VARBINARY(MAX)= NULL,
    @servfile NVARCHAR(MAX)=NULL

    AS
    BEGIN
    SET IDENTITY_INSERT [dbo].[tbl_serviceOSR] OFF

    IF @statementType = 'INSERT'
    BEGIN

    INSERT INTO [dbo].[tbl_serviceOSR]
    (
    SERV_NUMBER,
    CUST_ID,
    BRANCH_ID,
    USER_ID,
    SERV_CONTACTPERSON,
    SERV_PHONENO,
    SERV_EMAIL,
    SERV_DATE_REPORTED,
    SERV_DATE_ARRIVAL,
    SERV_DATE_COMPLETION,
    SERV_DESCRIPTION_COMPLAIN,
    ACTION_ID,
    ACTION_DIAGNOSTICS_ID,
    CONNECTION,
    SERV_PC_HOSTNAME,
    SERV_IPADD,
    SERV_SUBNET,
    SERV_GATEWAY,
    SERV_VERSION,
    SERV_PACK,
    SERV_FEEDBACK,
    JOB_STATUS_ID,
    STATUS_ID,
    SERV_CREATED,
    SERV_CREATEDBY,
    SERV_IMAGE,
    SERV_FILE
    )
    VALUES
    (
    @servno,
    @custid,
    @branchid,
    @userid,
    @servcontact,
    @servphone,
    @servemail,
    CONVERT(DATETIME, @servreport),
    CONVERT(DATETIME, @servarrival),
    CONVERT(DATETIME, @servcompleted),
    @servdesc,
    @actiontaken,
    @actiondiagno,
    @connection,
    @servhostname,
    @servipadd,
    @servsubnet,
    @servgateway,
    @servversion,
    @servpack,
    @servfeedback,
    @jobstatusid,
    @statusid,
    CONVERT(DATETIME, @servcreated),
    @servcreatedBy,
    @ImageName,
    @servfile

    )

    END

    Thursday, August 15, 2019 8:09 AM
  • User665608656 posted

    Hi jyehahaha,

    According to your stored procedure , I tested it and everything worked.

    I recommend that you try to delete if statements from stored procedures to test whether insertion is successful:

    IF @statementType = 'INSERT' 

    If this still does not solve your issue, I suggest that you can debug your stored procedure to confirm whether you have entered if criteria to execute insert statements.

    About how to debug your stored procedure, you can refer to this link: Debugging stored procedures in SQL Server Management Studio (SSMS)

    Hope these can help you.

    Best Regards,

    YongQing.

    Friday, August 16, 2019 8:51 AM
  • User753101303 posted

    Hi,

    If you see that 0 is returned (this is the "error" you are talking about ?) could it be that your INSERT statement is not called at all. It would happen if the db is case sensitive as you are using INSERT and Insert.

    If @statementType is not one of the expected value always add a ELSE case that triggers an error so that you know about that rather than having as SP that just does nothing.

    My personal preference is just to have xxDelete, xxInsert and xxUpdate SPs rather than a single SP and passing a "name" (in  short you pass as an argument a part of the name of the procedures you should have).

    Edit: if you have just that what if you just remove this IF test ?

    Friday, August 16, 2019 9:25 AM
  • User2132754812 posted

    Hi jyehahaha,

    According to your stored procedure , I tested it and everything worked.

    I recommend that you try to delete if statements from stored procedures to test whether insertion is successful:

    IF @statementType = 'INSERT' 

    If this still does not solve your issue, I suggest that you can debug your stored procedure to confirm whether you have entered if criteria to execute insert statements.

    About how to debug your stored procedure, you can refer to this link: Debugging stored procedures in SQL Server Management Studio (SSMS)

    Hope these can help you.

    Best Regards,

    YongQing.

    Hi Yongqing,

    Sorry for the delay.

    It is still not solved the problem. I have test as per your instruction but still not successful. 

    Wednesday, August 21, 2019 8:24 AM
  • User2132754812 posted

    Hi,

    If you see that 0 is returned (this is the "error" you are talking about ?) could it be that your INSERT statement is not called at all. It would happen if the db is case sensitive as you are using INSERT and Insert.

    If @statementType is not one of the expected value always add a ELSE case that triggers an error so that you know about that rather than having as SP that just does nothing.

    My personal preference is just to have xxDelete, xxInsert and xxUpdate SPs rather than a single SP and passing a "name" (in  short you pass as an argument a part of the name of the procedures you should have).

    Edit: if you have just that what if you just remove this IF test ?

    Hi PatriceSc,

    I think the single SP its not the problem. SP also not a case sensitive. I can insert, edit and delete without the problem. The problem only if i want to save the signature. The rest i can successful insert without problem. 

    Wednesday, August 21, 2019 8:27 AM
  • User753101303 posted

    I always prefer to take few minutes to see what actually happens rather than reading code and trying to guess.

    First what is the exact problem? Have you SEEN that the row is not inserted ? It should not fail silently and most often it is:
       - the insert code is not even called (use a breakpoint, here I would change the SP temporarily to make 100% sure the INSERT statement is called if the SP is called)
       - the insert code is called but the exception is ignored
       - the insert code is called and it actually works but something make you think it fails (unexpected return value, checking the wrong db, having code that delete added rows, a db file being overwritten each time etc...)

    If the row is inserted, but 0 is returned, get rid of the SET IDENTITY_INSERT statement (if ever turned ON, it should be turned OFF as soon as possible by the same script) to make 100% sure the returned information comes from the INSERT statement...

    Wednesday, August 21, 2019 9:15 AM