|
ALWAYS, ALWAYS, ALWAYS, ... continue for 10 more minutes ..., ALWAYS use PARAMETERIZED queries!
Using a parameterized query will automatically make sure that the date is translated into the appropriate format for the SQL statement. Also, the reverse is true. Any date that is returned will be converted back to the appropriate format for your code.
This is an example from a project I am working on:
Try
oConnection = GetDatabaseConnection()
oCommand = New SqlCommand
oCommand.Connection = oConnection
oCommand.CommandText = "SecurityValidateLogin"
oCommand.CommandType = CommandType.StoredProcedure
Dim sqlParam As New SqlParameter("@Username", SqlDbType.NVarChar, 50)
sqlParam.Value = sUserName
oCommand.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@Password", SqlDbType.NVarChar, 15)
sqlParam.Value = sPassword
oCommand.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@SourceIP", SqlDbType.NVarChar, 32)
sqlParam.Value = sSourceIP
oCommand.Parameters.Add(sqlParam)
oConnection.Open()
oCommand.ExecuteNonQuery()
oConnection.Close()
' code removed
Catch ex As Exception
LogErrorEvent("SecurityServices", "ValidateUserLogin", ex.Message, ex.StackTrace)
Throw ex
Finally
' Trash our open objects
If Not oCommand Is Nothing Then
oCommand.Dispose()
End If
If Not oConnection Is Nothing Then
If Not oConnection.State = ConnectionState.Closed Then
oConnection.Close()
End If
oConnection.Dispose()
End If
End Try
Return retValidateValue
RageInTheMachine9532
"...a pungent, gastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hey Dave,
Under what circumstances should we be using parameterized queries? 
Charlie
if(!curlies){ return; }
|
|
|
|
|
all, alL, aLL, ALL OF THEM I SAY! MUAHAHAHHAHAH!! 
But seriously, for those who read the posts long after we put them up (yeah, right )...
If you have an SQL stored proecdure or statement with a WHERE clause in it, always use parameterized queries to call it! It will save you a bunch of headaches like the original poster had. It's not hard to do, maybe a little tedious, and it's very easy to follow compared to some of the monsterous string built queries I've seen.
RageInTheMachine9532
"...a pungent, gastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hello Dave,
- Thank you for your advice, they are looking very clear ... except that I could not find the right SQLparameter for DateTime format (OLEDBparameters and in your example). We do not want to use them as CString!
- I did find out why I had the problem before, because I set the Region & Language setting in English (Canada) DateTime: Day/Month/Year instead of English USA DateTime: Month/Day/Year ... meanwhile My.mdb set Month/Day/Year! After I change, it works OK now!
*) However, your suggestion (if I can find the way) is more dynamic & appropriate because we do not need to re-set the Region & Language setting
---> If you know how to set SQLparameter more precisely, I will appreciate a lot
Hint: In VC++, we force CASTING DateTime variable to float, then we do not worry about which order the DateTime variable is!
strSQL.Format ("SELECT * FROM Table WHERE Date > %f", (float)fDate;)
Thanks,
|
|
|
|
|
You didn't say it was an Access database, but the idea is identical to an SQL Server version. This is an example based on the query you supplied in your first post:
Public Shared Function GetOleDBDataReader(ByVal searchDate As Date) As OleDbDataReader
Dim oleConnection As New OleDbConnection("connectionString")
Dim oleCommand As New OleDbCommand("storedProcedureName", oleConnection)
oleCommand.CommandType = CommandType.StoredProcedure
' or you could use this if the SQL statemnet is not in a stored procedure:
'Dim oleCommand As New OleDbCommand
'oleCommand.CommandText = "SELECT * FROM Invoice WHERE InvoiceDT >= ?"
'oleCommand.CommandType = CommandType.Text
'oleCommand.Connection = oleConnection
' OleDbType depends on the column definition in the database.
' Using this method, you don't have to worry about the proper date format.
Dim sqlDateParam As New OleDbParameter("@DateToSearchFor", OleDbType.Date)
sqlDateParam.Value = searchDate
sqlDateParam.Direction = ParameterDirection.Output
oleCommand.Parameters.Add(sqlDateParam)
oleConnection.Open()
Return oleCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
RageInTheMachine9532
"...a pungent, gastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi everyone.
I'm just asking, what is the better way to connectig to Oracle 8i DB. I know if I using OLE provider for oracle, ok.. Works fine..
But you know other way to fastes execute SQL statments. I looking for the efficient code for searching.
If anyone?
Thanks
Johnny Lizardo
|
|
|
|
|
Check out the System.Data.OracleClient namespace. It'll have everything you need...
RageInTheMachine9532
"...a pungent, gastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I have a Garmin GPS that I would like to communicate with via
a serial connection. I have downloaded the interface documentation.
The documentation indicates the communication packet has a checksum byte
that is the "2's complement Sum of all data before the checksum byte.
When I add up all of the bytes before the checksum I get a value that
is larger than an 8 bit byte. My final value is 270
If I understand correctly. I take the value of 270 and invert it
Then I add 1 to this value to get the 2's complement.
Example:
Value = 270
Original Value in bits
00000001 00001110 = 270
Value = Not Value
Inverted Value (1's complement)
11111110 11110001 = -271
Value = Value + 1
add 1 to value to get 2's complement
11111110 11110010 = -270
At this point I need to place this into a single 8 bit Byte
I assume I just drop the first 8 bits
to give me the following.
11110010 = 242
My two questions are
1) am I correct in striping off the left 8 bits?
2) how do I do this in VB.Net without having to create a bitarray and manually
processing the bits?
=================================
When I was in school, all I wanted was to get out into the real world.
Now that I'm in the real world, all I want is to go back to school.
|
|
|
|
|
can this be done? None of the books that I have available seem to address this question. It would make things a lot easier if I can hide certain items to eliminate having to perform another query. Thanks
|
|
|
|
|
There are a couple of ways to do this,
1. Set the column width to 0
2. Use datagridtablestyle and datacolumnstyle. Set a mapping to each column you want to show.
HTH
"if you vote me down, I shall become more powerful than you can possibly imagine" - Michael P. Butler.
|
|
|
|
|
after trying that using the gui it seems that the styles that I create don't have any effect on the columns that set them to. I found some code that is doing basically doing everything within that the gui setup should, and it works, but I am unsure why the gui setup doesn't work.
|
|
|
|
|
*shrugs*
bug maybe?
"if you vote me down, I shall become more powerful than you can possibly imagine" - Michael P. Butler.
|
|
|
|
|
How do i do a simple program using VB.Net to convert a particular table's column's datatype in Ms Access?
EG. Column Name Old Data Type New Data Type
Name Text Memo
Help!!!
|
|
|
|
|
Haha, that isn't going to be a simple program! You need to use DAO, research the Object Model...and good luck! 
|
|
|
|
|
Hi,
I want to deploy my VB.net application and have been reading about deployment at MSDN. But I cannot find out what I can do with my problem.
I have an application that functions as a shell around other applications such as Excel, Solidworks etc. After building the project there are a lot of dlls in the bin catalog many who have to do with me upgrading the application from VB 6.0 to .Net. I quess all these files are necessarry for another user to be able to run my application? Is there a way that I can package these together with my .exe? So that the user need only use the .msi installation?
Best regards
Pål Eilertsen
|
|
|
|
|
I guess u are using the deployment project using VS.NET itself. If that is the case, you can include the dependent files in your installation. The option allows you to do so...
There is nothing new under the sun,
But there are lots of old things we don't know.
- Ambrose Bierce
|
|
|
|
|
Hi,everyone.New in this forum to stay
situation
I have a form with three(3) panes and an array of usercontrols(various labels on it)
question
I need to click on label1 of the control(controls because of the array) with diferent answers deppending on which pane the control is
ej.
mysusercontrol(0) is over panel1. If I click on label1 I want the backcolor to be blue.If a move the mysusercontrol(0) to panel2 then if I click on label1 with the left button I want to change the label1.text and if myusercontrol(0) is on pane3 I want the control to be destroyed.
I can do it for myusercontrol(0) but I want to do it for all the controls of the array(mysuercontrol(1) etc,)
kind regards
|
|
|
|
|
Is you myUserControl() a child of these panels or is it just sitting on top of them as a child of the Form?
If it's a child of the panel, then you should be able to get it's parent or container property to determine which panel currently owns your control. If it's not a child of the panel, then your going to have to use the position coordinates of your myUserControl and compare them to the coordinates and size of the panel controls to determine which one the control is sitting on top of.
RageInTheMachine9532
"...a pungent, gastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
'this code go in the form_load
Dim mycontrolarray As arraycontrol
mycontrolarray = New arraycontrol(Me)
For i = 0 To j - 1
mycontrolarray.AddNewUsercontrol()
mycontrolarray(i).getdata = arrText(i)
mycontrolarray(i).Parent = Panel5
mycontrolarray(i).Top = Panel5.Height - 35 * (i + 1)
Next
mlabel01 = mycontrolarray(0).Label1
mlabel010 = mycontrolarray(0).Label10
mlabel11 = mycontrolarray(1).Label1
mlabel110 = mycontrolarray(1).Label10
Private Sub mlabel01_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles mlabel01.MouseDown
Select Case e.Button
Case MouseButtons.Left
If Panel3.Contains(mycontrolarray(0)) Then
mycontrolarray(0).Label1.BackColor = Color.Yellow
mycontrolarray(0).Label1.ForeColor = Color.Black
End If
End Select
End Sub
As you can see I can manage the situation for a single control in this case mycontrolarray(0) but I need to manage all of them with a single sub as they all the same.
|
|
|
|
|
marisolera wrote:
mycontrolarray.AddNewUsercontrol()
mycontrolarray(i).getdata = arrText(i)
mycontrolarray(i).Parent = Panel5
mycontrolarray(i).Top = Panel5.Height - 35 * (i + 1)
Next
This is illegal. There are no such things as control arrays in VB.NET like there were in VB6.
Besides, you doing two very different things here:
First, you define mycontrolarray as a single instance of type arraycontrol:
Dim mycontrolarray As arraycontrol
mycontrolarray = New arraycontrol(Me)
I have no idea what your doing with the Me reference because it's handled by the constructor code inside your arraycontrol code.
Then, later you refer to mycontrolarray as an indexed array:
mycontrolarray(i).getdata = arrText(i)
mycontrolarray(i).Parent = Panel5
mycontrolarray(i).Top = Panel5.Height - 35 * (i + 1)
This is illegal since your now trying to treat the mycontrolarray as an array and it wasn't defined as such.
Secondly, you have an event handler for a label that doesn't exist on the form. mlabel01 appears to be a member of arraycontrol. The event must be handled inside the control and not on the form.
RageInTheMachine9532
"...a pungent, gastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
On the one hand you see , and by the other, what can I tell you.
And nevertheless it moves....
See Control arrays in Visual Basic .Net in Microsoft Knowledge base.
|
|
|
|
|
dave,I sent you the code as marisolera because this is my name for the work
|
|
|
|
|
I am tring to use the copymemory API and it works fine in vb 6.0 but when i try to use this in .net. It throws an error and says that the object is not set to an instance. The API is declared in a public module and you do not have to instance a module. Well i never have and i have also tried putting the call in the class im working in. Still nothing. I have tried many variations in the coding of the call to satisfy its needs but always throws the error. This is the code that i am using in .NET
<br />
Public Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As Int32, ByRef Source As Object, ByVal Length As Int32)
This is the code that works in 6.0
Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByRef Destination As Any, ByRef Source As Any,ByVal Length As Long)
And also is the a equivalent of the any data type in 6.0 Object in a manner of speaking.
|
|
|
|
|
Yout want to post the code that is throwing the exception, including the above declare. What your doing will not directly translate to VB.NET simply because your trying to marshal managed objects to unmanaged code.
We need the code, including what your trying to copy, and what your trying to accomplish in order to help you. There may be alternative ways to do this...
RageInTheMachine9532
"...a pungent, gastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
This is a blowfish cryptography program that i am converting from 6.0 to .NET.
This project has to 2 Textboxs "Textbox1, Textbox2" 2 buttons "btnEncrypt, btnDecrypt" and 1 active X control named VbCryptoEngine1
This program begins when you click the encrypt button
Private Sub btnEncrypt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEncrypt.Click<br />
<br />
VbCryptoEngine1.CryptAlgorithm = CryptoEngine.vbCryptoEngine.enuCRYPTO_ALGORITHMS.acuBlowfish<br />
TextBox2.Text = VbCryptoEngine1.EncryptString(TextBox1.Text, VbCryptoEngine1.key, True)<br />
End Sub
The VbCryptoEngine1.CryptAlgorithm line works fine it sets a new instance of the class blowfish where all the code for blowfish is contained.
the function EncryptString looks like this
Public Function EncryptString(ByVal Text As String, Optional ByVal Key As String = "Cool", _<br />
Optional ByVal OutputInHex As Boolean = False) As String<br />
<br />
On Error GoTo errhandler<br />
If m_status = STAT_BUSY Or m_status = STAT_ERROR Then Exit Function<br />
EncryptString = <font color = "blue">m_Engine</font>.EncryptString(Text, Key, OutputInHex)<br />
m_status = STAT_READY<br />
RaiseEvent statuschanged(m_status)<br />
Exit Function<br />
errhandler:<br />
<br />
' ---------------------------------------------------------------------------<br />
' Raise friendly error to the handler<br />
' ---------------------------------------------------------------------------<br />
Call Err.Raise(Err.Number, "CryptoEngine:EncryptString", Err.Description)<br />
<br />
End Function
I was going to us the try catch statement but i will update code later.
the line with the blue sends the string info and a few other variables to the instances blowfish class
The Function looks like this
Public Function EncryptString(ByVal Text As String, Optional ByVal Key As String = "Cool", Optional ByVal OutputInHex As Boolean = False) As String Implements IAlgorithm.EncryptString<br />
Dim ByteArray() As Byte<br />
<br />
Dim [unicode] As Encoding = Encoding.Unicode<br />
Dim ascii As Encoding = Encoding.ASCII<br />
Dim unicodebytes As Byte()<br />
Dim asciibytes As Byte()<br />
m_status = STAT_BUSY<br />
RaiseEvent statuschanged(m_status)<br />
<br />
On Error GoTo errhandler<br />
<font color = "blue">unicodebytes = [unicode].GetBytes(Text)</font><br />
Encoding.Convert([unicode], Encoding.Default, unicodebytes)<br />
ByteArray = unicodebytes<br />
<font color = "Red">Call EncryptByte(ByteArray, Key)</font><br />
unicodebytes = Encoding.Default.GetBytes(CStr(CObj(ByteArray)))<br />
Encoding.Convert(Encoding.Default, [unicode], unicodebytes)<br />
<font color = "blue">EncryptString = CStr(CObj(unicodebytes))</font><br />
If OutputInHex = True Then EncryptString = EnHex(EncryptString)<br />
m_status = STAT_READY<br />
RaiseEvent statuschanged(m_status)<br />
Exit Function<br />
errhandler:<br />
m_status = STAT_ERROR<br />
RaiseEvent statuschanged(m_status)<br />
' ---------------------------------------------------------------------------<br />
' Raise friendly error to the handler<br />
' ---------------------------------------------------------------------------<br />
Call Err.Raise(Err.Number, Err.Source, Err.Description)<br />
End Function
Im not cofindent that my conversions are working in the manner that they should but they are not throwing the exception. I need the conversions to do the same thing that the StrConv did to convert from unicode and also to unicode. The lines between the blue are the ones with the converstion. The line in red is what is throwing the exception
The function EncryptByte looks like this
Public Function EncryptByte(ByVal ByteArray() As Byte, Optional ByVal Key As String = "Cool") As String<br />
<br />
<br />
Dim Offset As Long, OrigLen As Long, LeftWord As Long, RightWord As Long, CipherLen As Long, CipherLeft As Long, CipherRight As Long, CurrPercent As Long, NextPercent As Long<br />
<br />
If (Len(Key) > 0) Then Key = Keys<br />
OrigLen = UBound(ByteArray) + 1<br />
CipherLen = OrigLen + 12<br />
If (CipherLen Mod 8 <> 0) Then CipherLen = CipherLen + 8 - (CipherLen Mod 8)<br />
ReDim Preserve ByteArray(CByte(CipherLen - 1))<br />
<br />
<font color = "Red">call CopyMemory(CInt(ByteArray(12)), CInt(ByteArray(0)), CInt(OrigLen))</font><br />
Call CopyMemory(CInt(ByteArray(8)), CInt(OrigLen), 4)<br />
Call Randomize()<br />
Call CopyMemory(ByteArray(0), CInt(2147483647 * Rnd()), 4)<br />
Call CopyMemory(ByteArray(4), CInt(2147483647 * Rnd()), 4)<br />
<br />
For Offset = 0 To (CipherLen - 1) Step 8<br />
Call GetWord(CInt(LeftWord), ByteArray, Offset)<br />
Call GetWord(CInt(RightWord), ByteArray, Offset + 4)<br />
LeftWord = LeftWord Xor CipherLeft<br />
RightWord = RightWord Xor CipherRight<br />
Call EncryptBlock(LeftWord, RightWord)<br />
Call PutWord(LeftWord, ByteArray, Offset)<br />
Call PutWord(RightWord, ByteArray, Offset + 4)<br />
CipherLeft = LeftWord<br />
CipherRight = RightWord<br />
If (Offset >= NextPercent) Then<br />
CurrPercent = CInt((Offset / CipherLen) * 100)<br />
NextPercent = CInt((CipherLen * ((CurrPercent + 1) / 100)) + 1)<br />
RaiseEvent Process(CurrPercent)<br />
End If<br />
Application.DoEvents()<br />
Next<br />
<br />
If (CurrPercent <> 100) Then RaiseEvent Process(100)<br />
Exit Function<br />
errhandler:<br />
<br />
' ---------------------------------------------------------------------------<br />
' Raise friendly error to the handler<br />
' ---------------------------------------------------------------------------<br />
Call Err.Raise(Err.Number, Err.Source, Err.Description)<br />
End Function
The line in red is when it throws the exception back to the function EncryptString{^} and says that Object reference not set to an instance of an object. The API call is in a public module class along with many public varibles that you do dont see declared. Any help with be appreciated. Just tell me if anyone needs anything else.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
