[RESOLVED] Tableadapter Insert (or... LocalDB connection!???)

[Static]

grr.... this is driving me nuts!

I have the table adapter set.. added the insertnewcustomer "query"

and used this:

Try
                Dim TA As New POSDataTableAdapters.CustomersTableAdapter
                Dim TE As Integer = IIf(chkTaxExempt.Checked, 1, 0)
                Dim INS As Integer = TA.InsertNewCustomer(cboFirstName.Text, cboLastName.Text, txtCustBusiness.Text, txtAddress1.Text, txtAddress2.Text, txtCity.Text, txtState.Text, txtZip.Text, txtCustPhone_Home.Text, _
                                                        txtCustPhone_Cell.Text, txtCustPhone_Work.Text, txtExt.Text, txtEmail.Text, txtCustNotes.Text, TE)

                MsgBox(INS)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

no error.. msgbox INS gives the correct CustomerID (4) but nothing goes into the table!!

Could someone tell me what i am missing.. or tell me how to use regular SQL connections for a local DB?

Thanks!

[circuits2]

I assume you are talking about the SQL Express LocalDB? It uses the exact same connection methods as SQL and SQL Express.

I can give you a pretty simple example:

Dim sqlConn As SqlConnection = New SqlConnection(MyConnString)
        sqlCmd.Connection = sqlConn
 
        Try
 
            ' OPEN THE CONNECTION
            sqlConn.Open()
 
            ' EXECUTE THE COMMAND
            sqlCmd.ExecuteNonQuery()
 
            ' CLOSE THE CONNECTION
            sqlConn.Close()
 
        Catch ex As Exception
            Debug.WriteLine(ex.Message.ToString(), "SetData.NonQuery Error")
            Return False
        Finally
            If (sqlConn.State = ConnectionState.Open) Then
                sqlConn.Close()
            End If
            sqlCmd.Dispose()
            sqlConn.Dispose()
        End Try

Can you show us the code for your INSERT statement?

[Static]

this is the query created with the table adapter "insert"

INSERT INTO Customers
                         (CustomerFirstName, CustomerLastName, CustomerBusiness, CustomerAddress1, CustomerAddress2, CustomerCity, CustomerState, CustomerZip, 
                         CustomerPhone_Home, CustomerPhone_Cell, CustomerPhone_Work, CustomerPhone_WorkExt, CustomerEmail, CustomerNotes, TaxExempt)
VALUES        (@CustomerFirstName,@CustomerLastName,@CustomerBusiness,@CustomerAddress1,@CustomerAddress2,@CustomerCity,@CustomerState,@CustomerZip,@CustomerPhone_Home,@CustomerPhone_Cell,@CustomerPhone_Work,@CustomerPhone_WorkExt,@CustomerEmail,@CustomerNotes,@TaxExempt);  
SELECT CustomerID, CustomerFirstName, CustomerLastName, CustomerAddress1, CustomerAddress2, CustomerCity, CustomerState, CustomerZip, CustomerPhone_Home, CustomerPhone_Cell, CustomerPhone_Work, CustomerPhone_WorkExt, CustomerEmail, CustomerNotes FROM Customers WHERE (CustomerID = SCOPE_IDENTITY())

the problem is that it doesnt seem to actually insert it!

is there something that needs to be called after I insert?

[circuits2]

I think your INSERT Statement is wrong. You don't need to use the VALUES when you are adding fields from a SELECT statement.

Remove the VALUES portion and see if the INSERT works correctly.

[circuits2]

Here is an example of one of mine that works flawlessly:

INSERT INTO Customers_tbl (CuName_nvc, CuNumber_nvc)
SELECT DISTINCT a.CuName_nvc, a.CuNumber_nvc FROM Read_tbl a
WHERE a.Cust_id IS NULL

[Static]

Ok.. i figured out what was wrong. and.. it was lame. Running the app uses a different copy of the MDF file!!! so when i checked the data after, it was not there.... once i pointed the connection to the DB in the bin/debug folder i could see the change.

