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

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!

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

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 ....

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.

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
 

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.

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 ...

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.

no error, i return = 1 but open table, no records inside
do u mind i send u the project and have a look please ....

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.

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

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.

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 ....

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.

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.

HELLO!, IT WORKS !!! THX A LOT 
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.

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.

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 ...
 

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.

got it :) thx a a lot.