Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
trying to insert data to sql server via visual basic 6.0. there are no errors but the data is not reflecting in the database. please help

What I have tried:

my code:
Dim aconnection As New ADODB.Connection
Dim aRecSet As New ADODB.Recordset

Private Sub Command1_Click()
Dim sql As String
sql = "insert into krish(pid,name,class) values ("
sql = sql & "'" & Text1.Text & "',"
sql = sql & "'" & Text2.Text & "',"
sql = sql & "'" & Text3.Text & "')"
MsgBox "inserted"
End Sub

Private Sub Form_Load()
aconnection.Open _
"provider=sqloledb;" & _
"Data source=BMSCW_CSNL_22;" & _
"intial catalog=krishna;" & _
"trusted_connection=yes;"
End Sub
Private Sub form_unload(cancel As Integer)
aconnection.Close
End Sub
Posted
Updated 28-Aug-19 0:04am
Comments
Maciej Los 28-Aug-19 5:41am    
Are you sure that SQL Server enables remote connections? See: Configure the remote access Server Configuration Option - SQL Server | Microsoft Docs[^]
MadMyche 28-Aug-19 8:17am    
Can you say SQL Injection ?

First of all, an sql server have to be able to accept remote connections. See:
Configure the remote access Server Configuration Option - SQL Server | Microsoft Docs[^]
Configure the Windows Firewall to Allow SQL Server Access - SQL Server | Microsoft Docs[^]

Second of all, you have to use ADODB.Command to insert data. For further details, please see: Execute, Requery, and Clear Methods Example (VB) - SQL Server | Microsoft Docs[^]

BTW: i'd change connections string to this one:
sCon = "Provider=SQLNCLI11;Server=yourServerName\theInstanceOfSqlServerName;Database=yourDataBaseName;Trusted_Connection=yes;"

And i'd use With ... End With statement:
VB
'create connection
Set oCon = New ADODB.Connection
With oCon
    .ConnectionString = sCon
    .Open
End With
'create command
sql = "insert into krish(pid,name,class) values ("
sql = sql & "'" & Text1.Text & "',"
sql = sql & "'" & Text2.Text & "',"
sql = sql & "'" & Text3.Text & "')"

Set oCom = New ADODB.Command
With oCom
    Set .ActiveConnection = oCon
    .CommandText = sql 
    .Execute
End With


Also, i'd suggest to use error handlers. See: On Error Statement (Visual Basic) | Microsoft Docs[^]
 
Share this answer
 
Comments
Member 14570812 28-Aug-19 6:41am    
Dim ocon As New ADODB.Connection
Dim ocom As ADODB.Command
Dim sql As String
Private Sub command1_click()
sql = "insert into krish(pid,name,class) values ("
sql = sql & "'" & Text1.Text & "',"
sql = sql & "'" & Text2.Text & "',"
sql = sql & "'" & Text3.Text & "')"
Set ocom = New ADODB.Command
With ocom
Set .ActiveConnection = ocon
.CommandText = sql
.Execute
End With
End Sub

Private Sub Form1_Load()
Set ocon = New ADODB.Connection
sCon = "Provider=SQLOLEDB.1;Server=BMSCW_CSNL_22;Database=krishna;Trusted_Connection=yes;"
With ocon
.ConnectionString = sCon
.Open
End With
End Sub
Private Sub form1_unload(cancel As Integer)
ocon.Close
End Sub
Maciej Los 28-Aug-19 7:00am    
I'd not recommend to open connection while form is loaded and close the connection while form is closed. You should move entire code to the button_click procedure, because, when you would like to click button second time, you'll see error message that connection is already open.
Member 14570812 28-Aug-19 6:43am    
is that correct there is an error in the line(Set .ActiveConnection = ocon")
CHill60 28-Aug-19 6:54am    
What is the error?
Maciej Los 28-Aug-19 6:56am    
Yes, it is.
Follow the link i provided.
Quote:
there are no errors but the data is not reflecting in the database.

You are building the insert command, but you don't execute it. that is your first problem.

VB
sql = "insert into krish(pid,name,class) values ("
sql = sql & "'" & Text1.Text & "',"
sql = sql & "'" & Text2.Text & "',"
sql = sql & "'" & Text3.Text & "')"

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900