Click here to Skip to main content
15,880,469 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I has Date field in sql server
fieldname   Type
Billdate    datetime


I want save date and Time. So i give the insert query for date in vb.net
Datetime.Now.Tostring


And the when i get the report from that database there is some problem. I get the records between selected dates. But it will not display. i give following coding'

select Billno,Billdate,PName,Qty,TName from CancelOrder  where Convert(varchar,Billdate,101) >= '" & Format(dp1.Value, "MM-dd-yyyy") & "' and Convert(varchar,Billdate,101)<='" & Format(dp2.Value, "MM-dd-yyyy") & "'", Cn)

Please any body help get records between two dates
Posted

Use Parameters in your sql command!

Try something like this:

VB
Dim table As New DataTable()
Dim query As String = "select Billno,Billdate,PName,Qty,TName from CancelOrder where @Billdate1 >= @Billdate2"

Using cn As New SqlConnection(Configuration.DefaultConnectionString)
    Using cmd As New SqlCommand(query.ToString(), cn)
        cmd.Parameters.Add("@Billdate1", SqlDbType.DateTime).Value = Billdate1
        cmd.Parameters.Add("@Billdate2", SqlDbType.DateTime).Value = Billdate2

        cn.Open()

        Using reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            table.Load(reader)
        End Using
    End Using
End Using


You can also Google the following "vb.net using sqlcommand parameters".


Cheers
 
Share this answer
 
Comments
devausha 11-Apr-12 5:20am    
I am using dataadapter
Mario Majčica 11-Apr-12 5:22am    
And where do you say that in the question? Still you can use command parameters, just Google the keywords.

Check this out http://msdn.microsoft.com/en-us/library/bbw6zyha(v=vs.100).aspx

Cheers
devausha 11-Apr-12 5:39am    
I am using like this,But it is not working
Dim da As New SqlDataAdapter("select * from TempOrderDetails where Billdate >= @Billdate1 and Billdate<=@Billdate2 order by ID", Cn)
da.SelectCommand.Parameters.Add("@Billdate1", SqlDbType.DateTime).Value = Format(dp1.Value, "MM/dd/yyyy")
da.SelectCommand.Parameters.Add("@Billdate2", SqlDbType.DateTime).Value = Format(dp2.Value, "MM/dd/yyyy")
Mario Majčica 11-Apr-12 5:40am    
Try instead of Format(dp1.Value, "MM/dd/yyyy") just dp1.Value. Cheers
devausha 11-Apr-12 5:41am    
But I tried it, There are no results
Hi,
replace ur query by following
select Billno,Billdate,PName,Qty,TName from CancelOrder where Billdate >= '" & Format(dp1.Value, "MM-dd-yyyy") & "' and Billdate <='" & Format(dp2.Value, "MM-dd-yyyy") & "'", Cn)

best luck
Happy Coding
 
Share this answer
 
Comments
devausha 11-Apr-12 5:21am    
Already I tried it. But it is not working
Nilesh Patil Kolhapur 11-Apr-12 5:35am    
then check ur code this query work fine for me

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900