Evaluating/Validating Paramaters In A SqlDataSource
Dec 21, 2007
I want to do some error checking on the parameters found in a SQLDataSource before I run the insert. The problem is these are ControlParameters and I want to do this dynamically so I can't just call the Control.Text property and grab its value. So how can I get access to what the ControlParameter evaluates to?
Secondly, is there a way to access what the update parameters evaluate to in order to check them before they're inserted - if so how do I get access to these?
Here's an example of one of the data sources i'm using:<asp:SqlDataSource ID="sqlContact" runat="server" ConnectionString="<%$ ConnectionStrings:strConn %>"
}The InsertRow() function is then what i'm using to evaluate the values... So how can get the values those controlparameters actually are in order to evaluate them before I actually insert. Or is there a better way to do it?
I'm having trouble using paramaters in a full-text search with FormsOf(INFLECTIONAL:DECLARE @SearchWord nvarchar(4000) SET @SearchWord = 'tax' SELECT listing_id, RANK, name, address, city, zip, heading, phone FROM listings a, FREETEXTTABLE(listings, *, 'FormsOf(INFLECTIONAL, @SearchWord') WHERE [KEY] = a.listing_id ORDER BY RANK DESC, name
This returns no results. It treats the paramater as a NULL value. But this...
SELECT listing_id, RANK, name, address, city, zip, heading, phone FROM listings a, FREETEXTTABLE(listings, *, 'FormsOf(INFLECTIONAL, tax') WHERE [KEY] = a.listing_id ORDER BY RANK DESC, name
returns over 500 results. I've been banging my head against this for 2+hours. Google has been no help as every example shows the result hard-coded in like the second example.PLEASE HELP!!!!!!
I need to setup some asp security databases and I have seen several sets of instructions. Some say "Navigate to C:WINDOWSMicrosoft.NETFrameworkv2.0.50727" and some say at the "asp command box". I do know that the directory & file exists on my machine. I have run aspnet_regsql.exe to configure my local server. I now need to run it to configure my web host SQL Server 2005 and that requires some paramaters like "aspnet_regsql.exe -S [DB Server Name] -U [DB login] -P [Password] -A all -d [Database name]". I am old and started before windows. I know DOS. But obviously not well enough. How do you "Navigate to C:WINDOWSMicrosoft.NETFrameworkv2.0.50727"? If I say "CDWINDOWS", it works. When I am at the "C:WINDOWS>" prompt and say "CD Microsoft.NET" it says "invalid directory" When I am at the "C:>" prompt and say "CDWINDOWSMicrosoft.NET" it says "invalid directory" When I am at the "C:>" prompt and say "CDWINDOWSMicros~1" it says "invalid directory" Thanks for your help. John Brown
Hi, Just wondering if I could make sure that "hackers" don't tamper with my querystring - which is a parameter for a SQL query. i.e... Dim ListOfValues as string = request.querystring("listOfValues")
'Output would be this: 324234,5445,554654,45632,SQL command : Delete From table where product_id IN (@ListOfValues)
How can I validate it so that hackers can't add any characters other than 'numbers' and ' , ' to the sql parameter? I have tried to tryparse the 'ListOfValues ' as an integer - by replacing "," with "" but an integer overflow occured. Any questions or ideas? Thanks
I'm writing a search query in TSQL for MS SQL. Here is my code:
SELECT a.*, b.username, c.categoryname FROM video a inner join user b on a.userid = b.userid inner join video_category c on a.categoryId = c.categoryid where a.videotitle like '%@SearchText%' OR a.description like '%@SearchText%'
This returns no results, but wheN I replace the '%@SearchText%' with my search text inplace of the @SearchText it returns retuls. I have a feelign I am passing the params to the LIKE clause incorrectly. Any suggestions?
Does anyone know how i go about writing a search query using paramaters, ive been using this as my template: Select Distinct Recipedata.Name, Ingredients.Ingredient, Directions.Directions from Recipedata,Ingredients,Directions Where Ingredients.Ingredient Like '%Chicken%' and
RecipeData.RecipeID = Ingredients.RecipeID and Ingredients.RecipeID = Directions.RecipeID;
Problem is now that i want to use paramters instead of hard coded values as this is for a user. How do i go about this? This database has a VB2k5 front end for the desktop and PDA(and no the target PDA is not web enabled so web servcies arent available to me).
Hi, I'm creating a dynamic SQL statement in MS SQL Server that is similiar to this: EXEC('IF @' + @current_column + ' (SELECT ' + + @current_column etc... I'm basically looping through a large list of parameters that correspond to column names. However, since SQL Server treats EXEC() as its own scope when it gets to what "@' + @current_column" evaluates to it says the parameter must be declared. Is there a way to convert "@' + @current_column " into the actual value of the parameter?
We recently moved from v6.5 to v7.0. Now I have the databases and logs set to "autogrow". How can I monitor the disk space to ensure I do not run out of room (or is that preset as to how large it can grow ?). Can't find anything in the books online. Do I do this through the NT admin tool or through the SQL*Server Enterprise Manager and more importantly - how ??? Thanks so much for any help... Nancy
Hi,I am evaluating Lumigent's Entegra for doing security and businessaudit of some of the critical database(s) in the company I work for. Iwould like to know what has been your experience in using this productfor doing similar audits in your company, if you have also done suchaudits.Thanks,Sanjeev
Our Business partners request me to read the field names from a SQL table dynamically so that my SSIS package will not get impacted if Web Service hosts make a change.
Is there a way to evaluate a string at runtime that contains a field name ?
I have been looking all over for some info about other people having this problem, but haven't found anything.
I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:
Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!
Hi all€”I'm new to expressions, and am trying to write one where I can evaluate if a field is null, to handle it by inserting a default value if a null value is found. Given this table definition:
Hello all, I currently have a pain in the butt with a subquery that needs to be evaluated, and if the result gives me a null value, I want to re-evaluate the condition and fill the column with the proper information. I'll try to explain it as best as I can:
The query retrieves information from 2 tables basically, but i do need some inner joins of diferent tables in order to follow the relations.. I got 2 conditions that need to be acomplished, the 'typeof' (intId_Tipo) has to be diferent from 6 and a keycode equals to 3.
For the "general query" i do filter this just fine, but in the subquery is where I get stucked: Since the evaluation that I do in oder to find the initial point is just by substracting 1, there are some cases where the type called intId_Tipo is actually 6 and the subquery returns me a null value. In this particular case I want to substract 2..if not I just want to substract 1. I added a SQL CASE in oder to evaluate the null value, but it is not evaluating properly, why? I'm not sure, I need your help and recomendation guys
Here's the query that I currently have: (the comments were made in order to take the next screenshot)
Code Snippet SELECT --h.dt_Lectura, --h.int_Velocidad, rp.int_VelMaxima, (SELECT intpunto FROM tblRecorrido_Puntos WHERE intPunto = case when (intPunto) is null then rp.intPunto - 2 else rp.intPunto -1 END AND rp.intId_Ruta_Ramal =intId_Ruta_Ramal AND intId_Tipo =rp.intId_Tipo) as PuntoInicial, rp.intPunto as PuntoFinal --rp.strDescripcion_Punto as DescripcionPuntoFinal, --1 as Contador --a.idRuta_Guid, a.idRamal_Guid,a.idUnidad_Guid, a.idOperador_Guid
FROM tblHistorico h INNER JOIN tblAsignaciones a ON a.id=h.intIdAsignaciones INNER JOIN tblRutaRamal_Generado rrg ON rrg.id_Ruta = a.intRuta_Asignada INNER JOIN tblRecorrido_Puntos rp ON (rp.intId_Ruta_Ramal =rrg.intId_Ruta_Ramal AND rp.intPunto=h.int_PtoDest)
WHERE ( h.int_ClaveTDE =3 and rp.intId_Tipo <> 6 )
ORDER BY rp.intPunto ASC
Here's the screenshot of the query result: http://img218.imageshack.us/my.php?image=consultahd0.jpg
Hi I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!
The original Access SQL appears below:
SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded, ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode, ResearchStudent.RegistrationDate, StudentExamination.Decision, IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate, ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID) LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID) LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)] And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1)) AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False)) ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365
I have two int fields in my database, CEOAnnualBonus and CEOBonus, and I want to return the value of whichever one has the larger value as CEOBonusCombined. I thought using COALESCE would do the trick like below but there are many cases where either CEOAnnualBonus or CEOBonus have a zero value instead of NULL and it doesn't work.
SELECT COALESCE(CEOAnnualBonus, CEOBonus) AS CEOBonusCombined FROM tbenchmarktemp WHERE Ticker='F'
Hello All again, I have another question, I would like to validate the data in a sql table before my code runs and inserts duplicate information. Can anyone help out with this via example?
I have one table called product 1 and the second table is called product 2 i have DirNo, ProdNo, UpdCode and BranchOffice as fields i whant to loop trought every record in table product 1 and check in table product 2 if the record is the same and exist i f it does go to next record, if not delete the record in product 1 and move to the next record and so on. could someone please help me. iam very new at this. thanks
This question is rather open ended. Basically, I'm wondering the different approaches people use when validating input into a stored procedure. The rest of my post just describes a rather simple approach I'm using, and some ideas I have, but I'm eager to know what others are doing. So, if you'd like to comment on this, feel free to do so without reading the following.
I often find myself calling a stored procedure, and needing to validate some of the inputs before proceeding with the rest of the tasks. Such as, making sure a matching record isn't already in the database before adding a new record. Ideally, this sort of validation will report back to the user interface immediately, so that the user can get real-time response to their form input, instead of the all-too-common approach of redirecting to an error page if something goes wrong in the database transaction.
It's also convenient, at times (though perhaps not efficient in all cases) to let your database perform business rules validation of inputs (let the user interface make sure data types are valid, and such), so that you duplicate less code in the event that the stored procedure is called in more than one context.
To address these issues, I've taken to making two stored procedures instead of one. Let the stored procedure be called AddRecord. I'd create that, as well as the procedure named AddRecord_Validate, which would take identical inputs as AddRecord, whenever possible. Nearly the first thing done in AddRecord would be to execute AddRecord_Validate. The user interface would also call AddRecord_Validate, and return any validation errors to the user interface.
This seems rather convenient to me, in many cases, but often it doesn't work as well as I prefer. It's not uncommon for me to end up performing the same queries in the _Validate SP as I do in the parent SP. For example, let's say I'm passing a parameter that I use to look up a record I plan to edit. In my validation, I query the database to verify that the record is found. But in the parent SP, I run the same query again in order to get the stored ID of the record I need to edit. This ends up duplicating queries, making the pair of procedures less efficient on the whole, as well as introducing the likelihood of bugs when code changes in one of the SPs, but not the other.
So I've been brainstorming other approaches. The first idea is to execute a single stored procedure, but when I want to run it in "validation" mode, I simply rollback the transaction. This would let me know if the stored procedure would have run with the specified input, but won't ultimately change anything. Unfortunately, I see some badness in this, such as the entire SP perhaps taking a lot longer to execute that simple validation would have, and side-effects such as incrementing Sequences, or locking the database, and basically just doing a whole lot more work during validation than needs to be done.
The next idea was to require that the user specify the "run mode" via a parameter, either 'validate' or 'run'. Then, all of the validation would be performed at the top of the stored procedure, and a simple IF statement would exit the SP before actually making any changes if it's run in 'validate' mode. Otherwise, it will continue, and do the real work. The only real downside I see to this is forcing the developer to deal with an extra parameter. And, perhaps, it's not really a "relational" approach.
i am using visual studio 2008 to create an inventory management system and i am having trouble checking for a constraint violation. i have a partnumber colum in the database that needs to be unique and i am using datasets / tableadapters to interact with the database. when a user adds a new part number to the database i need to make sure its not already there i have tried to use catch ce as constraintexception debug.print ce.tostring end try
but the program still crashes with
System.Data.ConstraintException was unhandled ( see full error below)
no matter where i put the try statement it says its unhandeled
this error triggers when when i leave the combo box (when its validated) i have even tried placing it here
Private Sub PartNumberComboBox_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles PartNumberComboBox.Validating
Try
Catch CE As ConstraintException
Debug.Print(CE.Message)
Catch EX As Exception
Debug.Print(EX.Message)
End Try
End Sub
if someone could point me in the right direction id greatley appriciate it
System.Data.ConstraintException was unhandled Message="Column 'PartNumber' is constrained to be unique. Value '36LDVRRP' is already present." Source="System.Data" StackTrace: at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action) at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent) at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException) at System.Data.DataTable.SetNewRecord(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean fireEvent) at System.Data.DataRow.EndEdit() at System.Data.DataRowView.EndEdit() at System.Windows.Forms.CurrencyManager.EndCurrentEdit() at System.Windows.Forms.CurrencyManager.ChangeRecordState(Int32 newPosition, Boolean validating, Boolean endCurrentEdit, Boolean firePositionChange, Boolean pullData) at System.Windows.Forms.CurrencyManager.set_Position(Int32 value) at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e) at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m) at System.Windows.Forms.ComboBox.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam) at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m) at System.Windows.Forms.Control.WmCommand(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.GroupBox.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam) at System.Windows.Forms.NativeWindow.DefWndProc(Message& m) at System.Windows.Forms.Control.DefWndProc(Message& m) at System.Windows.Forms.Control.WmCommand(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ComboBox.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at Inventory_System.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:
I am using the following SQl statement in a DTS vb file. The data is coming from an Excel spreadsheet and being put into a SQl server table.
oCustomTask1.SourceSQLStatement = "select `Order No`,`Country`,`Desc`,`Amount` from `Sheet1$` WHERE `Order No` = 1 "
I want to validate the data being put into the table to ensure no duplicates records are entered UNLESS the record has changed in any way. E.g. If record 1 had a column called "NumberOne" which changed to "Number1" change this information and move the original value to another table.
Hi all,I am a newbie to sql and I need your help.I want to update column (email) from one table to another validating theCustomerid column in both table. Update the email address in productiontable with the email address in temp table if the customerid is same in bothtables.What would be the query?Thanks,
I'm totally new to SSIS and need some direction.I'ved worked with the Import/Export wizard to create a package that imports a text file into a SQL Server table. However, I'm told the format of the text file changes over time and that's not good. I need to program in a format validation check on the source file before it gets imported. If it changes, I'm suppose to throw an alert or something.Let's say the file has the columns: field1 (string[10]), field2 (date), field3 (integer), field4 (decimal).I did some testing and tried to change the data to a longer string in field1, and SSIS recognizes that and errors out. How do I get it to send the bad record to an bad record file? Do I just set a destination file connection for bad records and connect the red arrow from the source file to the destination file?I forget if the source file connection recognizes a bad date and errors out. I'll have to check again.But when I changed the data in the datafile for field3 from integer to decimal. It didn't recognize that as an error. It read it in "successfully". That's not good.Similar thing happened when I changed field4 from decimal to integer in the data file. But I'm not too worried about that.Any hints on how to do this or a better approach on checking for file format changes would be appreciated.Ken
I have one date parameter in my report. The user will be able to manually enter or use the calendar control. In the case of entering manually, is there any way to validate the input string? How can we prevent users from entering alphabets?
Also, is there any way to reduce the length of the parameter textbox. Can you please help?
I have a Problem with my SSIS and it is that when you open the package it says "Validating <Data Flow>" and it takes a loooooong time to finaly open ( by long I mean like 3 or 4 hours to just for open!!!). In 3 of the DF, I have conections to a Unisys server and in exactly in this DF is where it takes its time. This Unisys system its kind a slow but not that slow. I am running on a server that have 4 G and 8 processors, so it cant be the machine.
my question is, How can I make it validate faster ? I have tried to work Offline but when I run the package I need to be online.
Has anyone figured out how to test if a string is a valid date without risking a sql conversion error?
I'd really like to avoid parsing the strings myself in an import procedure I am writing.
I can run an insert/select with the text being converted to datetime data, but if it hits text that won't convert correctly (such as '13-1-1999') it fails with a sql error. This could mess me up quite a bit.