How Can I Perform Some Basic Queries Using VB2008 Express And MS SQL SD (or CE)?
Mar 1, 2008
I am using Visual Basic 2008 Express Edition and Microsoft SQL Server Database (or CE 3.5), and I need to be able to:
Create an open connection
Insert rows of data
Read rows of data
Delete rows of data
Close and dispose of the connection
I found some code examples out there, but nothing current and specific to VB2008 and MS SQL SD (or CE). If you could point me to sample code that covers this for the 2008 versions of VB and SQL that would be great!
Thanks.Techno
View 1 Replies
ADVERTISEMENT
Apr 17, 2008
Hello,
I have an application written in Visual Basic 2008, using a SQL Express 2008 database. This application runs in several locations on stand-alone PC's and there is no network connecting them.
I have to maintain a SQL database in a central location an would like to be able to distribute updated tables from that central location by using an USB stick and replace the tables of the same name in the different locations with the updated ones on my USB stick.
This I would like to do using code within my VB2008 application.
Can someone please help me on the way to achieve this. Is it possible to give some concrete code examples, as I am a newbie as a SQL Server user.
Many thanks and greetings,
Michel
View 5 Replies
View Related
May 23, 2008
Hello Friends. I have few doubts regarding the use of Sqldatasource server control. Is the control flexible enough to achieve these things:- 1.To perform complex queries using Sqldatasource. For eg:- using JOIN to retrieve/insert data from/into multiple tables.2.Performing cascading dropdownlist using sql database as datasource.3.Performing For loops eg:-populating the multiple items into the listbox from sqldatasource. and retrieving them at once.4.use ajax update panel.etc.any example/sample on how to achieve these task?thanks.jack.
View 3 Replies
View Related
Aug 18, 2006
I get an error dialog when I try to create a new SQL database, both via the Add New Item dialog and the property wizard of a new SqlDataSource control. The error is:
Local Database File:
User does not have permission to perform this action.
I've searched for help with this.
I ensured the App_Data folder exists and I added the local ASP.NET account to the group that have R/W access to it (although the RO flag is in an unchangeable tri-state on the folder).
The SQL Server Express error log is clean and indicates full functionality.
Everything is running locally.
No VWD installation errors.
Any ideas?
Thank you!
View 3 Replies
View Related
Dec 14, 2007
When I start Management Studio I get the following message and a suggestion to change a setting:
(I can choose to continue and the system databases appear, but the DB I created does not.)
"Unhandled exception has occurred in a component in your application. Cannot create a stable subkey under a volitile parent key."
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
For example:
<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
Any assistance is appreciated.
View 1 Replies
View Related
Jun 24, 2007
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
View 3 Replies
View Related
Oct 24, 2014
I am getting error by executing below query, I need to calculate the percentage.
select oe2.OrdSourceID,Count(oe2.OrdSourceID) as TotalOrders,
Percentage = Count(oe2.OrdSourceID) * 100.0 / SUM(Count(oe2.OrdSourceID))
from OeOrders Oe
iNNER JOIN OeOrders2 AS oe2
ON Oe.OrderID = oe2.OrderID
where
Oe.ProviderID = 'JOHN' and Oe.OrderDateTime between '10/07/2014' and '10/15/2014' and oe2.OrdSourceID is not null
AND Oe.[Status] NOT IN ( 'CANCEL', 'CANC', 'CNC', 'UNVER', 'UNV' )
Group by oe2.OrdSourceID
View 2 Replies
View Related
Oct 29, 2007
as a follow on from my last post, here is my sql to give the sum of all payments we have recived for a particular batch of data , dependant upon the rate.
Select
SUM(Case dp.ReceivedByID
When 2 Then dp.PaymentAmount * (cn.DirectRate / 100)
When 1 Then dp.PaymentAmount * (cn.FeeRate / 100)
Else dp.PaymentAmount * ((cn.FeeRate + cn.FieldRate) / 100)
End)
From
dbo.DebtPayment dp,
dbo.ImportBatchItem bi,
dbo.Debt d,
dbo.Contract cn
where
cn.ClientContractID=d.ContractID
AND d.Debtid=dp.DebtID
AND dp.DebtID=bi.ItemID
AND bi.ImportBatchID=101
AND cn.ClientID=1021
cn.DirectRate is the current contract rate. This rate can be changed at any time on the system. i have created a table that stores the the rate for a particulat time frame. with the sql to return ruturn the rate for a time period, client & contract like so.
select cfc.ComRate from dbo.mContractFeeChange cfc
where cfc.ClientID=1021
and cfc.contractid=12
and someDate >= cfc.StartDate AND someDate <= cfc.EndDate
when i plum this as a sub-query into my main query as so
Select
SUM(Case dp.ReceivedByID
When 2 Then dp.PaymentAmount * (
(select cfc.ComRate from dbo.mContractFeeChange cfc
where cfc.ClientID=1021 and cfc.contractid=12
and dp.PaymentDate >= cfc.StartDate AND dp.PaymentDate <=cfc.EndDate)/ 100)
When 1 Then dp.PaymentAmount * (cn.FeeRate / 100)
Else dp.PaymentAmount * ((cn.FeeRate + cn.FieldRate) / 100)
End)
From
dbo.DebtPayment dp,
dbo.ImportBatchItem bi,
dbo.Debt d,
dbo.Contract cn
where
cn.ClientContractID=d.ContractID
AND d.Debtid=dp.DebtID
AND dp.DebtID=bi.ItemID
AND bi.ImportBatchID=101
AND cn.ClientID=1021
I get the following error.
"Cannot perform an aggregate function on an expression containing an aggregate or a subquery"
any help most welcome
View 2 Replies
View Related
Dec 27, 2007
SELECT((datediff("d", arrival,departure)) - ((select case when datediff("d", arrival, '07/24/07') < 0 then
(select case when datediff("d", '07/27/07',departure) < 0 then 0 else datediff("d", '07/27/07',departure) end)
else datediff("d", arrival, '07/24/07') end)) as nights
does any one know how can i sum the nights
right now the error is
Server: Msg 130, Level 15, State 1, Line 4
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View 1 Replies
View Related
Aug 8, 2006
winxp pro, vs2005, sqlexpressIm building an application that uses a sqlexpress database and apparently something has changed, but im not sure what. When I try to expand the node for my sqlexpress database within visual studio, i get an error "user does not have permissions to perform this action". I can connect with sqlexpress management studio. What should I check ?My connection string looks like so: data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|mytestnetdb.mdf;User Instance=true
View 3 Replies
View Related
Apr 25, 2008
Hi,
I started working with Visual Basic 2008 and created a local database that I would like to use in a Visual Basic 2005 project.
When I attempt to open the local .sdf file, I get the following error:
"You are trying to access an older version of SQL Server Compact Edition database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact Edition 3.0 or later database, run Compact/Repair. [Db version=3505053, Requested version=3004180...."
Where do I find upgrade.exe or the Compact/Repair utility?
Are there any other migration tools you would recommend?
Thanks in advance,
-Ben
View 7 Replies
View Related
Feb 25, 2008
I am writing a program where I will be calculating some values that need to be sent to an SQL Database row by row. I am new to SQL Database programming and I was wondering what I need to include in my program as far as declarations, and data adapter connections in order to accomplish this. Or is it enough to already be connected to my database via a manual data connection? I also need to know some of the basic commands for inserting a new row, and adding data under existing columns, prior to moving on to the next row and repeating the process. If you can point me to a basic example that demonstrates this, including how to properly close out my database, I would appreciate it. If you know of a good VB2008 SQL Database book that covers all the basic & some advanced SQL Database programming, I would be interested in that as well.
For Example:
Row# Name CalcA CalcB CalcC CalcD
1 ? ? ? ? ?
2 ? ? ? ? ?
Etc€¦
Thanks.
Techno
Visual Studio Express 2008 user
View 5 Replies
View Related
Sep 13, 2006
HiIm trying to connect Visual Basic Express 2005 to a remote SQL Server Express 2005. I cant find how i can do that in VB.net Express.In Web developer there are no problem to connect to a remote SQL server but i cant find it in VB.net Express. The XP with the SQL server that i want to connect to is on the local network. Greatful for help!
View 1 Replies
View Related
Aug 18, 2006
This problem only occurs after deployment, not when debugging. In the table I am having a problem with I have an ID field designated as the primary key with the identity increment set to 1 and the identity seed set to 1. There is no data in the table when deployed. I can add records to the datagridview control but when I try updating the dataset I get this error indicating that the ID field already has a value of 1 present. If I close the application and re-start it, the exception no longer occurrs when I update the dataset. I am including the code to update the tables incase something is wrong there. Any suggestions?
Private Sub UpdateMemberTable()
Me.Validate()
Me.TblMembersBindingSource.EndEdit()
Me.TblMembersTableAdapter.Update(Me.ChurchFamilyDataSet.tblMembers)
Me.TblMemberDependentsBindingSource.EndEdit()
Me.TblMemberDependentsTableAdapter.Update(Me.ChurchFamilyDataSet.tblMemberDependents)
Me.TblMemberContributionsBindingSource.EndEdit()
Me.TblMemberContributionsTableAdapter.Update(Me.ChurchFamilyDataSet.tblMemberContributions)
End Sub
View 3 Replies
View Related
Jun 13, 2007
Hi Guys,
I created a Product database table using Visual Basic 2005 Express and SQL Server 2005 Express. I have just added a new column [Picture] to the database table, which of course, should store an image or picture of a product. I am writing to kindly ask you guys for help .
i) How do I include image files into this column [Picture]?
ii) How do I get this image to display on Visual Basic 2005 Express form, so that when a product is selected the product image is displayed accordingly?
Your help much appreciated. Thanks.
Paul
View 8 Replies
View Related
Jul 10, 2007
Hello,
Iam new to Visual basic 2005, I have installed SQL Express and prefers to use ADODB to connect to the database.
This is the code i tried
con.Open(cstring1 = "Provider=SQLNCLI.1;" _
& "Server=(local);" _
& "Database=D:VS 2005 ProjectsMyDB.mdf;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "Data Source=server1sqlexpress")
The error is comexception was unhandled,
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Can anyone help me out.
I have SQLExpress installed and Visual studio 2005 installed.
Connection string for ADODB ??
Regards,
Sathyan
View 4 Replies
View Related
Nov 26, 2005
I'm a long time user of SQL Server 2000 but no knowledge of 2005 Express.
View 5 Replies
View Related
Nov 21, 2007
Hi all, I have a var called sEP which contains a number. The problem I have is calling that var during an SQL query within Visual Basic. To test the update string I wrote the following query and simply told it to enter the text "Five Stars" on the row which contains the entry '1' in Episode Number column. It works fine. UPDATE JackBennySET Rating= 'Five Stars'
WHERE ([Episode Number]= '1') But how do I replace the '1' in the query with the var sEP (which will contain the actual number I want the query to run against)?I have tried various suggestions from sources including:UPDATE JackBenny SET Rating = 'Five Stars' WHERE [Episode Number] = '" + sEp + "'However, they all fail to update the database at all which makes me think the syntax is incorrect.Any help is very appreciated (I have been seeking help in various places for 2 days now) - I always send small paypal tips to people who help me on forums.
View 4 Replies
View Related
May 9, 2006
yiotis writes "Is there a way where you can programmatically (via visual basic) replicate two or more SQL Express databases located on multiple computers without the need of SQL Server 2005 acting as a publisher. From what i understand this is a limitation in SQL Express.
Right now i have SQL Express installed on 2 machines. I am able via vb to communicate with each database, read and write data between databases but cannot seem to figure out how to perform a simple replication.
Bottom line is, i need to be able to replicate via vb code 2 sql express databases with each other.
Thanks"
View 1 Replies
View Related
Jan 22, 2007
i have sql enterpirse evaluation version and visual basic express loaded on the same machine. from inside vb, when i select from the main menu project->new item then select the sql database icon then the add button i get the following error.
connections to sql server files (*.mdf) require sql server express 2005 to function properly. Please verify the installation of the coponent or dowload from the URL: http://go.microsoft.com/fwlink/!linkID=4951
does vb express work with sql enterprise evaluation version or not ??
if so, what is it that i'm missing.
View 2 Replies
View Related
Feb 17, 2007
Hi,
Can I upload a database using visual basic 05 express edition ? or do I have to download sql server ?
Thanks
View 8 Replies
View Related
Mar 28, 2008
I have an application written with Visual Basic Express 2008, which has a Sql Server Express Compact database incorporated into the application. I've been trying to follow the suggestions listed on several threads on how to backup up my database and have not gotten very far. The following code seems to be in most threads.
Dim dbstring = "Data Source=.SQLExpress;Integrated Security=true;User Instance=true;AttachDBFilename=|DataDirectory|ImmuLogDataBase.sdf"
Dim sqlconn As New SqlClient.SqlConnection(dbstring)
sqlconn.Open()
When run, this code returns the following error at the sqlconn.Open() command.
An attempt to attach an auto-named database for file G:ImmuLogImmuLoginDebugImmuLogDataBase.sdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
My solution is saved on a flash drive which I take between home and work. I'm hoping that that isn't the issue. I believe that Visual Basic loads the database when it starts and it is already open when this code is run. Is there a way to access this open database and run a backup procedure on it from within VB express 2008?
I'm new to this and self taught so explain everything in detail. Thanks for your help.
Bruce
View 3 Replies
View Related
Oct 8, 2007
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.
View 2 Replies
View Related
Nov 15, 2007
dear friends, I installed Sql ServerExpres SP2 spanish vertion in Vista home basic, I Cant Attach a data base, it reports me the error cant access to Model data base, error 916, and when I try to Create a new data Base it Reports me, Permition denied to create a data base in master, error 262
I am trying to download English Vertion, but When I download it olways try to download me the sqlexpres32_ESN.EXE which have these problems.
if any can help me,
Regards in advance Santiago
View 1 Replies
View Related
May 27, 2007
Hi,
I'm a complete novice concerning SQL Server (Express Edition)
I'm trying to Add or Delete rows froma VB 2005 Express Function or Sub. While the program is running everything is ok. Except when restarted added records are gone and deleted records are back.
Have i missed an option during installation?
Thx,
Steven
View 5 Replies
View Related
Feb 20, 2008
Hi,
I would be very grateful if someone could take a look at my command line install and offer advice on if I am doing it correctly.
The reason I am asking is because I cannot seem to get the server installed without having errors during the installation:
"The SQL Server (MSSQLSERVER) service terminated with service-specific error 17113 (0x42D9)"
Looking at the server error log indicates that master.mdf was not in the data directory (I have however specified a different data directory for ease of backup by our software and this is created during the install but does not have this database in the data directory - it is however in the template data directory!
I also get this error when the SQL Server service is started:
"The SQL Server (SQLEXPRESS) service terminated with service-specific error 3417 (0xD59)."
All advice is welcome. Please see below my batch file. Also, if you think I should do this in a different way then please let me know. I basically want to install SQL server on a customer computer and automate the install so the custoemr does not need to enter any information.
The goal of this install is:
[1] Deply a custom .Net application and SQL server at the same time on the same computer without user input.
[2] Allow remote .Net applications (designed by myself also) to connect over a LAN (and later maybe over a WAN) to the database to the above database.
The command line is this (I have also included the batch file I am usign below). For ease of reading I have put the parameters on a new line below:
setup.exe /qb
username="CustomerUsername"
companyname="OurCompany"
addlocal="SQL_Engine,SQL_Data_Files,SQL_Replication,Connectivity,SDK"
disablenetworkprotocols="0"
instancename="MSSQLSERVER"
INSTALLSQLDATADIR="c:SqlData"
USESYSDB="c:SqlData"
SAVESYSDB="1"
SQLAUTOSTART="1"
SECURITYMODE="SQL"
SAPWD="#StrongPaSSword#"
======= Batch file Start ======
SET MODE=/qb
SET SETUP_EXE=sqlfilessetup.exe %MODE%
SET SQLDATADIR=c:SqlData
SET SET_USERNAME=username="CustomerUsername"
SET SET_COMPANYNAME=companyname="OurCompany"
SET SET_ADDLOCAL=addlocal="SQL_Engine,SQL_Data_Files,SQL_Replication,Connectivity,SDK"
SET SET_DISABLENETWORKPROTOCOLS=disablenetworkprotocols="0"
SET SET_INSTANCENAME=instancename="MSSQLSERVER"
SET SET_INSTALLSQLDATADIR=INSTALLSQLDATADIR="%SQLDATADIR%"
SET SET_SAVESYSDB=SAVESYSDB="1"
SET SET_USESYSDB=USESYSDB="%SQLDATADIR%"
SET SET_SQLAUTOSTART=SQLAUTOSTART="1"
SET SET_SECURITYMODE=SECURITYMODE="SQL"
@REM === RE-INSTALL PARAMS - NOT CURRENTLY BEING USED?? SHOULD I ADD THESE ALSO? ===
SET SET_REINSTALLMODE=REINSTALLMODE="omus"
SET SET_REINSTALL=REINSTALL="SQL_Engine,SQL_Data_Files,SQL_Replication,Connectivity,SDK"
SET SET_REBUILDDATABASE=REBUILDDATABASE="1"
SET SET_SAPWD=SAPWD="#StrongPaSSword#"
@REM === START THE SETUP / RE-INSTALL ===
START %SETUP_EXE% %SET_USERNAME% %SET_COMPANYNAME% %SET_ADDLOCAL% %SET_DISABLENETWORKPROTOCOLS% %SET_INSTANCENAME% %SET_INSTALLSQLDATADIR% %SET_USESYSDB% %SET_SAVESYSDB% %SET_SQLAUTOSTART% %SET_SECURITYMODE% %SET_SAPWD%
======= Batch file End ======
Your advice is kindly welcome.
Thank you
Kind regards
Paul
View 2 Replies
View Related
Feb 11, 2008
I watched and followed the Intro to VB 2008 Express, but in the final part of the video, when you add a database, i did everything as the video says, still i get:
An error occurred creating the form. See Exception.InnerException for details. The error is: Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Ive tried the fix stated in another forum, the one about changing the "Target CPU" but the option doesn appear, what does appear its the "Platfrom" dropdown list, but its fixed to "Any CPU" and dont have any more options.
Ive also downloaded Microsoft SQL Server Compact 3.5 ENU and "re-installed" and selected repair, followed the steps, rebooted and still nothing.
The problem persists, any ideas, please?
View 6 Replies
View Related
Jul 17, 2007
Some queries take a long time to complete.
Setup is:
- SQL Express SP2
- Windows Vista Business
- 2 GB RAM
- Core 2 Duo processor
- Connecting to (local) server with SQL Authentication
- only 1 Instance of MSSQLSERVER
Simple queries (SELECT * FROM TableName) wher the table has only a few records. This query may take up to 30 or more to execute. This slowness is consistent to certain tables. Other much larger tables run queries fine.
If a different computer logs in to the same server, queries provide instantaneous results.
View 4 Replies
View Related
Jan 30, 2007
I opened up a new project using Visual Basic Express, and selected the SQL object to start creating a database. Well I finished putting all of my tables together and started making the queries but for some reason I can't save them like when I finished making the tables. When I finished making the tables I could just click the disk icon and name the table and it would appear in a drill down menu on the left. However when I do that with the queries it just says it's saving in the lower left corner but it doesn't do anything?
I am new to VB & SQL so any help is appreciated. Thank you.
View 7 Replies
View Related
Mar 12, 2007
Hi,
I want to make a component library in Visual Basic.NET and connect to a remote Sql Server. When I create a new DataSet with the wizard, I can only connect to a .mdf file, but not to a Sql Server. With Visual Web Developer I can connect to a Sql server. What is the difference between these enviroments ? How can I do the same with Visual Basic.NET ?
Thanks in advance.
View 1 Replies
View Related
Dec 1, 2006
Hello..
I've installed Visual Basic 2005 express edition & tried to connect to SQL Server 2005 express edition. I noticed that VB2005 Express Edition doesn't have any OLEDB provider for SQL Server. The only OLEDB Provider is for M.Access. Is this correct? if so, what should i do if i want to connect SQL Server 2005 express edition with VB2005 express edition by using OLEDB connection type?
I really appreciate any respond from you guys.. Thank's and waiting for the good news..
Best regards,
VITRELLE
View 1 Replies
View Related
Jan 23, 2008
Hi Jonathan Kehayias, Thanks for your valuable response.
I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread.
Here is my response to the last code/instruction you gave me:
I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me.
I got the following debug error message:
1) A Box appeared and said: String or binary data would be truncated.
The statement has been terminated.
|OK|
2) After I clicked on the |OK| button, the following message appeared:
This "SqlException was unhandled
String or binary data would be truncated.
The statement has been terminated."
is pointing to the "Throw" code statement in the middle of
.......................................
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
..........
Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC.
Thanks,
Scott Chang
The code of my Form1.vb is listed below:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7
command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig"
command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley"
command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave"
command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City"
command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD"
command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045"
command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com"
Dim resulting As String = command.ExecuteNonQuery
MessageBox.Show("Row inserted: " + resulting)
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
InsertNewFriend()
End Sub
End Class
View 6 Replies
View Related
May 5, 2007
Hi everybody!
I have made a simple application with VB6 and SQL 2005 Express.
And after testing it in a network simulated with Virtual-Machine Workstation, the "real world" application was first getting slower and slower, and finally giving timeout errors.
It is even difficult to connect. The application times out always at 30 secs although the ado connection timeout was established longer (45)
I have checked on the machine where the server is installed, just in case the slow down was due to network problems, but it fail locally too.
Any help is wellcome
View 6 Replies
View Related
