Solved

How to pass two parameters in Vb.net to a query done using oledbdataadapter Wizard which takes a two selected dates

Posted on 2006-10-25
6
342 Views
Last Modified: 2010-04-23
Hi There,
I have created a OledbDataadapter on Vb .net (Visual studio .net 2003).
The query on the OledbDataadapter is as follows

SELECT     [Time], [FINAL DATE] AS [Ship DT], OrdDT, [REQ DATE] AS [Req DT], SHPDEAD AS [SHIP DEADLINE], cus_alt_adr_cd AS [Store #], [WO #], Sta,
                      Carrier AS CAR, [Sum Of TRUCK] AS Weight, [Qty Ord] AS QOR, [Qty  Sh] AS QTY, PO, [Res Note], [Note], Hold, Loc, ID
FROM         [USER ENTRY]
WHERE     (Loc = 'WAC') AND ([Time] IS NULL) AND ([REQ DATE] BETWEEN ? AND ?)
ORDER BY cus_alt_adr_cd, [WO #]
---------------------------------------------------------------------------

I have a button on my form  on click of the button function I want to pass two date values for the above Req Date query.
and display the result on my grid.
The problem is, when I run the query from the oledbadapter, it works but on click of the button, it says parameter ?2
missing.
If I hard code into the query the date for one ? and for the other ? (of the between value) I supply value by passing through the parameter( the text box or the calendar output), from the onclick of the button, it works.
I'm unable to pass two values. I will have to pass both the values through the button click.

This is the onclick event of the button.


  Private Sub LOADBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LOADBTN.Click
        Try
            OleDbDataAdapter14.SelectCommand.Parameters("ReqDT").Value = A.SelectionStart()          
            OleDbDataAdapter14.SelectCommand.Parameters("ReqDT").Value =BDate.Text()

            FnLOAD()

        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try


I would appreciate if anybody can tell me how I can do this.
Thanks
-K
0
Comment
Question by:Sivasan
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:DjDezmond
Comment Utility
>>A.SelectionStart()
   this will either return or set the beggining of the text selection in the text box... it wont return any string/date values.

>>[REQ DATE] AS [Req DT]             >>SelectCommand.Parameters("ReqDT").value
   Which one is it? you have specified "ReqDT". Three different names here...

>>OleDbDataAdapter14.SelectCommand.Parameters("ReqDT").Value = A.SelectionStart()
     OleDbDataAdapter14.SelectCommand.Parameters("ReqDT").Value = BDate.Text()
 Why are you assigning two seperate values to the same paramater straight after each other? It will only remember the last one...?

I may be missing a few things here, there could be good reasons for these... but these are just my thoughts.

I tend to just fill a datatable using the dataadapter with a different SQL string for each time i need to populate, it doesn't end being as complex.

Can you post the code for  FnLOAD()?
0
 

Author Comment

by:Sivasan
Comment Utility
Hi DjDezmond,
Thanks for your reponse.Sorry for not being too clear

' When I posted the question,I was just trying to show the readers that I wanted to pass the begin and end date
that's the reason, I just had it as

OleDbDataAdapter20.SelectCommand.Parameters("REQ_DATE").Value = A.SelectionStart
OleDbDataAdapter20.SelectCommand.Parameters("REQ_DATE").Value = BDate.Text
I tried different ways like
OleDbDataAdapter20.SelectCommand.Parameters("REQ_DATE").Value = A.SelectionStart & BDate.Text
nothing worked.

A- is the name of my calendar object. So I pick a date from the calendar and another date from the text box name of it  BDate.
Let me put things the right way

This is my code for the View by Req Date button

'Button for Ord by Req Date

    Private Sub AppWoBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AppWoBtn.Click
        Try
            OleDbDataAdapter20.SelectCommand.Parameters("REQ_DATE").Value = A.SelectionStart

           FnAPPWO()
        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub
----------------------------------------------------------------------
'This is my FnAPPWO()

    ' FUNCTION FOR Open Order By Req Dt
    Private Sub FnAPPWO()
        WoAp1.Clear()
        OleDbDataAdapter20.Fill(WoAp1, "USER ENTRY")
        Me.DataGrid1.DataSource = Me.WoAp1.Tables("USER ENTRY").DefaultView
    End Sub
------------------------------------------------------------------------------------
' This is my OleDbDataAdapter20

SELECT     [Time], [FINAL DATE] AS [Ship DT], OrdDT, [REQ DATE] AS [Req DT], SHPDEAD AS [SHIP DEADLINE], cus_alt_adr_cd AS [Store #], [WO #], Sta,
                      Carrier AS CAR, [Sum Of TRUCK] AS Weight, [Qty Ord] AS QOR, [Qty  Sh] AS QTY, PO, [Res Note], [Note], Hold, Loc, ID
FROM         [USER ENTRY]
WHERE     (Loc = 'WAC') AND ([Time] IS NULL) AND ([REQ DATE] BETWEEN ? AND ?)
ORDER BY cus_alt_adr_cd, [WO #]
--------------------------------------------------------------------------------------

' After the users make the change, they click on Save button.

  ' Save Button for Ord by Req Dt

    Private Sub RegDtBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RegDtBtn.Click
        Try

            Dim DsLOADCH = WoAp1.GetChanges
            Dim nno As DBNull
            If (DsLOADCH Is nno) Then
                MsgBox("You Have Not Made Any Change To The Schedule.Hit Ok to Exit this message")

            Else

                OleDbDataAdapter20.Update(DsLOADCH)
                WoAp1.AcceptChanges()
                FnAPPWO()

            End If
        Catch ex As Exception
            'MsgBox(" The Changes That You Made To Missed Order Has Been Saved")
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub
---------------------------------------------------------------------------

So basically, I was to pass two Date value to my Oledbdatadapter20 so it can display order between those dates on my datagrid.
One date is the date selected on my calender that's A
the second date is the date entered in the textbox- that's BDate

I appreciate all your help.
Thanks
0
 
LVL 9

Expert Comment

by:DjDezmond
Comment Utility
Im assuming the dataadapter select command has been created using the connection wizard...?

What you can do is set that select command with a WHERE clause (as i see you have done), but instead of giving the ? values to specifiy dates elsewhere in your code, you can simply set the WHERE paramaters to be taken from your textbox and calender control.

I cant remember specifically step-by-step instructions, but when your adding the WHERE clause, the combo boxs on the left hand side (i think) can be used to specify values from existing controls. You'll have to play around abit until you find it unfortunately (my appoligies, but my old code where i did this cant be found at the moment, and i dont think 2005 has the same dbadapter controls).

Dez
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Sivasan
Comment Utility
Hi DjD,
Thanks again for taking your time to respond.
I'm sorry I don't quite I understand where you want me to try and use the combo box.
Yes, I created the Dataadpater using Wizard and gave I    ? and ? for the two vales for the date on the criteria.

So this automatically creates Selectcommand code. Did you want me to use the value there?

I also tried manually writing the connection on the Onlcick button

but when I try it with date it doesn't display the result, I guess, since I give the value " & BDate.Text &" and "
"& A.SelectionStart &" it is converting it into string values.
The same thing if I try for WO # which is an integer field " & BDate.Text  "& Otbox.text &" it does display the results.

Please find my code below
--------------------------------------------------------------------------------------------
 Private Sub LOADBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LOADBTN.Click
        Try
            'OleDbDataAdapter14.SelectCommand.Parameters("OrdDT").Value = A.SelectionStart()

            ' BDate.Text()

            ' Not using Wizard


            Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TexasBBB\WACOBBB.mdb"
            Dim myConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection
            myConnection.ConnectionString = connString



            Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(" SELECT [Time], [FINAL DATE] AS [Ship DT], OrdDT, [REQ DATE] AS [Req DT], SHPDEAD AS [SHIP DEADLINE], cus_alt_adr_cd AS [Store #], [WO #], Sta, Carrier AS CAR, [Sum Of TRUCK] AS Weight, [Qty Ord] AS QOR, [Qty  Sh] AS QTY, PO, [Res Note], [Note], Hold, Loc, ID FROM [USER ENTRY] WHERE (Loc = 'WAC') AND ( OrdDT BETWEEN  " & BDate.text & "   AND   " & A.SelectionStart & "  ) AND ([Time] IS NULL) ORDER BY cus_alt_adr_cd, [WO #]", myConnection)


            Dim ds As DataSet = New DataSet
            ' fill dataset
            ' ds.Clear()
            da.Fill(ds, "USER ENTRY")

            ' Attach DataSet to DataGrid
            '  DataGrid1.DataSource = ds.DefaultViewManager
            DataGrid1.DataSource = ds.Tables("USER ENTRY").DefaultView

            'End of Not using Wizard
            ' FnLOAD()  

        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub

---------------------------------------------------------------------------------------------------------

The same thing if I use for work order- I can see it pulls Work orders between the selected range.
please see the code
--------------------------------------------------

Private Sub LOADBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LOADBTN.Click
        Try
            'OleDbDataAdapter14.SelectCommand.Parameters("OrdDT").Value = A.SelectionStart()

            ' BDate.Text()

            ' Not using Wizard


            Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TexasBBB\WACOBBB.mdb"
            Dim myConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection
            myConnection.ConnectionString = connString



            Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(" SELECT [Time], [FINAL DATE] AS [Ship DT], OrdDT, [REQ DATE] AS [Req DT], SHPDEAD AS [SHIP DEADLINE], cus_alt_adr_cd AS [Store #], [WO #], Sta, Carrier AS CAR, [Sum Of TRUCK] AS Weight, [Qty Ord] AS QOR, [Qty  Sh] AS QTY, PO, [Res Note], [Note], Hold, Loc, ID FROM [USER ENTRY] WHERE (Loc = 'WAC') AND (  [WO #] BETWEEN  " & BDate.text & "   AND   " & OTbox.text & "  ) AND ([Time] IS NULL) ORDER BY cus_alt_adr_cd, [WO #]", myConnection)

            Dim ds As DataSet = New DataSet
            ' fill dataset
            ' ds.Clear()
            da.Fill(ds, "USER ENTRY")

            ' Attach DataSet to DataGrid
            '  DataGrid1.DataSource = ds.DefaultViewManager
            DataGrid1.DataSource = ds.Tables("USER ENTRY").DefaultView

            'End of Not using Wizard
            ' FnLOAD()  

        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub
---------------------------------------------------------------------------------------------------------------

I would prefer using it by the Wizard, but I don't mind writing the manual connection on the Onclick function,
but it doesn't like the Date, may be I'm not doing it right by putting the date " & Bdate.Text &" and "A.SelectionStart  &"

How can I do this, please advise.

Thanks a million for your help.
-K


0
 
LVL 9

Accepted Solution

by:
DjDezmond earned 500 total points
Comment Utility
( OrdDT BETWEEN  " & BDate.text & "   AND   " & A.SelectionStart & "  )

Sometimes you need to place ' around the input text... for example:

( OrdDT BETWEEN  '" & BDate.text & "'   AND   '" & A.SelectionStart & "'  )

So the SQL string will read ( OrdDT BETWEEN '29/10/2006' AND '5/11/2006' ) (but obviously not those dates).

It would be easier to use the connection wizard, but you can only have one type of data query per connection object. As you enter the where clause of the select command (where you originally put the ? values), there is an option to use exisiting values from controls on the form. Unfortunately I cannot open Visual Studio to give you specific instructions, but:

http://www.informit.com/articles/article.asp?p=26956&rl=1  >> goto figure 7. Click on "Command Builder"...

You will of seen this screen before (turns out its in the dbadapter not the connection object :p), in the WHERE field, there is a combo box (i think its on the left hand side), it gives you options for the input type, select controls and then a combo box else where on the form will populate with all the controls you can use.

Again, my apoligies for not being able to give clear instructions, but hopefully someone else can step in and help?

Failing that, you can use the manual string, but they can sometimes be complicated to see where you are going wrong.
0
 

Author Comment

by:Sivasan
Comment Utility
Hi DjD,
I had posted this question on the Programming section and somebody replied.
Please find it

You are setting the same parameter ("ReqDT") twice

OleDbDataAdapter14.SelectCommand.Parameters("ReqDT").Value = A.SelectionStart()          
OleDbDataAdapter14.SelectCommand.Parameters("ReqDT").Value =BDate.Text()

Shouldn't it be another parameter like "ReqDT1" and "ReqDT2"

OleDbDataAdapter14.SelectCommand.Parameters("ReqDT1").Value = A.SelectionStart()          
OleDbDataAdapter14.SelectCommand.Parameters("ReqDT2").Value =BDate.Text()
----------------------------------------------------------------------------------------------------------

So I tried this and it didn't work but when I changed it from ReqDt and RegDt1 instead of ReqDT2. It worked.
I just wanted to post the answer to you.
Since you worked so hard for me. I really appreciate your help very much.
Thank you so much.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now