UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert Into SQL Server, Any Version    
 
   
access2009eg
post Aug 14 2019, 04:06 PM
Post#1



Posts: 1,098
Joined: 19-February 08



Hello Friends


I use this code to insert or update record into SQL but not error appear and no record inserted or updated
what is the reason

Thank you

CODE
Private Sub Command0_Click()
Dim adoConn As New ADODB.Connection
'Trusted_Connection = Yes; if you use windows login if you want remove it add ' UID = addd ' Pwd= sdddsd
adoConn.ConnectionString = "Provider=SQLNCLI10; Server=DESKTOP-SEV77GF\SQLSERVER_ZEZO;Database =xx;Trusted_Connection=yes;"
adoConn.Open
If adoConn.State = 1 Then
Dim com As New ADODB.Command
Dim recordAffecteds As Long
com.ActiveConnection = adoConn
com.CommandType = adCmdText
'com.CommandText = "insert into Contacts (ContactName)Values ('abca')"
com.CommandText = "update Contacts set ContactName='abc'"
com.Execute
MsgBox recordAffecteds
adoConn.Close
End If

End Sub

This post has been edited by access2009eg: Aug 14 2019, 04:06 PM

--------------------
I would like to Thank you for your help
Go to the top of the page
 
theDBguy
post Aug 14 2019, 04:08 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,028
Joined: 19-June 07
From: SunnySandyEggo


Hi. I could be off but you seem to have commented the insert part and only doing the update one.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
access2009eg
post Aug 14 2019, 04:32 PM
Post#3



Posts: 1,098
Joined: 19-February 08



I remove the commenting but also not affect the SQL server
update
and
insert

--------------------
I would like to Thank you for your help
Go to the top of the page
 
cheekybuddha
post Aug 14 2019, 05:24 PM
Post#4


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Hi,

Try it like this:
CODE
  Dim adoConn As New ADODB.Connection
  Dim strSQL As String
  Dim recordAffecteds As Long

  strSQL = "insert into Contacts (ContactName) Values ('abca')"
  With adoConn
    .ConnectionString = "Provider=SQLNCLI10; Server=DESKTOP-SEV77GF\SQLSERVER_ZEZO;Database =xx;Trusted_Connection=yes;"
    .Open
    If .State = 1 Then
      Call .Execute(strSQL, recordAffecteds)
      MsgBox recordAffecteds
    End If
    .Close
  End With
  Set adoConn = Nothing


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 05:38 PM
Post#5


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


Try removing the spaces from your connection string:

change this:
adoConn.ConnectionString = "Provider=SQLNCLI10; Server=DESKTOP-SEV77GF\SQLSERVER_ZEZO;Database =xx;Trusted_Connection=yes;"

to:

adoConn.ConnectionString = "Provider=SQLNCLI10;Server=DESKTOP-SEV77GF\SQLSERVER_ZEZO;Database=xx;Trusted_Connection=yes;"

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
access2009eg
post Aug 14 2019, 05:43 PM
Post#6



Posts: 1,098
Joined: 19-February 08



cheekybuddha

the same result

I think the problem in SQL server

because I refresh the SQL, disconnect, connect

no record add to SQL

from access connection

maybe the port or the antivirus



--------------------
I would like to Thank you for your help
Go to the top of the page
 
access2009eg
post Aug 14 2019, 06:04 PM
Post#7



Posts: 1,098
Joined: 19-February 08



GroverParkGeorge

hat_tip.gif
hat_tip.gif
hat_tip.gif
hat_tip.gif
hat_tip.gif
hat_tip.gif
thumbup.gif

--------------------
I would like to Thank you for your help
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 06:07 PM
Post#8


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


Also ensure the database name is correct, as well as the server name.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2019, 06:47 AM
Post#9


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


I take it that you solved the problem?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 03:05 AM