date query on sql table

[russellmunday]

can someone tell me where i'm going wrong with this
Dim sdate As Date
sdate = Date.Today.AddMonths(1).ToString
Dim ssql As String
ssql = "select * from information where expirydate <'" & sdate & "'"

i want the records that are about to run out to be returned.
Thanks

 

[Rhys666]

Extract the value of sdate and ssql in debug. In my opinion it's likely to be a date format issue in either the value of sdate or the date formatting on Sql Server.

Rhys
"There are some oddities in the perspective with which we see the world. The fact that we live at the bottom of a deep gravity well, on the surface of a gas-covered planet going around a nuclear fireball 90 million miles away and think this to be normal is obviously some indication of how skewed our perspective tends to be"
DOUGLAS AD

 

[ca8msm]

Yeah, it's very likely to to be a date issue. If you use parameters you won't have these issues.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[russellmunday]

do you have any examples of using parameters?

 

[ca8msm]

A simple example would be:

Dim MyConnection as New SqlClient.SqlConnection("Connection String")
Dim MyCommand as nNew SqlClient.SqlCommand("StoredProcedureNameGoesHere", MyConnection)
MyCommand.Commandtype = CommandType.StoredProcedure
MyCommand.Parameters.Add(new sqlclient.sqlparameter("@FieldName", "Value")

Then, you would just open the connection and read the results however you needed (e.g. DataReader, DataTable etc...)


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[russellmunday]

If I use a stored proceedure how do I get the returned data into a dataset?
because the datasets i have used so far have had a string passed to them see my example

Dim sdate As Date
sdate = Date.Today.AddMonths(1).ToString
Dim ssql As String
ssql = "select * from information where expirydate <'" & sdate & "'"
Dim oconn As New SqlClient.SqlConnection(sconnection)
Dim da As New SqlClient.SqlDataAdapter(ssql, oconn)
Dim ds As New DataSet

Try
oconn.Open()
da.Fill(ds, "information")
oconn.Close()
dg1.DataSource = ds
dg1.DataMember = "information"
Catch ex As Exception
tx2.Text = ex.ToString
End Try

 

[ca8msm]

You should just be able to fill your dataset using the same method.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[russellmunday]

May be I'm getting this wrong but if the dataadapter does not have any idea of the parameter when it polls the database table all records will be returned and put into the dataset. and not the selected records I want.

 

[ca8msm]

Why won't it "have any idea of the parameter"? I included an example of how to add parameters (although I did miss out a bracket!):

MyCommand.Parameters.Add(new sqlclient.sqlparameter("@FieldName", "Value"))

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[russellmunday]

I still am not sure of whats happening here but I have tried it and am getting the following results i am probably doing something wrong but i just can't seem to grasp it;

System.InvalidOperationException: The SelectCommand property has not been initialized before calling 'Fill'.
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at ClubMembership.renewals.test1() in D:\Programming\vb.net\ClubMembership\renewals.vb:line 159

the code i am using:
Dim sdate As Date
sdate = DateTime.Today.AddMonths(1).ToShortDateString 'Date.Today.AddMonths(1)
tx1.Text = sdate
Dim ssql As String
ssql = "select * from information where expirydate <'" & sdate & "'"
Dim oconn As New SqlClient.SqlConnection(sconnection)
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet

Dim MyCommand As New SqlClient.SqlCommand("datefind", oconn)
MyCommand.Commandtype = CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlClient.SqlParameter("@exdate", "sdate"))

Try
oconn.Open()
MyCommand.ExecuteNonQuery()
da.Fill(ds, "information")
oconn.Close()
dg1.DataSource = ds
dg1.DataMember = "information"
Catch ex As Exception
tx2.Text = ex.ToString
End Try

 

[ca8msm]

The example I gave was to replace the code you had in which you passed in the SQL string.

The idea is that you set up the command object to be the stored procedure, add the parameters, set the dataadaptor up to use the command object and use it to fill the results into a DataTable/Set.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[russellmunday]

how?

 

[ca8msm]

