Give this a go:
<%
' Check if the form is submitted
If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
' Retrieve the signature data from the request
Dim signatureData
signatureData = Request.Form("signatureData")
' Split the base64 data from the "data:image/png;base64," part
Dim base64String
base64String = Mid(signatureData, InStr(signatureData, ",") + 1)
' From https://stackoverflow.com/questions/4920416/base64-image-decoder-for-asp-classic
Set objXML = Server.CreateObject("MSXML2.DOMDocument")
Set objDocElem = objXML.createElement("Base64Data")
objDocElem.DataType = "bin.base64"
objDocElem.text = base64String
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server}; Server=servername; Database=databasename; UID=username; PWD=password; Option=4"
' '@SignatureBlob' will only work when refering to a stored procedure I think, use ? and give the values in order (only 1 in this case).
Dim sql
'sql = "UPDATE call_events SET ce_technicians_signature_image = '@SignatureBlob' WHERE ce_Id = '56729908'"
sql = "UPDATE call_events SET ce_technicians_signature_image = ? WHERE ce_Id = '56729908'"
' Create a parameterized command and set the blob value
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
' Create a parameter and assign the blob value
Dim param
' No name required here as we used ? in the UPDATE statement
Set param = cmd.CreateParameter( , 205, 1, LenB(objDocElem.NodeTypedValue), objDocElem.NodeTypedValue)
'Set param = cmd.CreateParameter("@SignatureBlob", 205, 1, LenB(signatureBlob), signatureBlob)
cmd.Parameters.Append param
cmd.Execute
' Cleanup
Set param = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
' For testing can output the length of the image, or return it as a file to download
'Response.Write LenB(objDocElem.NodeTypedValue)
'Response.ContentType = "image/jpeg"
'Response.AddHeader "Content-Disposition", "attachment; filename=abc.jpg"
'Response.BinaryWrite objDocElem.NodeTypedValue
Set objXML = Nothing
Set objDocElem = Nothing
Response.Write "Signature successfully saved."
End If
%>
<!DOCTYPE html>
<html>
<head>
<title>Signature Capture Form</title>
<script src="https://unpkg.com/signature_pad"></script>
<script>
// Initialize the signature pad
var signaturePad;
window.onload = function() {
var canvas = document.getElementById('signatureCanvas');
signaturePad = new SignaturePad(canvas, {backgroundColor: 'rgb(255, 255, 255)'});
};
// Function to handle form submission
function handleSubmit(event) {
event.preventDefault(); // Prevent form submission
// Get the signature as a data URL
var signatureDataUrl = signaturePad.toDataURL("image/jpeg");
// Set the signature data as a hidden form field
document.getElementById('signatureData').value = signatureDataUrl;
// Submit the form
document.getElementById('signatureForm').submit();
}
</script>
<style>
#signatureCanvas {
border: 1px solid #000;
}
</style>
</head>
<body>
<h1>Signature Capture Form</h1>
<form id="signatureForm" method="post" action="">
<label for="signatureCanvas">Please sign below:</label><br>
<canvas id="signatureCanvas" width="400" height="200"></canvas><br>
<input type="hidden" id="signatureData" name="signatureData" value="">
<button type="submit" onclick="handleSubmit(event)">Submit</button>
</form>
</body>
</html>
It works as far as I can test (Server 2016, SQL 2017).
Some info in the comments, but apart from finding
this stack answer that gives a nice short way of converting base64 to binary, your UPDATE statement was the reason it was putting the same value in the database everytime, if we convert "@SignatureBlob" to hex some familiar characters:
root@5022ada6fc2a:~# echo '@SignatureBlob' | xxd -p
405369676e6174757265426c6f620a
@Parameters names seem to require a stored procedure as mentioned
here, so we need to use "?" in this case. If you have a stored procedure with the signature blob as a named parameter it should work as before.
Author
Topic: how to capture a signature and insert into SQL database as IMAGE field type (Read 12688 times)