Good lord that is dumb! now i need to create a DB folder so its not flopping around on me...

(and yeah.. that select is odd lol its just pulling the new customer ID back but doesnt need all the extra stuff...)


EDIT: is there a way to put the MDF file in like.. C:\Data\Data.mdf and leave it there? sp its not copied?

[dunfiddlin]

it was lame

Indeed. But disturbingly common!!!!!!

[circuits2]

EDIT: is there a way to put the MDF file in like.. C:\Data\Data.mdf and leave it there? sp its not copied?

I'm not sure what you are asking...

[techgnome]

No it isn't lame... it makes sense.... I want to keep a clean, pristine copy of my database... if I insert junk data, I don't want that going out...
In my sig, there's a link "I swear I saved my data, where'd it run off to"... it describes what's going on and why, and why (or why not) you want to change that (and how to).

The bottom line is that there are two MDF files.. the development one and the "production" one. The development copy sits with your project, while the production one gets copied out to the folder and is what the app will connect to. you can force it to overwrite the production one each and everytime (good if your'e doing a lot of incremental structure changes) or not... downside of that is any data you put into the DB isn't going to persist... it will get cleared out each time... so sometimes you might want to overwrite it (structure changes) and other times you may not (you want to keep the data you've entered.)

-tg

[Static]

i did mess around with that and wound up having to start over with the tables! oops..

anyway.. it would be nice to run the app.. add data.. stop the app , then go into the data explorer and see the changes.. to make sure its all there.

so.. IS there a way to do this?

thanks!

[techgnome]

yes... you just need to connect to the right copy... try looking in the bin folder...

-tg

[Static]

i see the other copy... its these stupid tableadapters! lol im lost!!

Maybe i need to just dump the adapters and just go right for the mdf file with sql connectors.

[circuits2]

Maybe i need to just dump the adapters and just go right for the mdf file with sql connectors.

Are you dealing with small or large amounts of data in your transactions?

[circuits2]

If you post the code in TA.InsertNewCustomer then I can help you use something other than table adapters.

[Static]

thats not the issue.. i know how to do all the sql for db inserts/select/updates etc.. its the tableadapters. They are odd and i feel like i cant control them well.

hey Techgnome...IF i use sql connections to the MDF file.. will it still copy over to the bin folder (since the connections will be coded) ?

[circuits2]

We cant help you use them better if we cant see how you are using them to begin with...

[Static]

scroll up.. I already posted how i was using them.


wait.. is there a way to "view" an mdf file? then i can just look in the one in the BIN to make sure it worked? thats the whole issue.. i need to see that everything in the database added correctly....

[circuits2]

Your first post shows the code where you declare the tableadapter, it does not show us how you are using it. I think you are actually using the table adapter in TA.InsertNewCustomer, but it seems you are reluctant to show that code.

[Static]

...you want me to go into the data designer and pull that code out? its autogenerated by vs... I did not write it

but.. i have installed SSMS and can attach and view the db file now!!

[circuits2]

I'm glad you got it working.

By the way, this code:

TA.InsertNewCustomer(cboFirstName.Text, cboLastName.Text, txtCustBusiness.Text, txtAddress1.Text, txtAddress2.Text, txtCity.Text, txtState.Text, txtZip.Text, txtCustPhone_Home.Text, _
                                                        txtCustPhone_Cell.Text, txtCustPhone_Work.Text, txtExt.Text, txtEmail.Text, txtCustNotes.Text, TE)

Just calls a method of your table adapter. It's not something magical that you just send it random data and it produces your desired output. The method it what I was interested in. The code you have provided simply declares your table adapter and then calls a method. There is more to it than that.

Are you using a 3rd party library for your SQL functions? That would explain why you don't see what I am getting at...

[Static]

no i see what you are getting at.... no 3rd party anything. this is all right from vb.net 2012... i opened the data file.. added the query to the table adapter. and it did the rest.

Doesnt matter. i scrapped it.. the next insert query kept telling me my OrderID field didnt exist.. lol um its there. scrapped it... doing it all manually now.