I want to set up my button control...so that if my data reader.HasRows then i want it to UPDATE the rows....If my datareader.HasRows is False then i want it to Insert the new information to its new ROW....here is what i came up with any help would be fantastic
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim sqlconn As SqlConnection
Dim sqlcmd As SqlCommand
Dim uptcmd As SqlCommand
Dim dr As SqlDataReader
Dim cstring, str As String
sqlconn =
New SqlConnection(cnConnection)sqlconn.Open()
If dr.HasRows = False Then
cstring = "INSERT INTO tblNEW " & _
"VALUES (" & _
"'" & Pull_Quotes(txtWatchID.Text) & _
"','" & Pull_Quotes(txtcenter.Text) & _
"','" & Pull_Quotes(txtenvelope.Text) & _
"','" & Pull_Quotes(txtSenditem.Text) & _
"','" & Pull_Quotes(dtDateofPur.Text) & _
"','" & Pull_Quotes(txtTrans.Text) & _
"','" & Pull_Quotes(txtfname.Text) & _
"','" & Pull_Quotes(txtlname.Text) & _
"','" & Pull_Quotes(txtprdcode.Text) & _
"','" & Pull_Quotes(txtvalue.Text) & _
"','" & Pull_Quotes(datefail.Text) & _
"','" & Pull_Quotes(dtServiceRecieve.Text) & _
"','" & Pull_Quotes(txtfailurecode.Text) & _
"','" & Pull_Quotes(txtrepaircode.Text) & _
"','" & Pull_Quotes(txtservice.Text) & _
"','" & Pull_Quotes(txtwhere.Text) & _
"','" & Pull_Quotes(txtregisternum.Text) & _
"','" & Pull_Quotes(txtaddress.Text) & _
"','" & Pull_Quotes(txtcity.Text) & _
"','" & Pull_Quotes(txtstate.Text) & _
"','" & Pull_Quotes(txtzip.Text) & _
"','" & Pull_Quotes(txtareacode.Text) & _
"','" & Pull_Quotes(txtphonenum.Text) & _
"','" & Pull_Quotes(txtproductdesc.Text) & _
"','" & Pull_Quotes(dtshipdate.Text) & _
"','" & Pull_Quotes(txtworkbdone.Text) & _
"','" & Pull_Quotes(txtauthnumber.Text) & _
"'," & (txtlabor.Text) & _
"," & (txtPart.Text) & _
"," & (txtTaxcost.Text) & _
"," & (txtTotal.Text) & _
",'" & Pull_Quotes(txtnotes.Text) & _
"','" & Pull_Quotes(txtClient.Text) & _
"','" & Pull_Quotes(dtshipdate.Text) & "')"
MsgBox(cstring)
sqlconn =
New SqlConnection(cnConnection)sqlconn.Open()
sqlcmd =
New SqlCommand(cstring, sqlconn)sqlcmd.ExecuteNonQuery()
Clear_all()
sqlconn.Close()
sqlcmd.Dispose()
sqlconn.Dispose()
Elsestr = "UPDATE tblNew Set" & _
"watch_id = '%" & Pull_Quotes(txtWatchID.Text) & "%','" & _
"service_center_num = '%" & Pull_Quotes(txtcenter.Text) & "%','" & _
"repair_envelope = '%" & Pull_Quotes(txtenvelope.Text) & "%','" & _
"store_number = '%" & Pull_Quotes(txtwhere.Text) & "%','" & _
"date_purchase = '%" & Pull_Quotes(dtDateofPur.Text) & "%','" & _
"transaction_num = '%" & Pull_Quotes(txtTrans.Text) & "%'," & _
"cust_fname = '%" & Pull_Quotes(txtfname.Text) & "%'," & _
"cust_lname = '%" & Pull_Quotes(txtlname.Text) & "%'," & _
"product_code = '%" & Pull_Quotes(txtprdcode.Text) & "%'," & _
"value_watch = '%" & Pull_Quotes(txtvalue.Text) & "%'," & _
"failure_date = '%" & Pull_Quotes(datefail.Text) & "%'," & _
"service_rdate = '%" & Pull_Quotes(dtServiceRecieve.Text) & "%'," & _
"failure_code = '%" & Pull_Quotes(txtfailurecode.Text) & "%'," & _
"repair_code = '%" & Pull_Quotes(txtrepaircode.Text) & "%'," & _
"service_request = '%" & Pull_Quotes(txtservice.Text) & "%'," & _
"store_number_senditem = '%" & Pull_Quotes(txtSenditem.Text) & "%'," & _
"register_number = '%" & Pull_Quotes(txtregisternum.Text) & "%'," & _
"street_address = '%" & Pull_Quotes(txtaddress.Text) & "%'," & _
"city ='%" & Pull_Quotes(txtcity.Text) & "%'," & _
"zip_code = '%" & Pull_Quotes(txtzip.Text) & "%'," & _
"state = '%" & Pull_Quotes(txtstate.Text) & "%'," & _
"area_code = '%" & Pull_Quotes(txtareacode.Text) & "%'," & _
"phone_num = '%" & Pull_Quotes(txtphonenum.Text) & "%'," & _
"product_desc = '%" & Pull_Quotes(txtproductdesc.Text) & "%'," & _
"service_center = '%" & Pull_Quotes(dtshipdate.Text) & "%'," & _
"work_to_bdone = '%" & Pull_Quotes(txtworkbdone.Text) & "%'," & _
"auth_num = '%" & Pull_Quotes(txtauthnumber.Text) & "%'," & _
"labor_cost = '%" & Pull_Quotes(txtlabor.Text) & "%," & _
"parts_cost = '%" & Pull_Quotes(txtPart.Text) & "%'," & _
"tax_cost = '%" & Pull_Quotes(txtTaxcost.Text) & "%'," & _
"total_cost = '%" & Pull_Quotes(txtTotal.Text) & "%'," & _
"notes = '%" & Pull_Quotes(txtnotes.Text) & "%'," & _
"client_number = '%" & Pull_Quotes(txtClient.Text) & "%','" & _
"service_ship = '%" & Pull_Quotes(dtshipdate.Text) & "%','" & _
"ship_hand = '%" & Pull_Quotes(txtshiphand.Text) & "%' & _"
MsgBox(str)
uptcmd =
New SqlCommand(str, sqlconn)dr = uptcmd.ExecuteReader
Clear_all()
dr.Close()
sqlconn =
New SqlConnection(cnConnection)sqlconn.Open()
sqlcmd =
New SqlCommand(cstring, sqlconn)sqlcmd.ExecuteNonQuery()
End If End Sub
SQL Question??
DbHd
Kriske
You really need to go to the VB forums. If you want to know how to do it in T-SQL, this is the place, but you need to give us tables and data you are working with. We can show you the T-SQL to use, and then you can automate it using VB.
Looking at your code, there is no WHERE clause on the UPDATE statement you are generating, so it will just update all rows. This is how the statement ends:
"ship_hand = '%" & Pull_Quotes(txtshiphand.Text) & "%' & _"
MsgBox(str)
Note that your SET clause is going to end with &_ which is not T-SQL syntax either.
Oscar Piqueras
I don’t know what you want to do, but the code above makes no sense to me. YOu are declaring a datareader and open a connection, but you didn’t execute the datareader yet. So the dr will be null. The cnConnection (as I assume that is the connectionstring) is defined in cstring not the cnConnection (perhaps you declared some variable in the class itself )
Furtherone, I *wouldn't* use this concatenated queries, though it hard to maintain, and hard to read. another option would be to use a procedure rather than the insert query.
Furtherone you code looks a bit opverblown as some things can be deleted like redefining the connection everytime and opening it every time.
Just my two cents.
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
sjkowal
Ok sorry you guys gut bog down in my messy code but o well...what i am trying to do is
If i enter an ID if that ID has a row associated with it in SQL SERver i want it to pull that rows info and let you UPDATE your information...If the ID does not have a row already i want it to INSERT all the new data in a row
I thought you would use the datareader.hasrows property to do that since its a true false value Dont they defualt to false
If dr.HasRow Then
Update
Else
Insert
how is this done please help! thanks for the advice with the stored proedures i have not got good at those yet!! but have seen them in action
ZAky
I'm not sure what problem you're having above (first thing i notice is that there's no relation between dr and your connection), but do yourself a favor and put your TSQL logic into a stored procedure, and then have your VB call execute the stored proc. It makes your code look cleaner, and will be easier to change the proc instead of VB code in the future.