In vb.net, how can I parameterise a list of values (strings in this case) to be used as an IN clause?
sSQL = "select foo_id, foo_type, foo_name from tbl_foos where foo_type in (:fooTypes) "`
`ocmd.CommandText = sSQL`
`ocmd.Parameters.Add("fooTypes", OracleType.VarChar).Value = "'Big','Small'"
I am trying to use the following query Dim sqlQry As String = "SELECT * FROM tblTest where Name=@NM and Rank=@RN"..Then I fill my dataadapter by Dim dAdt As New SqlDataAdapter(sqlQry, conStr) But do not know where to put the parameters that I have set after where clause.
View 3 RepliesI've been looking for the right answer how to select with parameters and execute it in VB.NET.
View 1 RepliesI'm connecting succesfully to an Oracle 10g DB with an the Microsoft ODBC for Oracle driver.Regular queries without parameters work fine, but parameterized queries act as if the parameters aren't getting passed in.ex.
--this works fine
Select * from tbl1 where column1 = 'test'
--this doesn't
select * from tbl1 where column1 = ?
--odbc string parameter 'test'
Here's what my connection string looks like:
"Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
[code]....
Currently working on a web-service with Oracle10g as back-end. Here is my problem.
1. The table I need to populate has 100 columns. I have no control over the design of the table.
2. I wanted to use the "deriveparameters" method of OracleCommandBuilder, but found out that this is "costly" in terms of performance. What I initially planned to do was to derive the parameters from an open connection, close the connection, then maybe cache the parameters obtained but I don't know if this is possible.
I am unsure how big of a hit the performance will get if I continue using the "deriveparameters" method WITHOUT caching the parameters driven. The environment is as follows
-intranet
-around 2000 users
-users spread out in different parts of the country
I am not familiar where they will put the web pages of the application, the web service and the database. I don't know if they'll be putting it in different servers or on one server, but that's something we cannot control too, that's why I am looking for the best approach.
I just don't want to hard-code all that parameters and if, for instance, we need to change some properties/parameters, it will be a pain to maintain the code. It would be nice if someone here can point a way to automate the whole thing w/o the performance of the system taking a hit.
Parameterizing an SQL IN clause? am using a following procedurehere i use "in clause" for attendancecodeid which accepts inetger value,how i can pass parameters for my "sql procedure in clause" from vb.net,i tried many thing nothing worked for me looking for a solution,
alter procedure MPEX_SP_ActivityException_Select
@startDate date,@endDate date,@AttCode nvarchar(max)
as
[code]....
Trying to write a list of oracle commands to XML but keep getting this error. "There was an error reflecting type 'System.Data.OracleClient.OracleCommand'."
Friend Sub WriteDataToFile(ByVal Commands As List(Of System.Data.OracleClient.OracleCommand))
Try
Dim PathName As String = OffloaderDataPath() & "Commands " & Now.ToLocalTime.Ticks.ToString & ".XML"
[code]....
I am reading a csv file in to a datatable in vb.net and making a few checks and appending an extra column. I then want to perform a bulk insert using microsofts Oracle.DataAccess (no choice in this) to an Oracle database. what would be the best way to perform this as there is no bulkImport like in SQLserver.
View 2 Repliescan you tell me,how can i add a provider for oracle(Microsoft Provider oledb for oracle).Actually i had Microsoft ODBC for oracle but i want to add a provider above mention.
View 2 RepliesI'm writing a query to select all records that has any part of parameter. I have one table called Employees. Some people have name like this: John David Clark If the parameter is
[Code]....
I should be able to get result back as long as there's a match in the parameters. If I use Function Contains (q.FirstName & " " & q.LastName).Contains(employeeName), I will not get any result back if employeeName is "John Clark" Function Contains looks only for next words from left to right. It doesn't match a single word at a time. So that's why I used this in the Linq to SQL:
[Code]....
I am getting error [07002] the # binded parameters < the # of parameters makers, i checked both parameters were perfect even though i am getting this error here is my code
[Code]...
Basically Commands has Parameters and parameters has functions like Add, AddWithValue, and etc. In all tutorials i've seen, i usually noticed that they are using Add instead of AddWithValue.
[Code]...
since it saves my coding time. So which is better to use? Which is safe to use? Does it improves performance?
is there a way to get the GET parameters and POST parameters in just one function or Collection in ASP.NET? Like using $_REQUEST in PHP? I'm using VB.NET.
View 3 Repliesi Use "Imports System.Data.OracleClient .. Private conn As New OracleConnection()" to connect to my Oracle database..but when i put my program to another PC I need install Oracle Client on it...is there some other way how to connect to Oracle DB without installing oracle client to client PC?? (Database is on Server an program is on client´s PCs)
View 2 RepliesI am learning how to use database. I am using sql server 2005. I would like to know how to make an sql query to the database from vb.net. I have tried some code. what i would like to know is, how to make the sqldataadapter statement with 2 or more where clause.
[Code]...
I have an application that does a search of our database (exposed via EF) for records meeting certain conditions. We have two main tables (Jobs and Recipients). Recipients are linked to the Jobs table by a Job ID.The job record has various fields (Process Date, NameJobType). The recipient has a lot of Name and ID fields (e.g. Account Number, Surname, etc)
View 1 RepliesPretty new to VB.Net. Having a bit of trouble here with something I though should be simple.Keeping it simple. Let's say i have a Document table with "Name" that I want to search on (in reality there are several other tables, joins, etc ..). I need to be able to build the query where clause based on string values passed in.
Example - user may pass in "ABC" or "ABC DEF" or "ABC DEF GHI".The final query would be (syntax not correct i know)Select * from Documents Where Name Like %ABC% AND Name Like %DEF% AND Name like %GHI%
So, I though I could do something like this.
Dim query = From document In _context.Documents
<< loop based on number of strings passed in >>
query = query.Where( ... what goes here?? )
For some reason, being brain dead or something, I can't figure out how to make this work in VB.Net, or if I'm doing it correctly.
I have a dataset setup by using the Visual Studio wizard and in the form is this line of code:
Me.StudentsTableAdapter.Fill(Me.StudentsDataSet.Students)
The select statement for the table adapter is[code]...
Can you tell me how to add a Where clause using coding on this table adapter?
I have two tables:Orders OrderProducts - An order can have 1 to many orderProducts records associated with it.What I am trying to do (unsuccessfully) is to implement a GridView search using a LinqDataSource wherein the search returns Order results where any of the OrderProduct.Manufacturer columns contain a search query.
I was hoping the following would work, however it seems lambda expressions do not work within a Where clause of a LinqDataSource (in VB):
[Code]...
how to get this working within the LinqDataSource definition, or will I have to handle and set up a custom OnSelecting event?
I am getting this error when trying to connect to the database which comes with Sage Line 50. It doesn't matter what table i try to look at i get this error when i try to create a datagridview. The near 'COMPANY' bit is the name of the table not one of its fields. Is this going to be something wrong with the SAGE ODBC (wouldn't surprise me) or does something need changing?
View 10 RepliesI have a table DailyTransaction in MsAccess as under: DTDate, DTTime, CustId, Description, Quantity, Price, Amount, AccountNumber, Name I want to extend following query with a Group By clause:I require to display all records in a Listview Group By CustId.Then I require to display all records in a Listview Group By AccountName.e how I can extend following query with Group By clause to achieve the required results
View 4 RepliesHow should I create between clause in my query?
View 3 RepliesI've develop an email function for my company. Basically the email will be sent to the student if a lecturer have any announcement to made.My company have 2 branches. Btw, all my queries written in the database. There are few fields that contains the queries.
This is my database looks alike :
Table Name - email
Fields : Contains
1. EmailID 1
[Code].....
I am using visual studio 2010/vb trying to display data that is based on a value read from a text file.Currently i can get it to read a text file with only 1 value and get this value into a label. Then by using a SqlDataSource and setting a parameter in the WHERE clause to use the value from the label, it will display the related data in a data GridView.
BUT i would like to be able to read multiple values seperated by a , or next row down in the text file and enter these values into a WHERE bookID in (value1,value2,...)
I have an application that does a search of our database (exposed via EF) for records meeting certain conditions. We have two main tables (Jobs and Recipients). Recipients are linked to the Jobs table by a Job ID.The job record has various fields (Process Date, Name, JobType). The recipient has a lot of Name and ID fields (e.g. Account Number, Surname, etc)I want to present a search screen where they see a list of fields to search on. The ASP.net code then sees which textboxes the user typed in (or selected), and builds a query based on that. [code] Where I'm stuck is figuring out how to add in additional where clauses. Should I just use Entity SQL?Can this return strongly typed EF objects as well? And is it possible to return both the Jobs and Recipients objects?
View 1 RepliesI am getting an "Automation Error" with the following:
Dim sheetdate As String
Dim sheetdate1 As String
sheetdate = "7/1/2011 23:59:0"
[code]....
I need to pass two variables to the WHERE clause. If I manually input 'sheetdate' and 'sheetdate1' into the clause -
WHERE (((ticketorders.TimeStamp)>#7/1/2011 23:59:0# AND (ticketorders.TimeStamp),#7/2/2011 23:59:0# ...
everything works fine. The problem is I have multiple sheets and I would rather not have to add the entire sub-routine to each and every sheet. My thought is, it would be easier to create a PUBLIC sub and pass the time values (sheetdate and sheetdate1) to the WHERE clause.
Is there a way to simply the Handles statement? What if i want this sub to handle 20 textboxes do I have to list each textbox there? I already have a loop in my script so i can cycle through my textboxes ("TextBox" & i.text). is there anyway to use a variable or a function after handles to get all the names of the text boxes?
Private Sub textChange(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress,TextBox2.KeyPress, TextBox3.KeyPress, TextBox4.KeyPress, TextBox5.KeyPress
Is it possible to have something similar to an SQL 'WHERE' clause in a For Each loop in VB.NET?I.E.
FOR EACH event IN events
'WHERE eventdate=getdate
NEXT
I've written an SQL query with a lot of 'or's in the 'where' clause: i.e. "SELECT * FROM myTable WHERE col1='a' or col1='b' or col1='c'...etc" I'm trying to run a query in access via vb.net, but I keep getting "Query is too complex" error message. I'm guessing I've hit some maximum limit. Anyone know a way around this, other than just to break it down into multiple queries?
View 5 Replieswhy Im getting "Syntax error in FROM clause" ERROR when Im connecting DATAGRID with ACCESS97 database QUERY file. Im using VB6 as fore-end.
View 3 Replies