Syntax Error INSERT INTO Statement – ADO.Net Solved

This is a common error occurred during the database operation in ADO.Net. The cause for this error is the column name matches some reserved words. To solve this issue suffix and prefix with “] and [” in column names.

Command object

If you are using command object use the following method in the insert statement.

OleDbCommand cmd = new OleDbCommand(“insert into acgroup(gname,parent,[note]) values(‘” + gname + “‘,'” + gparent + “‘,'” + gdescription + “‘” + “)”, Globals.con);
cmd.ExecuteScalar();

here ‘note’ cause the error.

Dataset and Command builder

If you are using dataset, adapter and command builder to insert records, use command builder Quickfix method to solve the issue as follows

dr = Globals.GroupDataSet.Tables[0].NewRow();
dr[“gname”] = gname;
dr[“parent”] = gparent;
dr[“note”] = gdescription;
Globals.cmdbuilder = new OleDbCommandBuilder(Globals.GroupAdapter);
Globals.cmdbuilder.QuotePrefix = “[“;
Globals.cmdbuilder.QuoteSuffix = “]”;
Globals.GroupDataSet.Tables[0].Rows.Add(dr);
Globals.GroupAdapter.InsertCommand = Globals.cmdbuilder.GetInsertCommand();
int stat = Globals.GroupAdapter.Update(Globals.GroupDataSet.Tables[0]);
if (stat > 0)
{
MessageBox.Show(“Group Information saved”);
}

Advertisements

Exporting DB tables to Excel in VB6

It is something like complicated when you want to export reports from reporting objects like List view in VB6. In fact Excel object has provided a way to directly export contents of a table into Excel format in a quick manner using DAO or ADO connection.

To illustrate this we need,

  1. A DAO, ADO database connection
  2. Excel Objects
  3. Loop to print column heading

Connection

You may be familiar with the database connections in VB6, the following is an example of ADO data connection.

Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordset

adodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\emp.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Accounts “, adodb_db, adOpenKeyset

The excel objects

The excel objects are required to create a new Excel file in order to export the rows of a table.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

The columns

The CopyFromRecordset of Excel object didn’t fetch columns, so we do it manually as follows.

If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst

‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c

End if

Copying the rows to Excel

The Excel Worksheet object’s CopyFromRecordset help you to copy records to excel rows, all you need to specify the cell range.

oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs

The complete code

Here is the complete code

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordset

adodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\SherPharma\SIGMA1819.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Account_Transactions “, adodb_db, adOpenKeyset

‘Set rs = MedicalShop.OpenRecordset(“select entryno,itemname,total from Sales_Particulars “)
If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst

‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c
oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs

oExcel.Visible = vbTrue

End If

 

Querying external database in vb6 DAO

DAO is one of many ways to access database in VB6. This tutorial tells you how you can access a .mdb database which is not pointed by the DAO Database object.

I have separate Access 2003 .mdb database files, one is pointed by the Database object conn and the other I want to access using the same connection.

SELECT * FROM [File Path][DBNAME].[TABLE]

Connection

Have a look at the connection. [May be you already know, what its look like, this for those not familiar with DAO in VB6]

Dim externalEmp As Recordset
Dim conn As Database
Set conn = OpenDatabase("D:\105443T.mdb")

Querying Records

The conn is now capable of querying any table within the 105443T database.

Say,

Set tr = conn.OpenRecordset("select * from Transactions")
If tr.RecordCount > 0 Then
 tr.MoveFirst
 tr.MoveNext
 tr.MoveFirst
 
 For c = 0 To tr.RecordCount - 1
 Debug.Print tr!date & "--" & tr!dr & "--" & tr!cr
 tr.MoveNext
 Next c
End If

Querying the external DB

Likewise, I can point external Database too with zero changes in conn object. Just specify the [path of file] [.] [data table Name] in place of the table name in the query.

Set externalEmp = conn.OpenRecordset("select * from C:\users\manoj\documents\office.emp")
If externalEmp.RecordCount > 0 Then
 externalEmp.MoveFirst
 externalEmp.MoveNext
 externalEmp.MoveFirst
 
 For c = 0 To externalEmp.RecordCount - 1
 Debug.Print externalEmp!Ename & "--" & externalEmp!edepart & "--" & externalEmp!ebasic
 externalEmp.MoveNext
 Next c
End If

The same is also possible with ADO and other connections.

That’s it.

RSS / ATOM Feed Parsing in Python

Feeds are good for showing content from one website to another. Feeds are basically XML file with user define tags. As Python fan, I found reading content from a RSS feed in python is very simple and interesting. Most of the sites have feeds.

In our example we are using SEBI’s feed, which will provide latest events in Indian Stock Exchange market.

First you need the parsing package from Pypi[Python Package Index]. You can Install the package using pip command as follows

pip install feedparser

Import the package and use it

import feedparser
#Programmer : MANOJ AP
#Email: develprm@outlook.com
feed = feedparser.parse("https://www.sebi.gov.in/sebirss.xml")

feed_title = feed['feed']['title']
feed_entries = feed.entries

for entry in feed.entries:
    article_title = entry.title
    article_link = entry.link
    article_published_at = entry.published # Unicode string
    article_published_at_parsed = entry.published_parsed # Time object  
    print "{}[{}]".format(article_title.encode('utf-8'), article_link.encode('utf-8'))
    print "Published at {}" .format(article_published_at)

The feedparser.parse accept a RSS feed and turn into Python object, as we know can access the elements in as in a list(feed[‘feed’][‘title’]). feed.entries return the feed elements, we can use a for loop to traverse through the elements

You are welcome to use the source code, SEBI Reader project

Create a simple Jason File

Like XML files JASON file can hold data in the form of  list or list of list. If you are familiar with Python list, it can be simplified. Using your favorite text editor you can create a Jason file.

JASON file can be used to store data for static web pages or can be used to transfer data between application. Let’s look at the very simple Jason file.

"institution":
[{
"name" : "RMHS MELATTUR",
"location" : "Melattur",
"catagory":"School",
"landmark":"Opp. GOVT. Hospital PMNA",
"distance":"12.km",
"Office phone":"947854545",
"district" :"Malappuram",
"web" :"http://rmhs.com",
"email" :"rmhs@outlook.com",
"course_offering" :["Plus Comp. Science","Plus Science"]
}]

In the above example we have a list called institution, and lots of keys and values, say location is a key and Melattur is the key value, you can identify the value with the key.  Each key is separated with a ‘.’ operator. and you can also have sub list which can be included within a ‘[‘ and ‘]’.

Can I hold more than one information in a Jason? yes. In the following format, within the same list.

{
"institutions":
[{
"name" : "RMHS MELATTUR",
"location" : "Melattur",
"catagory":"School",
"landmark":"Opp. GOVT. Hospital PMNA",
"distance":"12.km",
"Office phone":"947854545",
"district" :"Malappuram",
"web" :"http://rmhs.com",
"email" :"rmhs@outlook.com",
"course_offering" :["Plus Comp. Science","Plus Science"]
},
{
"name" : "NSS School",
"location" : "Manjeri",
"catagory":"School",
"landmark":"Opp. Municipality",
"distance":"12.km",
"Office phone":"947854545",
"district" :"Malappuram",
"web" :"http://rmhs.com",
"email" :"rmhs@outlook.com",
"course_offering" :["Plus Comp. Science","Plus Science"]
},
{
"name" : "MODEL School",
"location" : "Pandikkad",
"catagory":"School",
"landmark":"Opp.Buss stand",
"distance":"12.km",
"Office phone":"947854545",
"district" :"Malappuram",
"web" :"http://rmhs.com",
"email" :"rmhs@outlook.com",
"course_offering" :["Plus One Comp. Science","Plus One Science"]
},
{
"name" : "CENTRAL SCHOOL",
"location" : "Perinthelmanna",
"catagory":"School",
"landmark":"Opp. GOVT. Hospital PMNA",
"distance":"12.km",
"Office phone":"947854545",
"district" :"Malappuram",
"web" :"http://rmhs.com",
"email" :"rmhs@outlook.com",
"course_offering" :["Plus One Comp. Science","Plus One Science"]
},
{
"name" : "NSS COLLAGE",
"location" : "Palakkad",
"catagory":"Collage",
"landmark":"Opp. GOVT. Hospital PMNA",
"distance":"12.km",
"Office phone":"947854545",
"district" :"Palakkad",
"web" :"http://rmhs.com",
"email" :"rmhs@outlook.com",
"course_offering" :["BSc Comp. Science","BSc Physics,BSc Maths"]
},
{
"name" : "MES BED COLLAGE",
"location" : "Edathanattukara",
"catagory":"Collage",
"landmark":"Opp. GOVT. Hospital PMNA",
"distance":"12.km",
"Office phone":"947854545",
"district" :"Malappuram",
"web" :"http://rmhs.com",
"email" :"rmhs@outlook.com",
"course_offering" :["SS,MALAYALAM,ENGLISH"]
}
 ]
}

The file extension must be.jason.

In the next post, we will learn how to read a Jason file in Python.

Build a Flask-Python web App

Building a dynamic website made so easy with Python -Flask, a Micro FrameWork which helps us to create the websites like Twitter and even more. Forget about the complicated web programming languages, start learning Python. So where you start, Python or Flask ?. I should say, you have to learn basics of how Python first, just have a look at Python.org

IDE

Which IDE I should choose? IDE helps you to write, compile, build programs. Pycharm is a good choice, Visual Studio also had added the feature for Python programmers in their 2013 edition onwards. You can Google it for more options. I Prefer Pycharm.

Flask and Web, How it works?

Flask arranges web pages as basic URL or routes.  Let’s take a look at the Pycharm App code.

from flask import Flask
app = Flask(__name__)

@app.route(‘/’)
def hello_world():
return ‘Hello World!’

if __name__ == ‘__main__’:
app.run()

When the Index page is loaded the @app.route(‘/’) will be fired with an initial page. You can create as much as routes you want to say ‘/blog’, ‘/news/ etc.

 

How to host webpages on github for free

GitHub is my favorite place to host my personal web apps. It is one of the finest places to host your web-based [ Python, Javascript, Django etc ] static websites.

Features I love

  • You can keep source code on the web
  • Discuss with others
  • Initially, you can host a static web page [can make static dynamic apps, with Python, we will discuss it later]
  • Make changes from your computer and commit and push changes to the repository you made, changes will go online

How to host a website on GitHub?

  • The first thing you need to do the GitHub software.Download and Install
  • Prepare your website on your PC
  • Create a repository with the following format on Github.com

username.github.io

  • The username and subdomain should match to host the website completely, otherwise the page not go live. You can try additional website by creating another Organization and then repository
  • Click the clone download the button on GitHub Pages

2018-02-18_122237

  • Open GitHub on the PC, File-Clone Repository
  1. Choose the Repository Folder first, say D:/Github [ where your source code will reside ]
  2. Paste the clone link and it will create a folder username.github.io
  3. Copy all of your web pages and folders to the new folder.

2018-02-18_115018

  • Now you need to made first commit to the master[only master branch commit will affects the website]
  • Finally Push all changes back the GitHub and your site will go online.

Check mine