SteveCampbellUK

Hi

I have built a program which sends and receives data from an Access Database. Within the database I have a query which updates on of the columns in one of the tables. I am looking to call on this query when a form loads in my program. I am unsure of the text I require in order to call this query.

Any help would be greatly appreciated.

Regards




Re: Visual Basic Express Edition Calling a stored procedure in Visual Basic 2005 Express?


Re: Visual Basic Express Edition Calling a stored procedure in Visual Basic 2005 Express?

SteveCampbellUK

Thanks for that,

however on inputting this into my program it seems to work but then comes back with a connection error

System.Data.SqlClient.SqlException was unhandled
Class=20
ErrorCode=-2146232060
LineNumber=0
Message="An error has occurred while establishing a connection to the server. When connecting 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
Number=-1
Server=""
Source=".Net SqlClient Data Provider"
State=0
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at DiscLibrary.Loans.DiscCatalogueBindingNavigatorSaveItem_Click(Object sender, EventArgs e) in C:\Documents and Settings\uusteven\Desktop\DiscCatalogue\DiscLibrary\DiscLibrary\Loans.vb:line 9
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at DiscLibrary.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Any ideas on what this means Do I have to allow permissions from either access of Visual Basic

Regards






Re: Visual Basic Express Edition Calling a stored procedure in Visual Basic 2005 Express?

Martin Xie - MSFT

SteveCampbellUK wrote:

I have built a program which sends and receives data from an Access Database. Within the database I have a query which updates on of the columns in one of the tables. I am looking to call on this query when a form loads in my program. I am unsure of the text I require in order to call this query.

Hi Steve,

1. First I need to clarify the folling:

You are actually using SQL Server database not Access Database, right Stored procedures cannot be created in Access Database.

2. About the error.

Message="An error has occurred while establishing a connection to the server. When connecting 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

-> In VB Express edition, connection to a remote database is not supported, you can do it in code.

Assuming you used this connect string, please check if the Data Source is correct.

Dim conn As SqlConnection = New SqlConnection("Data Source=Servername\SQLInstance;Initial Catalog=Pubs;Integrated Security=True")

Or please try the standard connection string.

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Here are various connection strings to SQL Server 2005.

http://www.connectionstrings.com/ carrier=sqlserver2005

If I misunderstood you, please let me know.

Regards,

Martin






Re: Visual Basic Express Edition Calling a stored procedure in Visual Basic 2005 Express?

SteveCampbellUK

Hi Martin

It is actually in an access database and I'm unsure as to how to call it.. any suggestions

Regards

Steve






Re: Visual Basic Express Edition Calling a stored procedure in Visual Basic 2005 Express?

Martin Xie - MSFT

SteveCampbellUK wrote:

I have built a program which sends and receives data from an Access Database. Within the database I have a query which updates on of the columns in one of the tables. I am looking to call on this query when a form loads in my program. I am unsure of the text I require in order to call this query.

Hi Steve,

We cannot create Stored procedures in Access Database.

You can achieve the goal by means of executing Update SQL statement.

As far as Access database, you should use System.Data.OleDb instead of System.Data.SqlClient.

Code Block

Imports System.Data

Imports System.Data.OleDb

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim con As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\VBproject\myDB.mdb")

con.Open()

Dim cmd As New OleDbCommand("Update Table1 set Field1='EhhhEE' where Field1='EEE'", con)

cmd.ExecuteNonQuery()

con.Close()

con = Nothing

End Sub

Here is the UPDATE (T-SQL) Syntax.

Thanks,

Martin






Re: Visual Basic Express Edition Calling a stored procedure in Visual Basic 2005 Express?

Martin Xie - MSFT

When executing Update SQL statement, you can transfer values from TextBox.Text like this.

Code Block

Imports System.Data

Imports System.Data.OleDb

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\VBproject\myDB.mdb")

con.Open()

Dim cmd As New OleDbCommand("Update Table1 set Field1='" & TextBox1.Text & " ' where Field1='" & TextBox2.Text & " '", con)

cmd.ExecuteNonQuery()

con.Close()

con = Nothing

End Sub

I hope that can help you.






Re: Visual Basic Express Edition Calling a stored procedure in Visual Basic 2005 Express?

SteveCampbellUK

That worked just as I needed it too

Many Thanks