|
Subject:
|
Implement parameterized queries in vb6
|
|
Posted By:
|
Jade08
|
Post Date:
|
7/24/2008 2:49:09 PM
|
Hello,
I'm in desperate need of direction on how to implement parameterized queries in Visual Basic 6 codes. I've searched the net but can't seem to find any clear steps.
Any help would be appreciated.
|
|
Reply By:
|
BrianWren
|
Reply Date:
|
7/24/2008 3:16:59 PM
|
Parameterization of queries is a database technology, not a VB technology.
You use VB to create DB connections, and use those connections as the query communicator.
What is it exactly that you are trying to do? Perhaps knowing that, a good solution will become apparent.
|
|
Reply By:
|
Jade08
|
Reply Date:
|
7/24/2008 3:38:54 PM
|
Hi,
Thank you for replying.
I have an app in vb6 with SQL server 2000 as my backend. I'm trying to save some data to the database; a part of the data to be saved is a date. However i kept getting the error: "The conversion of a char datatype to a datetime datatype resulted in an out-of-range datetime value". It was suggested that the problem arose from my vb app and that I should use parameterized queries to input data into the database. Hence my question.
I hope I've given you enough information so that you may be able to assist me, since i really need assistance.
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
7/24/2008 9:04:16 PM
|
No code, no help.
You don't even say if you are using ADO or DAO to access the DB.
If you are using ADO, then that sounds like you are passing an ADODB.Parameter that is using the wrong data type.
It *COULD* be as simple a thing as you trying to pass
'24/07/2008'
to SQL Server, when SQL Server is expecting dates in MM/DD/YYYY (USA) format. Which is easy to fix in a couple of ways, but we need to see some code.
|
|
Reply By:
|
Jade08
|
Reply Date:
|
7/25/2008 7:43:09 AM
|
Sorry about the adsence of code. Before I show you my code may i can give a little more info.
I've chosen the datatime data type (at the server end) for my column to store dates from my app. I'm inputing the system date via a date variable into the database. My OS is XP and my regional settings are English United States but the short date format is customized to dd/mm/yyyy; initially i'd chosen English United Kingdom but i changed it. I've tried saving the date and time, just the date alone, I've tried formating a mask edit control for the date format, but nothing seems to work.
Now here is my code:
The following is the declaration of Curdate:
Private CurDate As Date
Private Sub Form_Load()
medtDate.Text = Format(Now, (Date)) 'A mask edit box to display the date only
End Sub
Save Procedure:
Private Sub cmdSave_Click()
Call Globalconnect 'Connection string
CurDate = medtDate.Text
SQLInsert = "INSERT INTO Transac_Table (TransNumber, Date_Received,Quantity, Amount)" & _
"VALUES ('" & TCode & "', '" & CurDate & "', '" & txtQuantity.Text & "', '" & txtAmount.Text & "')"
connect.Execute SQLInsert
MsgBox "Data saved", vbOKOnly
Call Clear_all 'clearing cells after save
connect.Close
End Sub
|
|
Reply By:
|
BrianWren
|
Reply Date:
|
7/25/2008 11:43:01 AM
|
Databases are very picky about date formats. In Oracle, in a statement like yours, you must use an Oracle conversion function, and a conversion string specifying where the individual components of the date are to be found. It is an Oracle function, not a VB function.
It sounds like you are getting an error because, for instance, the year is where the day is expected, and a month day of 2,008 is a bit on the large side...
Look in your SQL Server documentation for inserting dates, and I think you'll find the info on the proper string to send from VB.
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
7/25/2008 1:09:04 PM
|
Okay, so try this:
SQLInsert = "SET DATEFORMAT DMY; " _
& "INSERT INTO Transac_Table (TransNumber, Date_Received,Quantity, Amount)" _
& "VALUES ('" & TCode & "', '" & CurDate & "', '" _
& txtQuantity.Text & "', '" & txtAmount.Text & "')"
That tells SQL Server that you want to use the format dd/mm/yyyy for the date. By default, SQL Server uses USA standard, mm/dd/yyyy.
BUT...
But this has *NOTHING* to do with "parameterized queries"!!
In fact, this is the opposite of a parameterized query; it's an AD HOC query.
On top of everything, this kind of query is the *MOST* susceptible to SQL Injection.
So you *REALLY* should be doing SQL Injection protection!
Also, I see other *probable* problems in there. You have apostrophes around the value of TCode
...('" & TCode & "',...
Yet the field in the DB is named TransNumber. If it's truly a number, kill those apostrophes. And SURELY that is also true about fields named Quantity and Amount. SURELY those are also numbers, no???
SO *GUESSING* at what you need...and it really is a guess...
SQLInsert = "SET DATEFORMAT DMY; " _
& "INSERT INTO Transac_Table (TransNumber, Date_Received,Quantity, Amount)" _
& "VALUES (" & CLNG(TCode) & ", '" & CurDate & "', " _
& CDBL(txtQuantity.Text) & "," & CDBL(txtAmount.Text) & ")"
The calls to CLNG and CDBL in there are to *ENSURE* that nobody has managed to slip in SQL Injection to those form fields (may not be needed for TCode...can't tell).
If this still doesn't work, then use
MsgBox SQLInsert
to see *exactly* what the query is that you are using and show that to us.
|
|
Reply By:
|
Jade08
|
Reply Date:
|
8/27/2008 12:49:05 PM
|
I know this is an extremely late comment, but for the benefit of persons searching for a similar solution, the above solution (using SET DATEFORMAT DMY) works!!!
Thank you for your help experts.
|
