Select Command in VB.Net GUI form to relate with SQL Server

[ericanz]

Interview Application

Hi,

I want to create a multiple form application in VB, but want the data entered when the application is run to be exported elsewhere and saved as a file. I need the file to be in a format that can be printed with a decent looking layout, but also to be able to be emailed around and used by various users.

What are your suggestions for various ways I could do this? Should I be making an XML file and then altering it after the initial application has got the data? Or should I link the application directly to MS Word or something? The application is to be an interview in which I need the answers to questions to be exported so that they can be read by my company but are also in a professional format for customers to view.

I apologise if this is world's most retarded question, if you've linked info elsewhere, or if this is in the wrong place. I did look. Thanks.

 

[JuggaloBrotha]

rtf files can provide some nice formatting and rtf is supported by all word processors
(and no, the windows notepad is not a word processor)

 

[fie]

Hi, could anyone help pls. I am just learning VB.Net and trying to design an application. I have designed a form with 3 fields (txtUserID, txtUserName, txtPassword) to Add records using Insert command to a table (password) in SQL Server. This was successful.
Now I used Select command to confirm if a record actually exist before adding to avoid duplication of entries, but it has not been working, my codes are as follows please:

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
If Trim(txtUserID.Text <> "" AndAlso txtUserName.Text <> "" AndAlso txtPassword.Text <> "") Then
UserOleDbConnection.Open()
UserOleDbDataAdapter.SelectCommand.CommandText = _
"SELECT * FROM tblpassword WHERE " & _
"UserID = '" & txtUserID.Text & "'," & _
"UserName = '" & txtUserName.Text & "'," & _
"Password = '" & txtPassword.Text & "'"

