VB.net connect to SQL Express Stored Procedure

Dear Experts, i am writing a VB.net window application. I created a SQL Express database inside the project. Inside the SQLExpress database, i created a stored procedure.
I would like to know how to call that SP.

Below is what i did and it raised a error "Missing provider"
I tried to add "'Provider=SQLOLEDB;'", the missing provider error message gone but another error raised as below..

No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).


Sub PushtoDB()
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim da As OleDbDataAdapter

        Dim sConStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StopWatchDB.mdf;Integrated Security=True;User Instance=True"

        con = New OleDbConnection(sConStr)

        cmd.Connection = con
        cmd.CommandText = "select * from MonitorRecords"

        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub

Please let me know wht to do experts.
Thank you
Kenneth
0
kennethhon
9/4/2008 5:23:26 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

20 Replies
861 Views

Similar Articles

[PageSpeed] 15

Your using OLEDB for SQL, SQL has it's own provider

<add name="ConnectionString.Live" connectionString="Persist Security Info=False;User ID=MyID;Initial Catalog=MyDatabase;Data Source=MyComputername\sqlexpress; Password=myPassword" providerName="System.Data.SqlClient"/>

 

Have a look here for SP

http://aspnet.4guysfromrolla.com/articles/062905-1.aspx


Remember to click “Mark as Answer” on the post if it helped you. Thank you!
0
kemical
9/4/2008 12:45:39 PM
hi, the unexceptional error fixed but another weird error raised, which is no error but when i open the table, no record reflected. below is the code, on the last line, i can see the table is not empty (return 1) but after run, when i open the table, no record Dim myConnection As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StopWatchDB.mdf;Integrated Security=True;User Instance=True") Dim ds1 As New DataSet Try myConnection.Open() Dim myCommand As New SqlDataAdapter("InsertMonRecords", myConnection) myCommand.SelectCommand.CommandType = CommandType.StoredProcedure myCommand.SelectCommand.CommandTimeout = "300" Dim pMonUserName As SqlParameter = New SqlParameter("@MonUserName", SqlDbType.VarChar, 20) pMonUserName.Value = "Victor" myCommand.SelectCommand.Parameters.Add(pMonUserName) Dim pStartDate As SqlParameter = New SqlParameter("@StartDateTime", SqlDbType.DateTime) pStartDate.Value = lblStartTimeValue.Text myCommand.SelectCommand.Parameters.Add(pStartDate) Dim pEndDate As SqlParameter = New SqlParameter("@EndDateTime", SqlDbType.DateTime) pEndDate.Value = lblEndTimeValue.Text myCommand.SelectCommand.Parameters.Add(pEndDate) Dim pMonDuration As SqlParameter = New SqlParameter("@MonDuration", SqlDbType.Int) pMonDuration.Value = CInt(lblDurActual.Text) myCommand.SelectCommand.Parameters.Add(pMonDuration) myCommand.Fill(ds1) Dim i As Integer = ds1.Tables(0).Rows.Count
0
kennethhon
9/5/2008 1:57:01 AM
i have tried to duplicate the code to web application, everything is working fine.
then i create a new window application and copy everything from the web application, same problem again, no error but record cannot be added in the sqlexpress table ....
0
kennethhon
9/5/2008 2:39:00 AM

Hi kennethhon,

Dim sConStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StopWatchDB.mdf;Integrated Security=True;User Instance=True"

Since you're using the User Instances function of SQL Express database, you first need to ensure the SQL Express database has been installed. Then you have to specify the SqlClient provider rather than SQLOLEDB provider. Therefore, the connection string should be( we can put this in web.config file ):

<connectionStrings>
     <add name="TestVB1.Settings.Database1ConnectionString" 
          connectionString="Data Source=.\SQLEXPRESS;
          AttachDbFilename=|DataDirectory|\Database1.mdf;
          Integrated Security=True;
          User Instance=True"
          providerName="System.Data.SqlClient" />
</connectionStrings>

By using this connection string, you can use the attached database file without problems. Please take a try on it.

