In this article we are going to learn how to insert records into a sql database. The function we will create will be setup to insert into a non Data Collection Table.
The first thing we will do is take the previous script , keep the Metadata , but modify the routing section to look like this,
Routing(Web2)
Dim oInfo
Set oInfo = CreateObject("Scripting.Dictionary")
oInfo.Add("Message","")
oInfo.Add("ID",-1)
Name.Ask()
' TODO
ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
ConnectionInfo.Show()
End Routing
Ok so the idea in this example is to allow the user to enter their name into the survey and we will then take that data and place it in a separate SQL Table. The SQL table will need to be created in the first place before this script will work. Our table was called Names and it was stored in the participants database. It had two fields in it , one was a ID, which was the unique key and the other was a text field called Name. The SQL Syntax generated was as follows
USE [Participants] GO /****** Object: Table [dbo].[Names] Script Date: 02/05/2010 09:12:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Names]( [ID] [int] NOT NULL, [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
So back to Data Collection, Type the following where you see the line ‘ TODO, and replace it with the following code.
' Lets Insert the Record
Set oInfo = InsertDataRecord("MyServer","Participants", _
"Names","ID,UserName", _
CTEXT(iom.Info.Serial) + ",'" + Name.Response.Value + "'", _
"MyAdmin","ABC",oInfo)
This is very similar code as before , we have a new function called “InertDataRecord” , that we pass in the servername, the database that we want to update followed by the Table name that we want to insert into. The next property we have is the names of the variables that we want to insert into. In our example we have two fields ID and Username so we create a string and separate the two field names with a coma like so
"ID,UserName"
After the field names we need to pass in the values that we want stored in them, and in our case it is going to be the respondent.serial value and the value that the user entered into the text question. So to do this again we have to pass in a string and because the serial number is a numeric we will have to convert it to a text value. SO how do we get the respondent.serial number , well to do this we use the IOM.info.serial property and the convert it to text with the CTEXT function.
CTEXT(iom.Info.Serial)
As we are passing in two values , like the field names , we need to separate the values with a comma. The next value is the value entered into the name question. As this is a text question we do not need to convert it , but we do need to surround it with a single speech mark.
CTEXT(iom.Info.Serial) + ",'" + Name.Response.Value + "'"
What this all means is , if the name we entered was test we would end up with a string that looks like the following.
1,'Test'
So what does the Insert function look like, type in the following just before the end Routing statement,
Function InsertDataRecord(sServer,sDatabase,sTable, _
sFields,sValues,sUser,sPassword,oReturnObject)
Dim oConnection, oRecordset
Dim sInfo ,sConnection, sSQL
On Error Goto ErrorMessage
Set oConnection = CreateObject("ADODB.Connection")
sConnection = "Provider=SQLOLEDB.1;Persist Security Info=-1;"
sConnection = sConnection + "Password=" + sPassword + ";"
sConnection = sConnection + "User ID=" + sUser + ";"
sConnection = sConnection + "Initial Catalog=" + sDatabase + ";"
sConnection = sConnection + "Data Source=" + sServer
oConnection.Open(sConnection)
If ( oConnection.State = 1 ) Then
' TODO
oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database worked, Insert Complete")
Else
oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database failed")
End If
oConnection.Close()
Goto EndOfFunction
ErrorMessage:
sInfo = sInfo + "
Error :
"
sInfo = sInfo + Err.Description + "
"
sInfo = sInfo + CTEXT(Err.LineNumber)
oReturnObject.Remove("Message")
oReturnObject.Add("Message", sInfo)
EndOfFunction:
Set oConnection = Null
Set InsertDataRecord = oReturnObject
End Function
Again this is standard code, or code we have seen before so there will be no need to explain it. But it does have one difference. You should see that the connection string is different to the previous ones. And that is because we are connecting to a normal SQL table not a VData or Data Collection table. Next we have to add the insert code into the script. Locate the ‘ TODO and replace it with the following.
sSQL = "INSERT INTO " + sTable + " (" + sFields + ") VALUES " + "(" + sValues + ")"
oConnection.Execute(sSQL)
For more information about the SQL insert statement you can click here, but basically first off we have the INSERT INTO function command followed by the table name and then the fieldnames in brackets followed by the keyword VALUES then the Values themselves in brackets. So for us the SQL insert statement that gets created would be,
INSERT INTO Names (ID,UserName) VALUES ( 1,'Test')
So the first time we run the code we should see the following message ( if all our syntax is correct )
And then if we look inside of our SQL table we should see
If however we run the script again, and you are testing this from professional , you will see the following message.
this just means that you are trying to insert the same row again, because we are using professional the serial number returned by the code IOM.Info.Serial will always be 1. If how you launch your survey and test it on your server the second time you run the code you will not see the message.
And there you have it, another generic function that you can use to enter information into an SQL tables. In our final article we will show you how to update a record that is already in a database table.
The full code for this article can be found in :http://CodeCorner.SmaterDimensions.Com @ Connect to a SQL Database and insert a record into a table