DAO connection to SQLEXPRESS, add new record and update

Hello,

I have been working for a long time now to find a solution for using vb6 together with DAO to
1. Create new SQLserver database
2. Create new tables
3. Perform Queries
4. Update tables.

I have searched other posts as well, but so far I did not find a solution. would somebody be so kind to give me a example code for this?

I am offering 500 points to walk me through this process.

Regards,
Dennis
SchuttendAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
why DAO? ADO is more appropriate...

dim ado as adodb.connection
set ado = new adodb.connection
' for connection strings to sql express, see: http://www.connectionstrings.com/?carrier=sqlserver2005
ado.open "connection string goes here" 
'create a database (means your connection should specify "master" as database initially
'the example shown is the minimum code, leaving all the options at default.
'check the CREATE DATABASE docs/syntax for more information
ado.execute "CREATE DATABASE [name_of_database]" 
'create a table:
ado.execute "CREATE TABLE [name_of_database].dbo.[name_of_table] (col1 int identity primary key, data varchar(10), date_col datetime) " 

'perform a query:
dim cmd as adodb.command
dim rst as adodb.recordset 
set cmd = new adodb.command
set cmd.activeconnection = ado
cmd.commandtext = "SELECT * FROM [name_of_database].dbo.[name_of_table] "
set rst = cmd.execute 
while not rst.eof
  
  rst.movenext
wend
rst.close
set rst = nothing
set cmd = nothing 
'etc ...
ado.close
set ado = nothing

Open in new window

0
 
SchuttendAuthor Commented:
Hi,

First problem from this code arises from selecting the correct component, because I get an error message in line : dim ado as adodb.connection.
Which component should I add??

Regards,
Dennis
ado.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Microsoft ActiveX Data Objects 2.x  (with x being the highest possible, ie 2.8 for example)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SchuttendAuthor Commented:
I found the correct object reference and I also got the connectionstring. Still I get attached error.
Any ideas??

Regards,
Dennis
errorado.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
SchuttendAuthor Commented:
Ok next step... with connectionstring:
ado.Open "Driver={SQL  Native Client};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

Is giving me a login error. Any suggestions?

Regards,
Dennis
Login.jpg
loginsa.jpg
Management.jpg
Security.jpg
0
 
SchuttendAuthor Commented:
Please disregard last message. Connection works now...
I am only missing the code for adding data to a table.
Could somebody tell me this?

Regards,
Dennis
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
again, the adodb.command object is the easiest way to go:
dim cmd as adodb.command
set cmd = new adodb.command
set cmd.activeconnection = ado
cmd.commandtext = "INSERT INTO [name_of_table] (col1, col2) VALUES (@val1, @val2) "
cmd.parameters.Append cmd.CreateParameter("@val1", adParamInput, adVarchar, 30, 'test') 
cmd.parameters.Append cmd.CreateParameter("@val1", adParamInput, adVarchar, 50, 'value') 
cmd.execute 

Open in new window

0
 
SchuttendAuthor Commented:
Hi,

I get an error in the code. Both in the cmd.parameters.append lines. Do you have a solution for this?

regards,
Dennis
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I get an error in the code.
it might be helpful to know what error you get???

anyhow, it looks like I reversed 2 parameters
dim cmd as adodb.command
set cmd = new adodb.command
set cmd.activeconnection = ado
cmd.commandtext = "INSERT INTO [name_of_table] (col1, col2) VALUES (@val1, @val2) "
cmd.parameters.Append cmd.CreateParameter("@val1", adVarchar, adParamInput, 30, 'test') 
cmd.parameters.Append cmd.CreateParameter("@val1", adVarchar, adParamInput, 50, 'value') 
cmd.execute 

Open in new window

0
 
SchuttendAuthor Commented:
As you can see, this is not the solution. Or do I need some additional components to use this code?

Regards,
Dennis
errorcmd.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, i wrote 'test', and it should be "test" ... it's the value you want to pass as argument for the parameter object...
0
 
SchuttendAuthor Commented:
Hi,

Thanks. I am splitting up the code for learning purposes.Please see code snippet which gives an error.
Also the 'errorpic' is attached about column name being incorrect.
Into the command text line it's not clear to me what the difference is between the first val1 and the second one.
INSERT INTO [Table1] (val1,val2) VALUEs (val1,val2) " 

'Create Table
Private Sub Command3_Click()
Dim ado As ADODB.Connection
Set ado = New ADODB.Connection
Dim name As String
name = "woensdag"
 
ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
ado.Execute "CREATE TABLE [monday].dbo.[Table1] (col1 int identity primary key, val1 varchar(10), val2 varchar(10)) "
ado.Close
Set ado = Nothing
 
End Sub
'Add Data to Table
Private Sub Command4_Click()
Dim ado As ADODB.Connection
Set ado = New ADODB.Connection
 
ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
 
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.activeconnection = ado
 
 
cmd.commandtext = "INSERT INTO [Table1] (val1, val2) VALUES (val1, val2) "
cmd.Parameters.Append cmd.CreateParameter("val1", adVarChar, adParamInput, 30, "test")
cmd.Parameters.Append cmd.CreateParameter("val2", adVarChar, adParamInput, 50, "Value")
cmd.Execute
ado.Close

