CODEWITHSUNDEEP
asp.netsqlvb.netms-access
Neth Munson
Neth Munson

Reputation: 57

Update Query using two tables asp.net vb

Please help me with this update query. I COMPLETELY understand how redundant this is but I NEED it. Upon users creating a User account which only contains the ID, Username, Password and CustomerID I must also have a record in Customers create itself but null everything and the User.User_Customer_ID must update to the newly created Customer.Customer_ID

I have everything working but this pesky update query. The ONLY thing it needs to do is change User.User_Customer_ID to Customer.Customer_ID I managed to story the User.User_ID in an integer called UserID and Customer.Customer_ID in an integer called CustomerID I have tried a million different ways with no success

Here is what one of the many queries I have tried.

1. UPDATE [User]
   SET User_Customer_ID = Customer.Customer_ID
   FROM Customer
   WHERE [User].User_ID = '16'
   AND Customer.CUstomer_ID = '19'
  ( ? would be the integer UserID and CustomerID )

Here is how it looks before it is inserted into the SQL command in VB

   UpdateUserCustomerID = "UPDATE [User] SET User_Customer_ID = Customer.Customer_ID FROM Customer WHERE ([User].User_ID ='" & UserID & "' AND Customer.Customer_ID = '" & CustomerID & "')"

Access Database / ASP.Net / VB

Table:

Customer:

Customer_ID
Customer_First_Name
Customer_Last_Name
Customer_Street_Address
Customer_City
Customer_State
Customer_Zip
Customer_Email

User:

User_ID
User_UserName
User_Password
User_Customer_ID

Upvotes: 0

Views: 1808

Answers (1)

Fionnuala
Fionnuala

Reputation: 91366

It seems pretty unlikely to me that the IDs are text fields (columns), if they are number fields, the quotes are one of the the problems.

As far as I can see, this:

"UPDATE [User] SET User_Customer_ID = Customer.Customer_ID FROM Customer WHERE ([User].User_ID ='" & UserID & "' AND Customer.Customer_ID = '" & CustomerID & "')"

Should be:

"UPDATE [User] SET User_Customer_ID = " & CustomerID _
& " WHERE ([User].User_ID = " & UserID

Upvotes: 2

Related Questions