Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 2005 > Pro Visual Basic 2005
|
Pro Visual Basic 2005 For advanced Visual Basic coders working in version 2005. Beginning-level questions will be redirected to other forums, including Beginning VB 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro Visual Basic 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 15th, 2007, 09:37 AM
Registered User
 
Join Date: May 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Oracle connection-passing input to query!!

I am having a problem with oracle 10g and vb2005 express.

I can connect. But I am trying to do a select statement with a condition where the variable value is read from a text box.

It cannot understand that the variable value needs to be read from the box. Now the same works in access and sql server so the question is HOW DO YOU PASS A PARAMETER TO A QUERY THAT WILL BE UNDERSTOOD BY VB2005?

I am getting an ORA-01008 all variables are not bound message.

Code below:
Dim ocon As New OracleConnection(cstring)
        Dim sql As String = "insert into dept values (@deptno,'@dname','@loc')"
        Dim sqlb As String = "select deptno,dname,loc from dept where deptno=&deptno" Dim rfs As Integer
        ocon.Open()
        MsgBox("Database is open")
        Dim objcommand As New OracleCommand(sqlb, ocon)
        objcommand.Parameters.Add("&deptno", Data.OracleClient.OracleType.Char, 10, tbox1.Text)
        Dim objdataadapter As New OracleDataAdapter(objcommand)
        Dim objdatatable As New Data.DataTable("dept")
        objdataadapter.Fill(objdatatable)
        Dim objdatarow As DataRow
        ListBox1.Items.Clear()
        For Each objdatarow In objdatatable.Rows
            ListBox1.Items.Add(objdatarow("deptno"))
        Next
        If ListBox1.Items.Count > 0 Then
            MsgBox("There is already an entry")
            ListBox1.Items.Clear()
            tbox1.Clear()
            ocon.Close()

        Else
            Dim objcommandb As New OracleCommand(sql, ocon)
            objcommandb.Parameters.Add("@deptno", OracleType.VarChar, 10).Value = tbox1.Text
            objcommandb.Parameters.Add("@dname", OracleType.VarChar, 10).Value = tbox2.Text
            objcommandb.Parameters.Add("@loc", OracleType.VarChar, 10).Value = tbox3.Text
            Dim objdataadapterb As New OracleDataAdapter(objcommandb)
            rfs = objcommandb.ExecuteNonQuery
            objdataadapterb.Dispose()
            objdataadapterb = Nothing
            objcommandb.Dispose()
            objcommandb = Nothing
        End If
        MsgBox("Database is now closed")
        ocon.Close()



 
Old October 18th, 2007, 03:21 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Code:
Dim sqlb As String = "SELECT deptno, dname, loc " & _
                     "FROM   dept               " & _
                     "WHERE  deptno = " & deptno.Text
                     If DeptNo is VARCHAR rather than NUMBER, use
Code:
Dim sqlb As String = "SELECT deptno, dname, loc " & _
Code:
                     "FROM   dept               " & _
                     "WHERE  deptno = '" & deptno.Text & "'"
                     ' singlequotes   ^                   ^





Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing Input Date Parameter values preethig Crystal Reports 0 February 28th, 2008 08:39 AM
Passing an Input Field to a Parameter Query stcraig BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 4 November 6th, 2006 06:06 PM
problems with passing user input to a query boozin Classic ASP Databases 3 March 10th, 2004 05:59 PM
passing input to a query mirage Access 3 October 23rd, 2003 08:43 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.