Categories
Data access
Public Shared connString As String
Categories
webforms

Bootstrap DatePicker

A DatePicker control in a form can remove a lot of uncertainty and mistakes with data entry. e.g. US vs UK, european, etc. formats and also make it easier for the user. Third party controls are available but are now almost unnecessary if you are using Bootstrap (v4 onwards).

Just change the TextMode attribute of an asp:TextBox:

<asp:TextBox ID="TextBoxDateCreated" runat="server" TextMode="Date" CssClass="form-control"></asp:TextBox>
Basic DatePicker
DatePicker showing calendar

If necessary you can further style it by adding some styling like this:

style="border: 2px solid rgb(0, 128, 28); width: 200px; height: 30px;"
DatePicker styled

Note that this is for an asp:Text box, not an html5 Input. If you are not using an asp:TextBox you use the Type property.

A web search will reveal lots of alternatives such as the ability to limit the input to a specified range, styling certain dates differently and changing the style of the calendar that appears.

Always remember to ensure you add any neccessary back end validation too, especially if it is for an internet web site.

Note that if you want to populate the text box from code behind the date needs to be sent in yyyy-MM-dd format (irrespective of the format the text box displays).

Categories
asp.net

Numeric Text Box

We frequently need to ensure only numbers are entered via a Text Box control. Many 3rd party vendors sell contols that do this but that sometimes means buying the suite or that they can’t be styled to match your website. You can do this quite easily using a standard TextBox control and a RegularExpressionValidator.

<asp:RegularExpressionValidator ID="RegularExpressionValidator1"
    ControlToValidate="TextBoxNum" runat="server"
    ErrorMessage="Only Numbers allowed"
    ValidationExpression="^[+-]?(\d*\.)?\d+$" CssClass="smallRed"></asp:RegularExpressionValidator>

If you need posititve integers only use this Regex:
^[1-9]\d*$

Categories
SQL

Get the ID of the Inserted row