UserOleDbDataAdapter.SelectCommand.ExecuteNonQuery ()
MessageBox.Show("Record Already Exist", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
UserOleDbConnection.Close()
Else
'create first the SQL query to insert a row
UserOleDbConnection.Open()
UserOleDbDataAdapter.InsertCommand.CommandText = _
"INSERT INTO tblpassword(userid, username, password)VALUES('" & txtUserID.Text & "', '" & txtUserName.Text & "', '" & txtPassword.Text & "')"
'send Data
UserOleDbDataAdapter.InsertCommand.ExecuteNonQuery ()
MessageBox.Show("Entry successful", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
UserOleDbConnection.Close()
End If
End Sub


Secondly please, how do I make whatever is entered in the GUI txtpassword.Text to appear as XXXXXXXXXXX to the user but the characters entered are stored in the database.

Thank You for your response

fie

 

[TechGnome]

The select is syntaticaly wrong...
"SELECT * FROM tblpassword WHERE " & _
"UserID = '" & txtUserID.Text & "'," & _
"UserName = '" & txtUserName.Text & "'," & _
"Password = '" & txtPassword.Text & "'"

In the WHERE clause, you don't use commas to separate the conditions. You use "AND" or "OR"

"SELECT * FROM tblpassword WHERE " & _
"UserID = '" & txtUserID.Text & "' AND " & _
"UserName = '" & txtUserName.Text & "' AND " & _
"Password = '" & txtPassword.Text & "'"

-tg

 

[fie]

Thanks I appreciate your prompt response but I have corrected that but I am still having problem with the line
dReader = UserOleDbDataAdapter.SelectCommand.ExecuteNonQuery()
Please see how I have it.


Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
If Trim(txtUserID.Text <> "" AndAlso txtUserName.Text <> "" AndAlso txtPassword.Text <> "") Then
'clear Dataset from last operation
UserDataSet.Clear()
'Create SQL query to find contact with specified field
Dim dReader As System.Data.OleDb.OleDbDataReader
UserOleDbDataAdapter.SelectCommand.CommandText = _
"SELECT * FROM tblpassword WHERE " & _
"UserID = '" & txtUserID.Text & "' AND " & _
"UserName = '" & txtUserName.Text & "' AND " & _
"Password = '" & txtPassword.Text & "'"
UserOleDbConnection.Open()
dReader = UserOleDbDataAdapter.SelectCommand.ExecuteNonQuery()
If dReader.HasRows Then
MessageBox.Show("Record Already Exist", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
UserOleDbConnection.Close()
Else
'create first the SQL query to insert a row
UserOleDbConnection.Close()
'UserOleDbConnection.Open()
UserOleDbDataAdapter.InsertCommand.CommandText = _
"INSERT INTO tblpassword(userid, username, password)VALUES('" & txtUserID.Text & "', '" & txtUserName.Text &
"', '" & txtPassword.Text & "')"
'send Data
UserOleDbDataAdapter.InsertCommand.ExecuteNonQuery()
MessageBox.Show("Entry successful", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
UserOleDbConnection.Close()
End If
End If
End Sub

 

[TechGnome]

Well that's your problem....
dReader = UserOleDbDataAdapter.SelectCommand.ExecuteNonQuery ()

ExecureNonQuery does just that: Executes a command with no query.... it's not going to return any thing.

If you need a DataReader, then you should .ExecuteReader instead.

-tg

 

[Blokz]

When checking to see if the userID etc already exists, you process the query but do not check to see if it returned anything.

Since you are just checking to see if the userID already exists, try this:

If Trim(txtUserID.Text <> "" AndAlso txtUserName.Text <> "" AndAlso txtPassword.Text <> "") Then
 UserOleDbConnection.Open()
Dim myCmd as New OleDbCommand("SELECT * FROM tblpassword WHERE " & _
             "UserID = '" & txtUserID.Text & "'," & _
             "UserName = '" & txtUserName.Text & "'," & _
             "Password = '" & txtPassword.Text & "'",UserOleDbConnection)
Dim myObject as object
myObject = myCmd.ExecuteScalar  ()
If Not myObject Is Nothing Then
MessageBox.Show("Record Already Exist", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            UserOleDbConnection.Close()
Else
...the rest of your code

Secondly, put the character you want for the password mask in the PasswordChar property for the textbox

HTH

Blokz

 

[fie]

Oh my God, what have I done wrongly again. Can't understand why the Insert command that was ok before is now giving problems again.

I am so sorry for bothering you. I must say thank you for your patient. I am so greatful I can see my fault gradually. I really really appreciate you. Thank you once again.

I wonder what has happened again to the line with the following command which was working and is being highlighted after correcting the the dReader

UserOleDbDataAdapter.InsertCommand.ExecuteNonQuery()

Please find below the codes:

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
If Trim(txtUserID.Text <> "" AndAlso txtUserName.Text <> "" AndAlso txtPassword.Text <> "") Then
'clear Dataset from last operation
UserDataSet.Clear()
'Create SQL query to find contact with specified field
Dim dReader As System.Data.OleDb.OleDbDataReader
UserOleDbDataAdapter.SelectCommand.CommandText = _
"SELECT * FROM tblpassword WHERE " & _
"UserID = '" & txtUserID.Text & "' AND " & _
"UserName = '" & txtUserName.Text & "' AND " & _
"Password = '" & txtPassword.Text & "'"
UserOleDbConnection.Open()
dReader = UserOleDbDataAdapter.SelectCommand.ExecuteReader
If dReader.HasRows Then
MessageBox.Show("Record Already Exist", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
UserOleDbConnection.Close()
Else
MessageBox.Show("Do you want to create another User?", "Error", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning)
'create first the SQL query to insert a row
UserOleDbConnection.Close()
UserOleDbDataAdapter.InsertCommand.CommandText = _
"INSERT INTO tblpassword(userid, username, password)VALUES('" & txtUserID.Text & "', '" & txtUserName.Text & "', '" & txtPassword.Text & "')"
'send Data
UserOleDbConnection.Open()
UserOleDbDataAdapter.InsertCommand.ExecuteNonQuery()
MessageBox.Show("Entry successful", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
UserOleDbConnection.Close()
ClearTextBoxes()
End If
End If
End Sub 'cmdAdd_Click

 

[fie]

Thanks I am been handled by another genius like you name TechGnome and I think am on the right part

I appreciate you.

Fie

 

[TechGnome]

What does it say that the error is?

-tg

 

[fie]

The error is
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

 

[TechGnome]

Hmm.... that's not helpful is it?

Try this:

Replace this line:

 UserOleDbDataAdapter.InsertCommand.ExecuteNonQuery ()

with this:

Try
   UserOleDbDataAdapter.InsertCommand.ExecuteNonQuery ()
Catch OleDBEx As System.Data.OleDb.OleDbException
  MessageBox OleDBEx.ToString
Catch Ex
  MEssageBox Ex.ToString
End Try

This will setup an error handler to catch any db specific errors and display them.

-tg

 

[fie]

This is what I did pls
Try
UserOleDbDataAdapter.InsertCommand.ExecuteNonQuery()
Catch Ex As System.Data.OleDb.OleDbException
MessageBox.Show(Ex.ToString)
Catch Ex As Exception
MessageBox.Show(Ex.ToString)
End Try

the error I got

System.Data.OleDbException: Statement has been terminated
String or Binary Data would be truncated at System.OleDataReader.NextResullts(ImultipleResults imultipleResults OleDBConnection Connection, OleDBCommand Command)
At System.Data.OleDB.OleDBCommand.ExecuteReaderInternal(CommandBehavior, behavior,String Method)
At System.Data.OleDB.OleDBCommand.ExecuteNonQuery()
at AR.frmUsers.CmdAdd_Click(objectsender, Eventargs e) in c:\myDocument\Visual Studio Projects\AR\frmusers.vb:line 238


I have checked the line 238 this is what I have there
UserOleDbDataAdapter.InsertCommand.ExecuteNonQuery()

 

[fie]

This is what I have in the table tblpassword
Column Name Data Type Length
UserID nvarchar 3
UserName nvarchar 8
Password nvarchar 50

 

[TechGnome]

Yeah... it's a problem with the SQL statement itself.... that's why I had you add the try catch in there, so we could see the real error from the database.

The key is this right here: "String or Binary Data would be truncated "....
Basicaly that's the dabase's way of say, "Hey, whoah there. You have a 10 pund bag there, but the space can only handle 5 pound bags."

I would suspect that either your username and/or password fields have a limit on them and one or the other (or both) is longer than what is allowed by the database.

Check the table definition and see what the max length on it is. Then to avoid the problem, set the MaxLength properties on your text boxes to the same values as what's in the DB. That will prevent the user from trying to add a username & password that is too long.

-tg