|
listview1.FocusedItem.SubItems(0).Text
|
|
|
|
|
Hi
I am working on login form using VB 2005 and sql 2000 i made 2 text boxes and 2 buttons
on click on ok button it will cal the follwing function
EmployeeCode = txtEmployeeCode.Text
EmployeePassword = txtEmployeePassword.Text
ConnectionString = "Data Source=HAKMEH;Initial Catalog=HMS;Integrated Security=True"
Login(ConnectionString, EmployeeCode, EmployeePassword)
THE FUNCTION IS :
Public Sub Login(ByVal ConnectionString As String, ByVal EmployeeCode As String, ByVal EmployeePassword As String)
Dim ds As New DataSet
ds.Clear()
Dim MyConnection As New SqlConnection(ConnectionString)
Dim SelectQuery As String = "Select EmployeeCode,EmployeePassword from tblEmployees where EmployeeCode = ('" & txtEmployeeCode.Text & "') and EmployeePassword = ('" &_ txtEmployeePassword.Text & "')"
Dim adapter As New SqlClient.SqlDataAdapter
Dim MyCommand As New SqlCommand(SelectQuery, MyConnection)
MyConnection.Open()
Dim Command As String = MyCommand.ExecuteNonQuery
**** adapter.Fill(ds, "tblEmployees") ****
Dim dt As DataTable = ds.Tables(0)
If dt.Rows.Count <> 0 Then
frmAdministrator.Show()
Else
MsgBox("You are not Authorized to access")
End If
MyConnection.Close()
End Sub
when it comes to the line with stars it gave me the follwoing message
The SelectCommand property has not been initialized before calling 'Fill'.
so where is the error
Final Fantasy Maniac
|
|
|
|
|
You need to change all of this:
1 - your SQL retrieves the correct user password, so anyone who had access to the code, could find out the password.
2 - you're just inserting text from a textbox, which means someone who is not logged in can erase your entire database. Read up on SQL injection attacks.
The code looks wrtong, you create a command, open the connection manually, call ExecuteNonQuery ( this is definately wrong, as it doesn't return a result, why would you run it that way ? ), and then try to fill the dataste from the adapter ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hello i am new with vb 2005 and the programming world
and right now i dont care about SQL injection all what i want is run this form (Step by Step)
thanks
Final Fantasy Maniac
|
|
|
|
|
Hakmeh Mohannad wrote: and right now i dont care about SQL injection all what i want is run this form (Step by Step)
So no-one is ever going to use this form ?
Hakmeh Mohannad wrote: Hello i am new with vb 2005 and the programming world
Then this is a ridiculous thing for you to be working on. Buy a book on VB.NET that teaches the most basic concepts, and work through that before you start writing applications with login pages.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Christian Graus wrote: Hakmeh Mohannad wrote:
and right now i dont care about SQL injection all what i want is run this form (Step by Step)
So no-one is ever going to use this form ?
Hmmm, I think the OP should learn how to prevent sql injection attacks now, so he can develop careful coding habits...
|
|
|
|
|
Yeah - that's the point I wanted to make. Saying 'I just want this to work badly' doesn't cut it. I meant to drive home that what he's writing cannot be used, although I may have implied it was OK to write bad code in that case, I was assuming he expects to use it, and therefore would realise he needs to do it properly.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hakmeh Mohannad wrote: right now i dont care about SQL injection all what i want is run this form (Step by Step)
You should know how to prevent them so you can get in the habit if you are going to write code that access databases...
Preventing SQL Injection Attacks[^] is a great article on this site, and it is a fairly easy reading 
|
|
|
|
|
There's a bunch of problems here. Where do we start...
First, the connection string shouldn't be passed into functions that use SQL, unless you're doing something very special. A login check doesn't count. Have the connection string retrieved from a Shared method or just get a SqlConnection object from a Shared method. It should get the connection string itself.
Public Class SqlHelpers
Public Shared Function GetSqlConnection() As SqlConnection
Dim connStr As String = GetSqlConnectionString() ' You have to define this method!
Dim newConn As New SqlConnection(connStr)
Return newConn
End Function
Public Shared Function GetSqlConnectionString() As String
' You would normally get the connection string from the app.config file, or some other
' source. With a few changes, this could also support multiple connection strings for
' multiple databases and database types quite easily.
'
' In this example, we'll just return the string you have.
Return "Data Source=HAKMEH;Initial Catalog=HMS;Integrated Security=True;"
End Function
Now, moving on to the actual SQL stuff. You created a DataAdapter and a seperate command, executing the same SQL twice in two different ways. This is, of course, really bad practice. You don't use a DataAdapter for a query like this. All you want is a yes or no answer from the database. You don't retrieve the users ID and password from it!
Also, you made the typical newbie mistake in your SQL by using string concatenation to build your SQL statement, opening yourself to really bad SQL Injection attacks.
Use parameterized queries to block a bunch of injection attacks and it also does the job of escaping special characters for you.
You also didn't validate any of the data comming from the TextBoxes. Rule #1: NEVER trust any data comming from the user.
And you're storing passwords in clear text in the database. If your system is worth securing, secure the passwords from prying eyes, encrypt them! This isn't just a security risk to your app, but to every other system on your network too. People tend to use the same passwords for multiple systems. Don't give anyone a way of easily finding out what that password is and unlocking every system the user has access to.
A better SQL statement would be something like:
SELECT COUNT(UserId) FROM tblEmployees WHERE EmployeeCode=@EmployeeCode AND EmployeePassword=@Password
Note the two parameters with the "@" signs in front of them. They'll get fill in with this:
Public Function Login(ByVal EmployeeCode As String, ByVal EmployeePassword As String) As Boolean
Using conn As SqlConnection = GetSqlConnection()
Dim SelectQuery As String = "SELECT COUNT(UserId) FROM tblEmployees WHERE EmployeeCode=@EmployeeCode AND EmployeePassword=@Password"
Dim comm As New SqlCommand(SelectQuery, conn)
comm.Parameters.Add(New SqlParameter("@EmployeeCode", SqlDbType.NVarChar, 25)).Value = EmployeeCode
comm.Parameters.Add(New SqlParameter("@EmployeePassword", SqlDbType.NVarChar, 25)).Value = EmployeePassword
Dim result As Integer
conn.Open()
result = comm.ExecuteScalar
If result > 1 Then
' Something weird happened. We had more than one user with the same ID in the database!
' Yikes! Someone is going to get shot over this one! :(
' Handle this error however you want.
End If
' If the result came back 1, the login was successfull, otherwise it should be 0.
' So, If result = 1, return True to the caller so it knows the login was successfull.
Return (result = 1)
End Using
End Function
This is by no means a completely secure solution, and is, frankly, "hacked together", but it's stronger than what you had. You would still need to add encryption for the password, making it even stronger. As well as transferring the SQL to an Sql Stored Procedure.
-- modified at 9:04 Wednesday 30th May, 2007
|
|
|
|
|
Dave, that is a brilliantly comprehensive answer for a forum reply. Got my 5!
|
|
|
|
|
Who would have thought such a simple operation would be so complicated. 
|
|
|
|
|
Dave Kreskowiak wrote: Who would have thought such a simple operation would be so complicated.
If that is complicated, you should see the multi-threaded invitation-to-renew* plug-in that I'm writing just now for the document delivery system that I wrote last year.
* I work for an insurance company - As policies are about to expire we send documents inviting the policy holder to renew
|
|
|
|
|
Colin Angus Mackay wrote: If that is complicated, you should see the multi-threaded invitation-to-renew* plug-in
No, I just mean from the newbie's point of view. "Bang out a "simple" little login form - no problem!" - yeah right. Until you sit down and think about it. BOOM! Their brains explode forth like biscuit dough out of a can.
I'm quite familiar with the headaches, and rewards, of multithreading. The hard prt is explaining all the pitfalls to someone who has a hell of a time writing a login form!
|
|
|
|
|
Dave Kreskowiak wrote: No, I just mean from the newbie's point of view
Sorry, I mean "you" as in the general "you". English really needs a plural form of you. Like the Scots "youse"
Dave Kreskowiak wrote: BOOM! Their brains explode forth like biscuit dough out of a can.
That's quite an image. I have a very vivid vision of that in my head now.
|
|
|
|
|
Any pointers/samples on how I can pass a string from a Vb.NET dll to a C++ dll and vice versa would be greatly appreciated.
Thank you
|
|
|
|
|
That depends on what the C++ code is expecting, l;ike what type of string and if your expect that string to be modified by the code your calling and how you're calling it. This[^] is about the only guide that covers most of the possiblities.
|
|
|
|
|
Thank you for the reply and link, Dave.
The "C" dll passes a "char*" as input parameter to the VB.NET dll. The VB.NET dll "receives" this input param as a "String" object. This String object is not manipulated/modified by the VB.NET dll - essentially it is a path to a file that it reads and performs a base64 conversion. The base64 converted "String" object is then passed back to the "C" dll.
|
|
|
|
|
Hi Everyone:
I have set up a dataset that uses an auto increment field that is set to start at minus one and step by minus one when I add a new record to the dataset.
Example dataset:
1. Existing Record 1
2. Existing Record 2
3. Existing Record 3
4. Existing Record 4
5. Existing Record 5
The ID field is an auto increment field in SQL Server. To prevent accidental data corruption, whenever I add a new record to the dataset I start with -1. So the dataset will look like this when I add new records:
1. Existing Record 1
2. Existing Record 2
3. Existing Record 3
4. Existing Record 4
5. Existing Record 5
-1. 1st New Record
-2. 2nd New Record
-3. 3rd New Record
Here is my problem when I try to save the new record into the dataset I get the following error:
“Constraint Exception was unhandled. Column ‘ID’ is constrained to be unique. Value ‘4’ is already present.”
I don’t understand why this is happening. The dataset’s auto increment field should put a -1 on the new record I’m adding. Instead it looks like it is starting at the last record ‘5’, subtracts one to get four and is trying to place the new information in record four instead of creating a new record in the dataset and starting the ID number at -1.
What is going on?
Thank you,
Quecumber256
|
|
|
|
|
Actually, it should DBNull. The new record doesn't get an ID until it's written to the database and refetched.
You've been hammering away at this for so long, I have no idea why the ID is so important that it has to remain in order. Databases don't care about the order of records so why are you trying to get these in order?? Only when the records are displayed in some UI do they get any kind of ordering.
|
|
|
|
|
Dave,
I sincerely thank you for your help.
I hope I can explain my problem accurately enough so you can at least get an understanding of what I'm trying to do.
I think I mentioned in a previous post that I have a data entry form that has four text boxes data bound to the BindingSource control. The BingingNavigator control is bound to the BindingSource control to allow the user the ability to navigate through the dataset while it is stored in memory. I designed the form so I can edit existing records as well as add new ones during a session.
The dataset's schema is derived from the table the Dataset is recovering the information from. So in theory I have all four columns ID, Ordinal, Code and name in the dataset.
I'm using the SqlCommandBuilder to automatically generate the add and update queries to update the Database table. Edited records need the ID assigned from the database table before any changes made to them can be written to the database.
The new records need to be distingushed from the existing records by setting the ID column in the dataset as a negitively incrementing number. I got this idea from Microsoft MSDN.
So in theory if I edit an existing record and then add a new one. The DataSet knows which record was edited by its ID number while the new record is seperately distinguished by its negitive increment.
Unless you set the dataset's autoincrement column to start a -1 and step by -1 whenever you add a new record to the dataset it will start a 1 and if you already have a record with the ID of 1 that record will be updated with the new record's data. It thinks you are editing record number one, not adding a new one.
Does this help?
Quecumber256
|
|
|
|
|
Quecumber256 wrote: The new records need to be distingushed from the existing records by setting the ID column in the dataset as a negitively incrementing number. I got this idea from Microsoft MSDN.
No they don't. The records don't exist until they are in the database. Until then, they are potentially records.
Quecumber256 wrote: So in theory if I edit an existing record and then add a new one. The DataSet knows which record was edited by its ID number while the new record is seperately distinguished by its negitive increment.
The DataAdapter, not the DataSet, knows which record was added as opposed to edited by each DataRow's RowState property in each table. The adapter then uses the correct SqlCommand (SQL UPDATE, INSERT, DELETE) to update the database with the new data. It has nothing to do with the ID number you give the record.
Quecumber256 wrote: Unless you set the dataset's autoincrement column to start a -1 and step by -1 whenever you add a new record to the dataset it will start a 1
No, it won't. Any additions to the database are written without an ID number. The database assigns auto-incrementing ID's when the record is commited to the database. You really shouldn't be editing the ID number anyway unless you want to ADD the possibility of duplicate, or incorrect ID's.
|
|
|
|
|
Dave,
I know the DataSet is an Array type object. Whenever data is added to an empty array it starts at 0 and increments by one. So information in a DataSet before it is appended to the database looks like this:
0 Record 1
1 Record 2
2 Record 3
And so on.
The id column in the database will store it in this manner:
1 Record 1
2 Record 2
3 Record 3
and so on.
I must have missed something with the DataAdapter because when I tried to add a new record to the dataset after populating it with data returned from the database I got something like this:
1 <blank>
2 Record 2
3 Record 3
4 Record 4 <the added="" record="">
Can you tell me what I did wrong?
Quecumber256
|
|
|
|
|
Quecumber256 wrote: I know the DataSet is an Array type object.
Uhh, where did you see that?! DataSet implements a collection of DataTable objects as an implementation of the IListSource interface (among others), VERY different from an Array.
Have you just NOT put the ID numbers in??
BTW: A DataSet can't hold DataRows, only a DataTable can. DataSet is a collection of DataTables, which are collections of DataRows.
|
|
|
|
|
Honestly unless this app is going to be run disconnected to the sql server you should probably take a different approach. If you will always be connected to the sql server. I would pass the new record values into a stored procedure that will do the insert. You can have the stored procedure pass back the indentity that it just inserted, or you can just refresh the select query.
If your app can be run dissconnected, then perhaps you should look at adding a large number to your internal dataset, just make sure when the insert happens that it is null so that you get the correct auto increment id from sql server.
Hope that helps.
Ben
|
|
|
|
|
I want the app to run disconnected from the SQL Server database; thus the reason I want to use a DataSet.
I just responded to Dave, I hope the response I gave to him explains what I'm trying to accomplish.
I don't see a reson why I can't distingush a new record added to the dataset by assigning a negitive number to it in the auto increment column while the existing records in the dataset as positive.
Do you have any suggestions on how to approach this problem?
Thanks for you input,
Quecumber256
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