One way of getting the ID of the row you just inserted into a table would be to run another sql statement following the insert one. Use an aggreagate statement something like SELECT MAX (CusID from tblCustomers.

You can also get it using the insert statement with the OUTPUT clause…

INSERT INTO tblCustomers (FirstName, LastName) OUTPUT INSERTED.CusID
VALUES (‘Fred’, ‘Bloggs’)

(Assuming CusID is the primary key field in the table.)

If you’re running the insert in an application, the command needs to ExecuteScalar rather than just Execute…

In C#:

Return (Int32)cmd.ExecuteScalar();

Categories
SQL

Parameters with Wildcards

Be careful not to fall into the trap of adding the wildcard to the sql. It needs to be added to the parameter…

SELECT * FROM tblCustomers WHERE LastName LIKE '%mit%'

will return rows where the LastName field contains “mit”, at the beginning, end or anywhere with in the last name as there are 2 wildcards.

If you want to be able to specify the search criteria at run time, i.e. not just “mit”, replace the hard coded search with a parameter like @Search…

SELECT * FROM tblCustomers WHERE LastName LIKE '%@Search%'

If you are using something like a Sqlcommand in asp.net you will supply the parameter to it through perhaps a TextBox control so you would think you should do this…

Cmd.Parameters.Add("@Search", SqlDbType.NvarChar).Value = TextBoxSearch.Text

BUT THIS IS WRONG AND WON’T WORK!!!

The sql should not contain the wildcards. It should be :

SELECT * FROM tblCustomers WHERE LastName LIKE @Search

and the parameter should have the the wildcards:

Cmd.Parameters.Add(“@Search”, SqlDbType.NvarChar).Value = “%” + TextBoxSearch.Text + “%”

Categories
webforms

Limit the Number of Characters in a TextBox

I normally work in asp.net so use asp:TextBoxes frequently to capture data input to a database. With the best intentions I guess how many characters the user will need to enter and make the field in the database able to hold that many characters. I may also be limited by the spacing on the form. Especially in “Notes” text boxes a user then wants to enter a life story! The TextBox lets them type what they want but the database doesn’t store it all and when they read the record they find their typing has been cut off.

This jQuery script helps by not letting them type (or paste) too much.

  1. At the top of your page (in the head section perhaps), if you haven’t already got a link to jQuery, add one.
  2. Add a link to the MaxLength script.
  3. Add javascript functions for each TextBox you want to affect.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Scripts/jquery-3.4.1.min.js"></script>
    <script src="Scripts/MaxLength.min.js"></script>
    <script type="text/javascript">
        $(function () {
            //Specifying the Character Count control explicitly
            $("[id*=TextBox1]").MaxLength(
                {
                    MaxLength: 50,
                    CharacterCountControl: $('#counter')
                });

            //Disable Character Count
            $("[id*=TextBox2]").MaxLength(
                {
                    MaxLength: 20,
                    DisplayCharacterCount: false
                });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <label for="TextBox1">Text Box 1:</label>
            <asp:TextBox ID="TextBox1" runat="server"           
                 Height="85px" TextMode="MultiLine"
                 Width="260px">
            </asp:TextBox>
            <br />
            <br />
            <label for="TextBox2">Text Box 2:</label>
            <asp:TextBox ID="TextBox2" runat="server"            
                 Height="85px" TextMode="MultiLine"
                 Width="260px">
            </asp:TextBox>
        </div>
    </form>
</body>
</html>
Text boxes with MaxLength

TextBox1 has 20 characters in it. It has a maximum of 50 characters allowed and the remaining characters is displayed.

TextBox2 has 20 characters in it and no more typing is possible as the maximum is 20 characters. The remaining characters display is off for this TextBox.

MaxLength.js is available from https://gist.github.com/lscott3/3835702

I found this at https://www.jqueryscript.net/form/Max-Length-Text-Fields-jQuery.html

Categories
Microsoft Access

Custom Shortcut Menu

For most Access applications that are created for end users (as opposed to yourself), you don’t want the user to be able to go to Design View. This post specifically deals with the right click or shortcut menu.

If you right click on a form one of the menu items is Design View. You can show no menu at all on all forms in the database or on a form by form basis.

Remove shortcut menu completely

Access Options Dialogue Box

From File, Options, choose Current Database on the left, scroll down to the Ribbon and Toolbar Options and untick Allow Default Shortcut Menus.

Remove From Specific Forms

Change the Shortcut Menu property of the form to No.

Create a Custom Shortcut Menu

Add a Reference to the Microsoft Office 15.0 / 16.0 Object Library. (In the VBA Code window, choose Tools, References.)

Create a new Module (Insert, Module) and copy and paste this code. DO NOT paste into a form or report module.

Public Sub CreateRightClickBar()
'Run this once only.
 Dim cmbRC As CommandBar
 Dim cmbButtonCopy As CommandBarButton
 Dim cmbButtonCut As CommandBarButton
 Dim cmbButtonPaste As CommandBarButton
 Dim cmbButtonClose as CommandBarButton
 Dim strBarName As String
 strBarName = "CustomRightClick"
 On Error Resume Next
 CommandBars(strBarName).Delete
 On Error GoTo 0
 Set cmbRC = CommandBars.Add(strBarName, msoBarPopup, False)
 Set cmbButtonCopy = cmbRC.Controls.Add(msoControlButton, 21)
 Set cmbButtonCut = cmbRC.Controls.Add(msoControlButton, 19)
 Set cmbButtonPaste = cmbRC.Controls.Add(msoControlButton, 22)
Set cmbButtonClose = cmbRC.Controls.Add(msoControlButton, 1567)
 'Cleanup
 Set cmbRC = Nothing
 Set cmbButtonCopy = Nothing
 Set cmbButtonCut = Nothing
 Set cmbButtonPaste = Nothing
 Set cmbButtonClose = Nothing
End Sub

In the Immediate Window (Ctrl + G) type the name of the procedure (CreateRightClickBar) and press Enter.

Back in Access Options you will now see your new Shortcut Menu Bar is available.

Choosing your new shortcut menu bar

As previously, you could bypass this and apply the menu to each form individually via the form’s Shortcut Menu Bar property. (Ensure the Shortcut Menu property is set to Yes.)

Cleanup

You only need to run the code once to create the menu bar so you don’t need to save the code or the module. You should also remove the reference to the Microsoft Office 15.0 / 16.0 Object library.

Code Analysis

For most users, the ability to right click on a form and close it, rather than navigating to the cross to close the form is a great time saver. Copying and pasting is obviously so too. So in most cases this code is sufficient, but it’s possible to find the ID’s of other button in the Microsoft Office Library (21, 19, 22, 1567) to include in the menu – but that’s for another time!

Categories
Microsoft Access

SQL Server Write Conflict

When using a SQL Server backend, if you get a “Write Conflict” message when editing data, try adding a field of data type timestamp to the table. I often just name it “x”. (Remember to relink in Access).

Categories
Microsoft Access

Execute a Stored Procedure from Access

Create a pass through query in Access. Its content should call the sp like this:

Exec spCusInfo 65

The stored procedure will usually need a parameter, in the example, 65 could be the CusID. If sending a string, surround the parameter in single quotes. If there is more than 1 parameter, separate them with commas.

If you execute the query, you will always get the data for CusID=65. So in a button click event or an AfterUpdate event somewhere add VBA code like this:

Dim qry As QueryDef

Set qry = CurrentDb.QueryDefs("qrySpCusInfo")

qry.SQL = "exec spCusInfo " & Nz(Me.txtCusID, 0)

Me.Requery

See also https://oakdome.com/programming/MSAccess_StoredProcedureDataSource.php