Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forums/database-forum/)
-   -   Binding parameter for insertion (http://www.go4expert.com/forums/binding-parameter-insertion-t15811/)

pkjena 13Jan2009 13:09

Binding parameter for insertion
 
Hi,
For binding purpose i'm using the followng query--
"INSERT INTO table_bank (Name,Balance,Withdraw,Address) VALUES (?,?,?,?)";

but when i'm going to bind a 5th parameter like
"INSERT INTO b1 (Name,Balance,Withdraw,Address,DateTime) VALUES (?,?,?,?,?)";

i observed-
- No compilation error.
- Not adding any thing to database even the rest 4 parameters which were adding to database earlier .

I just want to know whether is there any insertion limit or any thing i'hv to change with my Query.
Also i'hv attached the code stuff as i'm using .Please find the same .txt file.

Please let me know soon.

Code:

// DBApplicationDlg.cpp : implementation file
//

#include "stdafx.h"
#include "DBApplication.h"
#include "DBApplicationDlg.h"

#include <windows.h>
#include <sqlext.h>
#define LENGTH 200
#define LENGTH_COL 100



#ifdef _DEBUG
#define new DEBUG_NEW
#endif
/////////////////////////////////////////
////////////////////////////////////////////Global Declaration ////////////////////////////////Litu  SQL Query ...................
HENV hEnv = NULL;
HDBC hDBC = NULL;
HSTMT hStmt = NULL;
UCHAR szDSN[SQL_MAX_DSN_LENGTH] = "BankInfo";
UCHAR* szUID = NULL;
UCHAR* szPasswd = NULL;


UCHAR szSqlStr[250]= "Select * From b1";
UCHAR szSqlStrPre[250]= "INSERT INTO b1 (Name,Balance,Withdraw,Address,DateTime) VALUES (?,?,?,?,?)";
UCHAR szSqlStrSel[250]="SELECT Name FROM b1";

RETCODE retcode;
CString date;

////////////////////////////////////////////////////////////////////////Global Declaration End/////////////////////////////Litu

// CAboutDlg dialog used for App About

class CAboutDlg : public CDialog
{
public:
        CAboutDlg();

        // Dialog Data
        enum { IDD = IDD_ABOUTBOX };

protected:
        virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support

        // Implementation
protected:
        DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
        CDialog::DoDataExchange(pDX);
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
END_MESSAGE_MAP()


// CDBApplicationDlg dialog




CDBApplicationDlg::CDBApplicationDlg(CWnd* pParent /*=NULL*/)
: CDialog(CDBApplicationDlg::IDD, pParent)
{
        m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CDBApplicationDlg::DoDataExchange(CDataExchange* pDX)
{
        CDialog::DoDataExchange(pDX);
        DDX_Control(pDX, IDC_EDIT1, m_name);
        DDX_Control(pDX, IDC_EDIT2, m_Balance);
        DDX_Control(pDX, IDC_EDIT3, m_Withdraw);
        DDX_Control(pDX, IDC_EDIT4, m_Addss);
        DDX_Control(pDX, IDC_EDIT6, m_date);
        DDX_Control(pDX, IDC_EDIT5,m_test);
        DDX_Control(pDX, IDC_LIST1, m_fieldList);

}

BEGIN_MESSAGE_MAP(CDBApplicationDlg, CDialog)
        ON_WM_SYSCOMMAND()
        ON_WM_PAINT()
        ON_WM_QUERYDRAGICON()
        //}}AFX_MSG_MAP
        //        ON_EN_CHANGE(IDC_EDIT4, &CDBApplicationDlg::OnEnChangeEdit4)
        ON_BN_CLICKED(IDC_BUTTON1, &CDBApplicationDlg::OnBnClickedButton1)
        //        ON_EN_CHANGE(IDC_EDIT1, &CDBApplicationDlg::OnEnChangeEdit1)
        ON_BN_CLICKED(IDC_BUTTON2, &CDBApplicationDlg::OnBnClickedButton2)
        ON_BN_CLICKED(IDC_BUTTON4, &CDBApplicationDlg::OnBnClickedButton4)
        ON_BN_CLICKED(IDC_BUTTON3, &CDBApplicationDlg::OnBnClickedButton3)
END_MESSAGE_MAP()


// CDBApplicationDlg message handlers

BOOL CDBApplicationDlg::OnInitDialog()
{
        CDialog::OnInitDialog();

        // Add "About..." menu item to system menu.

        // IDM_ABOUTBOX must be in the system command range.
        ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
        ASSERT(IDM_ABOUTBOX < 0xF000);

        CMenu* pSysMenu = GetSystemMenu(FALSE);
        if (pSysMenu != NULL)
        {
                CString strAboutMenu;
                strAboutMenu.LoadString(IDS_ABOUTBOX);
                if (!strAboutMenu.IsEmpty())
                {
                        pSysMenu->AppendMenu(MF_SEPARATOR);
                        pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
                }
        }

        // Set the icon for this dialog.  The framework does this automatically
        //  when the application's main window is not a dialog
        SetIcon(m_hIcon, TRUE);                        // Set big icon
        SetIcon(m_hIcon, FALSE);                // Set small icon

        // TODO: Add extra initialization here
        m_name.SetFocus(); ////////////Litu
        m_test.ShowWindow(0);
        int day, month, year;
        int hour, minute,sec;
        CTime currentTime = CTime::GetCurrentTime();
        day = currentTime.GetDay();
        month = currentTime.GetMonth();
        year = currentTime.GetYear();
        hour = currentTime.GetHour();
        minute = currentTime.GetMinute();
        sec=currentTime.GetSecond();
        //date.Format(" date- %d/%d/%d    Time- %d:%d:%d",day,month,year,hour,minute,sec);
        date = "678";
       
        return TRUE;  // return TRUE  unless you set the focus to a control
}

void CDBApplicationDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
        if ((nID & 0xFFF0) == IDM_ABOUTBOX)
        {
                CAboutDlg dlgAbout;
                dlgAbout.DoModal();
        }
        else
        {
                CDialog::OnSysCommand(nID, lParam);
        }
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CDBApplicationDlg::OnPaint()
{
        if (IsIconic())
        {
                CPaintDC dc(this); // device context for painting

                SendMessage(WM_ICONERASEBKGND, reinterpret_cast<WPARAM>(dc.GetSafeHdc()), 0);

                // Center icon in client rectangle
                int cxIcon = GetSystemMetrics(SM_CXICON);
                int cyIcon = GetSystemMetrics(SM_CYICON);
                CRect rect;
                GetClientRect(&rect);
                int x = (rect.Width() - cxIcon + 1) / 2;
                int y = (rect.Height() - cyIcon + 1) / 2;

                // Draw the icon
                dc.DrawIcon(x, y, m_hIcon);
        }
        else
        {
                CDialog::OnPaint();
        }
}

// The system calls this function to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CDBApplicationDlg::OnQueryDragIcon()
{
        return static_cast<HCURSOR>(m_hIcon);
}

void CDBApplicationDlg::OnBnClickedButton1()
{
       
        // Allocate memory for ODBC Environment handle
        SQLAllocEnv (&hEnv);

        // Allocate memory for the connection handle
        SQLAllocConnect (hEnv, &hDBC);

        // Connect to the data source "BankInfo" using userid and password.
        retcode = SQLConnect (hDBC, szDSN, SQL_NTS, szUID, SQL_NTS, szPasswd, SQL_NTS);
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
        {
                // Allocate memory for the statement handle
                retcode = SQLAllocStmt (hDBC, &hStmt);

                CString  nm ,ads,bal,wdr;//,date;

                m_name.GetWindowText(nm);
                m_Balance.GetWindowText(bal);
                m_Withdraw.GetWindowText(wdr);
                m_Addss.GetWindowText(ads);

                /*int day, month, year;
                int hour, minute;
                CTime currentTime = CTime::GetCurrentTime();
                day = currentTime.GetDay();
                month = currentTime.GetMonth();
                year = currentTime.GetYear();
                hour = currentTime.GetHour();
                minute = currentTime.GetMinute();
                date.Format(" Date- %d/%d/%d            Time- %d:%d",day,month,year,hour,minute);*/
                CString testvalue ;
                m_test.GetWindowText(testvalue);       

                TCHAR  nme[LENGTH], addss[LENGTH],datetime[LENGTH];
                TCHAR blnce[LENGTH],wdrw[LENGTH];

                SDWORD  cname, cbalnce, cwithdraw,caddress,cdttm;
                //        SDWORD count;


                retcode = SQLPrepare(hStmt,szSqlStrPre,sizeof(szSqlStrPre));////////////////////Litu prepare
                //retcode = SQLPrepare(hStmt,szSqlStr,sizeof(szSqlStr));

                cname = SQL_NTS;                    /* null terminated string      */
                cbalnce = 0;
                cwithdraw = 0;
                caddress=SQL_NTS;
                //cdttm=SQL_DATA_AT_EXEC;

                retcode = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                        SQL_CHAR, LENGTH, 0,nme, 0, &cname);

                retcode = SQLBindParameter(hStmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                        SQL_INTEGER, LENGTH, 0,blnce, 0, &cbalnce);

                retcode = SQLBindParameter(hStmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR,
                        SQL_INTEGER, LENGTH, 0,wdrw, 0, &cwithdraw);

                retcode = SQLBindParameter(hStmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR,
                        SQL_CHAR, LENGTH, 0,addss, 0, &caddress);

                  retcode = SQLBindParameter(hStmt,4, SQL_PARAM_INPUT, SQL_C_CHAR,
                        SQL_CHAR, LENGTH, 0,datetime, 0, &cdttm);

                strcpy_s(nme, nm);
                cname = strlen(nme);
                strcpy_s(blnce, bal);
                cbalnce = strlen(blnce);
                strcpy_s(wdrw, wdr);
                cwithdraw = strlen(wdrw);
                strcpy_s(addss, ads);
                caddress = strlen(addss);
               
                testvalue = date ;
                strcpy_s(datetime, testvalue);
                caddress = strlen(datetime);

                // SQLCHAR query[] = "INSERT INTO b1 VALUES(?,0,1,'test1')";//////       
                retcode = SQLExecDirect(hStmt,szSqlStrPre, SQL_NTS);         

               
                retcode = SQLExecDirect(hStmt, (SQLCHAR*)"UPDATE b1 SET Name = 'Litu' WHERE Address = 'shd'", SQL_NTS);  /////// UPDATE UPDATE UPDATE
                retcode = SQLExecDirect(hStmt, (SQLCHAR*)"DELETE FROM b1 WHERE withdraw = 9860", SQL_NTS);  /////// DELETE......

               
                // Execute the SQL statement handle
                retcode = SQLExecute (hStmt);

               
                // Get row of data from the result set defined above in the statement
                retcode = SQLFetch (hStmt);

               
                // Free the allocated statement handle
                SQLFreeStmt (hStmt, SQL_DROP);

                // Disconnect from datasource
                SQLDisconnect (hDBC);
        }
}


shabbir 13Jan2009 15:43

Re: Binding parameter for insertion
 
Attachment moved to post

xpi0t0s 13Jan2009 20:52

Re: Binding parameter for insertion
 
What is the datatype of column DateTime in the database?


All times are GMT +5.5. The time now is 05:26.