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'.
INSERTs given me the BLUES
coreus
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
Steven Habex
Andreas6483
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
sam_jeba
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) & "')"
ligerdave
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), "
titanico
It should probably be "txtcenter.Text" and not "txtcenter". It is the second control you use to concatenate the value with.
hle63704
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
mms18
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%'
Janee
Ok, the first thing that is wrong is:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '%0010000269%'.
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.
sinu
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)"
MrGTI
GGus
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 :)
Dave Coder
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.
topmar
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 '%0010000269%','%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