|
Hello all,
I'm using VB.NET(VS2003) connect with MySQL5.0 database. I'm trying to migrate the previous system which is using VB6 to VB.NET here.I am not familiar with VB6 code and I found this problem below:
Public Function CSQLQuote(ByVal Value As String) As String
' Value = Replace(Value, "\", "\\")
' CSQLQuote = Replace(Value, "'", "''")
CSQLQuote = mysql_escape_string(Value)
End Function
'API DECLARATION FOR mysql_escape_string FUNCTION CALL
Public Declare Function api_mysql_escape_string Lib "libmySQL.dll" _
Alias "mysql_escape_string" _
(ByVal strTo As String, _
ByVal strFrom As String, _
ByVal lngLength As Long _
) As Long
Public Function mysql_escape_string(dirtystring As String) As String
Dim strFrom As String 'SOURCE STRING PASSED TO FUNCTION
Dim lngFromLength As String 'LENGTH OF SOURCE STRING
Dim strTo As String 'DESTINATION STRING COMING FROM FUNCTION
Dim lngToLength As Long 'LENGTH OF DESTINATION STRING
strFrom = dirtystring 'STORE FUNCTION INPUT
lngFromLength = Len(strFrom) 'GET LENGTH OF INPUT
strTo = Space(lngFromLength * 2 + 1) 'ALLOCATE A BUFFER FOR OUTPUT OF FUNCTION
'2 BYTES PER CHARACTER PLUS A BYTE FOR NULL
'TERMINATOR USED BY FUNCTION
lngToLength = api_mysql_escape_string(strTo, strFrom, lngFromLength) 'CALL API
mysql_escape_string = Left(strTo, lngToLength) 'TRIM NULL TERMINATOR
End Function
These function will be called inside the SQL query like this:
strSQL = "SELECT LocationCode, TerminalCode, Source, Prefix, LastNo, Length" & _
" FROM POS_RunningNo" & _
" WHERE LocationCode='" & CSQLQuote(strLocationCode) & "'" & _
" AND TerminalCode ='" & CSQLQuote(strTerminalCode) & "'" & _
" AND Source='" & CSQLQuote(strSource) & "'"
I'm wondering I still need to used the above function apply on my new VB.NET system or I can just directly used the normal SQL query? And anyone can tell me whats the function above trying to do?
Hope someone can give me some ideas about it.Thanks for reading.
Regards
Drex
|
|
|
|
|
You don't need to escape like this in VB.NET. Use parameterized queries instead.
|
|
|
|
|
Thanks Navaneeth for your solution here. I have search and found that Here[^] give the example which using oledb connection but I'm currently using ODBC to connect with MySQL 5.0 database. Can I still used the parameterize query with my ODBC connection? Do anyone have any sameples about this for me here?
Hope someone can give me some sample.
Thank you all for reading.
Regards
Drex
|
|
|
|
|
drexler_kk wrote: Can I still used the parameterize query with my ODBC connection?
I believe so. ODBC is considered slow as it adds an extra layer to the communication. So it's better to go with "MySQL Connector .NET" which is built specialy for MySQL. You can download it from here[^]
|
|
|
|
|
Yeah,I have read some articles talk about it too. But I found a problem with the MySQL .NET connector actually. I'm doing with VB.NET Window Form Application here. The version of Visual Studio .NET 2003 Version 7.1 for my VB.NET developer tools now doesn't support the MySQL .NET connector.
It doesn't allow me to Add Reference for the MySQL.Data.dll file into my project. So I have really no choices to used ODBC Connector here to connect with MySQL 5.0 database. You have any other better recommendation for me?
Thanks for reading everyone.
Regards
Drex
|
|
|
|
|
drexler_kk wrote: .NET 2003 Version 7.1
Ahh, then you might need to use ODBC. BTW, have you checked MySQL connector has a .NET 1.1 compatible version ?
|
|
|
|
|
Hello Navaneeth,
Yes,I have no choices so I choosed to used ODBC connector. I have check the .NET 1.1 but its still not compatible with the VS2003 as I'm using here.
I'm wondering is that still possible for me to used the parameterized query as suggested with this ODBC? I'm just using the most simple query,is that any good and bad for the query I'm using now for long term in my window form application?
Hope to hear from you all again soon. Thanks for reading and giving more solution.
Regards
Drex
|
|
|
|
|
drexler_kk wrote: I'm wondering is that still possible for me to used the parameterized query as suggested with this ODBC?
Yeah, you can always use parameterized queries with ODBC. I guess ? is used to represent parameters in ODBC. So you can supply a query like
SELECT *
FROM Customers
WHERE CustomerId = ?CustId ?CustId is the parameter here. You can add this to OdbcCommand object's Parameters collection.
|
|
|
|
|
Thank you for the solution Navaneeth. I will change the query in my WinForm from now on. Its really a new knowledge for me doing this kind of query to make a better system.
Thanks for your effort.
Regards
Drex 
|
|
|
|
|
Glad it was helpful. Parameterized queries helps you to avoid SQL Injection attacks. CP has a good article[^] on the subject, no doubt it's worth reading.
|
|
|
|
|
Hello,
I'm a newbie programmer. I want to try to make a steganography application using Visual Basic with LSB method. This application using audio files as carrier (*.mp3 and *.wav) and the message is taken from a choosen message (*.txt).
Is there anyone can help me about it. I'm already new in this field.
Thank you.
|
|
|
|
|
You want to turn audio files into text ? Good luck with that... I'd say choose an easier first project.
Christian Graus
No longer a Microsoft MVP, but still happy to answer your questions.
|
|
|
|
|
Christian Graus wrote: choose an easier first project.
You got there before me 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Member 3746629 wrote: I want to try to make a steganography application using Visual Basic with LSB method.
Member 3746629 wrote: newbie programmer
I highly recommend you take on simpler tasks if you are a newbie. The program you want to try ( no problem with trying ), is like learning to run before you can walk.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
How can I format a textbox value into single data datatype with 2 decimals?
On my form I allow the user to type in a quantity and rate. Both fields are stored in a single data type. In addition, they are multiplied to get the billing amount. I would like to store them in the database with two decimals and make sure that the bill amount also has two decimals. Here is what I am currently doing - is this right or is there a better way?
'Variables to get the information from the screen. This runs if the field is numeric
m_OrdQty = CSng(Val(txtQuantity.Text))
m_MaterialCost = CSng(txtMatrlCost.Text.Trim)
'Database fields
m_MMatrltrns.OrderQuantity = CSng(Math.Round(m_OrdQty, 2))
m_MMatrltrns.BillRate = CSng(Math.Round(m_MaterialCost, 2))
m_MMatrltrns.BillAmount = CSng(Math.Round(m_MMatrltrns.BillRate * m_MMatrltrns.OrderQuantity, 2))
I looked into using the tostring("F") option and the format(field, "#.00") option but I am unsure as to which way is better.
Thank you in advance,
digicd1
|
|
|
|
|
Formatting the number does not affect the undelying data. What you are doing is chopping off the trailing decimals. You need to decide whether to round ot truncate.
Take a look at the Math class, it has lots of functions that may give you a more elegant solution. There is nothing intriinsically wrong with your solution it just feels like a kludge.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you Mycroft. That is what I was looking for.
|
|
|
|
|
I installed Oracle 9.2.0.1.0 client on my machine and wrote the following piece of code to call a stored function from my asp.net application
Imports System
Imports System.Data.OracleClient
Imports System.Data.Common
Imports System.Configuration
Imports System.Data
Dim connection As OracleConnection = Nothing
connection = New OracleConnection(ConfigurationManager.ConnectionStrings("Conn1").ToString())
Dim command As OracleCommand = New OracleCommand("dtx_ivr_sf", connection)
'command.Connection = connection
'command.CommandText = "dtx_ivr_sf(P_parcel_id)"
command.CommandType = CommandType.StoredProcedure
Dim RetVal As New OracleParameter("RetVal", OracleType.VarChar, 200)
Dim P_parcel_id As New OracleParameter("P_parcel_id", OracleType.VarChar, 10)
RetVal.Direction = ParameterDirection.ReturnValue
P_parcel_id.Direction = ParameterDirection.Input
P_parcel_id.Value = "0000006611"
command.Parameters.Add(RetVal)
command.Parameters.Add(P_parcel_id)
connection.Open()
command.ExecuteNonQuery()
Dim str As String = RetVal.Value.ToString()
If Not String.IsNullOrEmpty(str) Then
m_boo_IsDelinquent = True
End If
But I am getting an exception on "command.ExecuteNonQuery()" as
"ORA-06502: PL/SQL: numeric or value error: character string buffer too smal"
Do I need to install odp.net for this? Please help. I know my Oracle stored function is working fine, as I can run it from Oracle forms. Any help is appreciated
|
|
|
|
|
At a guess I think your return parameter is probably too small
ljammala wrote: Dim P_parcel_id As New OracleParameter("P_parcel_id", OracleType.VarChar, 10)
try increasing the 10 and see what result you get.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I still get the same error increasing size to 100.
|
|
|
|
|
I increased the size of my output parameter "RetVal" and that solved the problem. Thank you very much
|
|
|
|
|
I am wondering whats wrong with my code as i defined a button to click, search and copy the results but the data would not be inputted to the next datarow. It just keeps overridding the first datarow.
<br />
Button_click<br />
<br />
Dim dr As DataRow<br />
Dim dt As New DataTable<br />
<br />
dt = objDataSet.Tables(0)<br />
<br />
<br />
Dim dt2 As New DataTable<br />
<br />
<br />
For Each dr In dt.Rows<br />
For i As Integer = 0 To dt.Columns.Count - 1<br />
<br />
If dr(i).ToString = TextBox1.Text Then<br />
<br />
Dim arData() As Object = {dr(0), dr(1), dr(2)} <br />
dt2.Columns.Add("IC Roll")<br />
dt2.Columns.Add("F2")<br />
dt2.Columns.Add("F3")<br />
<br />
dr = dt2.NewRow()<br />
dt2.Rows.Add(arData)<br />
<br />
Exit For<br />
End If<br />
Next<br />
DataGrid2.DataSource = dt2<br />
Next<br />
|
|
|
|
|
You are really need to pay attention to the order of execution (what executes next)
Dim dr As DataRow
#1) you are creating a new instance and then turning around and replacing it
Dim dt As New DataTable
dt = objDataSet.Tables(0)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Dim dt2 As New DataTable
For Each dr In dt.Rows
For i As Integer = 0 To dt.Columns.Count - 1
If dr(i).ToString = TextBox1.Text Then
#2) you are creating columns inside the for next loop
Dim arData() As Object = {dr(0), dr(1), dr(2)}
dt2.Columns.Add("IC Roll")
dt2.Columns.Add("F2")
dt2.Columns.Add("F3")
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#3)you ask for a new row -- you replaced your variable in your for next loop and you never populate it nor give it to any table
dr = dt2.NewRow()
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
dt2.Rows.Add(arData)
Exit For
End If
Next
DataGrid2.DataSource = dt2
Next
<<<<<<<<<<>>>>>>>>>>>>>
should look like this
Dim dr As DataRow, drNew as DataRow
Dim dt As DataTable = objDataSet.Tables(0)
Dim dt2 As New DataTable
dt2.Columns.Add("IC Roll")
dt2.Columns.Add("F2")
dt2.Columns.Add("F3")
For Each dr In dt.Rows
For i As Integer = 0 To dt.Columns.Count - 1
If dr(i).ToString = TextBox1.Text Then
drNew = dt2.NewRow
drNew(0) = dr(0)
drNew(1) = dr(1)
drNew(2) = dr(2)
dt2.Rows.Add(drNew)
drNew = Nothing
Exit For
End If
Next
Next
DataGrid2.DataSource = dt2
'Never argue with an idiot; they'll drag you down to their level and beat you with experience.' ~ anonymous
'Life's real failure is when you do not realize how close you were to success when you gave up.' ~ anonymous
|
|
|
|
|
When you are adding new rows with an object array, you don't have to execute the NewRow() method before doing the Add. I'm also not sure why you would be setting your datagrid's datasource while still inside the for loop, or why you are adding columns to dt2 while inside the for loop. Maybe I'm just not seeing the forest for the trees. Anyways, one suggestion would be for you to change your code to something like the following and see if that resolves your issue. I'm not sure it will, but it's a thought.
Dim dr As DataRow
Dim dt As New DataTable
dt = objDataSet.Tables(0)
Dim dt2 As New DataTable
dt2.Columns.Add("IC Roll")
dt2.Columns.Add("F2")
dt2.Columns.Add("F3")
For Each dr In dt.Rows
For i As Integer = 0 To dt.Columns.Count - 1
If dr(i).ToString = TextBox1.Text Then
Dim arData() As Object = {dr(0), dr(1), dr(2)}
dt2.Rows.Add(arData)
Exit For
End If
Next
Next
dt2.AcceptChanges()
DataGrid2.DataSource = dt2
|
|
|
|
|
Hi
Hope you can help. I've got data saved in SQL DataTable in 1 column is the date an invoice was sent. 2nd column is a Bit(Boolean). This tells me whether the invoice has been paid.
I want to work it so that on form load, a msgbox tells the User to send a reminder for a particular invoice.
The code I'm using works fine for 1st 2 rows and then misses out all other rows.
Any ideas please? 
Dim CurrentDate As Date
Dim ReminderDate As Date
Dim InvoiceDate As Date
Dim msgbxReminder As MsgBoxResult
CurrentDate = Date.Now.Date
InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate")
ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate)
For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows
If dr("Cleared") Is System.Convert.DBNull Then
If CurrentDate = ReminderDate Then
msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.")
End If
End If
Next
Thanks all 
Kris MCP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
