dates between whats entered in the textbox

Question

User1717218719 posted

Hi All

I have a textbox in which i want to enter a date (for now just in string format - yymmdd eg.190725). I have two colums in my sql table I am reading in one the first effective date and the second last effective date. inside last effective date if it is formatted 999999 there is no expire date. what I want to know is how to code for the event where for example todays date 190706 is typed into the textbox, display only rows in gridview that this date is between (ie. greater than first effective date but less than the last effective date. How would i go about coding this. any help is appreciated.

This is what I have started with so far but am stuck now:

            strSQLCon = "Data Source=xxxxxxxConnection=True;"
            conConn = New SqlConnection(strSQLCon)
            comComm = New SqlCommand

            With comComm

                .Connection = conConn
                .CommandType = CommandType.Text
                .CommandText = "SELECT * FROM table WHERE (LstEffDte = @LstEffDte)"


                .Parameters.AddWithValue("@LstEffSalsDte", TextBox2.Text)


            End With

            adpAdap = New SqlDataAdapter(comComm)
            adpAdap.Fill(DataS, "tbl1")

Answer 1

User753101303 posted

Hi,

My understanding is that you want a where clause such as :

WHERE @EffDte BETWEEN FstEffDte AND LstEffDte

which is a shortcut for WHERE @EffDte>=FstEffDte AND @EffDte<=@LstEffDte

Answer 2

User1717218719 posted

Yes this is kind of what im looking for. I have the following code but the gridview that is displayed is not applying effective date code. I dont have the column name EffDte in my table only columns named  FrstEffDte and LstEffDte.

  conConn = New SqlConnection(strSQLCon)
        comComm = New SqlCommand

        With comComm

            .Connection = conConn
            .CommandType = CommandType.Text
            .CommandText = "SELECT * FROM table WHERE (Cde = @Cde)  AND @EffDte BETWEEN FrstEffDte AND LstEffDte"




            .Parameters.AddWithValue("@Cde", TextBox1.Text)
            .Parameters.AddWithValue("@EffDte", TextBox2.Text)


        End With

        adpAdap = New SqlDataAdapter(comComm)
        adpAdap.Fill(DataS, "tblX1")


        GridView1.DataSource = DataS
        GridView1.DataBind()



    End Sub
        GridView1.DataSource = DataS
        GridView1.DataBind()



    End Sub

Answer 3

User475983607 posted

Your question is very confusing.  A start and end date is required to to filter a result set between two dates.  The code example has only one date.  The SQL script should look like the following as explained above.

.CommandText = "SELECT * FROM table WHERE (Cde = @Cde)  AND EffDte BETWEEN @startDate AND @endDate"

Answer 4

User1717218719 posted

Thankyou for yor help! sorry if its  a little confusing which part is confusing and i can explain I recieve the error "Must declare the scalar variable "@FrstEffDte". "  on the  line  Line 40: adpAdap.Fill(DataS, "tblX1") when I run that code.

here is the code I am running exactly:

 strSQLCon = "Data Source=xxxxxxxx;Trusted_Connection=True;"
        conConn = New SqlConnection(strSQLCon)
        comComm = New SqlCommand


        With comComm

            .Connection = conConn
            .CommandType = CommandType.Text
            .CommandText = "SELECT * FROM table WHERE (Cde = @Cde)  AND EffDte BETWEEN @FrstEffDte AND @LstEffDte"


            .Parameters.AddWithValue("@TaxCde", TextBox1.Text)
            .Parameters.AddWithValue("EffDte", TextBox2.Text)


        End With

        adpAdap = New SqlDataAdapter(comComm)
        adpAdap.Fill(DataS, "tblX1")

        GridView1.DataSource = DataS
        GridView1.DataBind()


    End Sub

Answer 5

User475983607 posted

You added a new parameter to the query but did not set the new parameter.  You should have 3 .Parameters.AddWithValue lines.  One line for each SQL parameter.

.Parameters.AddWithValue("@Cde", TextBox1.Text)
.Parameters.AddWithValue("@FrstEffDte", TextBox2.Text)
.Parameters.AddWithValue("@LstEffDte", TextBox3.Text)

Given your code and question, it is not clear if you have a requirement bug, code bug, or design bug.

Answer 6

User1717218719 posted

ok great thanks. the only thing is so textbox2 is where I enter the date i am checking (eg.180607).  so I there is no textbox3. Effdte is what is entered in textbox2. what would my parameters be now ? 

when I try the following i get the error "Invalid column name 'EffDte'."

        With comComm

            .Connection = conConn
            .CommandType = CommandType.Text
            .CommandText = "SELECT * FROM table WHERE (Cde = @Cde)  AND EffDte BETWEEN @FrstEffDte AND @LstEffDte"


            .Parameters.AddWithValue("@TaxCde", TextBox1.Text)
            .Parameters.AddWithValue("@FrstEffDte", TextBox2.Text)
            .Parameters.AddWithValue("@LstEffDte", TextBox2.Text)

Answer 7

User475983607 posted

Your design is becoming more and more confusing as you reveal more information.  Should we assume 180607 is yymmdd format and the column is a VARCHAR?  Can you share the table schema and sample data?

Answer 8

User1717218719 posted

I explained all of those in my original question if you scroll to the top it explains

Answer 9

User475983607 posted

I explained all of those in my original question if you scroll to the top it explains

No, you did not show the schema or the data.  Schema means the table definition in SQL.  And the data is the data within the table.  Given your recent responses we need this information because you are having difficulty with syntax and basic filtering.

Answer 10

User1717218719 posted

The SQL table I am using is quite complex and large so here is a sample from it:

table example:

FrstEffDte	LstEffDte
180223	999999
150423	180806
160311	180425

when I type into textbox:

180607

the output should be:

FrstEffDte	LstEffDte
180223	999999
150423	180806

</head>
<body>

    <form id="form1" runat="server">
        <div class="divSpacer">
            <asp:Panel ID="Panel1" runat="server" DefaultButton="Button1">

                <table class="auto-style2">
                    <tr>
                        <td>Enter Code:</td>
                        <td>
                            <asp:TextBox ID="TextBox1" runat="server" Height="34px" MaxLength="2"></asp:TextBox>
                        </td>
                        <td>
                            <asp:Button ID="Button1" runat="server" Text="Submit" Width="113px" CssClass="auto-style1" /></td>
                        <td>&nbsp;</td>
                    </tr>
                </table>

            </asp:Panel>

        </div>


 <asp:GridView CssClass="auto-style1" ID="GridView1" runat="server" AllowPaging="false" AllowSorting="True" Width=" 100%">
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="#BFE4FF" />
        </asp:GridView>

    </form>

</body>
</html>

Answer 11

User475983607 posted

Thanks for the table but you did not provide the column definition as requested.  Is FrstEffDte and LastEffDate an INT?  A VARCHAR?  Is the format yymmdd?

Answer 12

User1717218719 posted

VARCHAR and yes as I said above format is yymmdd.

thanks

Answer 13

User753101303 posted

In my original response @EffDte  is just the user entered date passed as a parameter to your query. My understanding is that you have two columns in your database and that you want to find rows where the user entered date is falling between those two values. Is this correct ?

If yes you should understand that testing a single column against two user entered data is not what you want (or if it is you should have understood my response is not what you wanted). Could you clarify ?

If my first response your code ran but doesn't select the expected rows, the next step is to understand why it doesn't match. Try for example without filtering on Cde. My understanding is that you are using strings formatted using the yymmdd pattern.

Edit :

SELECT 'Found' WHERE '180607' BETWEEN '180223' AND '999999' -- Returns a row

works. Are you sure the issue is not the test on Cde ?

Edit 2: avoid to change your code while others are trying to help. Especially when new, do things one step at a time to avoid introducing a new issue before solving the previous one.

Answer 14

User1717218719 posted

In my original response @EffDte  is just the user entered date passed as a parameter to your query. My understanding is that you have two columns in your database and that you want to find rows where the user entered date is falling between those two values. Is this correct ?

YES THIS IS CORRECT

If yes you should understand that testing a single column against two user entered data is not what you want (or if it is you should have understood my response is not what you wanted). Could you clarify ?

I AM TESTING A SINGLE USER INPUT AGAINST TWO COLUMNS. TO SEE IF USER INPPUT DATE FALLS BETWEEN TWO COLUMNS

If my first response your code ran but doesn't select the expected rows, the next step is to understand why it doesn't match. Try for example without filtering on Cde.

CORRECT AGAIN. THE CDE WORKS PERFECTLY I HAVE TRIPPLE CHECKED THIS. AS TO WHY IT DOESNT MATCH IM NOT SURE, AS THE CDE MATCHED PERFECTLY.

have u any suggestions as to why the code ran but doesn't select the expected rows?

Lastly I didnt mean to confuse things by changing code just wanted to show what I was trying sorry for the confusion.

thankyou for your help

Answer 15

User475983607 posted

Sorry, missed the first post I guess.  Compare the user input to each column.

SELECT * FROM table WHERE  @userInput >= FrstEffDte AND @userInput <= LstEffDte

Or use BETWEEN.

Answer 16

User1717218719 posted

do I need to dim userinput as =textbox2.text?

and what would my parameters be.

thanks a mill

Answer 17

User475983607 posted

do I need to dim userinput as textbox2.text?

and what would my parameters be.

thanks a mill

Populate an input parameter that matches the SQL variable @. 

.Parameters.AddWithValue("@userInput", TextBox2.Text)

I used @userInput as an example.  You can use whatever variable name you like.

Secondly, you should always validate user input to make sure the text is formatted correctly.

Answer 18

User753101303 posted

This is just what you had already in your 2nd post (though later your ASPX markup doesn't show any more TextBox2 at all ?)

Or if doing things one step at a time you could harcode a test value such as :

.Parameters.AddWithValue("@EffDte", "180607")

to check it works before wiring the user input to your parameter (use the name you want, just be consistent with the name used in your SQL statement).

Answer 19

User1717218719 posted

Thanks guys I managed to get there in the end with your help. it was the parameter that was causing the problem in the end thanks a  lot.