cstring = cstring + "VALUES('%" + txtWatchID.Text + "%','%" + txtcenter + "%'" & _
cstring = "INSERT INTO tblNEW (watch_id, service_center_num, repair_envelope, store_number"
cstring = cstring + "date_purchase, transaction_num, cust_fname, cust_lname, product_code"
cstring = cstring + "value_watch, failure_date, service_date, failure_code, repair_code"
cstring = cstring + "service_request, store_number_senditem, register_number, street_address"
cstring = cstring + "city, state, zip_code, area_code, phone_num, product_desc, service_center"
cstring = cstring + " work_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_cost"
cstring = cstring + "notes, client_number)"
cstring = cstring + "VALUES('%" + txtWatchID.Text + "%','%" + txtcenter + "%'" & _
this is the error is get, but i did the same thing on a select statement and it works fine...do i need to add something to the string or what i am kinda confused and help would be great.....
Operator '+' is not defined for types 'String' and 'System.Windows.Forms.TextBox'.
It should probably be "txtcenter.Text" and not "txtcenter". It is the second control you use to concatenate the value with.
|||Well, this is realy not a SQL Server question, but...
my guess is that:
"%','%" + txtcenter + "%'" & _
txtcenter is a textbox and you must do something more like:"%','%" + txtcenter.text + "%'" & _
Also, you seem to have ignored what everyone told you earlier about injection attacts and text entry because you arent using quotename (or Pull_Quotes from your example code.)
Louis
|||This is a function i wrote that takes care of the quotes, but what do you mean about injection attacts? and where can i read about what an attacts is? i have seemed to miss that part of SQL do you have any links for that topic, and this is how i have the INSERT now will it work this way or was i better off keeping it the other way?
Public Function PrepareStr(ByVal strValue As String) As String
If strValue.Trim() = "" Then
Return "NULL"
Else
Return "'" & strValue.Trim() & "'"
End If
End Function
cstring = "INSERT INTO tblNEW (watch_id, service_center_num, repair_envelope, store_number"
cstring = cstring + "date_purchase, transaction_num, cust_fname, cust_lname, product_code"
cstring = cstring + "value_watch, failure_date, service_date, failure_code, repair_code"
cstring = cstring + "service_request, store_number_senditem, register_number, street_address"
cstring = cstring + "city, state, zip_code, area_code, phone_num, product_desc, service_center"
cstring = cstring + " work_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_cost"
cstring = cstring + "notes, client_number)"
cstring = cstring + "VALUES PrepareStr(txtWatchID.Text),PrepareStr(txtCenter.Text),PrepareStr(txtenvelope.Text),PrepareStr(txtSenditem.Text)" & _
cstring = cstring + "PrepareStr(dtDateofPur.Text), PrepareStr(txtTrans.Text), PrepareStr(txtfname.Text), PrepareStr(txtlname.Text),PrepareStr(txtprdcode.Text)" & _
cstring = cstring + "PrepareStr(txtvalue.Text), PrepareStr(datefail.Text), PrepareStr(dtshipdate.Text), PrepareStr(txtregisternum.Text), "
|||check here:
http://www.sommarskog.se/dynamic_sql.html#Security2
Amazingly good coverage of the topic and then look at the quote name explanation. Better than I could explain it here :)
|||An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll
Additional information: Cast from string "INSERT INTO tblNEW (watch_id, se" to type 'Boolean' is not valid.
Where does the Boolean value come in i thought it was a string....my type in the DB is not boolean where is this coming from any help?
I get this exception when i am trying to excute this Query and i even added my Pull_Quotes Function....here is the query.....
cstring = "INSERT INTO tblNEW (watch_id, service_center_num, repair_envelope, store_number"
cstring = cstring + "date_purchase, transaction_num, cust_fname, cust_lname, product_code"
cstring = cstring + "value_watch, failure_date, service_date, failure_code, repair_code"
cstring = cstring + "service_request, store_number_senditem, register_number, street_address"
cstring = cstring + "city, state, zip_code, area_code, phone_num, product_desc, service_center"
cstring = cstring + " work_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_cost"
cstring = cstring + "notes, client_number)"
cstring = cstring + "VALUES Pull_Quotes(txtWatchID.Text),Pull_Quotes(txtCenter.Text),Pull_Quotes(txtenvelope.Text),Pull_Quotes(txtSenditem.Text)" & _
cstring = cstring + "Pull_Quotes(dtDateofPur.Text), Pull_Quotes(txtTrans.Text), Pull_Quotes(txtfname.Text), Pull_Quotes(txtlname.Text),Pull_Quotes(txtprdcode.Text)" & _
cstring = cstring + "Pull_Quotes(txtvalue.Text), Pull_Quotes(datefail.Text), Pull_Quotes(dtshipdate.Text), Pull_Quotes(txtregisternum.Text)" & _
cstring = cstring + "Pull_Quotes(txtaddress.Text), Pull_Quotes(txtcity.Text),(txtstate.Text), (txtzip.Text), Pull_Qoutes(txtareacode.Text), Pull_Quotes(txtphonenum.Text)" & _
cstring = cstring + "Pull_Quotes(txtproductdesc.Text), Pull_Quotes(txtworkbdone.Text), Pull_Quotes(txtauthnumber),(txtlabor.Text), (txtPart.Text)" & _
cstring = cstring + " Pull_Quotes(txttaxcost.Text), Pull_Quotes(txtTotal.Text), Pull_Quotes(txtNotes.Text), Pull_Quotes(txtClient.Text)"
|||Just like last time, for help in this group, we need to see the SQL statement that is being sent to the client:
INSERT into tblNEW.. etc. For eerrors like: 'System.InvalidCastException' There isn't much we can do.
I can guess that the & _ is probably not right in this statement:
string = cstring + "Pull_Quotes(txtproductdesc.Text), Pull_Quotes(txtworkbdone.Text), Pull_Quotes(txtauthnumber),(txtlabor.Text), (txtPart.Text)" & _
cstring = cstring + " Pull_Quotes(txttaxcost.Text), Pull_Quotes(txtTotal.Text), Pull_Quotes(txtNotes.Text), Pull_Quotes(txtClient.Text)"
But really no idea. If you can do the messageBox trick again and post the INSERT statement the you will get useful help.
|||Ok i got rid of the exception i was using to many line continuations thats why i got that exception but here is what the Insert is sending to the server
INSERT INTO tblNEW(watch_id, service_center_num, repair_envelope, store_numberdate_purchase, transaction_num, cust_fname, cust_lname, product_codevalue_watch, failure_date, service_date, failure_code, repair_codeservice_request, store_number_senditem, register_number, street_addresscity, state, zip_code, area_code, phone_num, product_desc, service_centerwork_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_costnotes, client_number, service_ship)VALUES '10000269%','%23432%','%234123%','%2341%''%3/22/2006%','%2412%', '%Demetrius%', '%Powers%','%2341%''%25,000.00%', '%3/22/2006%', '%3/22/2006%', '%34234%''%43534dfggsdg%', '%sdgsdg%','%fg%', '%42342%','%453%', '%435345%''%fgsdfgsd%','%2343%', '%gsdgsdg%', '%345345%',(txtlabor.Text), (txtPart.Text)'%534534%', '%45345%', '%sdgsdgdfgfger%', '%4234%', '%3/22/2006%'
I know some of it is garble but you know the saying Fuctionality first then make is pretty but this is the statement...and it does not work is it something with the ((txtlabor.Text or txtPart.Text)) should i add the Pull_Quotes function for all of them or what....kinda lost any help would be fantastic! thanks
|||I do not think you should have all these % when you insert a column into the table.
It makes sense when searching for sometime inside a column.
insert into TestTable (sometext) values ('this is a test')
To find a row in TestTable that contains 'test' you do
select * from TestTable where sometext like '%test%'
|||Ok, the first thing that is wrong is:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '10000269%'.
This is because you need parenthesis around the stuff in the values clause.
Second:
You need a comma between these parts:
'%2341%''%25,000.00%'
Finally:
for the txtLabor.text stuff you need to look at how your quotes are in your statement. You probably have something like:
Pull_Quotes("(txtClient.Text)")
Or something along those lines, but that is a VB question, and I don't know.
|||Yeah, just completely missed that fact. I was so focused on the syntax that I missed the values being inserted :)|||Well here is what is being sent to the server from my insert statement
WatchTracker
INSERT INTO tblNEW(watch_id, service_center_num, repair_envelope, store_numberdate_purchase, transaction_num, cust_fname, cust_lname, product_codevalue_watch, failure_date, service_date, failure_code, repair_codeservice_request, store_number_senditem, register_number, street_addresscity, state, zip_code, area_code, phone_num, product_desc, service_centerwork_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_costnotes, client_number, service_ship)VALUES '0010000269','345234','342523','3453','3/22/2006','53534', 'Demetrius', 'Powers','45345','25,000.00', '3/22/2006', '3/22/2006', '453245','45324gdsfgsgfd', 'dfgsdgsdg','rt', '43545','454', '43534534','sdfasfdasa','34544', 'sdfasfasd', '345345','43543', '3453','345', '3453', 'ghkweklfklasdfklneklrnkl', '435345', '3/22/2006'
OK
I am still not getting those results i need, is there a tool that comes with the SQL Server 2000 management studio? Or is the code syntax still incomplete?
|||Those results? Are you getting an error? Yes, I know you are:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '0010000269'.
At the very least you need parens, and you have no comma between repair_code and service_request or store_number_send and item or street_address and city or service_center and work_to_bdone:
INSERT INTO tblNEW(watch_id, service_center_num, repair_envelope, store_numberdate_purchase, transaction_num, cust_fname, cust_lname, product_codevalue_watch, failure_date, service_date, failure_code, repair_code, service_request, store_number_send,item, register_number, street_address, city, state, zip_code, area_code, phone_num, product_desc, service_centerwork_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_costnotes, client_number, service_ship)
VALUES ('0010000269','345234','342523','3453','3/22/2006','53534', 'Demetrius', 'Powers','45345','25,000.00', '3/22/2006', '3/22/2006', '453245','45324gdsfgsgfd', 'dfgsdgsdg','rt', '43545','454', '43534534','sdfasfdasa','34544', 'sdfasfasd', '345345','43543', '3453','345', '3453', 'ghkweklfklasdfklneklrnkl', '435345', '3/22/2006')
Once you get your statement into this form use the query tool to execute the statement until you get a result that works. Fix your code and then test again. Wrap the statement in a transaction and the database won't be affected:
BEGIN TRANSACTION
test statement
ROLLBACK TRANSACTION
|||Why are you performing a direct INSERT from the client code? It has security implications and in most cases a bad practice. Create a stored procedure that performs the insert and then call it from the client.|||Thanks here is the final query that worked for me thanks for the help!! It was great!!!
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(txtSenditem.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) & "')"
No comments:
Post a Comment