If this still can't resolve the problem, please post the new code, web.config content and the error message here.

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 2:21:44 AM

Hello,

Actually i already changed the connection string from using SQL OLE to SQL Express as below.

Dim myConnection As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StopWatchDB.mdf;Integrated Security=True;User Instance=True")

And the program (windows base application) can run without any error but the only problem is after executed the Store Procedure, no row inserted in the table.
I also made a same test on ASP.Net with VB code and everything is fine, row reflected on the table.

Below is the program source code.

    Sub PushtoDB()
        Dim myConnection As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StopWatchDB.mdf;Integrated Security=True;User Instance=True")
        Dim ds1 As New DataSet

        Try
            myConnection.Open()
            Dim myCommand As New SqlDataAdapter("InsertMonRecords", myConnection)

            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure
            myCommand.SelectCommand.CommandTimeout = "300"

            Dim pMonUserName As SqlParameter = New SqlParameter("@MonUserName", SqlDbType.VarChar, 20)
            pMonUserName.Value = "Victor"
            myCommand.SelectCommand.Parameters.Add(pMonUserName)

            Dim pStartDate As SqlParameter = New SqlParameter("@StartDateTime", SqlDbType.DateTime)
            pStartDate.Value = "2008-1-2"
            myCommand.SelectCommand.Parameters.Add(pStartDate)

            Dim pEndDate As SqlParameter = New SqlParameter("@EndDateTime", SqlDbType.DateTime)
            pEndDate.Value = "2008-1-4"
            myCommand.SelectCommand.Parameters.Add(pEndDate)

            Dim pMonDuration As SqlParameter = New SqlParameter("@MonDuration", SqlDbType.Int)
            pMonDuration.Value = 4
            myCommand.SelectCommand.Parameters.Add(pMonDuration)

            myCommand.Fill(ds1)

            Dim i As Integer = ds1.Tables(0).Rows.Count 'at here when run time return 1 record...... but after execute and open the table, nothing inside..



        Catch ex As Exception

        Finally
            If myConnection.State = ConnectionState.Open Then
                myConnection.Close()
            End If
        End Try

    End Sub
 
0
kennethhon
9/8/2008 2:47:47 AM

Hi kennethhon,

I'm a little confused on your meaning. Please clarify the questions to me.

1) Do you want to use the stored procedure to insert a new record in database?

2) After inserting the new record, do you want to select the table in database into the dataset?

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 3:01:36 AM

Thx a lot for help

Yes, i use Stored Procedure to insert record to database.

After insert new record, i want to select the table into dataset.

Turn out the dataset have records but when i check the database table, empty inside ...

0
kennethhon
9/8/2008 3:18:08 AM

Hi kennethhon,

There're some problems in the code, please try following:

Sub PushtoDB()
        Dim myConnection As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StopWatchDB.mdf;Integrated Security=True;User Instance=True")
        Dim ds1 As New DataSet

        Try
            myConnection.Open()
            Dim myCommand As New SqlCommand("InsertMonRecords", myConnection)

            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.CommandTimeout = "300"

            Dim pMonUserName As SqlParameter = New SqlParameter("@MonUserName", SqlDbType.VarChar, 20)
            pMonUserName.Value = "Victor"
            myCommand.Parameters.Add(pMonUserName)

            Dim pStartDate As SqlParameter = New SqlParameter("@StartDateTime", SqlDbType.DateTime)
            pStartDate.Value = "2008-1-2"
            myCommand.Parameters.Add(pStartDate)

            Dim pEndDate As SqlParameter = New SqlParameter("@EndDateTime", SqlDbType.DateTime)
            pEndDate.Value = "2008-1-4"
            myCommand.Parameters.Add(pEndDate)

            Dim pMonDuration As SqlParameter = New SqlParameter("@MonDuration", SqlDbType.Int)
            pMonDuration.Value = 4
            myCommand.Parameters.Add(pMonDuration)

            myCommand.ExecuteNonQuery()

            ' For selecting
            Dim cmd2 As SqlCommand("select * from xx", myConnection)           
            Dim da As SqlDataAdapter(cmd2)
            da.Fill(ds1)
            Dim i As Integer = ds1.Tables(0).Rows.Count


        Catch ex As Exception

        Finally
            If myConnection.State = ConnectionState.Open Then
                myConnection.Close()
            End If
        End Try

    End Sub

