Need For Multi-Threaded Visual Basic.NET To SQL Server Data Access Example Code
Feb 10, 2007
Of all the Visual Basic.NET data access books that I have purchased and all the Internet site example code that I have reviewed, none have had any good examples of multi-threaded VB.NET code doing data access.
I am trying to avoid the non-responsiveness in a VB app while a simple data retrieval from SQL Server 2005 is in progress.
If anyone knows of any book titles or web sites that have example code (good or not) of multi-threaded VB.NET applications doing data access against Microsoft SQL Server (7, 2000, or 2005) or even against Microsoft Access(TM), it would be very much appreciated if you could provide the book title or URL to point me in the right direction.
I am currently working on a project in which I am using SqlTransaction objects with their IsolationLevel set to Serializable. I am wondering what would be the effect if multiple threads were to call this code at the same time? Would the second transaction (created by the second thread on the same connection), for example, be queued until the first one (created by the first thread on the same connection) finishes? Or would an exception be thrown for the second transaction? In case an exception is thrown, would using the synchronization (locks) around critical sections solve the issue? I guess it is easier to understand the scope of transactions in terms of multiple users, but it is confusing (for me) when I start thinking in terms of multiple threads. The underlying data is held in a single table and the scope of the transaction spans multiple SELECTs and an UPDATE or an INSERT.
I have been trying to find a feasible answer, and haven't found anything credible yet. Help!
I have a problem in which I want the run an SSIS based on a file being dropped in a directory. I've tried the WMI event watcher as well as the File Task Watcher component. The problem that I'm seeing is that if the process is currently processing in the pipeline while another file dropped, the newly dropped file doesn't get picked up. Is there a way to create a FileWatcher Task in SSIS which will spawn a SSIS job and return immediately for watching files?
I am working in an access data project. I have a stored procedure that runs fine when I open and run it directly in sql. When I use the DoCmd.OpenStoredProcedure method in VB code, the stored procedure also runs fine (and successfully adds records as it should) but then I get an error: #7874 "...can't find the object...'[Name of sp'". This halts the vb code and is a problem. Here's example code from a sp that causes this problem:
Insert into Table (Field1, Field2, Field3, Field4) Select Field1, 'Test', Field5, GetDate() from View1
I understand there may be another syntax to run a stored procedure from access visual basic other than DoCmd. I would very much appreciate guidance as to how to do this.
I'm struggeling for more than a week now with this problem, without a finding the solution.
I have two databases, MS Access and SQL Server 2005 Express Edition
Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)
I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...
Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?
Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).
the procedure goes like this;
' Create a connection to the MS Access Database Dim connectionToAccess As New OleDbConnection(DBConnectionAccString) strsql = "SELECT * FROM [TABELLA]" connectionToAccess.Open() Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)
Dim DsAccess As New DataSet("ACCESS") DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella") DaAccess.Fill(DsAccess, "Tabella")
' Create a connection to the SQL Database Dim connectionToSQL As New SqlConnection(DBConnectionSQLString) connectionToSQL.Open() Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)
Dim DsSQL As New DataSet("SQL") DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella") DaSQL.Fill(DsAccess, "Tabella")
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time. I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server. Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines?? Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server? Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
I have novice level visual basic knowledge using Visual Studio 2005 and have little knowledge of SQL using SQL express. I am attempting to create a SQL database which initially consists of a single table bound to a Visual Basic form for data entry. So far I have been able to bind a VB form to a SQL database creating a DataSet, DataAdapter, and utilizing the default binding navigator. I also seem to have functioning Stored Procedures.
The VB form is able to add data to the SQL table and I can toggle through the data while the VB app is running but when I terminate the app and load it again all newly added data is not available from the SQL table.
I have seen other strings on this subject and viewed many tutorials but they only bring me as far as I am. None of them address the concept of permanently committing data to SQL from the VB form. FYI€¦data entered manually into the SQL table is stored, just not data from the form.
I am developing a program on Visual Basic 2005 to a pocket pc, I want to make the SQLceconnection but It says that the file doesn´t exists, I use this code:
I am building a Visual Sudio process that will Truncate and load data to an Access database (2010) from SQL Server (2012). I have one "Execute SQL Task" for one table (LungCancer_HF_Patients):
"DELETE LungCancer_HF_Patients.* FROM LungCancer_HF_Patients;" that parses and works.
I have another table (Report1):
"DELETE Report1.* FROM Report1;"
that does not parse with a message "The query failed to parse. Incorrect syntax near '*'."
Why the first one parses correctly and the other one does not?
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here: Function Main() on error resume next dim cn, i, rs, sSQL Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>" set rs = CreateObject("ADODB.Recordset") set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount sSQL = rs.Fields(0).value cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution rs.MoveNext Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script: - The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Hi experts I am new to SQL Server and have got to do a project using VB & SQL Server. right now i got to prepare case study document wherein i got to mention the pros & cons of using VB+sql server... kindly help me. Thanks in advance.
We have forms written in Visual Basic 6.0. They were previouslywritten for an Access database which was moved to SQL Server 2000.Here's the connect string I used:ODBC;UID=visualbasic;PWD=password;SERVER=MPSQL;DRI VER={SQLSERVER};DATABASE=MPPhotoThe user visualbasic and the user logged in to the computer have fullrights to the database.When they open the form which is based on a view it is opened as readonly with an error 3027. The data control is not set to read only.The database file is not read only. All of the permissions are set toallow this user read, write and modify.Can anyone hep me?
Hi, i am new to sql server and visual basic, i need to connect my sql server database to a new application i've developed in visual basic 2005 express. Can any one tell me the steps to do this. Many Thanks.
I am using the SQL Server 2000 query analyser to write stored procedures for an SQL Server 2000 database.
I would like to find a debugger to help me "look into" my stored procedures as they are executed (e.g. to watch a Cursor fill a table row by row, see the contents of temporary tables as a stored procedure progresses, etc.)
Could anybody, please, direct me to-
* Any debugger that does this OR * Simple instructions how to run stored procedures from Visual Basic. I suspect :) that VB's debugger may be able to look into the SQL as it is being executed, but I don't actually know how to use VB itself.
I go to Server Explorer, right click on Data Connection, input the server name (that is, my own computer), give a new name to the database to create, and then inevitably get the error window saying:
An error occurred while establishing a connection to the server. When connectiong to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (Provider: Named Pipes Provider, error 40, could not open a conection to SQL Server)
So, how to open a connection to SQL server 2005? I looked in the msdn but found absolutely nothing.
I hav the following problem. I have written an stored procedure in sql server 2000 as the following CREATE PROCEDURE dbo.pa_rellena @pFechaInicio datetime
AS declare @pFechaFin datetime declare @auxcod_cen char(10)
--Borramos las tablas temporales si las hemos creado con anterioridad y no se han borrado if object_id('tmpCentros') is not null drop table tmpCentros
if object_id('tmpCentros2') is not null drop table tmpCentros2
if object_id('tmpMaxCajas') is not null drop table tmpMaxCajas
if object_id('tmpCajasCentro') is not null drop table tmpCajasCentro
if object_id('tmpVales') is not null drop table tmpVales
if object_id('tmpDiarioEfectivo') is not null drop table tmpDiarioEfectivo
if object_id('tmpDiarioTalones') is not null drop table tmpDiarioTalones
if object_id('tmpDiarioTarjetas') is not null drop table tmpDiarioTarjetas
if object_id('tmpDiarioSegundaForma') is not null drop table tmpDiarioSegundaForma
if object_id('tmpDiarioGastosTarjetas') is not null drop table tmpDiarioGastosTarjetas
if object_id('temp1') is not null drop table temp1
--Seleccionamos todos los centros de Salvador Bachiller select * into tmpCentros2 from centros where centros.tienda=1 order by cod_cen
--Seleccionamos el maximo de cajas por cada centro
select cod_cen, max(cod_caja) as cajas into tmpMaxCajas from cierrecaja where fecha>=@pFechaInicio and fecha<@pFechaFin group by cod_cen order by cod_cen
--Mezclamos los centros con el maximo de cajas select c.cod_cen, c.Centro, c.Direccion, c.localidad, c.provincia, c.cpostal, c.telefono, m.cajas, operaciones, cajas_tot, tienda, franquicia into tmpCentros from tmpCentros2 as c left outer join tmpMaxCajas as m on c.cod_cen=m.cod_cen
--Cajas por centro select distinct cod_cen as cod_cen, cod_caja as cod_caja into tmpCajasCentro from cierrecaja where fecha>=@pFechaInicio and fecha<@pFechaFin
--Los vales de cada centro select cod_cen,sum(importe) as imp1 into tmpVales from vales where fecha>=@pFechaInicio and fecha<@pFechaFin group by cod_cen
--Efectivo de cada centro select cod_cen,'01' as vendedor,'EFECTIVO' as descripcion, (sum(diario.TotEuro)-Sum(Diario.Imppa2)) as importe1,0 as exp1, (sum(Diario.TotEuro)-sum(Diario.imppa2)) as importe2 into tmpDiarioEfectivo from diario where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='01' group by cod_cen
--Talones por centro select centros.cod_cen,'02' as vendedor,'TALONES' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro) as importe2 into tmpDiarioTalones from centros inner join diario on centros.cod_cen=diario.cod_cen where fecha>=@pFechaInicio and fecha<@pFechaFin and diario.cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='02' group by centros.cod_cen
--Tarjetas por centro select cod_cen,'03' as vendedor,'TARJETAS' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro*(FPago.Descuento/100)) as importe2, sum(Diario.TotEuro) - sum(Diario.TotEuro*(FPago.Descuento/100)) as importe3 into tmpDiarioTarjetas from FPago left join Diario on fpago.Cod_pago=Diario.cod_pago where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen
--Segunda Froma de Pago select cod_cen,'03' as vendedor,'TARJETAS' as descripcion,sum(diario.imppa2) as importe1 into tmpDiarioSegundaForma from fpago left join Diario on Fpago.cod_pago=diario.cod_pa1 where fPago.cod_pago<>'99' and fecha>=@pfechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen
--Comisiones tarjetas de pago select cod_cen,'10' as vendedor, 'GASTOS (-)' as descripcion, sum(Diario.imppa2*(fPago.Descuento/100)) as importe2 into tmpDiarioGastosTarjetas from Fpago left join Diario on FPago.cod_pago= Diario.cod_pa1 where fPago.cod_pago<>'99' and fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen /* --Venta neta por centro declare cursortemporal cursor for select cod_cen from TmpCentros2
open cursortemporal delete detallecaja_aux fetch next from cursortemporal into @auxcod_cen while @@fetch_status=0 Begin select @importeVales=imp1 from tmpVales where cod_cen=@auxcod_Cen select @importeEfectivo=importe2 from tmpDiarioEfectivo where cod_cen=@auxcod_Cen select @importeTalones=importe2 from tmpDiarioTalones where cod_cen=@auxcod_cen select @importeTarjetas1=importe3 from tmpDiarioTarjetas where cod_cen=@auxcod_cen select @importeTarjetas2=importe1 from tmpDiarioSegundaForma where cod_cen=@auxcod_cen select @importeGastos=importe2 from tmpDiarioGastosTarjetas where cod_cen=@auxcod_cen
insert into detallecaja_aux (cod_cen,importe1) values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos) fetch next from cursortemporal into @auxcod_cen
I have already created package which loads a text file to database using the dts wizard in Enterprise Manager.How do I execute that package using visual basic?Please provide the Code!!!Thanks
I need help. 160821A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. I need help. Our SQL Server is crashing. The Database is still recovering. I can not kill any Process when the Server crashed. I shoot down and restarted the server. Nothing to do. I can not access to the database. It is recovering. How long? I do not know. What can be the reason of the recovering? Nor the event log of Windows NT or the log files of the SQL SERVER can help me.
Here is a part of the log file
23216Arithmetic overflow error for type %s, value = %f.
10915There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
99/01/19 01:14:25.69 spid25 bufwait: timeout, BUF_IO, bp 0x1bba600, pg 0x11b50, stat 0x801000/0x6, obj 0x23494814, bpss 0x124a2a0 99/01/19 01:14:27.15 ods Error : 17824, Severity: 10, State: 0 99/01/19 01:14:27.15 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'myserver'. 99/01/19 01:14:27.15 ods OS Error : 232, The pipe is being closed.
Firstly, apologies if this topic is out of the scope of this board, but I am stumped.
Basically, we have an old Visual Basic 6.0 application which currently accesses an Access database, but which we would like to upgrade to an SQL compact edition database. I have created the database, but have so far turned up next to no details reagarding how to connect to the damn thing in VB6.
Can anyone help? Or at least point me in the right direction?
Hi, I am developing an application for pocket pc 2003 smart device which utilizes synchronization between SQL CE 2005 and SQL server 2005 by means of REMOTE DATA ACCESS method call PULL( ) and PUSH( ) but currently these are the details of the error Im encountering when PULL( ) method is called:
Error code: -2147467259 Descripion: An error occured on the computer running IIS.Restart the IIS server.
Minor error: 28022 Source: SQL server CE Currently Im developing this application as a final project and due to some limitations I have setup my pc to act as the SQL server and the IIS server both on the same machine.
I want to use Visual Basic to create DTS connection. In the example below a connection is created using an ODBC string. However, I would like to make the Connection's DataSource be an OLE-DB provider instead of ODBC. However, I cannot find any information on the syntax for using an OLE-DB provider as the DataSource.
Your help is appreciated.
Les
strODBCdsn = "server1" 'Create a DSN called SQL7 or change name strUserName = "sa" 'User name assume same source and dest strPassword = "" 'with the same Password Set oConnection = oPackage.Connections.New oConnection.ID = 1 oConnection.DataSource = strODBCdsn oConnection.UserID = strUserName oConnection.Password = strPassword oPackage.Connections.Add oConnection
Anyone have any code i can use to call a DTS package from Visual Basic. I would like to have an outlook macro that will run a dts package when i receive an email. All i need is the code that will call a dts package for a sql server that is not local to the box. I've tried this code but i get an error run_tmie error '-2147217900 (80040e14)' automation error
Public Sub main()
Dim oPkg As DTS.Package2 Set oPkg = New DTS.Package2
I use SQL Server 2005 Compact Edition RC1 with Visual Basic 6.0. connection provider that i use is Microsoft.SQLSERVER.MOBILE.OLEDB.3.0. for database management i use SQL Server Management Studio. some query can't execute in visual basic but in sql management studio, that query can run very well. for example: 1. select KodeSatker, KodeSatker + ' - ' + Nama as Nama from TMSatker where len(kodesatker)=3 order by KodeSatker 2. Select a.kdprog, a.nama, b.kodesatker + ' - ' + b.nama as SKPD, a.nonurusan from TMProgram a left join TMSatker b on substring(a.kdprog, 1, len(a.kdprog) - 2) = b.kodesatker
2 query above can run in sql management studio, but in visual basic 6.0, that query have an error. error msg:run time error '2147217887 (80040e21)': multiple-step operation generated errors. check each status value
I am new to this type of programming and and have read all articles on adding an image to the database and it seems they all use sql queries to add an image but I want to add an image at design time. I am using Visual Basic 2005. I am also using Visual Basic 2005 Express Edition to try the same thing. I am trying to build a Translator program for english to Brazilian Portuguese and the reason I want to add the images is so that when I translate the word cat from english to Portuguese, I can also show an image of a cat. Can anyone please help me
I am not able to see the Data Source in Visual studio 2012. I have tried several things like 'Alt + shift + D' which is meant to bring up the datasource tab but it does not. I have also looked in view->Other windows but its not displayed there.
I'd like to be able to query a database to get the value of a certain row. However, I'd like to save this value into a variable and use them as I'd like, NOT nessessarily display them in a data control such as a gridview etc. I've been able to find a lot of information on binding datasourses to predefined data controls, but I can't find anything on simply querying a database for values and using those values to display a page as I want.My basic end goal is to create what's basically a shopping site like Amazon only for about 100 or so products.Thanks in advance for any help. I'm just getting into Visual Web Developer so please excuse my ignorance and lack of posts.
I am creating a program in VB .NET to insert an image in one of the fields in MS SQL2000.
VB variant type DIM'd as "Image", SQL field type is "Image". I cannot get the SQL insert statement to work. I'm trying to get a single insert statement to fill all fields in the row, one of which is image.
error=Operator '&' is not defined for types 'String' and 'System.Drawing.Image'
The '&' symbol is used to concatenate the SQL statements items
