SQL Question...

I am appending data to a table. One of the columns in the database is a numeric field, and some data coming in is the letter "x" which messes things up, how can i convert the "x" to "999" on the way in?

Can you supply more details? Where is the input data coming from? Can you show some of your code?

Hi,

you should be more concerned about how to stop the x from the input than how to patch this case...

However (even if I'm not pro-patch) you could try a trigger on INSERT of SQL server that is, if your database is SQL SERVER driven ! This trigger could select from the "Inserted" table of the database and test for numeric values. If not numeric then update the "Inserted" table with the numeric value you want...

E-mail me for details at [email protected] if needed...

ok the data is coming from a text delimited file that is exported off an AS400, and is a linked table in the database. Here is what i do to append all the items that dont have 'x' in the field

Code:

---

sSQL = "INSERT INTO Sales (Customer_No, Customer_Name, Sales_1, Sales_2, Sales_3, Sales_4, Sales_5, Sales_6, Sales_7) " & _
"SELECT Mmarptcsv.Field2, Mmarptcsv.Field3, Mmarptcsv.Field4, Mmarptcsv.Field5, Mmarptcsv.Field6, Mmarptcsv.Field7, Mmarptcsv.Field8, Mmarptcsv.Field9, Mmarptcsv.Field10 " & _
"From Mmarptcsv " & _
"WHERE FIELD1 <> 'x'"
dbSalesman.Execute sSQL

---

now how can i convert the x to 999 and then append those records.

You could select the data into a temporary table, then run SQL updates and finally append the records from the temporary table.

select * into temptable from Mmarptcsv
update temptable set field4='999' where field4='x'
[...]
update temptable set field10='999' where field10='x'
insert into Sales(...) select [...] from temptable

thx, i will try that tonight, and see how it werks, looks good though.

thx

Im not sure if this is applicable, but if im reading from a field where the user should have entered a number, ill often force it to a Integer or Numeric Value with Int(X) or Val(X), example below:

Code:

---

' With ADO
adoRset.Update "id", Int(txtID.Text)

' SQL too
adoConn.Execute "UPDATE mytable SET id = " & Int(txtID.Text) & " WHERE id = 0"

---

Code:

---

sSQL = "INSERT INTO Sales (Customer_No, Customer_Name, Sales_1, Sales_2, Sales_3, Sales_4, Sales_5, Sales_6, Sales_7) " & _
"SELECT Mmarptcsv.Field2, Mmarptcsv.Field3, Mmarptcsv.Field4, Mmarptcsv.Field5, Mmarptcsv.Field6, Mmarptcsv.Field7, Mmarptcsv.Field8, Mmarptcsv.Field9, Mmarptcsv.Field10 " & _
"From Mmarptcsv " & _
"WHERE Mmarptcsv.FIELD1 <> 'x'"
dbSalesman.Execute sSQL

---

Hope this is working.