I didn't test it and if there's any problem, please post the error message here.

Thanks. 


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 4:03:00 AM

no error, i return = 1 but open table, no records inside

do u mind i send u the project and have a look please ....

0
kennethhon
9/8/2008 4:48:11 AM

Hi kennethhon,

no error, i return = 1 but open table, no records inside

do u mind i send u the project and have a look please ....

That's possibly caused by the stored procedure. You could provide us the stored procedure and the table schema in database.

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 4:54:29 AM

thx for the continously help

below is the sp:

 

ALTER PROCEDURE dbo.InsertMonRecords 
@MonUserName varchar(20),
	@StartDatetime datetime,
	@EndDateTime datetime,
	@MonDuration int
AS
	/* SET NOCOUNT ON */
	
	insert into MonitorRecords (
	MonUserName, 
	MonDateTime,
	StartDateTime,
	EndDateTime,
	MonDuration)
	Values (
	@MonUserName,
	getdate(),
	@StartDatetime,
	@EndDateTime,
	@MonDuration
	)
	
	RETURN

 Below is the DB Schema

MonUserName, varchar(20), not null
MonDateTime, datetime, not null
StartDateTime, datetime, not null
EndDateTime, datetime, not null
MonDuration, int, not null

0
kennethhon
9/8/2008 5:04:01 AM

Hi,

There's no problem with the stored procedure. I found another problem in code-behind. The CommandTimeout property needs a INT value while you assign a string. Try this please:

myCommand.CommandTimeout = 300
Thanks. 
David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 5:35:23 AM

hello, changed but still same problem.

actually the same program and set up is working perfectly on web base.... but not on my window base ....

0
kennethhon
9/8/2008 6:03:48 AM

Hi kennethhon,

do u mind i send u the project and have a look please ....

You could send the project to me. My email is: v-weqian@online.microsoft.com (remove "online").

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 6:51:15 AM

Hi kennethhon,

It's caused by the App_Data folder. It has no this code in Windows application. You have to use the physical path of the database file.

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 8:35:29 AM

HELLO!, IT WORKS !!! THX A LOT Smile

I have one more question, once deployed the window application, i don't know user will save the exe and db at which location, how do i make the path dynamic in program please.

0
kennethhon
9/8/2008 8:44:02 AM

Hi kennethhon,

You can get the application path in runtime by some API. Here's a link:

http://www.csharp-examples.net/get-application-directory/

Also, you could do a research on MSDN.

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 9:05:18 AM

Hello, i am sorry but i still have a noob question abt the application path