Open in new window

erroradd.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>cmd.commandtext = "INSERT INTO [Table1] (val1, val2) VALUES (val1, val2) "
is wrong, it must be:

cmd.commandtext = "INSERT INTO [Table1] (val1, val2) VALUES (@val1, @val2) "

otherwise, the parameters will not be placed correctly

0
 
SchuttendAuthor Commented:
Hi,

Just that I understand correctly. Strings "Test" is placed in column1 (val1) and"Value" is placed in column2 (val2).
however I get a error about the @val1 being not declared, please see attached pic.

'Add Data to Table
Private Sub Command4_Click()
Dim ado As ADODB.Connection
Set ado = New ADODB.Connection
 
ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
 
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.activeconnection = ado
 
 
cmd.commandtext = "INSERT INTO [Table1] (col1, col2) VALUES (@val1, @val2) "
cmd.Parameters.Append cmd.CreateParameter("@val1", adVarChar, adParamInput, 30, "test")
cmd.Parameters.Append cmd.CreateParameter("@val2", adVarChar, adParamInput, 50, "Value")
cmd.Execute
ado.Close
 
 
End Sub

Open in new window

errorval.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
interesting...

'Add Data to Table
Private Sub Command4_Click()
Dim ado As ADODB.Connection
Set ado = New ADODB.Connection
 
ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
 
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.activeconnection = ado
 
 
cmd.commandtext = "INSERT INTO [Table1] (col1, col2) VALUES (@val1, @val2) "
cmd.NamedParameters = true
cmd.Parameters.Append cmd.CreateParameter("@val1", adVarChar, adParamInput, 30, "test")
cmd.Parameters.Append cmd.CreateParameter("@val2", adVarChar, adParamInput, 50, "Value")
cmd.Execute
ado.Close
 
 
End Sub

Open in new window

0
 
SchuttendAuthor Commented:
Interesting?? What do you mean by that?

Regards,
Dennis
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I mean that normally, it should work without the line I added (cmd.NamedParameters = true)
0
 
SchuttendAuthor Commented:
unfortunetely still the same error....
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please try this:
ado.Open "Provider=SQLNCLI;Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

Open in new window

0
 
SchuttendAuthor Commented:
Does not work. Provider cannot be found. I am using SQLServer 2008 express....


provider.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
:-(
0
 
SchuttendAuthor Commented:
With the code I can now
1. Create database
2. Delete database
3. Create a table

The only thing I am missing is to add data into the table...
'Create Database
 
Private Sub Command1_Click()
Dim ado As ADODB.Connection
Set ado = New ADODB.Connection
' for connection strings to sql express, see: http://www.connectionstrings.com/?carrier=sqlserver2005
ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
'ado.Open "Driver={SQL Native Client};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
'create a database (means your connection should specify "master" as database initially
'the example shown is the minimum code, leaving all the options at default.
'check the CREATE DATABASE docs/syntax for more information
ado.Execute "CREATE DATABASE [monday]"
'create a table:
'ado.Execute "CREATE TABLE [monday].dbo.[Table1] (col1 int identity primary key, data varchar(10), date_col datetime) "
 
'perform a query:
'Dim cmd As ADODB.Command
'Dim rst As ADODB.Recordset
'Set cmd = New ADODB.Command
'Set cmd.activeconnection = ado
'cmd.commandtext = "SELECT * FROM [monday].dbo.[Table1] "
'Set rst = cmd.Execute
'While Not rst.EOF
  
 ' rst.movenext
'Wend
'rst.Close
'Set rst = Nothing
'Set cmd = Nothing
'etc ...
ado.Close
Set ado = Nothing
End Sub
' Delete Database
Private Sub Command2_Click()
Dim ado As ADODB.Connection
Set ado = New ADODB.Connection
ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
ado.Execute "DROP DATABASE [monday]"
ado.Close
Set ado = Nothing
 
 
End Sub
'Create Table
Private Sub Command3_Click()
Dim ado As ADODB.Connection
Set ado = New ADODB.Connection
Dim name As String
name = "woensdag"
 
ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
ado.Execute "CREATE TABLE [monday].dbo.[Table1] (col1 int identity primary key, val1 varchar(10), val2 varchar(10)) "
ado.Close
Set ado = Nothing
 
End Sub
'Add Data to Table
Private Sub Command4_Click()
Dim ado As ADODB.Connection
Set ado = New ADODB.Connection
 
ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
'ado.Open "Provider=SQLNCLI;Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.activeconnection = ado
 
 
cmd.commandtext = "INSERT INTO [Table1] (col1, col2) VALUES (@val1, @val2) "
cmd.NamedParameters = True
cmd.Parameters.Append cmd.CreateParameter("@val1", adVarChar, adParamInput, 30, "test")
cmd.Parameters.Append cmd.CreateParameter("@val2", adVarChar, adParamInput, 50, "Value")
cmd.Execute
ado.Close
 
 
End Sub

Open in new window

0
 
SchuttendAuthor Commented:
thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.