VB.NET User Input textbox to be used within a SQL Script Command

Question

I am trying to create a vb.net program that will ask a user to input a beginning date and ending date within two textboxes. I want to use these dates that they inputed in a stored procedure that I created to run a query that will return results based on those dates.

Here is my current SQL Script.

SET NOCOUNT ON
DECLARE @DBNAME NVARCHAR(MAX)
DECLARE @DBNAME1 NVARCHAR(MAX)
DECLARE @varSQL NVARCHAR(MAX)
DECLARE **@BEGDATE DATE**
DECLARE **@ENDDATE DATE**

DROP TABLE "SQL-EDD5 TOTALS" 
CREATE TABLE "SQL-EDD5 TOTALS" 
(SERVERNAME NVARCHAR(128),
TOTAL_IMAGES INT, 
TOTAL_FILE_SIZE_MBS INT)

DECLARE DBNAME CURSOR FAST_FORWARD FOR
select name from sys.databases where [NAME] LIKE 'Z%' AND create_date between @BEGDATE  and @ENDDATE
ORDER BY [create_date]

OPEN DBNAME

FETCH NEXT FROM DBNAME INTO @DBname
WHILE (@@FETCH_STATUS=0)
BEGIN

    Set @varSQL='INSERT INTO "SQL-EDD5 TOTALS" (TOTAL_IMAGES, TOTAL_FILE_SIZE_MBS) 
    SELECT SUM(PGCOUNT), sum(filesize/1024/1024) FROM ['+@DBNAME+'].dbo.tbldoc 
    WHERE created between between @BEGDATE  and @ENDDATE'
    EXEC SP_EXECUTESQL @varSQL

    FETCH NEXT FROM DBNAME 
    INTO @DBNAME
    END

    CLOSE DBNAME
    DEALLOCATE DBNAME

    INSERT INTO "SQL-EDD5 TOTALS"(SERVERNAME)
    SELECT CONVERT (NVARCHAR (128), SERVERPROPERTY('MachineName')) AS 'SERVER NAME'

    SELECT SERVERNAME AS 'SERVER NAME',SUM(TOTAL_IMAGES) AS 'TOTAL IMAGES', SUM(TOTAL_FILE_SIZE_MBS) AS 'TOTAL FILE SIZE IN MBS'
    FROM "SQL-EDD5 TOTALS"
    GROUP BY [SERVERNAME] 
    ORDER BY [SERVERNAME] desc

Here is my current Vb.net Script for the textboxes. I want to link the data in the textboxes into @BEGDATE DATE, @ENDDATE DATE. These are the variables that I want to be used to store and use the dates inputted by the user. What can I do to link them.

Here is the script:

Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
    Dim BEGDATE As DateTime
    BEGDATE = "@BEGDATE"
End Sub

Protected Sub TextBox2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
    Dim ENDDATE As DateTime
    ENDDATE = "@ENDDATE"
End Sub

End Class

msarchet · Accepted Answer · 2011-02-16 18:22:49Z

1

Okay first off you can't bind (link) to the SQL parameters the way you think you can.

What you need to do is set the text to a variable and then set that variable to the SQL parameter before your execute the SQL Statement.

You should be executing your Script using osmething similar to the following:

Using cn As SqlClient.SqlConnection(dbConnectionString)
   cn.Open()
   Using cmd AS SqlCleint.SqlCommand(cn)
       cmd.CommandText = "Select * From Table Where Between(@BeginDate, @EndDate)
       cmd.Parameters.AddWithValue("@BeginDate", BeingDate)
       cmd.Parameters.AddWithValue("@EndDate", EndDate)

       cmd.ExecuteNonQuery()
       'Or cmd.ExecuteQuery()
   End Using
End Using

This will replace all instances of @BeginDate and @EndDate with the associated Parameter value

edited Feb 16, 2011 at 18:22

answered Feb 16, 2011 at 18:17

msarchet

15.3k2 gold badges45 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Over a year ago

where would I insert this? within my gridview?

msarchet Over a year ago

Where ever you are querying the data now

Collectives™ on Stack Overflow

VB.NET User Input textbox to be used within a SQL Script Command

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related