OLEDB Connection Issue

Question

Mike Askew 131 Veteran Poster

13 Years Ago

The program is currently trying to pull information from three different database tables and compiling the information onto one screen, to do this i am using three different connections and a function to handle the data at each stage.

The issue is as my code hits PageLoadStage of "2" and runs the QueryDatabase() Function i am encountering an error of: "OleDB exception unhandled: No value given for one or more of the required parameters." This occurs on line 15 of the first code snippet. Can anyone explain this and/or identify any code issues if that is the cause.

Most Appreciated,
Mike

Function QueryDatabase()
        Select Case PageLoadStage
            Case 1
                Con.ConnectionString = DBP & DBS
                Con.Open()
                SQL = "SELECT * FROM tbl_Information WHERE Product_Name = '" & SelectedProduct & "';"
                DA = New OleDb.OleDbDataAdapter(SQL, Con)
                DA.Fill(DS, "Info")
                Con.Close()
                Call InformationRender()
            Case 2
                Con.Open()
                SQL = "SELECT * FROM tbl_Stock WHERE Product_Number = " & SelectedProduct & ";"
                DA = New OleDb.OleDbDataAdapter(SQL, Con)
                DA.Fill(DS, "Stock")
                MaxLocations = DS.Tables("Stock").Rows.Count
                Con.Close()
                Call InformationRender()
            Case 3
                Con.Open()
                SQL = "SELECT * FROM tbl_Location WHERE Line_Number = '" & SelectedProduct & "';"
                DA = New OleDb.OleDbDataAdapter(SQL, Con)
                DA.Fill(DS, "Location")
                Con.Close()
                Call InformationRender()
        End Select

        If PageLoadStage <> 4 Then
            Call QueryDatabase()
        End If
End Function

Function InformationRender()
        Select PageLoadStage
            Case 1
                lbl_ProductNameV.Text = DS.Tables("Info").Rows(0).Item(1)
                lbl_ProductPriceV.Text = "£" & DS.Tables("Info").Rows(0).Item(2)
                lbl_AdditionalInfoV.Text = DS.Tables("Info").Rows(0).Item(3)
                SelectedProduct = DS.Tables("Info").Rows(0).Item(0)
                PageLoadStage = 2
            Case 2
                lbl_LineNumberV.Text = DS.Tables("Stock").Rows(0).Item(0)
                lbl_SoldbySectionV.Text = DS.Tables("Stock").Rows(0).Item(1)
                lbl_CurrentStockV.Text = DS.Tables("Stock").Rows(0).Item(2)
                lbl_NextDeliveryDateV.Text = DS.Tables("Stock").Rows(0).Item(3)
                lbl_NextDeliveryTimeV.Text = DS.Tables("Stock").Rows(0).Item(4)
                SelectedProduct = DS.Tables("Stock").Rows(0).Item(0)
                PageLoadStage = 3
            Case 3
                img_ProductImageDisplay.Image = DS.Tables("Location").Rows(0).Item(4)
                PageLoadStage = 4
        End Select
    End Function

vb.net

2 Contributors
8 Replies
148 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by Mike Askew

prvnkmr194 0 Posting Whiz in Training

13 Years Ago

