I will try to keep this as brief as possible.
I have a function called GetData(ByVal query As String) whose sole purpose is to populate a data table multiple times based on certain conditions. As you can see, the function accepts a string variable where the SQL statement resides. What I am trying to do is add a scalar variable, "@date" in my case, and no matter where I try to add this variable it throws an error stating "Must declare scalar variable @date.
Edit: I should mention that it is throwing the "must declare variable" error on the sda.Fill(dt) line.
GetData Function
Private Shared Function GetData(ByVal query As String) As DataTable
Dim constr As String = ConfigurationManager.ConnectionStrings("WarrantyConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query)
Dim dt As DataTable = New DataTable()
cmd.Parameters.Add("@date", SqlDbType.Date).Value = Date.Today
Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
cmd.Parameters.AddWithValue("@date", Date.Today)
sda.Fill(dt)
End Using
Return dt
End Using
End Using
End Function
I am calling the function in a procedure that has the query and handles all of the conditions I need.
Procedure
Dim queryStart As String = "SELECT ( SELECT SUM(DealerNet) FROM Agreement WHERE VoidDate IS NULL "
Dim queryAlias As String = "AS Actual, "
Dim queryStart2 As String = "(SELECT SUM(Amount) FROM AccountingUS.dbo.ProjectedSales "
Dim queryAlias2 As String = "AS Projected "
If chart = "pmtd" Then
Dim queryCondition As String = "AND IssueDate BETWEEN (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @date)-1, 0)) AND @date) "
Dim queryCondition2 As String = "WHERE [Month] = MONTH(@date) AND [Year] = YEAR(@date)) "
Dim query As String = queryStart + queryCondition + queryAlias + queryStart2 + queryCondition2 + queryAlias2
Dim xMember1 As String = "Actual"
Dim xMember2 As String = "Projected"
Dim dt As DataTable = GetData(query)
pmtdChart.DataSource = dt
The variable in question is the @date variable in the strings within the "If" statement, the only value it holds is todays date. Currently, I have tried to use "cmd.Parameters.Add("@date", SqlDbType.Date).Value = Date.Today in the GetData function, however, I still receive the same "Must declare scalar variable" error. I have also tried replacing the @date variable with simply "" + Date.Today + "" or a variable that holds todays date, but upon doing so I receive an operand error about "Operand Clash: Date is incompatible with Int"
Any help regarding this issue would be greatly appreciated, I am relatively new to programming and would appreciate any tips or criticisms regarding best practices. If you need any additional information or clarification regarding this issue I would be happy to provide what I can. Thank you in advance.
3
Answers
If you look closely, you setup a cmd command, but you never actually pass it to the DataTable. So it doesn’t know anything about your params.
How about this instead (copied untested from Trying to pass SqlCommand in SqlDataAdapter as parameters):
Ok, a few things:
I would actually pass a command object to that get data routine.
And your issue is you feeding the query to the "adaptor", but NOT supplying the @date parameter to that "sda"
this:
In other words, you NOT EVEN using the cmd object!!!
So, you would need to add the parameter’s to the sda object!!
eg this:
So, yes, you WILL get that error about "@date" not being declared, since you NOT using the cmd object to fill the table, but are using the data adaptor.
So, as a future suggest?
Pick one way, or the other way.
I MUCH over the years have decided that I will use/have/adopt and cookie cut over and over the SqlCommand object.
I find the Sql cmd object better, since:
So, what this means?
I suggest this code for get data:
So, we don’t need a data adaptor. In fact, you only need a adaptor if you going to update the resulting table (think a "adaptive" table to remember this). You not going to update the data, so really, no need to use a "adaptor" at all here. (and sadly, far too many examples use a "adaptor" anyway. They are for ALLOWING update of the data table, and you not doing that!
So, use a command object. Do note that you ALWAYS must then open the confection, but since we have "using" blocks, it will ALWAYS be closed for you.
And note how then we don’t create to "use" the "reader" from the adaptor, nor a fill command. (so, we eliminated one whole confusing object!!).
So, in your example, you created a SQL command object, correctly added the parameter to the command object, but THEN DON’T use it, and then decided to create a data adaptor, and use that!!!
So, you could/can leave your code as you had with the sda "prameter " fix I posted above.
However, but I think your better off to use a sql command object.
Note even better?
Pass the command object to the GetData routine.
I have a global "general" purpose routine called MyRstP(), and I pass it a command object, even for just plain jane sql.
but, if you decide to add parameter’s, you can!
Do note that parameter’s can be added 100% independent of the SQL string, and they can be added before, or after you set the sql string.
And you can add parameter’s WITHOUT a valid working connection (or have created one just yet). So, "parameters" are just a colleciton – it does not care about the SQL (well, at least not yet!!).
So, here is my RstP, and I dumped this into a plain jane "module1" which VB has (this means you don’t have to create a static class, and this works then just like VB6, or VBA.
So, this:
So, now to say fill a grid view, I use this:
or say a given date of some such:
How about all hotel visit dates from start of year.
So, this:
note then how I have that MyRstP (like your get data), but I can pass it quite much anything I want, including parameter’s from the "calling" code, NOT in that general routine.
Anyway, the above use and adding the parameter’s to the "adaptor" will fix this, but I would change over to using just a command object and a connection – the adaptor really not required, and as noted, they really are to be used WHEN you actually want to update the data table, and then send it back to the database in one shot.