Not sure how to handle this sort of speaking FoxPro DBF
-
This Foxpro program keeps using spaces on the invoice number column in front or the data. Perhaps that was the norm back then before I started programming E.G. "70155" would be " 70155" So I not sure if the column is fixed, or if every invoice number starts with a " " blank space. e.g. " 155" I wrote this placing a space in front of the invoice number, but afraid it may backfire on me when the invoice number grows larger
Dim c5 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01H.dbf SET FPRICE = 0.00 WHERE FINVNO = ' " & pFINVNO & "' AND FITEMNO = '" & pFITEMNO & "'", connection)
I thought using a wildcard, but I've been searching for a couple of hours and cant get things like $,% to work
If it ain't broke don't fix it
-
This Foxpro program keeps using spaces on the invoice number column in front or the data. Perhaps that was the norm back then before I started programming E.G. "70155" would be " 70155" So I not sure if the column is fixed, or if every invoice number starts with a " " blank space. e.g. " 155" I wrote this placing a space in front of the invoice number, but afraid it may backfire on me when the invoice number grows larger
Dim c5 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01H.dbf SET FPRICE = 0.00 WHERE FINVNO = ' " & pFINVNO & "' AND FITEMNO = '" & pFITEMNO & "'", connection)
I thought using a wildcard, but I've been searching for a couple of hours and cant get things like $,% to work
If it ain't broke don't fix it
This works at the moment, I'll run with it today
Dim c3 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01.dbf SET FPRICE = " & pFPRICE & ", FAMOUNT = " & pFPRICE & " WHERE FINVNO LIKE '%" & pFINVNO & "%' AND FITEMNO = '" & pFITEMNO & "'", connection)
If it ain't broke don't fix it
-
This works at the moment, I'll run with it today
Dim c3 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01.dbf SET FPRICE = " & pFPRICE & ", FAMOUNT = " & pFPRICE & " WHERE FINVNO LIKE '%" & pFINVNO & "%' AND FITEMNO = '" & pFITEMNO & "'", connection)
If it ain't broke don't fix it
Pad left (with blanks) all compared fields to their "defined lengths" (i.e. what's defined in the dbf), and then compare those. That's your safest route (IMO).
"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal
-
This works at the moment, I'll run with it today
Dim c3 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01.dbf SET FPRICE = " & pFPRICE & ", FAMOUNT = " & pFPRICE & " WHERE FINVNO LIKE '%" & pFINVNO & "%' AND FITEMNO = '" & pFITEMNO & "'", connection)
If it ain't broke don't fix it
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query. Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^] Query Parameterization Cheat Sheet | OWASP[^]
Using c3 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01.dbf SET FPRICE = @FPRICE, FAMOUNT = @FAMOUNT WHERE FINVNO LIKE '%' + @FINVNO + '%' AND FITEMNO = @FITEMNO", connection)
c3.Parameters.AddWithValue("@FPRICE", pFPRICE)
c3.Parameters.AddWithValue("@FAMOUNT", pFPRICE)
c3.Parameters.AddWithValue("@FINVNO", pFINVNO)
c3.Parameters.AddWithValue("@FITEMNO", pFITEMNO)
...
End UsingAlso, be very wary of your
LIKEclause. If yourpFINVNOis, for example,"1", your query will update every record where theFINVNOcolumn contains1, including'10','21','123456', etc.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query. Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^] Query Parameterization Cheat Sheet | OWASP[^]
Using c3 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01.dbf SET FPRICE = @FPRICE, FAMOUNT = @FAMOUNT WHERE FINVNO LIKE '%' + @FINVNO + '%' AND FITEMNO = @FITEMNO", connection)
c3.Parameters.AddWithValue("@FPRICE", pFPRICE)
c3.Parameters.AddWithValue("@FAMOUNT", pFPRICE)
c3.Parameters.AddWithValue("@FINVNO", pFINVNO)
c3.Parameters.AddWithValue("@FITEMNO", pFITEMNO)
...
End UsingAlso, be very wary of your
LIKEclause. If yourpFINVNOis, for example,"1", your query will update every record where theFINVNOcolumn contains1, including'10','21','123456', etc.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
It started out using parameters, and it's a Windows App. But I wasn't able to match the parameters and started easter egg hunting to figure out why. Back to the case sensitive stuff and that extra blank char prefix. So I wrote it the old school way to diagnose it. I'll try and go back to parameters on that, but will have to use the ? in order for that. It's suppose to be more FoxPro than SQL, like speaking pure FoxPro using VFPOLEDB
If it ain't broke don't fix it
-
Pad left (with blanks) all compared fields to their "defined lengths" (i.e. what's defined in the dbf), and then compare those. That's your safest route (IMO).
"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal
-
I have a width of 74 for that column. I'm not sure what the units are. I'll google it today.
If it ain't broke don't fix it
-
What do you mean by "Google" it? The definition of the field is in the DBF; you can even determine that with code at run-time to make it generic.
"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal
