Application Role Losing Connection?
Sep 6, 2007
Hi,
I'm testing the use of application roles for security. The customer I work for has still a lot of ASP intranet applications running. We're migrating the databases to a SQL Server 2005 server.
I've changed the connection string to a user without any permissions but to log on. After that I use an application role for permission to select different tables and to execute Stored Procedures.
The first queries do execute but after that I get "Permission denied", like I haven't got the application role anymore.
Any ideas?
Adrian
View 6 Replies
ADVERTISEMENT
Apr 2, 2007
I have a VB6 application that has MSDE as its current backend. In
testing moving to SLSQLExpress I've noticed, since this application is run on laptops, that if I disconnect the network cable while I'm in the VB6 app I lose "connection" to the database. If I end task on the app and restart it it works fine. If I reconnect the network cable while the app is running and then disconnect it the app runs fine. Also this happens when I'm connected to a wireless network and then disconnect and disable that connection.
In my template.ini file I have DISABLENETWORKPROTOCOLS=0. Is there a setting I'm missing? I can't seem to find anything else to set in the ini file.
View 4 Replies
View Related
May 18, 2007
Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
thanks
View 2 Replies
View Related
Jan 23, 2008
Hi all, I am trying to connect to the database using application role. But gives an error 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)
for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
Dim cmd
As SqlCommand
Dim
param As SqlParameter
Dim
cookie As Byte()
Dim cn As New
SqlConnection(connstring)
If
(cn.State = ConnectionState.Closed) Then
cn.Open()
End If Please help..
Thanking you, Nirmala
View 2 Replies
View Related
Apr 17, 2008
Hi - I am attempting to run an SSIS package through a SQL Agent job step. The package is fairly basic - I run some Execute SQL tasks, build an ADO recordset, enter into a For-Each loop, and perform some table updates based on the ADO recordset. So far so good. Approximately 1/3 of the records in my For-Each loop are processed, then I get a "Failed to acquire connection error". All I am doing for each iteration of the For-Each loop is updating one record in a table based upon the current value that is being processed from the ADO recordset. I am running the job on a 64-bit box, version 9.00.3050.00. I have also run this on two other 64-bit boxes (versions 9.00.1399.06 and 9.00.1406.00) and had the same issue. The owner of the Agent job is a sysadmin on the box. I don't understand why some items would be processed, then the connection drops mid-way through processing. What's even odder is the connection is dropped at the same point every time I run. The connection that I have defined in my package (I only have one connection defined in my package) is Provider type "Native OLEDBSQL Native Client", and I am using Windows authentication. The version of Visual Studio that I have developed this package in is 8.0.50727.42. Please help.
View 7 Replies
View Related
Mar 5, 2008
Hi, I'm working on a project that connects to a database on every view of a site. It works fine for a few hours but eventually it just stops connecting to the database. There's nothing in the application logs to suggest where to look, it seems like the connections are just dying.
I am creating the connection in a pretty normal manner:SqlCommand myAwesomeCommand = new SqlCommand();myAwesomeCommand.Connection = new SqlConnection(myAwesomeConnectionString);myAwesomeCommand.CommandText = myAwesomeSQL;myAwesomeCommand.CommandType = CommandType.Text; // Otherwise known as awesomeText
myAwesomeCommand.ExecuteScalar();
I also use a SqlDataAdapter and fill it for some of my stuff for grabbing more complex data, but all happy System.Data.SqlClient stuff.
But yeah, the thing seems to just give up after a few hours. Everything I have read about connection pooling in ADO.Net says that the connections won't be reused. And yes, I believe I am closing all of my connections after I use them. The site is getting, oh, 7500 hits an hour maybe? Somewhere around that, maybe more maybe a little less.
Anyone have any ideas?
View 2 Replies
View Related
Sep 11, 2007
On my SSIS package I have 2 connection managers, both are connecting to an Oracle db. I enter in the ID and Password then click 'test connection' I'm able to connect to the database fine. I then go to a data flow of one of my control flow task. Open up my OleDb Source and click preview. The SQL query executes with no issues. I can see the data from my connection manager source.
I then go and run the package and I get this error message:
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OracleConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I've been racking my brain on this for a few days and still no luck on getting this to work.
View 8 Replies
View Related
Aug 18, 2004
I am confused . What is considered an application and how SQL would know ?
If I have a web site accessing SQL VIA IIS will SQL Server treat it as an application ? How about MS Excel ?
Also , If I was to use the application of Power builder , using app role , how do control which user can use the app ?
Thanks
View 1 Replies
View Related
Mar 1, 2001
Hi all,
This one is a real X FIle, just without Mulder, Scully or the Lone Gunmen!
I have a database, to which access must be restricted via a sole application. So, I have to use an application role. I go in the database and run these statements to add and activate the roles, respectively;
Exec sp_addapprole 'Sirius', 'password'
(The system confirms the role is created.)
Exec sp_setapprole 'Sirius', 'password' 'odbc'
(The system confirms the role is activated.)
Right, now I should not be able to connect using anything but this role, agreed? But here's where things go wrong. I can then successfully connect from another computer by using MS query from Excel, from a login that is not even a member of the Public Role!
I tried again, started and stopped the Server/DTS/Agent services and dropped the old role after each successful login before recreating it. I've checked my syntax exhaustively. I must be doing something wrong, or overlooking something, otherwise MS has a major security problem! (Just hope the Cancer Man doesn't find out!!)
Thanks in advance everyone,
Jaishel.
View 1 Replies
View Related
Sep 7, 2006
Hi dear reader
I made an application that uses a Sql Server 2005 Express DataBase.
In the database I made a application role.
When the user logs into my application I run this procedure:
If Not sqlConnectionCR Is Nothing Then
If Not sqlConnectionCR.State = ConnectionState.Open Then
sqlConnectionCR.Open()
SqlConnection.ClearAllPools()
ConsultasSqlCommand = New SqlCommand
ConsultasSqlCommand.CommandType = CommandType.Text
ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"
ConsultasSqlCommand.Connection = sqlConnectionCR
ConsultasSqlCommand.ExecuteNonQuery()
End If
Else....
I understand that this procedure connects to my sqlserver database as my application role
Ok, so far no problems in reading and manipulating data.
The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....
I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role).
So, my cuestion is: How can I tell Report Server to use the application role to display reports?
Thank you for your time and help.
Giber
View 5 Replies
View Related
Sep 4, 2007
We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data.
I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database?
Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator?
Thanks
View 1 Replies
View Related
Oct 6, 1999
I know how to create an Application role in SQL server 7. Now how do I connect to the database from VB using that Application Role? I can't find anything about this topic anywhere. Is this the purpose of an Application role or am I way off?
Thanks for the help
Steven Abt
StevenA@grsgroup.com
View 2 Replies
View Related
Nov 23, 2005
I want to add bulkadmin permission to my applicatio role. Is it aposible.My windows account havo only public permission on database.I'm using application roleEXEC sp_approlepassword 'MyRole', 'password';Therefore I want to BULK some data with BULK INSERT command.Error is:The current user is not the database or object owner of table'tablename'. Cannot perform SET operation.Thanks in advance.
View 1 Replies
View Related
Oct 28, 2005
In SQL Server 2005, you must have the VIEW SERVER STATE permission in order to access sys.syslockinfo (http://msdn2.microsoft.com/en-us/library/ms189497).
View 7 Replies
View Related
Feb 4, 2007
Hello,
Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?
Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005
Thanks
John
View 5 Replies
View Related
Mar 22, 2007
Hi,
I want my application to create database and I do the following things:
1)Create application role
2)Grant create database to application role
3)Activate application role
4)Create database
and I get the answer:
CREATE DATABASE permission denied in database 'master'.
View 1 Replies
View Related
Oct 16, 2006
I've created a database in SQL Express and I have a Windows form attempting to connect to it through SQL Authentication. Connection string:
private string connString = @"Data Source=.sqlexpress;Initial Catalog=SQLTestDatabase;User ID=SearchAppRole; Password=password;";
The role I have added to the database is an Application Role. It has been added to the Database permissions with Grant checked for "Select" and "Authenticate".
If I test this with query analyzer, it returns expected results (if I remove Grant from 'Select', it fails)
sp_setapprole 'SearchAppRole', 'password'
select * from recipe
If I edit my connection string (for testing purposes) to use the sa account, the application can connect and run the Select statement:
private string connString = @"Data Source=.sqlexpress;Initial Catalog=SQLTestDatabase;User ID=sa; Password=sa_password;";
However, I cannot get the application to successfully logon and run the select statement when using the user id and password of the Application Role. I get error:
System.Data.SqlClient.SqlException: Login failed for user 'SearchAppRole'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj
I can't find much information on Application Role...I just want one basic permission for the application as a whole. Any help is appreciated. Thanks.
View 3 Replies
View Related
Jan 16, 2007
I am attempting to use dbmail from an application that logs in to my database using an application role. Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it: I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights.
Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail. But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission." When I run my spSendEmail stored procedure from the calling database, I get the same error.
View 1 Replies
View Related
Oct 8, 2007
Hi all.
I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005.
My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables:
1) Table A
2) Table B
3) Table C
The USER login is a restricted user which has only SELECT permission to Table A, B and C.
I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime".
Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A.
I have tried for almost 2 days but am still clueless. Any ideas?
Thank you so much.
View 7 Replies
View Related
Feb 17, 2006
Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX).
Developing a desktop application which calls a local instance of ".sqlexpress".
This app needs to set data base options and add/del various table columns.
When activating the application role, I get the following message:
HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level.
Anyone know what this message means?
I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious.
Thanks
Gary
View 10 Replies
View Related
May 10, 2006
Hi every body
I'm developing a web application and i like to use the sql server 2005 role management features istead of developing a role management package in my program, I can do it on my tables and othe database items but I have no idea about using database access rights in my web pages to permit some one viewing or updating a web form... Is there any system table or system stored procedure showing access rights in my data base? or is there another idea to do this??
by Thanks
Javaneh
View 3 Replies
View Related
Aug 23, 2007
Hi everybody.
I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1
I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
SELECT USER_NAME()
I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:
The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?
The table in DB2 has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.
Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
Vania
View 5 Replies
View Related
Jul 24, 2006
Hi All,
Is there any limitation for setting password to an approle (like it should be 8 character long, should not start with numbers) ?
If so, what are those limitations ?
Does the same is applicable for sp_setapprole which uses the same password ?
please confirm, for both SQL Server 2000 and 2005 versions.
thanks in advance,
Regards,
Kailai
View 1 Replies
View Related
Jun 25, 2007
I am very frustrated. Everything works on the local host but when I upload to server I can login to the admin role I created, but when I try to access pages that have role priveleges I get the following error:
The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.
The ASPNETDB.MDF database was uploaded using the Database Publishing Wizard.
Please help!
View 1 Replies
View Related
Jul 12, 2006
Hi
I have two machines, the one has Windows 2003 server and the other has Windows Xp Pro on.
The 2003 one has SQL Standard Edition 2000 on where as the XP Pro has MSDE 2000.
I'm trying to run a windows based app from the 2003 server and it connects to a database on the XP Pro PC.
When running the application i keep getting the following exception:
"A Transport-Level error has occured when sending the request to the server (Provider: Named Pipes Provider, error: 0 - The specified network name is no longer available)
After using Preformance monitor on the XP machine it seems that when the app starts and the user logins in it make a SQL connection but then the connection is lost then the exception pops up. then i click continue and try it again then it works, then a couple of secs when using the system it pops up again. The Performance Monitor Test shows that the SQL connection keep being lost. The network is fine. its has something to do with SQL and Named Pipes and stuff.
When running the app on the Xp machine and the Connection is made to the 2003 machine there is no problem.
There are other connection made to the Windows XP Machine but i doubt that it affects the app.
The Windows firewall is off as well.
Please can anyone help.
View 4 Replies
View Related
Aug 11, 2005
Dear members,I'am thinking about the "best" way to connect to my sql-server viaADO.Net (if there is a best way).My application uses the sql-server very intensively. At the momentevery database-operation opens a connection, executes and then closesthe connection.Another way would be to use only a single connection to the database,which is opened when the application starts.What do you think is the better way to get a high performing sqlserver: using one single application connection vs. using oneconnection for every operation?Cheers, Sebastian
View 2 Replies
View Related
May 15, 2008
hi
i m making a win application
for which i have used sql server 2005 .i faced a very big problem that i have made the database but i cannot able to connect the database with winn application .for this purpose i ned coding.so please help me.i need your help urgent.
sheraz
View 4 Replies
View Related
Jun 28, 2007
Hey anyone,
I got a problem to check a connection whether it is established or not from my C# Application to SQL Server.
I am developing my application which can run in 2 modes:
1. Online: There must be a connection to SQL Server to get new data updates.
2. Offline: I dont need a connection to SQL Server. I set a timer to tick every 5 seconds and it will check the connection with SQL Server and my problem here: (this is my code)1 public static bool checkConnection()
2 {
3 SqlConnection conn = new SqlConnection("mydatasource");
4 try
5 {
6 conn.Open();
7 return true;
8 }
9 catch (Exception ex) { return false; }
10 }
Yes, it works but my application seems to wait about >3 seconds to get the return "False"; I tried to add "Connect TimeOut=1" in my Connection String, but the problem is still there.
I dont know whether there is a faster way to check the connection to SQL server which is established or not.
Thank you very much,
Joesy
View 5 Replies
View Related
Sep 27, 2007
In my asp.net application , I have to open/close SQL databse connection many time, and I dont want to declare Following variable everytime I open SQL database connection
Dim cntec As New SqlClient.SqlConnection(cnStrtec)
Dim datec As New SqlClient.SqlDataAdapter
Dim dstec As New DataSet
Dim cmdtec As New SqlClient.SqlCommand
so do I decalre above variable in module.vb as Public variable or is there any other way to do this.
Please some one give me idea how to do this
thank you
maxmax
View 3 Replies
View Related
Sep 27, 2007
In my asp.net application , I have to open/close SQL databse connection many time, and I dont want to declare Following variable everytime I open SQL database connection
Dim cntec As New SqlClient.SqlConnection(cnStrtec)
Dim datec As New SqlClient.SqlDataAdapter
Dim dstec As New DataSet
Dim cmdtec As New SqlClient.SqlCommand
so do I decalre above variable in module.vb as Public variable or is there any other way to do this.
Please some one give me idea how to do this
Please give me some examples
thank you
maxmax
View 1 Replies
View Related
Nov 12, 2007
Hi there,
Has anyone ever encountered problems making .Net applications connect to a SQL database on SQL 2000 with SP3a?
It's running on a virtual machine with a Windows Server 2000 Std with SP4, the .Net application is working with .Net Framework 1.1.4322.573. For some reason it can't connect to the SQL database using the specified username and password in the web.config file even though the user exists in the SQL database. I've tried using the SA password but this still doesn't work. I can logon to SQL Query Analyzer using the SA password but not using the other username.
The .Net application does work on other systems of mine all of which can run on Windows Server 2000 and 2003, I have compared them but can't find any differences.
Any ideas anybody?
Thanks,
Chris
View 1 Replies
View Related
Jun 11, 2008
I have application that is connecting to SQL Server and runs bunch of stored procedures, but I don't think that am able to connect to my database server. When it hits the line to execute the stored procedure it comes up with syntax error saying the syntax error is at the stored procedure. I know how to set up the connection string with web app from the web config, but never done it on an app. Here is the connection string pointing to my server which I pass to my SqlConnection object: "Data Source=ServerXXXX;Initial Catalog=DB;Integrated Security=True; User ID=xxxx; Password=xxxx;" And that is the code that is using the above connection string: Public Sub MigrateNFSData(ByVal calcTbl As DataTable, ByVal strDBConnection As String) Dim sqlServerConn As New SqlConnection(strDBConnection) 'Define stored procedures Dim command As New SqlCommand Dim getAccID As New SqlCommand("GetAccountID", sqlServerConn) Dim getActionID As New SqlCommand("GetActionID", sqlServerConn) Dim getExchangeID As New SqlCommand("GetExchangeID", sqlServerConn) 'Dim getParrentAccID As New SqlCommand("GetParentAccID", sqlServerConn) Dim getStatusID As New SqlCommand("GetStatusID", sqlServerConn) Dim getTraderID As New SqlCommand("GetTraderID", sqlServerConn) Dim getGroupID As New SqlCommand("GetGroupID", sqlServerConn) 'Define insert records stored procedures Dim insertAcc As New SqlCommand("InsertAccount", sqlServerConn) Dim insertAction As New SqlCommand("InsertAction", sqlServerConn) Dim insertExchange As New SqlCommand("InsertExchange", sqlServerConn) Dim insertGroup As New SqlCommand("InsertGroup", sqlServerConn) Dim insertStatus As New SqlCommand("InsertStatus", sqlServerConn) Dim insertTrader As New SqlCommand("InsertTrader", sqlServerConn) Try sqlServerConn.Open() Catch ex As Exception 'Exception message goes here End Try 'Set parameters to helper Get Stored Procedures to retreive Id's getAccID.Parameters.Add("@AccName", SqlDbType.NVarChar) getAccID.CommandType = CommandType.StoredProcedure getActionID.Parameters.Add("@ActionName", SqlDbType.NVarChar) getActionID.CommandType = CommandType.StoredProcedure getExchangeID.Parameters.Add("@ExchName", SqlDbType.NVarChar) getExchangeID.CommandType = CommandType.StoredProcedure 'getParrentAccID.Parameters.Add("@ParentName", SqlDbType.NVarChar) 'getParrentAccID.CommandType = CommandType.StoredProcedure getStatusID.Parameters.Add("@StatusName", SqlDbType.NVarChar) getStatusID.CommandType = CommandType.StoredProcedure getTraderID.Parameters.Add("@TraderName", SqlDbType.NVarChar) getTraderID.CommandType = CommandType.StoredProcedure getGroupID.Parameters.Add("@GroupName", SqlDbType.NVarChar) getGroupID.CommandType = CommandType.StoredProcedure command = New SqlCommand("InsertTradeTransaction", sqlServerConn) command.CommandType = CommandType.StoredProcedure 'Set Parameters for Insert stored procedures insertAcc.Parameters.Add("@Account", SqlDbType.Text) insertAction.Parameters.Add("@ActionName", SqlDbType.Text) insertExchange.Parameters.Add("@Exchange", SqlDbType.Text) insertGroup.Parameters.Add("@Group", SqlDbType.Text) insertGroup.Parameters.Add("@ACCID", SqlDbType.Int) insertStatus.Parameters.Add("@StatusName", SqlDbType.Text) insertTrader.Parameters.Add("@Group", SqlDbType.UniqueIdentifier) insertTrader.Parameters.Add("@IP", SqlDbType.Text) insertTrader.Parameters.Add("@TraderName", SqlDbType.Text) 'Adding stored Get Stored Procedure's parameters----------------------- command.Parameters.Add("@OrderNum", SqlDbType.Text) command.Parameters.Add("@ACC_ID", SqlDbType.Int) command.Parameters.Add("@Group_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Trader_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Exch_ID", SqlDbType.Int) command.Parameters.Add("@Date", SqlDbType.DateTime) command.Parameters.Add("@Time", SqlDbType.DateTime) command.Parameters.Add("@ActionID", SqlDbType.Int) command.Parameters.Add("@StatusID", SqlDbType.Int) command.Parameters.Add("@TimeSent", SqlDbType.DateTime) command.Parameters.Add("@Qty", SqlDbType.Int) command.Parameters.Add("@Product", SqlDbType.Text) command.Parameters.Add("@MMYYY", SqlDbType.Text) command.Parameters.Add("@ExchOrderID", SqlDbType.Text) command.Parameters.Add("@TimeTicks", SqlDbType.Int) command.Parameters.Add("@W2G", SqlDbType.Int) command.Parameters.Add("@W2Exch", SqlDbType.Int) command.Parameters.Add("@G2ExchDerived", SqlDbType.Int) command.Parameters.Add("@ExchDate", SqlDbType.DateTime) 'command.Parameters.Add("@ParentID", SqlDbType.Int) 'Paremeters Defenition-------------------------------------- 'Write table with computed NFS data to sql server DB For Each dr As DataRow In calcTbl.Rows command.Parameters("@OrderNum").Value = dr.Item("OrderNo").ToString() getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() If getAccID.ExecuteScalar() = 0 Then insertAcc.Parameters("@Account").Value = dr.Item("Acct").ToString() insertAcc.ExecuteNonQuery() //THIS IS WHERE IT FAILS and pops up with the message that there is syntax error at this procedure getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() command.Parameters("@AccName").Value = getAccID.ExecuteScalar() Else command.Parameters("@ACC_ID").Value = getAccID.ExecuteScalar() End If getGroupID.Parameters("@GroupName").Value = dr.Item("GroupID").ToString() Try command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try getTraderID.Parameters("@TraderName").Value = dr.Item("TrdID").ToString() Try command.Parameters("@Trader_ID").Value = getTraderID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try getExchangeID.Parameters("@ExchName").Value = dr.Item("Exch").ToString() Try command.Parameters("@Exch_ID").Value = getExchangeID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try getActionID.Parameters("@ActionName").Value = dr.Item("Action").ToString() Try command.Parameters("@ActionID").Value = getActionID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try getStatusID.Parameters("@StatusName").Value = dr.Item("Status").ToString() Try command.Parameters("@StatusID").Value = getStatusID.ExecuteScalar() Catch ex As Exception 'Insert new Account if given account not being found from get stored procedure End Try 'getParrentAccID.Parameters("@ParentName").Value = "" 'Try 'command.Parameters("@ParentID").Value = getParrentAccID.ExecuteScalar() ' Catch ex As Exception 'End Try command.Parameters("@Date").Value = DateTime.Parse(dr.Item("Exch Date").ToString()) command.Parameters("@Time").Value = DateTime.Parse(dr.Item("Time").ToString()) command.Parameters("@TimeSent").Value = DateTime.Parse(dr.Item("Time Sent").ToString()) If (dr.Item("Qty").Equals(System.DBNull.Value)) Then command.Parameters("@Qty").Value = System.DBNull.Value Else command.Parameters("@Qty").Value = Int32.Parse(dr.Item("Qty").ToString()) End If command.Parameters("@Product").Value = dr.Item("Product").ToString() command.Parameters("@MMYYY").Value = dr.Item("MMYYYY").ToString() command.Parameters("@ExchOrderID").Value = dr.Item("ExchOrderID").ToString() If (dr.Item("TimeTicks").Equals(System.DBNull.Value)) Then command.Parameters("@TimeTicks").Value = System.DBNull.Value Else command.Parameters("@TimeTicks").Value = Int32.Parse(dr.Item("TimeTicks").ToString()) End If command.Parameters("@ExchDate").Value = Date.Parse(dr.Item("Exch Date").ToString()) command.Parameters("@W2G").Value = 2 command.Parameters("@W2Exch").Value = 2 command.Parameters("@G2ExchDerived").Value = 2 command.ExecuteNonQuery() sqlServerConn.Close() Next End Sub Thank you guys as always being helpful.
View 4 Replies
View Related
Nov 24, 2003
Hello,
I was wondering if someone could help me answer this question.
If I had a site like Yahoo, that constantly had users accessing it. Would it be a good or bad idea to automatically open a database connection to SQL Server on Application Start? What would you recommend? Would opening and closing the DB connection on every page save alot of overhead and bottleneck? Thank You.
View 2 Replies
View Related