I 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 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 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'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 RepliesI want to set data binding source filter. I want to show only 10 results in descending order by date column.
I know how to use TOP clause in ordinary 'sql' query, but I don't know how to set up filter to binding source.
Dim dataContext As New ACSLostFoundEntities()
Dim query = From s In dataContext.FosterForms() _
Join c In dataContext.Fosters() _
On c.License Equals s.License _[code]....
At the end of this I want to do a Where WantedPets.Contains(criteria), is this possible?
I'm need to do the equivalent of a t-sql statement like this using LINQ:
SELECT * FROM mytable WHERE idnumber IN('1', '2', '3')
so in LINQ I have:
Dim _Values as String = "1, 2, 3"
Dim _Query = (From m In mytable Where idnumber = _Values Select m).ToList()
Not sure what to do with _Values to make idnumber evaluate each value in the string.
Whats wrong with the below query, I am getting this error: Nullable object must have a value.
[Code]...
I'm currently trying to loop through an array (two values) and use both values in a query inside the loop Right now my code doesn't work. I'm trying to populate dynamically the "appType" parameter within the "SelectParameters" tags of the SQLDataSource, but this won't work.
[Code]...
I am using VS2010 windows application , SQL server 2008 express at backend.I am designing a windows form with fields to search records in database.Say there are 3 fields in the form :
1) material no 2) material group 3)creation date the corressponding table in database with above fields is " Material_master" User can search materials in the database by entering any of the above fields.No I want to write a query where if user doesn't enter anything then my query should work like
"select * from materialmaster "
if only material no is enterd at screen then
"select * from materialmaster where MatNo = materialno "
if both date of creation and material no enterd then
" select * from materialmaster where MatNo = materialno and Date = creationdate "
In other words , field with no values in it should not be accounted in the where clause. Only one query I want to write.How we can achieve this in VB.net windows application?
I'm adding queries to a wizard generated Table Adapter with WHERE condition that involes the "%" wildcard:
SELECT * FROM Customers WHERE Country = @Country AND CustomerID LIKE @CustomerID
--If I add % after @CustomerID, I get an error message that the --query can't be parsed but if I run the query as above it returns all --the rolls irrespective the value I supplied for the CustomerID column.Only performance counts!
I've got two tables:
tClient:
ClientID Name=============1 John2 Sally3 Joe4 Nick
tInvoice:
InvoiceID ClientID DateAdded==========================1 1 2010/09/012 1 2010/09/023 3 2010/09/01
I'm trying to write sproc where I need the following info:
[Code]...
I built a prototype system with some database queries. Since it was just a prototype and I was very new to databases, I just used a direct string. This is the string I used and it works fine:
command = New OleDbCommand("SELECT * FROM " + prefix + "CanonicForms WHERE Type=1 AND Canonic_Form='" + item + "'", dictionary_connection)
Now, in putting it in a real system, I wanted to use the more secure parametized method, so after some googling I came up with this:
command = New OleDbCommand("SELECT * FROM @prefix WHERE Type=1 AND Canonic_Form=@form", dictionary_connection)
command.Parameters.AddWithValue("@prefix", prefix + "CanonicForms")
command.Parameters.AddWithValue("@form", item)
But all I get is an error for an incomplete query clause. What have I done differently between the two?
I want to pass a comma delemited list of values as a parameter to a query I'm building using the designer in Visual Studio 2008 based on some strongly typed DAL tutorials I was going through. The query is going against a DB2 database. Here's what I want to do:
select * from prices where customer in(?)
It works fine win I pass in 123456 as ?But fails when I pass in '123456' (it is a char field so I don't know why this doesn't work; it must be adding these behind the scenes) or 123456, 123457 or '123456', '123457' I'm adding this page to a portal where all the data access is being done based on the DAL designer model with a BLL that calls it so I wanted to do it this way for consistency.
Here is the code:
Function getData(ByVal id As String)
Dim reader As SqlClient.SqlDataReader
Dim statement As String
[code].....
When Creating a query using the sear Criteria Builder . I keep getting the error : "The schema returned by the new query differs from the base query" what does that mean and how do i avoid this problem in future
View 2 RepliesI 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 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'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 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
why 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 RepliesI am brand new to programming I'm trying to finish my last programing assignment for the semester and have hit a wall. I'm suppose to create a database that list a countries name, that countries currency type, and the conversion rate from 1 USD to a selected currency type.I'm using VB 2010 the connection method I'm using is Microsoft Access Database File (OLE DB)
here is my code i have high lighted the part of code in red that is giving me the problem and entered my question in green text at the point i keep getting the error. Private Sub btnConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConvert.Click
[Code]...
End Sub