Need Help

[shakizil]

I am new to VB.Net and need some help creating a simple project. I am tryint to user select and insert queries to make it work but somehow nothing happens when i click on the button. Here is the code I am using to do so. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & ("C:\db1.mdb") End Sub Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click Dim searchcommand1 As String = "SELECT * FROM Change Control WHERE (Log number = 'txtlognum')" Private Sub btnsave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & ("C:\dblog.mdb") Dim InsertCommand1 As String = " INSERT INTO Change Control VALUES ('txtlognum','txtdtrqst','txttrgdt','txttime','txtrqstby','txtprojname','txtdscp','txtusers','txtdpt')" MsgBox("New entry has been added", vbOKOnly + vbExclamation, "Success") ---------------------------------------------------------------- On the left column under Server Explorere I can see all the a tables in my database which means I can connect to it. Can somebody please guide me. I don't know what am I doing wrong. Thanks.

[Dave Kreskowiak]

Well, your Select and Insert commands are going to choke because you can't have a space in a table or columns names without enclosing them in square brackets. You're not passing the values of your textboxes correctly either. Drop the code in the Form_Load event. It's useless. You're not even executing these SQL commands! You put together a SQL statement in a string, define a connection string, but then you don't use them to do any work! Of course nothing is going to happen! Also, DON'T concatenate strings together like this to build SQL statements. It's just bad practice and leads to difficult to find errors, and opens your code up for SQL Injection attacks.

Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
Dim searchcommand1 As String = "SELECT * FROM [Change Control] WHERE ([Log number] = " & txtLognum.Text
 
' You're missing the code to actually execute this statement against the database.
' You're also missing code to do something with the returned data, like display it in
' a datagrid!
End Sub
 
Private Sub btnsave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dblog.mdb"
Dim InsertCommand1 As String = "INSERT INTO [Change Control] VALUES " & _
"'" & txtlognum.Text & "','" & txttrgdt.Text & "','" & txttime.Text &_
"','" & txtrqstby.Text & "','" & txtprojname.Text & "','" & txtdscp.Text & _
"','" & txtusers.Text & "','" & txtdpt.Text & "')"
 
' You're missing the code to actually execute this statement against the database.
 
MsgBox("New entry has been added", vbOKOnly + vbExclamation, "Success")
End Sub

RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[Dave Kreskowiak]

From what I've seen in your code, you really need to go through some tutorials to show you the basics of what your doing. Try this[^]. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[shakizil]

You are so AWSOME!! Thanks for your help. I will try this but I am sure I will have more questions as I start getting the hang of it. Some how MSDN did not help. Shahid.;)

[Matthew Hazlett]

That will work, I would write it diffrently:Public Function SQLSafe(str as String) Return str.replace("'", "''") End Function Private Sub btnsave_Click_1(......) Handles btnsave.Click Dim Cmd as String = "INSERT INTO [Change Control] VALUES ('{0}','{1}','{2}','{3}','{4}',{5}','{6}','{7}','{8}')" Dim Exec = String.Format(Cmd, SQLSafe(txtlognum.Text), SQLSafe(txttrgdt.Text), SQLSafe(txttime.Text), _ SQLSafe(txtrqstby.Text), SQLSafe(txtprojname.Text), SQLSafe(txtdscp.Text), SQLSafe(txtusers.Text), SQLSafe(txtdpt.Text)) End Sub
Matthew Hazlett

[Dave Kreskowiak]

I'd modify it even further by scraping it and using parameterized stored procedures. I can't stand building SQL statements with strings and try to avoid it using all possible methods. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[shakizil]

Thanks guys. Actually that dotnetspider.com was helpfull but I now I am really stuck. I downloaded a sample application from there and helped me build most of my code. I created a bunch of functions within one big class. Now when I try to use the Insert query I get error msgs (Value of type 'String' cannot be converted to 'WindowsApplication1.Change'). Offcourse I am doing a lot of things wrong but I guess I am dumb enough to not know where the problem is. Here is the code for the SAVE button. Private Sub btnsave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click Dim ad As New Change txtlognum.Text = ad.LogNumber txtdtrqst.Text = ad.DateRequested txttrgdt.Text = ad.TargetDate ad.LogNumber = Change.Createdata("Change", "LogNumber") ad.DateRequested = Change.Createdata(txtdtrqst.Text) ad.TargetDate = Change.Createdata(txttrgdt.Text) ad.Time = Change.Createdata(txttime.Text) ad.RequestedBy = Change.Createdata(txtrqstby.Text) ad.ProjectName = Change.Createdata(txtprojname.Text) ad.ChangeDescription = Change.Createdata(txtdscp.Text) ad.Users = Change.Createdata(txtusers.Text) ad.Department = Change.Createdata(txtdpt.Text) MsgBox("New entry has been added", vbOKOnly + vbExclamation, "Success")--------------------------------------------------------------------- Here is my Class Public Class Change Public CategoryHardware As CheckBox Public categorySoftware As CheckBox Public CategoryNetwork As CheckBox Public CategoryDatabase As CheckBox Public CategoryTcom As CheckBox Public CagegoryFacilities As CheckBox Public RiskHigh As CheckBox Public RiskMedium As CheckBox Public RisLow As CheckBox Public NewProgram As CheckBox Public Maintnace As CheckBox Public Problem As CheckBox Public Enhancement As CheckBox Public Other As CheckBox Public LogNumber As Int16 Public DateRequested As DateTime = Nothing Public TargetDate As DateTime = Nothing Public Time As String Public RequestedBy As String Public ProjectName As String Public ChangeDescription As String Public Users As String Public Department As String Public TypeEr As String Public TypeException As String Public TypeNormal As String Public Description As String Public Completed As CheckBox Public Initials As String

[Dave Kreskowiak]

I would HIGHLY suggest dropping this for now and picking up a book on VB.NET for Beginners. You've got a TON of misconceptions floating around this code and almost as much code MISSING. You still don't have any code in here to actually read/write anything to the database! I already posted a link to something that will show you the basics of database access. READ IT[^]! RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

[shakizil]

I guess you are right. I will dig more on that website to get help. Thanks.