Link to home
Start Free TrialLog in
Avatar of Sivasan
SivasanFlag for United States of America

asked on

How to pass value from a button to Oledbdataadapter in Vb.net

Hi There,
I'm reposting this question in this section. Please read through and I would really appreciate if any body can help on this.
Thanks

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
 
 Send to a Friend     Printer Friendly  
   
 Comment from DjDezmond
Date: 10/26/2006 01:29AM PDT
 Comment  


>>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()?
 
Comment from Sivasan
Date: 10/26/2006 09:36AM PDT
 Your Comment  


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
 
Comment from DjDezmond
Date: 10/27/2006 12:48AM PDT
 Comment  


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
 
Comment from Sivasan
Date: 10/27/2006 11:20AM PDT
 Your Comment  


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



 
Comment from DjDezmond
Date: 10/29/2006 05:00AM PST
 Comment  


( 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.

 
 
ASKER CERTIFIED SOLUTION
Avatar of hclgroup
hclgroup

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial