Parametrized Queries With ADO.NET
May 18, 2012
I've been going through a lot of forums and see a lot of mention of ADO.NET and how it's better to use. I guess because it's newer and being maintained with current times?I've done google searches for this, but every time I see different programming languages other than VB.NET implementing param queries with ADO.net, or it brings me to results that have oledb commands again.Is there a good ADO.NET for VB.NET tutorial with parametrized queries?
View 3 Replies
ADVERTISEMENT
Sep 3, 2009
I got the whole database thing figured out now I've got a problem with these parametrized queries. If I remove them and just explicitly put in the data I'm looking for the datareader works, however doing as I have below returns no results in the datareader.
[Code]...
View 2 Replies
Jun 25, 2009
There is not a VB.net database forum, and there is a HUGE difference (unfortunately) between ADO and ADO.net. I have a connection to a MS SQL database, which I understand is capable of handling multiple SQL statements in one Execution, ie
Using cmdExe As New System.Data.SqlClient.SqlCommand
sSQL = "Delete * from mytable where UniqueID=123;Insert into mytable (UniqueID, Field) values (123, 'something')"
.CommandText = strSingleSQLLine
[Code]....
There are two questions here actually...can I add the parameters before setting the command text, and do I have to add the same parameter over and over again?
View 3 Replies
Jan 6, 2010
I am writing a message system on my server, the xml is something like this
<xml>
<entry>
<sender>[code]....
my problem now i guess is 2 fold, i wish to combine the first 3 queries into a single query and place in a list or a collection or is there a way to do this with a single query that will give me my desired result?
View 1 Replies
Feb 6, 2012
This is the updated code:
sql = "INSERT INTO Strings (String_Name, Long_Text, Short_Text, Alternate_Text, Multi_String_ID, Lang_ID) VALUES (?,?, ?,?,?,?)"
Dim command = New OleDbCommand(sql, pConn)
command.Parameters.AddWithValue("@webStringName", "String_Name")
[code]....
I am trying to create and INSERT statement with and OLE adapter. I had it working without paramtersm but now I am trying to add parms and am having issues with the syntax.Here is the code so far...
command = New OleDbCommand(sql, pConn)
sql = "INSERT INTO Strings (String_Name, Long_Text, Short_Text) VALUES (?,?, """ & ptsShortText & """)"
command.Parameters.Add("@webStringName", OleDbType.VarChar, 250, "String_Name")
command.Parameters.Add("@webLang_String", OleDbType.VarChar, 250, "Long_Text")
[code]....
Was just trying to get the first two variables parametrized.
View 1 Replies
Sep 9, 2011
I'm using Vb2005 to hit a SQL server. i have a pretty complicated query that hits identically structured databases on the server. I was looking into parameterizing the FROM clause but cant seem to do it.[code]Depending on the users needs I will hit the 'DriverDb' or the 'MaintDb' or 'DispDb' databases. the SQL string is actually much more complicated than that with references to the db in about 5 places so wanted to simplify it so that i could just replace with a parameter.
View 2 Replies
May 28, 2009
Now that I've switched over to parameterized queries, everything is great except along with handling all the special characters that would otherwise disrupt an all text SQL stament, it's handling the word NULL. My code basically does this for every variable used in the entire SQL statement:
If strCallName = "" Then
strCallName = "NULL"
End If
[Code]...
and that worked great for my old all text ones because leaving the word NULL out of single quotes would tell it yeah, that's actually null and pass it a true null value. Now with parameterized ones, it's literally passing it the word NULL I tried just leaving the string empty but then when I try to execute the command, it says that the parameter cannot be a zero length string. So how do you give a null value to a a parameterized query and make it actually send that to the database in the statement? Would strCallName = vbNull work or would it still say it's a zero length string?
View 10 Replies
Jun 9, 2011
I'm trying to LINQ two tables based on a dynamic key. User can change key via a combo box. Key may be money, string, double, int, etc. Currently I'm getting the data just fine, but without filtering out the doubles. I can filter the double in VB, but it's slooooow. I'd like to do it in the LINQ query right out of the gate.
LinqMasterTable:
-------------------------------------------------------------
| AppleIndex | AppleCost | AppleColor | AppleDescription |
------------------------------------------------------------
[Code].....
View 1 Replies
Nov 3, 2011
I am trying to run what I thought was a rather simple parameterized insert query, but am running into all sorts of problems. SQL EXPRESS 08R2, VB.net
Here is the VB code that builds the parameters...
UPDATED Variable names:
itemDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("MyConnectionString").ToString()
itemDataSource1.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
itemDataSource1.InsertParameters.Add("@short_Text", short_Text)
[Code]....
View 2 Replies
Jun 10, 2011
I'm writing small VB.Net app which should build reports based on data gathered from some external MDB-files (Access 2007).
It was planned that this app will use parametrized SQL queries to collect data from DBs.
One of the parameters for these queries is path to the external MDB-file.
Here goes sample code:
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TempTemp.mdb;")
conn.Open()
[Code]....
Looks simple but doesn't works. After launch my app throws following exception - System.Data.OleDb.OleDbException: Disk or network error.
Have spent a whole day to make it work with no success. What have I done wrong?
View 2 Replies
Jun 10, 2011
I'm writing small VB.Net app which should build reports based on data gathered from some external MDB-files (Access 2007). It was planned that this app will use parametrized SQL queries to collect data. One of the parameters for these queries is path to the external MDB-file.
Here goes sample code:
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:TempTemp.mdb;")
conn.Open()
[Code]....
View 1 Replies
Nov 30, 2011
The data was being read from a Gridview which had the html encoding. The solution was the following...
Dim Sht_Text As String = HttpUtility.HtmlDecode(row.Cells.Item(0).Text)
I am trying to prevent from having to escape apostrophes and other special characters in my string variables by using a parameterized query with an OleDbConnection. I have all of the parameters in the same order, the insert is working, it just does not maintain the characters. I keep getting those pesky html codes. I am trying to insert into an Access db from a web pages. As I mentioned, its working, just not the special characters.
Here's the code:
Dim pConn As New OleDb.OleDbConnection pConn = New OleDbConnection(cb.ConnectionString)
Dim SqlString As String = "INSERT INTO Strings (Mlt_String_ID, Lng_ID, Strg_Name, Sht_Text, Lng_Text, Alt_Text) Values (?, ?, ?, ?, ?, ?)"
[code]....
jp and en records already existed, the insert of fr record pushes the html codes for the small circle which represents the degree symbol. Happens for all other special characters as well.
View 1 Replies
Jul 16, 2010
Thought I'd see if Stack overflow is quicker than me testing something while I get a thousand interruptions from other work :)
I'm updating an old VB net application and trying to refactor some of the logic as I go. The app looks for data from a single date across a few tables and writes that view to a file.
Writing the query in SQL I'd get the equivalent of
SELECT * FROM table
WHERE CAST(FLOOR(CAST(table.date AS float))AS datetime) = '15-Jul-2010'
Ideally I'd use
SELECT * FROM table WHERE date=@input
and add a date object as a parameter to a System.Data.SqlClient.SqlCommand instance
Are those two comparable? Will I get the results I expect?
View 1 Replies
Dec 5, 2010
I have a Access 2003 backend db and I'm using vb 2005 to connect to the db via conntection string. It works ok! I can see all the tables but only some of the queries. Why should some queries be absent? In 1 query I have a user define function which cuts up a string. I've created a new VB project with a new connection but the same result. I've compacted the database with not change.
View 2 Replies
Jun 8, 2011
I am trying to UNION the two queries below without success.
Dim
ds1
As
New
DataSet
Dim
MyConnection1 As
New
OleDb.OleDbConnection(GetPOWERConnectionString())
[Code] .....
View 2 Replies
Aug 3, 2011
I am trying to perform a search query using drop down boxes with a button named search. I want it to bring up the data that is searched for. I dont know where to start from, i have looked around for some coding and different ways to do it but they seem complicated, this is the bit of ASP that is my weakness, need some assistance and guidance. below is the code for the page;
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="ForSale.aspx.vb" Inherits="Users_ForSale" title="Properties For Sale" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
[code]......
View 1 Replies
Feb 19, 2010
I'm trying to figure out how to do SQL queries towards an SQL 2005 Express server and format the return value to JSON. I got the queries working using this (perhaps very newbie-like) code;
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI
[code]....
View 3 Replies
Aug 6, 2009
Where can I find informations on which WMI queries are in fact available for .NET interface through ManagementObjectSearcher. The command below returns exception for "Win32_LogicalDisk", but works for "Win32_Environment". Every reference in msdn directs to msdn library, which contains all Win32 and COM development classes (that seems not to be available from vb .net code):
[Code]...
View 2 Replies
Nov 5, 2010
I have a form that will have at least 3 seperate sql queries that I want to have submitted with an onclick event.
View 4 Replies
Dec 14, 2011
finish my querie, it works for the first 2 rows, I need ti to loop through my dgv:
[Code]...
View 4 Replies
Sep 22, 2010
I want to send the a vb.net application which will run some updates on the database. I have built the sql script files which i would normally run thru query analyser but that is not available thru MSDE.I know i can use OSQL or ISQL to run these scripts but i really want to run them thru a VB.Net application.here is the code i'm using which is generating an error beacuse i'm trying to execute more than one statement?
Dim connectionString As String = ' CONNECTION STRING IS HERE
Dim conn As New SqlClient.SqlConnection(connectionString)
Dim cmd As New SqlClient.SqlCommand
[code]....
View 9 Replies
Nov 23, 2009
i have a form which once the user has entered their correct Account No they will access the jobs relating to their number. I now need to use and update query so that once I select yes in the Query handled box, after clicking the update button, this row will then be deleted from the table. Here is the code that I have so far:
Public Class Form1
Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click
End Sub
[code]....
View 1 Replies
May 18, 2009
I am a beginner when it comes to VB express. I went to the FAQ and found what looked like the answer to my question in "Database - How can I put the value of a variable/control into my SQL statement?" which said I should use thecode ...... where Field = ' " & variablename & " ' at the end of the SQL statement
I am using a field defined as a nchar with 10 charecters and my variable is defined as a string. my field has been given the value "abcdefghij" so it is full to avoid confusion with spaces and even though the variable is identical and I have used the len command to check there are no hidden spaces etc. When I run the query I get no records selected. What am I doing wrong?. If I use Where Field = 'abcdefghij', ie specify the value in the SQL I get the correct records selected but this does not give me the flexibility I need
View 6 Replies
Aug 24, 2011
I have a basic grid of Linq2Sql entities (frmList). I have a separate form for editing (frmEdit). frmEdit has its own DataContext (as that appears to be the recommended way). How can I tell when my detail form has saved changes?
I don't see a built-in method. I am considering raising an event on the detail form when I save the datacontex, but not sure how I consume this event on frmList, and I have a feeling these events are already created for me somewhere?
View 1 Replies
Aug 2, 2011
We have a reporting system where in we are getting unexpected results:
1) The size of reports at different time of interval is different?
2) The reports are writen as pdf to a directory. When we have thousand of older unused pdf(which will not get merged) in the directory the Adobe Life Cycle(to murge the PDF) throws error.But when we clear the directory by deleting the old unused pdf, it works fine?
3) Some of the pdf that are rejected by Adobe life cycle may or may not be corrupted. Meaning they may or may not open up with Adobe Reader, manually.
View 1 Replies
May 6, 2010
I have a data driven application in <acronym title="Visual Basic">VB</acronym> which works with an SQL Server 05 Database.Since this app makes use of many queries and commands, occasionally i'me getting a timeout error as the connection pool is full. So i figured that at a specifc point in time within the app,i'll clear the connection pools using the ClearAllPools method rather than increase max pool size.
I'm using the performance monitor on my XP development machine (NumberOfPooledConnections) however not once do i see the number of Pooled Connections reset to 0 even when that method is called.How can I determine that my solution is working (if it is) before I deploy the update?
View 1 Replies
Apr 15, 2012
always had a problem getting my head around it. I don't have a very strong mental model of how the code flows. My main problem is i think i understand the syntax, but not really its application. Any links people found useful would be great.
Couple of questions. The code below doesn't make sense to me (written by someone else) the application is basically a csv reader, but manipulates the data.
Public Sub entryType()
[code].....
View 1 Replies
Apr 23, 2009
I am using C# and MSAccess as my database.
Code:private void ExecuteQuery(string strempname,string strQuestion,string IndividualRating,string OverallRating, string Comment1, string Comment2,string Comment3, string SessionName) { string strQuery= string.Empty; try { strQuery = "insert into FeedbackDetails(EmpName,Question,IndividualRating,OverallRating,Comment1,Comment2,Comment3,SessionName)"
[code]....
so this actually opens and close connection for every method. but i need to open and close the connection only one time. so how to customize this code to achieve my need. now i am hitting database for 14 times in a button click as of now. instead i need to hit only once but all the rows has to be inserted in my database.i am using MSAccess as Database.
View 3 Replies
Dec 4, 2009
Using parameterized queries with ms access 2003 integration. To search any data according to different criteria.
View 2 Replies
Oct 11, 2010
Let's say I have 3 tables: carts, baskets and eggs where a basket can contain many eggs and where carts contain many baskets. Each basket has a foreign key that maps to a cart and each egg has a foreign key that maps to a basket.I need to return a table that contains these 3 columns:Cart Name | Count of Baskets in Cart | Count of Eggs in Cart.Each table is an EF and I'm using linq with VB.So far, I have 2 queries: one that returns the columns Cart Name and Count of Basket and another one that returns Cart Name and Count of Eggs. How can I combine these two result tables so that I get the results in one table?
Dim query1 = (From cart In myEntities.Carts
Where cart.UserID = TheUserID
Join baskets In myEntities.Baskets On baskets.CartID Equals cart.CartID
[code]...
View 2 Replies
