Connection Error: Cannot Generate SSPI Context
Nov 11, 2007
hi,
While trying to connect to SQLSever 2000 or SQLSever 2005 from a vb.net application ,
a user is getting the error "Cannot generate SSPI context" while other users
can still connect to the server.
and moreover, this user were able to connect before.
and if he restarts his computer, then try to connect after a lap of time, it will connect.
but if he waits and opens other applications,and then tries to open the application,it will not work.
so how to resolve this problem?
Regards,
View 3 Replies
ADVERTISEMENT
Sep 14, 2007
We are runnig Windows server 2003 R2 and SQL Server 2005 Standard on the same computer.
We have workstations that log into our domain with Windows Authentication. The software loads just fine and then later up pops the error noted above.
Any help you can give me is greatly appreciated.
Thanks
Paul
View 1 Replies
View Related
Oct 26, 2015
I have 2 servers. On one server i install sql server 2014 and its working fine, i am able to get connected with sql with domain account.
From second machine i want to connect my sql server, for that i create udl file when i give sql server address and click refresh getting following error: Connection failed cannot generate SSPI context
Firewall is off, created fire rule too, i am able to get ping first machine by ip and servername but still getting issue.
View 3 Replies
View Related
Dec 12, 2006
Dear Sir,
I tried installing sql server using mixed mode authentication but i am constantly receiving a error as follows and its getting shutting down
A connection could not be established to AICRR
Reason :Cannot generate SSPI context
Please verify sql server is running and check your sql server registeration properties(by right clicking on the AICRR node) and try again.
Please give me the resolution as soon as possible.
Regards
Arif
View 1 Replies
View Related
May 22, 2013
I regularly (every month or so) get the error "The target principle name is incorrect. Cannot generate SSPI context" when trying to remotely connect to my SQL 2012 instance. The SQL service is running using a managed service account. I understand this error can occur when the service account cannot authenticate with AD properly. Looking at the properties of the managed service account, the password for the account was automatically changed this morning - just when the error started.
View 9 Replies
View Related
Apr 20, 2007
Hi,
I am working on sql server 2005 .
when I am trying to connect to server:
following error is occuring:
TITLE: Connect to Server
------------------------------
Cannot connect to servername
------------------------------
ADDITIONAL INFORMATION:
Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)
Could any one help What might be the problem ? for this ?
Thanks,
Ramki
View 1 Replies
View Related
Jan 3, 2008
Hi-I have a program that I am developing on a laptop, then deploying it on a server.I have the prgram running passing a username and PW in the connection object, but like the idea of using windows authentication MUCH better.I just joined the domain, so I am domain/me for example.If I log into the server, and look at securities, logins and added domain/me to the logins.I then try and set up a sql connection via both visual studio, and sql server magt studio, and get the dreaded "Cannot generate SSPI context" error. Anyone else have this problem? SHOULD V.S. be using domain/me to connect? TIA dan
View 6 Replies
View Related
Sep 21, 2004
I was trying out the Building an End-to-End Application (VB.Net) exercises on Web Matrix Guided Tour and encountered the mentioned errors. Please help. Thanks.
The code is as follows :
<%@ Page Language="VB" Debug="true"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Welcome.Text = "Hello, " + User.Identity.Name
If Not Page.IsPostBack Then
' Databind the master grid on the first request only
' (viewstate will restore these values on subsequent postbacks).
MasterGrid.SelectedIndex = 0
BindMasterGrid()
BindDetailGrid()
End If
End Sub
Sub MasterGrid_Select(Sender As Object, E As EventArgs)
BindDetailGrid()
End Sub
Sub MasterGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
If MasterGrid.SelectedIndex <> -1 Then
' unset the selection, details view
MasterGrid.SelectedIndex = -1
BindDetailGrid()
End If
MasterGrid.CurrentPageIndex = e.NewPageIndex
BindMasterGrid()
End Sub
Sub BindMasterGrid()
' TODO: Update the ConnectionString and CommandText values for your application
Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"
Dim CommandText As String = "select OrderID, OrderDate, CustomerName from Orders"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet()
myCommand.Fill(ds)
MasterGrid.DataSource = ds
MasterGrid.DataBind()
End Sub
Sub BindDetailGrid()
' get the filter value from the master Grid's DataKeys collection
If MasterGrid.SelectedIndex <> -1 Then
' TODO: update the ConnectionString value for your application
Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"
' TODO: update the CommandText value for your application
Dim filterValue As String = CStr(MasterGrid.DataKeys(MasterGrid.SelectedIndex)).Replace("'", "''")
Dim CommandText As String = "select OrderDetailID, ProductName, Quantity, UnitPrice from OrderDetails where OrderID = '" & filterValue & "'"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand(CommandText, myConnection)
myConnection.Open()
DetailsGrid.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End If
DetailsGrid.DataBind()
End Sub
</script>
And this is the compilation error :
Cannot generate SSPI context.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot generate SSPI context.
Source Error:
Line 49:
Line 50: Dim ds As New DataSet()
Line 51: myCommand.Fill(ds)
Line 52:
Line 53: MasterGrid.DataSource = ds
Source File: C:WorksWebWISHDefault.aspx Line: 51
Stack Trace:
[SqlException: Cannot generate SSPI context.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +311
System.Data.SqlClient.SqlConnection.Open() +383
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
ASP.Default_aspx.BindMasterGrid() in C:WorksWebWISHDefault.aspx:51
ASP.Default_aspx.Page_Load(Object Sender, EventArgs E) in C:WorksWebWISHDefault.aspx:14
System.Web.UI.Control.OnLoad(EventArgs e) +55
System.Web.UI.Control.LoadRecursive() +27
System.Web.UI.Page.ProcessRequestMain() +731
View 1 Replies
View Related
Feb 5, 2007
All of a sudden I cannot connect to sql server from Visual Studio. Nor I can't conenct to Sql server through IIS and it gives me the same error saying "Login failed: Cannot Generate SSPI Context". Lots of my development projects got stuck because of the SSPI Issue. I know there was a network upgrade from Windows NT (users) to Active directory. I can only access the sql server from the host machine.if someone can guide or give me a quick run down on things to look for that would be wonderful.
View 2 Replies
View Related
Jul 23, 2005
HiMy company uses a Windows 2000 server with MS SQL Server 2000.We have many clients with administrative software that use the MS SQLserver, the problem is that we experimented randomly the error"Cannot Generate SSPI Context" and the only way to work with DB is logout the client.I'm looking the MS Technical sheet but there's no resolving hints thatcan help.Anyone has a similar problem ?Thanks.
View 1 Replies
View Related
Feb 6, 2007
Win Server 2003
SQL Server 2000 SP4
SharePoint Server
Win Server 2000 (Domain Controller / Active Drectory)
Clients: Win XP / Access XP
I have two clients that can access the SQL Server and one that can not. All clients can access SharePoint (SLQ Server Back end) and directories controlled by AD.
The one that cannot access the SQL Server errors: "Cannot Generate SSPI Context". I have verified:
1) date and time
2) occurances of Security.DLL (win/system32; program file/common files/AOL...; Service Pack Files/i386 (x2))
3) Not using cached credentials
we recently moved our network and did not change any settings on any of the computers. I read KB811889; those suggestions did not resolve the issue.
Please assist me in touble shooting this connection problem. Also, how does changing the SQL Server Port on the server affect SharePoints connection to its database?
View 1 Replies
View Related
Jul 27, 2006
I have a SQL Server 2000 in a specific W2K server, that belongs to a domain that is administered by a W2003 server.
There is a client that has a new computer with WXP in the same domain. It used to have an old machine with W2K pro. and with the same user it worked OK.
When accesing SQL from the client with an Office2003 application via ODBC with windows authentication, after several minutes working OK, suddenly it gets this message:
Conection error. SQL state: S1000
Cannot generate SSPI context.
In the client I have executed cliconfg.exe and created a piped name protocol, but still it won't work.
View 6 Replies
View Related
Jul 3, 2007
Howdy,
Firstly, I have read through so much information on this, that my eyes are starting to bleed and I am right at the end of my rope with it all..
This issue has only started showing since I re-installed my dev box, so my server has not changed at all..
Server is 2003 with SQL 2005.
Dev box is
Vista (Ultimate)
Visual Studio Pro 2005..
A quick but painful solution is for me to log out of my box and then back in, but this is unworkable at times... about the only difference I can think of this install from the last is that now I have also installed SQLExpress on the dev box. I have gone into services and made sure that the service does not start auto. I have connection to the server and am running as administrator on both until such time as I can sort this out, but the error still occurs.
Authentication for the dbase is both windows and credential.
Can someone please give me a stratight out solution for this.
Cheers
View 5 Replies
View Related
Mar 12, 2008
I'm signing into a sqlexpress 2005 local server using windows integration and it fails with a "Cannot generate SSPI context". After 2 years of working with this, it just started to happen after I installed a new piece of software to test. My colleague thinks it hosed our domain names. I've read all the articles and most of them have to do with a non-local server. I also read that this is caused with TCP/IP so I disabled it for sqlexpress and tried again. Then I get "An error has occurred while establishing a connection to the server. When connecting to the sql server 2005, this failure may be caused by the fact that under the default settings sql server does not allow remote connections." I know I have remote enabled so that isn't true. If it uses VIA, "Login failed for user '(null)'. Not a trusted sql connection" or something like that. Anyway, I think something is hosed in my domain and my colleague fixed it by downloading some xp tool that he can't remember where it came from. Any chance anyone else knows about this tool?
View 2 Replies
View Related
Jan 27, 2015
Our end users is getting below error, when they try to connect to our database:
"Cannot generate SSPI Context."
That is Windows based application and we have done everything like restart our DB Server , reinstall exe in users system, but still issue is same.The issue has occured from when DB Server has restarted and at the same time few users are connected. before that it was working fine. we could not find what is issue.
View 1 Replies
View Related
Jun 2, 2006
Hi,
I have a MSSQL database running in a Microsoft cluster. I was working on troubleshooting for another SAP problem when they suggested I change the setting using hte SQL Server Client Network Utility to 'named pipes' from 'tcp/ip'. Every since, despite changing it back I'm unable to start the database in the cluster.
I'm kind of a newbie at this so I'd really appreciate any help in getting this database up and running.
Here is the error message from the event log:
sqsrvres] checkODBCConnectError: sqlstate = HY000; native error = 0; message = [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
View 2 Replies
View Related
Dec 7, 2005
Please can anybody help me in fixing this error I am getting on ASP page while accessing ASP database page using an SQL server database.
View 4 Replies
View Related
Aug 1, 2007
Hi,
I have gone through so many kb articles to understanding the problem, but no luck as yet.
Here is the situation:
I have two dev servers : Server A and Server B. Both are set up in an identical fashion. Windows 2003 sp2, Sql Server 2005 sp1 Developer Edition, both are on the same domain, Both have all the three protocols enabled: Shared Memory, Tcpip, and Named Pipes in that order. I can ping both servers, and telnet using the default port. Both have only default instances. Both use the same domain user account for sql service. Both have the error in the log saying that spn could not be registered so authentication may fall back to NTLM.
Here is the issue: When I try to register Server A specifying tcp-ip connection, I am successful and I can see that the authentication is NTLM - is as expected. since the spn registration did not happen and since the sqlservice account is not a domain admin, the authentication used NTLM over tcp-ip. However, I try to register Server B specifying tcpip, I get a cannot generate sspi context error. Why did the authentication not fall back to NTLM as in Server A? From what I read in the KB articles, I asked our network admin to check up the spn, but he assures me that spn were never manually registered.
Can somebody throw more light on this issue?
Thanks
KRSE
View 1 Replies
View Related
May 24, 2007
Hi,
I am using .net 2 to connect to sql server 2005 - All works fine. When I move the sql server machine date forward (to test the application) the above message is returned.
Any ideas as to why this message is generated when I move the clock forward will be gratefully received.
Thanks
Pete Clements
View 1 Replies
View Related
May 29, 2007
We are looking at developing an SQL Server 2005 Database and I would like to use Windows Authentication rather than SQL Server Authentication to connect our client app.
In our development environment, we have two Servers, one being used as a file server and the other as an SQL Server. We have now set up a domain using the file server as the domain controller. (We had previously been set up to use a workgroup).
I have set up an active directory group called SqlDevelopers and added an active directory user called Jonathan to it.
On the SQL Sever, in management studio, I have set up a new server login which uses windows authentication called DomainSqlDevelopers. I used the GUI to verify I could see the domain and the group.
The default database is set to a test database on the server. A user in the test database is mapped to the DomainSqlDevelopers and given the Roles dbo, db_datareader, db_datawriter.
To test the log in, on the server, I logged out as administrator and in as Jonathan. I could successfully access the server through management studio using windows authentication.
However, if I log in as Jonathan on my client PC and try to access the SQL Server using management studio and windows authentication, I have problems.
The first time I try I will get a timeout error. If I try again will get either:
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Or
Cannot generate SSPI context
I can€™t determine any pattern to which of the above errors I get.
However, if I log in as administrator on my client PC, I can connect to the server using management studio and windows authentication.
Sounds like Active Directory/Domain or other Network issue (Not really my area). I would be grateful for any help.
Thanks,
Jon
View 9 Replies
View Related
Dec 3, 2005
It makes no difference if I'm working with Enterprise Manager, QueryAnalyzer, Access, self written app with OleDb or Visual studio 2003builtin DB manager.Everything is fine but aufter some time - about 30 minutes I getmessage "cannot create SSPI context"After rebooting my machine I can work for another few minutes.Configuration:NB01 is my notebook with Windows XP ProSQL01 is another Windows XP Pro machine with SQL Server 2000 desktopDC01 is the domain controllerall machins within the same domain DOMAIN01The user is a domain admin and has every rightI'v seen some postings for this problem but not one useful idea how toresolve.As it runs perfectly for some time I don't thint it is a DNS problem orsomething like this.As there is no useful error message it must be some kind of bug.Has anybody resolved such a problem?
View 3 Replies
View Related
Oct 3, 2007
Hai,
I'm having Windows XP SP2 version and I have installed SQL server 2005. Whenever I'm opening SQL server management studio in Windows Authentication Mode I get a pop message indicating that "SSPI context error message". What may be the problem and how to sort it out?
Narayanamoorthy
View 1 Replies
View Related
Oct 5, 2007
Hai,
I usually connect SQL Server 2005 using Windows authentication mode (trusted connection). I have one server to which four client machines are connected and all the clients are windows XP SP2. The SQL Server 2005 I have installed is Standard Edition. When I'm connecting I get a pop message indicating that "SSPI context error message". I have seen the Troubleshooting Article ID : 811889. I was'nt able to resolve it. What may be the problem and how to sort it out ?
Narayanamoorthy
View 3 Replies
View Related
Jul 25, 2007
Hai,
I usually connect SQL Server 2005 using Windows authentication mode (trusted connection). I have one server to which four client machines are connected and all the clients are windows XP SP2. The SQL Server 2005 I have installed is Standard Edition. When I'm connecting I get a pop message indicating that "SSPI context error message". I have seen the Troubleshooting Article ID : 811889. I was'nt able to resolve it. What may be the problem and how to sort it out ?
View 3 Replies
View Related
Dec 28, 2006
Hello together,
since over a week I've been looking to
find out the reason for two error messages I get from our users and
servers, unfortunately without success up to now.
I really hope you can help me.
We are using Win 2k servers with active directory and SQL Server 2000,
clients are all Win XP with SP2. Versions of access are 2002 &
2003. The errors are userspecific and occur in both versions. The
SQL-Servers are accessed with an adp-file in 2002-format.
We have one usergroup which is member of specially many groups. This
affects the size of their windows access token which becomes constantly
larger. In order to enable those users to still access their mailboxes
on our Exchange servers, the DWORD entry "MaxTokenSize" with the
decimal value "65535" was made to the newly created key "Parameters" of
their registry branch
"HKLMSYSTEMCurrentControlSetControlLsaKerberos ".
Since then those users can not access any of our SQL Servers using the
windows authentification. One of them gets the error
"[DBNETLIB][ConnectionRead(recv().] General network error. Check your
network documentation.", the others the error "Check connection:
Failed: Cannot generate SSPI context".
In case of resetting the registry entries (by deleting them), the one
user receives the same error message as the rest while it doesn't make
any difference to those (but at least they can't connect to their
mailboxes).
After having researched the web, I realised in one of the SQL-Servers
logfiles the entry "Invalid buffer received from client.." which lead
me to start to believe it could have to do something with the kerberos
encryption in the first place. Therefore I asked if changes to the
tokensize had been made. I applied the change with "EXEC sp_configure
'network packet size', 65535 RECONFIGURE" on our testsystem and "EXEC
sp_configure" confirms that the value is run.
Consequence: The entry in the SQL Server log doesn't appear any longer, but the users still receive their error messages.
Do you have any hints?
Your comments will be highly appreciated!
Regards,
caracol
View 10 Replies
View Related
Mar 7, 2006
Hello, I have a sql 2005 server, and I am a developer, with the database on my own machine. It alwayws works for me but after some minutes the other developer cant work in the application
He got this error
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.140]
and When I see the log event after that error, it comes with another error.
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.140]
He has IIS5 and me too.
I created a user on the domain called ASPSYS with password, then in the IIS on anonymous authentication I put that user with that password, and it works, on both machines.
and in the connection string I have.
<add key="sqlconn" value="Data Source=ESTACION15;Initial Catalog=GescomDefinitiva;Integrated Security=SSPI; Trusted_Connection=true"/>
I go to the profiler, and I see that when he browses a page, the database is accesed with user ASPSYS, but when I browse a page, the database is accesed with user SElevalencia.
Thats strange.
The only way that the other developer can work again on the project is to restart the whole machine. He has windows xp profession, I have windows 2000.
If you want me to send logs please tellme
View 20 Replies
View Related
Jul 26, 2005
Hello,
I have an XP box with SQL 2k working without any problems. I am trying to install SQL 2005 CTP April on the same machine. Installation goes fine, but when I try to connect, I am getting an error "Can not Create SSPI Context". Why am getting this error ? DOes the machine needs to be on a domain to get connected? If yes, is there a work around? Can I use mixed mode? WhereHow do I specify password for sa?
View 4 Replies
View Related
Jan 7, 2004
I am getting error once in a while. It can be while either updating/inserting/selecting a record. I may have about 100000 hits give or take a few to the database.
The VB Application identified by the event source logged this Application MODE: Thread ID: 3300 ,Logged: ------------------------------------------------------------------------------------ Error Number :-2147467259 Error Description :Invalid connection string attribute Error Source :Microsoft OLE DB Provider for SQL Server ------------------------------------------------------------------------------------ System Error Number: 0 System Error Description: System Error Source: ------------------------------------------------------------------------------------ Context/Detail Error Description : :: updateOpsRecord :: ADO Error :: Error #-2147467259 Cannot generate SSPI context (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: HY000) (NativeError: 0) No Help file available
This is intresting to see. I tried looking up in all the forums and help file but no ansewers. Help would be appreciated.
Thanks,
Karan
View 1 Replies
View Related
Mar 3, 2008
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
View 1 Replies
View Related
Mar 24, 2006
Dear all,
I am getting the message "Canot generate SSPI context",while i am working in a client system.I hope this is something related to networking.How to solve the problem.I can not connect to SQL Server once i got above mentioned message.
Please give your advice to understand and rectify the problem.
regards
Graceson
View 3 Replies
View Related
Apr 9, 2008
I am using windows impersonation in a CLR that is retrieving folder structure. In order to use impersonation I need to pass the windows credential to the SQL server so I am trying to use "integrated security=SSPI" and I also tried "integrated security=true" as well with the same result...I get the error: Login failed for user ''. The user is not associated with a trusted SQL Server connection.
The SQL Server and IIS are located in different un-trusted domain. I am however able to connect to the SQL Server using user id and password. In my web.config file:
<add name="ConnectionName1" connectionString="Data Source=IpAddress,Port#;Initial Catalog=DatabaseName;Integrated Security=true;persist security info=False; Trusted_Connection=Yes;" providerName="System.Data.SqlClient"/> à Doesn’t work
<add name="ConnectionName2" connectionString="Data Source=IpAddress,Port#;Initial Catalog=DatabaseName;uid=user;pwd=password;" providerName="System.Data.SqlClient"/> Ă WorksBased on the error message it looks like there is no user passed to the SQL server.
What am I missing? At this point I am thankful for any input…
View 5 Replies
View Related
Feb 24, 2006
Hello Guys,
I need some some clarifications on how Context connections and transactions inter operate in CLR.
The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.
SqlConnection sqlConn = new SqlConnection("context connection=true");
SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);
I guess my ambiguity comes from the fact that the Transaction is not specifically specified.
In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.
Thank you,
Lubomir
View 1 Replies
View Related
Oct 6, 2006
I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?
Private Shared Function BulkDataTransfer2(ByVal _tblName As String, ByRef _dt As DataTable, ByRef emailLog As String) As Boolean
Dim success As Boolean = False
emailLog = emailLog & System.DateTime.Now.ToString & " - bulk transfer2 - " & _tblName & vbCrLf
Dim insertStr As String = "INSERT INTO " & _tblName & "("
Dim values As String = ") Values("
Dim drow As DataRow = Nothing
Dim dCol As DataColumn = Nothing
'add the column names
For Each dCol In _dt.Columns
insertStr = insertStr & dCol.ColumnName.ToString & ", "
values = values & "@" & dCol.ColumnName.ToString & ", "
Next
'remove the last comma & form the final string
insertStr = insertStr.Substring(0, insertStr.Length - 2)
values = values.Substring(0, values.Length - 2)
insertStr = insertStr & values & ")"
Dim connStr As String = "context connection = true"
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand = Nothing
Using conn
Try
conn.Open()
For Each drow In _dt.Rows
cmd = New SqlCommand(insertStr, conn)
For Each dCol In _dt.Columns
cmd.Parameters.AddWithValue("@" & dCol.ColumnName.ToString, drow.Item(dCol.ColumnName.ToString))
Next
SqlContext.Pipe.ExecuteAndSend(cmd)
Next
success = True
Catch ex As Exception
emailLog = emailLog & System.DateTime.Now.ToString & " " & ex.ToString & vbCrLf
success = False
Finally
Try
conn.Close()
conn.Dispose()
Catch ex As Exception
success = False
End Try
End Try
End Using
Return success
End Function
View 4 Replies
View Related