You simply use the code I provided above and then follow my instructions in the last post e.g.

        Dim da As New SqlClient.SqlDataAdapter
        da.SelectCommand = MyCommand
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "information")

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[russellmunday]

thanks ill give it a go

 

[russellmunday]

I am still getting the following error when connecting

System.Data.SqlClient.SqlException: Error converting data type nvarchar to datetime.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

the code is now
Dim sdate As Date
sdate = DateTime.Today.AddMonths(1) 'Date.Today.AddMonths(1)
tx1.Text = sdate
'Dim ssql As String
'ssql = "select * from information where expirydate <'" & sdate & "'"
Dim oconn As New SqlClient.SqlConnection(sconnection)

Dim MyCommand As New SqlClient.SqlCommand("datefind", oconn)
MyCommand.Commandtype = CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlClient.SqlParameter("@exdate", "sdate"))
'code bit

Dim da As New SqlClient.SqlDataAdapter
da.SelectCommand = MyCommand
Dim ds As DataSet = New DataSet


Try
oconn.Open()
MyCommand.ExecuteNonQuery()
da.Fill(ds, "information")
oconn.Close()
dg1.DataSource = ds
dg1.DataMember = "information"
Catch ex As Exception
tx2.Text = ex.ToString
End Try

the stored proceedure

ALTER PROCEDURE dbo.datefind
(
@exdate datetime
)
as
select * from information where
(
expirydate < @exdate
)

any ideas?

 

[Rhys666]

Try replacing;

MyCommand.Parameters.Add(New SqlClient.SqlParameter("@exdate", "sdate"))

with

MyCommand.Parameters.Add(New SqlClient.SqlParameter("@exdate", SqlDbType.DateTime, sdate))

Rhys
"There are some oddities in the perspective with which we see the world. The fact that we live at the bottom of a deep gravity well, on the surface of a gas-covered planet going around a nuclear fireball 90 million miles away and think this to be normal is obviously some indication of how skewed our perspective tends to be"
DOUGLAS AD

 

[russellmunday]

I added the your line but the sdate was underlined when i hover over it the message says "value of type 'date' cannot be coverted to integer

 

[Rhys666]

Not sure where thats coming from.

The original code was wrong because sdate was wrapped in quotes meaning the value of the parameter you were adding was "sdate" not the value of the sdate variable, hence the SqlClient error being unable to convert "sdate" to a date value.

Ok, we'll try to get around what's erroring. Try replacing;

Dim MyCommand As New SqlClient.SqlCommand("datefind", oconn)
MyCommand.Commandtype = CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlClient.SqlParameter("@exdate", "sdate"))

with;

Dim MyCommand As New SqlClient.SqlCommand("datefind", oconn)
MyCommand.Commandtype = CommandType.StoredProcedure

Dim SqlParameter pDate = MyCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@exdate", System.Data.SqlDbType.DateTime))
pDate.Direction = System.Data.ParameterDirection.Input
pDate.Value = inReadDate

It's a little more convoluted, but all I'm really trying to do is define the parameter as a DateTime type input parameter belonging to your SqlCommand before we assign it the value of the sdate variable.

Rhys
"There are some oddities in the perspective with which we see the world. The fact that we live at the bottom of a deep gravity well, on the surface of a gas-covered planet going around a nuclear fireball 90 million miles away and think this to be normal is obviously some indication of how skewed our perspective tends to be"
DOUGLAS AD

 

[Rhys666]

Oops, soory. Incorrect variable names and a bit c# flavoured, ahem

Try this;

Dim MyCommand As New SqlClient.SqlCommand("datefind", oconn)
MyCommand.Commandtype = CommandType.StoredProcedure

Dim pDate As SqlParameter
pDate = MyCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@exdate", System.Data.SqlDbType.DateTime))
pDate.Direction = System.Data.ParameterDirection.Input
pDate.Value = sdate

Rhys
"There are some oddities in the perspective with which we see the world. The fact that we live at the bottom of a deep gravity well, on the surface of a gas-covered planet going around a nuclear fireball 90 million miles away and think this to be normal is obviously some indication of how skewed our perspective tends to be"
DOUGLAS AD