Inserting contents of text boxes into database?

[PaulBinCT]

First of all, I'll a real beginner so please excuse any incorrect terminology or other signs of stupidity...

I'm trying to write my first Windows app. This is a system for tracking tool inventory for large contractors that have multiple crews. I have tables for things like: Inventory items, manufacturers, assigned users. I also created a whole bunch of forms for things like entering new tools, manufacturers, and assigned users. Each of these forms has buttons for example when a new entry is made to "Add this tool to inventory". I'd rather, if possible, not use the "prepackaged" views for my forms such as grid or detail view. In addition, I'd rather not use the menu strip for the navigator to do things like add the new record. For the life of me, in spite of watching a dozen or so MS videos, and looking through 2 books as well as Googling, I can't find any reference for doing this.

Example: I fill in a form for a new manufacturer with name, address, phone. I have a corresponding table with those fields. I have the above referenced button that should write this entry into my dataset and then resolve it back to the database. Ummmmmmmm... how?

Lastly...if I haven't exhausted your collective patience... In an ideal world I'd like the "name" field in the above case (for instance) to incorporate an autocomplete so that if the manufacturer was already in the table, it would appear as typing begins, both to avoid duplication and to allow changes of addresss, etc.

Sighhhhhhhhh... much obliged for any help!

Paul

(Edit: Sorry, I'm sure I should have mentioned I'm using VS2008, so I'm doing this in VB2008 and SQL Server 2008)

[Hack]

Welcome to the forums.

I always use SQL when manipulating data with a database. Here is an example INSERT...modify as necessary

Private Sub btnAddNewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNewRecord.Click
'then run your insert
Dim sSQL As String
      
sSQL = "INSERT INTO YourTable (Field1, Field2, Field3, Field4, Field5) "
sSQL = sSQL & "VALUES (@FirstField, @SecondField, @ThirdField, @FourthField, @FifthField)"
Dim command As New SqlCommand(sSQL, myConnection)
command.Parameters.AddWithValue("@FirstField", TextBox1.Text)
command.Parameters.AddWithValue("@SecondField", TextBox2.Text)
command.Parameters.AddWithValue("@ThirdField", TextBox3.Text)
command.Parameters.AddWithValue("@FourthField", TextBox4.Text)
command.Parameters.AddWithValue("@FifthField", TextBox5.Text)
command.ExecuteNonQuery()
End Sub

"myConnection", of course, is your connection object.

[PaulBinCT]

Gulp...

Well, thanks for the welcome and the help. I thinkkkkkkkk I get the gist of it, but I've never used SQL. I'm guessing I also make the following substitutions:

Replace "YourTable" with the name of mine
" " Field1 with one of my table field names?
" " @FirstField with... the corresponding field name, again from my
table?

And that I rearrange the text box instances to correspond with the correct text boxes for each field?

Sorry to tax your patience! Hopefully once I get this working, I can muddle my way through adapting it to my other updates, deletions, etc..

Paul

[dee-u]

You are correct on your assumptions Paul, have a go for it!

[PaulBinCT]

OK... I guess I'm not as smart as I thought, or I'm clearly in way over my head. I even went out and bought a book on beginning SQL Server 2008. I tried pasting the code into my VB code view and before I can even get to rearranging the text box fields, I get an error that says "type sqlcommand is not defined". So, looking through the book I just got, I tried replacing "sqlcommand" with "OleDb.OleDbCommand" which seemed to clear that error. However, I copied the connection string by starting the new connection window, selecting use existing connection and copied it, which was:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Tool_Tracker.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

and that doesn't seem to be acceptable. I tried deleting "Data Source" as a guess and a few other things but the string doesn't seem to be correct.


OK... I'm completely humiliated by these questions but short of jumping off the roof or standing in the corner with a dunce cap I don;t know what else to do...

Thanks

Paul

[dee-u]

That connectionstring is for SQL Server Express, have a look at www.connectionstrings.com for the appropriate connectionstring for SQL Server 2008.

[PaulBinCT]

OK... back to report...

Nothing I've tried gives me a connecction string that is acceptable. I am using SQL Express. I've tried copying the string that appears in the server explorer property window, as well as starting the new connection wizard and copying the string that appears there under existing strings. Neither works. Beyond that the code suggestion nicely given previously seems to generate a number of other errors. I really can't believe this is so hard... any other suggestions are of course welcome

Here's the closest I've gotten so far to making this work, garnered from other forums, videos and advice:

____________________________________________________________

Imports System.Data.SqlClient
Public Class addMfgForm


Private Sub addMfgForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load




End Sub

Private Sub addMfgcompleteBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addMfgcompleteBtn.Click

Using conn As New SqlConnection(Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Tool_Tracker.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True)

Using cmd As New SqlCommand("INSERT INTO Makes(Maker_Name, Address,Phone,mfgNotes) VALUES (Textbox1.text, Textbox2.text, Textbox3.text, Textbox4.Text)", conn)
conn.Open()

End Using
End Using


Dashboard.Show()
Me.Hide()

End Sub

End Class

_____________________________________________________________

This generates the following errors:

1 'System.Data' is a namespace and cannot be used as an expression.

2 Comma, ')', or a valid expression continuation expected.


May well be others, but those are the only 2 so far

Sighhhhhhhhhhhhhhhhhhh

[dee-u]

Have you tried this for SQL Server 2008? Or are you using an Express edition?

[GaryMazzone]

You have issues with your SQL command also

Using cmd As New SqlCommand("INSERT INTO Makes(Maker_Name, Address,Phone,mfgNotes) VALUES (Textbox1.text, Textbox2.text, Textbox3.text, Textbox4.Text)", conn)


This should be written using Parameters or like this

Using cmd As New SqlCommand("INSERT INTO Makes(Maker_Name, Address,Phone,mfgNotes) VALUES ('" & Textbox1.text & "','" & Textbox2.text & "','" & Textbox3.text & "','" & Textbox4.Text& "')", conn)

The textbox.text needs to be outside the string statement so that you pick up the values of the data in the textboxes

[PaulBinCT]

Yes I have tried that Dee... I am using Express but I even following the syntax they give doesn't work. Plus, I'd assume (maybe incorrectly) that when I C/P the connection string established by the connection wizard it's already formatted correctly no? In the current state, it "under lines" the "Data Source" portion of the string and flags it as I noted... wth???

Gary, thanks for that. Even for me that was a rookie mistake that hopefully I'd have figured out quickly when all my fields were filled in with "Textbox1.text"

[techgnome]

took me a sec... but the reason with your connectionstring is that as is... it isn't a string....

Need to put quotes around it to make it a string:
Using conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Tool_Tracker.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

-tg

[PaulBinCT]

OH FOR GOD'S SAKE... sighhhhhhhhh...yes it IS called a connection STRING

OK, bear with me just a littleeeeeee bit longer.

After inserting the last suggestion:

Using cmd As New SqlCommand("INSERT INTO Makes(Maker_Name, Address,Phone,mfgNotes) VALUES ('" & Textbox1.text & "','" & Textbox2.text & "','" & Textbox3.text & "','" & Textbox4.Text "')", conn)

I have the following errors remaining:

1 ) Argument not specified for parameter 'connection' of 'Public Sub New(cmdText As String, connection As System.Data.SqlClient.SqlConnection, transaction As System.Data.SqlClient.SqlTransaction)'.