i referred to the url u gave, the physical path i got as below (by using code

Path.GetDirectoryName(Application.ExecutablePath

C:\Users\QIComm_Kenneth\Documents\Visual Studio 2005\Projects\StopWatchNew\StopWatchNew\bin\Debug

but the mdf should be under

C:\Users\QIComm_Kenneth\Documents\Visual Studio 2005\Projects\StopWatchNew\StopWatchNew\

Weird ...

 

0
kennethhon
9/8/2008 9:27:30 AM

Hi kennethhon,

C:\Users\QIComm_Kenneth\Documents\Visual Studio 2005\Projects\StopWatchNew\StopWatchNew\bin\Debug

but the mdf should be under

C:\Users\QIComm_Kenneth\Documents\Visual Studio 2005\Projects\StopWatchNew\StopWatchNew\

I'm sorry that I'm not familiar with Windows application. From the problem, I think you could do some string operations to cut off the static string "bin\Debug". You have to try it by yourself.

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
9/8/2008 9:46:09 AM

got it :) thx a a lot.

0
kennethhon
9/8/2008 9:54:38 AM
Reply:

Similar Artilces:

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

SQL Server Stored Procedures/VB.Net
IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example..... Most likely the easiest way, is, when you retrieve the data from the sProc, put it in a DataView.. That way, you can sort The DataView by any field, ASC or DESC that you wantDavid WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on th...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consul...

Can somebody help
I Seem to be going nuts ! I know it has to be simple but I am missing the link. I haven't used crystal reports with asp.net before. So pardom me if my problem sounds silly. My requirement is very simple actually. I am using asp.net 1.1 with vb.net in vs.net 2003 with the inbuilt crystal reports control. I have to connect to a sql server 2000 database to extract rows from a stored procedure which accepts a parameter and the report has to be in Mail label format. I referred various books and web sites and according to that , added a crystal reports file to the project and selected the store...

Problem accessing SQL Stored Procedure From .Net using hibernate object
Hi, I am calling a SQL Stored procedure from .net application using Hibernate object, but somehow its telling me "The 'callable' attribute is not declared.", I am not sure what's wrong I have done here. Will really appreciate if someone can help me for this. Herewith I am providing my Stored Procedure, my .hbm file and code to call it. can someone pls tell me what's wrong in this? Also, if possible, pls tell me how to set result in a list object. Thanks in advance, ######################################### MY STORED PROCEDURE : ALTER PROCEDURE [dbo].[RulesDetails_...

how to pass vb.net 2 dimensional array to a sql stored procedure
I am developing a online shoing cart for a ISP company. according to the business when we add some products we keep set of records for products such as domain name, address , tel, hosting option and so on so my basket contains a list of items as follow i would prefer to convert these in to datatable and pass to a stored procedure item     tel           name        add1      add2       add3      postcode   &n...

How to execute stored procedure from vb.net CodeBehind instead of a sql statement?
I've learned how to exeucte a simple sql statement from my vb.net codebehind (see sample below), but I want to go a step further and learn how to execute a stored procedure from my code behind.  I've already written the stored proc, just don't know how to reference it and the parameters in my codebehind..Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxx").ConnectionStringconn = New SqlConnection(connectionString) comm = New SqlCommand("INSERT into xxx (a...

Creating a .NET Stored Procedure in Sql Server 2005 Express Edition
Could somebody tell me how do we create a .NET Stored Procedure in Sql Server 2005 Express Edition and deploy and debug it against the database from Visual Studio 2005 or Visual Web Developer?  Can some one also let me know which approach is faster among .NET stored procedure or T-SQL stored procedure? Regards... Shashi Kumar Nagulakonda.  Shashi Kumar NagulakondaXinthe Technologies I can't help you with the first question. The answer to the second question is obviously it depends.  As far as I know (and I know very little about .NET sps), given the same task, the T-SQL...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

ADO.NET 2.0 - Three-tier application - connection string error
Using ASP.NET 2.0 beta, VS2005 beta 1, W2k3 server with IIS I try to build a three-tier app structure as followed: - the store procedure in SQL with a input parameter as Username and retrieve the UserId as output parameter. - the business logic (class) with connection string defined in web.config (“AppConnectionStringASPNETapp”) - the presentation aspx file after the user has logged in through login.aspx I have work out 2 scenarios with 2 different errors. Scenario 1: Here is the code is business logic (class) (catalog.cs) using System; using System.Data; usi...

connection between VB.net and SQL
 I would like to use VB2005.Net (Express) connect to MSSQL2005 (Express), how can I do it? I can complete the development via local database (with "attach" method in connection string).  I try the remote connection via client machine with string ("Data Source=w2000-vm1\SQLEXPRESS;Initial Catalog=ABC;Integrated Security=SSPI;Connect Timeout=30;User Instance=True") and it was still pop up error shown below (using web deployment for the client machine).  But I can do the same with Excel VBA sucessful. Error Message: See the end of this message for details o...

Web resources about - VB.net connect to SQL Express Stored Procedure - asp.net.object-datasource

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Resources last updated: 12/24/2015 11:57:47 AM