Every thing is look like Correct accept you miss the single quote (') so change your line number 15 to this

SQL = "SELECT * FROM tbl_Stock WHERE Product_Number = '" & SelectedProduct & "';"

I hope it will work fine for you
Best Of luck

prvnkmr194 0 Posting Whiz in Training

13 Years Ago

Sorry for late response

Dim SelectedProduct As String

SelectedProduct is a string type variable ok.
Now come to form load event

SelectedProduct = ProductSearch.txt_HiddenVarStore.Text
        Call QueryDatabase()

Here I assume that you initialize selectedProduct with a string (product name), upto that every thing fine and work too right.

Now what happen when your case 2 is execute

Case 2
                Con.Open()
                SQL = "SELECT * FROM tbl_Stock WHERE Product_Number = '" & SelectedProduct & "';"
                DA = New OleDb.OleDbDataAdapter(SQL, Con)
                DA.Fill(DS, "Stock")
                MaxLocations = DS.Tables("Stock").Rows.Count
                Con.Close()
                Call InformationRender()

here Product_Number is of type number in table and your SelectedProduct is a string type variable, so you have to convert to integer

SQL = "SELECT * FROM tbl_Stock WHERE Product_Number = " & Convert.ToInt16(SelectedProduct) & ";"//with out single quote (')
// but SelectedProduct must contain number

Same thing is happening in your case three also, so change all this mistake
I hope it will help you other wise feel free to ask

Mike Askew commented: Helpful +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Mike Askew 131 Veteran Poster Featured Poster · Answer 1 · 2011-02-28T22:51:23+00:00

Thank you for pointing this out, it was an experiment to see if that was the cause and forgot to replace in code before copying out of Vb.Net

Unfortunately this is not the issue, most appreciated though.

prvnkmr194 0 Posting Whiz in Training · Answer 2 · 2011-03-01T00:22:55+00:00

Can you able to post your full code so that any can able to help you better.

Mike Askew 131 Veteran Poster Featured Poster · Answer 3 · 2011-03-01T00:38:25+00:00

Full code for form:

Public Class MainLocation
    Dim SelectedProduct As String
    Dim PageLoadStage As Integer = 1
    Dim LocationPossibilities(XTotal) As String
    Dim MaxLocations As Integer
    Dim X As Integer
    Dim XTotal As Integer
    'Database Variables
    Dim Con As New OleDb.OleDbConnection
    Dim DBP As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    Dim DBS As String = "Data Source = Z:\Documents\College\Computing\Project\Project-MikeAskew\ProjectDBFinal.mdb"
    Dim SQL As String
    Dim DS As New DataSet
    Dim DA As OleDb.OleDbDataAdapter

    Private Sub MainLocation_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
        SelectedProduct = ProductSearch.txt_HiddenVarStore.Text
        Call QueryDatabase()
    End Sub

    Function QueryDatabase()
        Select Case PageLoadStage
            Case 1
                Con.ConnectionString = DBP & DBS
                Con.Open()
                SQL = "SELECT * FROM tbl_Information WHERE Product_Name = '" & SelectedProduct & "';"
                DA = New OleDb.OleDbDataAdapter(SQL, Con)
                DA.Fill(DS, "Info")
                Con.Close()
                Call InformationRender()
            Case 2
                Con.Open()
                SQL = "SELECT * FROM tbl_Stock WHERE Product_Number = '" & SelectedProduct & "';"
                DA = New OleDb.OleDbDataAdapter(SQL, Con)
                DA.Fill(DS, "Stock")
                MaxLocations = DS.Tables("Stock").Rows.Count
                Con.Close()
                Call InformationRender()
            Case 3
                Con.Open()
                SQL = "SELECT * FROM tbl_Location WHERE Line_Number = '" & SelectedProduct & "';"
                DA = New OleDb.OleDbDataAdapter(SQL, Con)
                DA.Fill(DS, "Location")
                Con.Close()
                Call InformationRender()
        End Select

        If PageLoadStage <> 4 Then
            Call QueryDatabase()
        End If

    End Function

    Function InformationRender()
        Select PageLoadStage
            Case 1
                lbl_ProductNameV.Text = DS.Tables("Info").Rows(0).Item(1)
                lbl_ProductPriceV.Text = "£" & DS.Tables("Info").Rows(0).Item(2)
                lbl_AdditionalInfoV.Text = DS.Tables("Info").Rows(0).Item(3)
                SelectedProduct = DS.Tables("Info").Rows(0).Item(0)
                PageLoadStage = 2
            Case 2
                lbl_LineNumberV.Text = DS.Tables("Stock").Rows(0).Item(0)
                lbl_SoldbySectionV.Text = DS.Tables("Stock").Rows(0).Item(1)
                lbl_CurrentStockV.Text = DS.Tables("Stock").Rows(0).Item(2)
                lbl_NextDeliveryDateV.Text = DS.Tables("Stock").Rows(0).Item(3)
                lbl_NextDeliveryTimeV.Text = DS.Tables("Stock").Rows(0).Item(4)
                SelectedProduct = DS.Tables("Stock").Rows(0).Item(0)
                PageLoadStage = 3
            Case 3
                X = 0
                ' Below currently unused as needs replanning and rewriting
                'Do Until X = MaxLocations
                'LocationPossibilities(X) = DS.Tables("Product-Location").Rows(X).Item(2)
                'X = X + 1
                'Loop
                'Location Handling Goes Here
                img_ProductImageDisplay.Image = DS.Tables("Location").Rows(0).Item(4)
                PageLoadStage = 4
        End Select
    End Function

    Private Sub btn_GoBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_GoBack.Click
        lbl_LineNumberV.Text = "."
        lbl_ProductNameV.Text = "."
        lbl_ProductPriceV.Text = "."
        lbl_AdditionalInfoV.Text = "."
        lbl_SoldbySectionV.Text = "."
        lbl_CurrentStockV.Text = "."
        lbl_NextDeliveryDateV.Text = "."
        lbl_NextDeliveryTimeV.Text = "."
        SelectedProduct = ""
        ProductSearch.Visible = True
        Me.Visible = False
    End Sub
End Class

Mike Askew 131 Veteran Poster Featured Poster · Answer 4 · 2011-03-01T15:32:18+00:00

Just to update the thread, i ran the SQL off line 14 directly in access and the query constantly asks for parameter input. I am guessing this is the issue and now its just a matter of working out why my SQL is misbehaving :(

Any input?

prvnkmr194 0 Posting Whiz in Training · Answer 5 · 2011-03-01T20:53:05+00:00

Sorry for late response

Dim SelectedProduct As String

SelectedProduct is a string type variable ok.
Now come to form load event

SelectedProduct = ProductSearch.txt_HiddenVarStore.Text
        Call QueryDatabase()

Here I assume that you initialize selectedProduct with a string (product name), upto that every thing fine and work too right.

Now what happen when your case 2 is execute

Case 2
                Con.Open()
                SQL = "SELECT * FROM tbl_Stock WHERE Product_Number = '" & SelectedProduct & "';"
                DA = New OleDb.OleDbDataAdapter(SQL, Con)
                DA.Fill(DS, "Stock")
                MaxLocations = DS.Tables("Stock").Rows.Count
                Con.Close()
                Call InformationRender()

here Product_Number is of type number in table and your SelectedProduct is a string type variable, so you have to convert to integer

SQL = "SELECT * FROM tbl_Stock WHERE Product_Number = " & Convert.ToInt16(SelectedProduct) & ";"//with out single quote (')
// but SelectedProduct must contain number

Same thing is happening in your case three also, so change all this mistake
I hope it will help you other wise feel free to ask

Mike Askew 131 Veteran Poster Featured Poster · Answer 6 · 2011-03-01T21:34:38+00:00

Yes thanks the issue was this coupled with a phantom [space] in one of the table names that only became apparent during testing recently. +1'ed