2) Comma, ')', or a valid expression continuation expected.

3) Value of type 'System.Data.SqlClient.SqlConnection' cannot be converted to 'System.Data.SqlClient.SqlTransaction'.

OK, #2 I can probably figure out for myself (probably) once my eyeballs aren't melting. But 1 and 3... ummmmmmmmmm what?

Seriously... isn't this supposed to be simpler? Feeling very discouraged...

[techgnome]

they are the product of the same problem.... you've gotten your quote marks out of synch some how ... easy to do.... one thing that jumps out at me is that after TextBox4.Text.... you're missing an & ... jsut before the quotes there.... put one in (So that it will concatenate the string)... and see how many errors that clears up (I've seen a single misplaced comma generate hundreds of errors before) ... take things one at a time, and it'll all fall into plac.

If you haven't yet, look at the DB FAQ in the Database Forums section, the one written by si_the_geek... it's a collection of tutorials and posts and other odds and ends (including different types of quircks) that we've collected over the years. He's done a bang up job putting it all together and maintaining it.

-tg

[ecowar]

 
strSQLInsert = "INSERT INTO tblPrint(txtProjectNum, txtPathNum, dtmPrintDate, blnBillable, lngTotalPages, lngSlipSheets) VALUES ('" & strProjNum & "','" & strPathNum & "','" & dDate & "','" & blnPayday & "'," & lngPageCount & "," & lngSlipCount & ")"

cmdCounts.ActiveConnection = cnPrintTotals
cmdCounts.CommandText = strSQLInsert
cmdCounts.Execute

This is not an attempt to hijack this thread. I'm posting here because the INSERT string being used is the closest to what I am using. The table I am inserting into has 6 fields and the data types for fields 1 - 6 are:
text, text, date, boolean, number (long), number (long). I keep getting a "Data Type Mismatch in Expression" error once it gets to cmdCounts.Execute. I am using VB6 and Access 2003.

[techgnome]

Here's the problem... now there's noise and clutter in this thread that doesn't have anything to do with the original posting.
That said, we're here now, so let's make the best of it. First, in Access (and most SQL for that matter) the ' signifies a STRING.... sooo... if you are inserting a NUMBER, it's not a string is it? So don't try to insert it like it is one. Secondly, in Access, # is used to define a date, like this #1/1/2009# ... again, it isn't a string, don't try to insert it like one. Lastly, regarding strings.... I'm a big advocate for dumping concatenated SQL.... Using parameterized queries go a looooong way to solving these kinds of problems as well as help protect against SQL Injection (where some one hijacks your sql statement and runs malicious code). Our url=http://vbforums.com/showthread.php?t=337051]Database FAQ section [/url] has some threads and tutorials on how to do this.

-tg

[dee-u]

So let's make the best of it, you can also have a look at the 'Add Records' link in my sig for the different methods of inserting records to a database.

[Hack]

And, for an INSERT, UPDATE, DELETE etc, you do not use commandobject.Execute or commandobject.Open

You use commandobject.ExecuteNonQuery