|
Hi,
As a disclaimer, I'm new to DB programming and .NET in general. My questions regard using parameters. I am developing a local application, and placing the right types of quotes in SQL statements depending on the datatype being passed is cumbersome. So, after doing some reading I'm attempting to use parameters.
So, if I'm using parameters, does it suffice to specify the OleDbDataType to catch an attempt to pass an incorrect datatype into a database field.
I suppose I'm a little unsure as to how much type-checking I need to apply to user input when using parameters.
Thanks for your help.
|
|
|
|
|
Whenever I've used parameters, trying to execute SQL causes an error if I've provided the wrong data type. I typically use a generic Try Catch block around the code that executes the SQL to catch errors like that.
Example:
Try<br />
myDataAdapter.Fill(myDataTable)<br />
Catch ex As Exception<br />
MessageBox.Show("Error: " & ex.Message)<br />
End Try
Hope this helps.
|
|
|
|
|
Hi all,
I am building an employee search application depending on 6 criterias(emp name,emp grade,dept,hire date,qualification,work timings).I have 4 tables-Employee,Emp_Qualifications,Work_Details,dept which are as follows.
Employee
------------------------------------------------------------------------
empid(PK) | fname | lname | grade | Designation | Dept_id(foriegn key) |
------------------------------------------------------------------------
dept
-----------------------------
Dept_id (PK) |Dept_name(PK) |
-----------------------------
Emp_Qualifications
---------------------------------
empid (PK) |qualification(PK) |
---------------------------------
123 | J2ME |
----------------------------------
123 | SAP |
----------------------------------
100 | Java |
----------------------------------
Work_Details
---------------------------------------------------------------
empid(PK) | Clear_Date(Date time)(PK) | document_name(PK) |
---------------------------------------------------------------
123 | 12/01/2007 | Balance sheet |
----------------------------------------------------------------
123 | 12/01/2007 | data sheet |
---------------------------------------------------------------
i want to select empid,fname,lname,designation,dept_name,(Collection of all expertise seperated by comma) for that empid from all tables where empid,name,dept,grade,qualification,work timings are provided by user.
Thanks in advance!!!
|
|
|
|
|
I'm assuming that you are using SQL-Server. You will need to use a database function to get the list of qualifactions:
CREATE FUNCTION dbo.ConcatQualifications(@EmpId INT) RETURNS VARCHAR(200)
AS BEGIN
DECLARE @Expertise VARCHAR(200), @Qualification VARCHAR(20)
DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
SELECT Qualification FROM Emp_Qualifications
WHERE EmpId = @EmpId
ORDER BY Qualification
OPEN c1
WHILE (@@FETCH_STATUS = 0) BEGIN
FETCH c1 INTO @Qualification
IF (@@FETCH_STATUS = 0) BEGIN
SET @Expertise = IsNull(@Expertise + ', ', '') + @Qualification
END
END
CLOSE c1
RETURN @Expertise
ENDThe basic query will look something like:
SELECT Employee.empid, Employee.fname, Employee.lname, Employee.designation,
Department.dept_name, dbo.ConcatQualifications(Employee.empid) Expertise
FROM Employee
INNER JOIN Department
ON Department.deptid = Employee.deptid
WHERE ####Your criteria goes here####
ORDER BY Employee.empid You can then insert any criteria that is relevent (e.g. "Employee.lname LIKE 'Smith%').
Once you have this working, you need to do a search on this forum for "SQL Injection" because the above criteria will fail (potentially dangerously) if the employee's name of "O'Leary".
Hope this helps.
Andy
|
|
|
|
|
Hi,
I set up SQL Express 2005 on WinServer 2003 and my program works fine locally with following connection string: (as suggested in www.connectionstrings.com)
Provider=SQLNCLI;Server=myServerName\MYSQLEXPRESS;Database=myDataBase;Uid=myUsername; Pwd=myPassword
Now, I want to run the program from a client connected to the server. I tried the following string:
Provider=SQLNCLI;Server=myServerName\MYSQLEXPRESS,1433;Database=myDataBase;Uid=myUsername; Pwd=myPassword
and it failed to connect with the following error message:
Code = 800a0e7a<br />
Code meaning = Unknown error 0x800A0E7A<br />
Source = ADODB.Connection<br />
Description = Provider cannot be found. It may not be properly installed.
I have the following enabled:
- remote connection "using both tcp/ip and named pipes" (from Surface Area Configuration)
- shared memory (from SQL Server Configuration Manager)
- SQL Server Browser service
On side note: (I don't know if these matter)
- SQL Server 2000 (full version) is installed
- the server is a domain controller (I am not sure, since I didn't set it up)
- there is no firewall
Is it possible that 1433 is not the right port? How to get/check the right port?
Can anyone help me here? Thanks in advance.
|
|
|
|
|
Sounds like you need to install SQL 2005 Network Client on the client box.
|
|
|
|
|
I'm not sure if your "Server=..." setting is valid.
This article may help with connection strings: Using Connection String Keywords with SQL Native Client[^]
Maybe "Address=..." is the one you want?
To configure TCP or check the configured port I believe you can use SQL server Management Studio.
Right-click the server, choose SQL Server Configuration Manager and go to the network
configuration/ protocols section.
|
|
|
|
|
I finally found and fixed the problem. It was my original hunch, that 1433 is not the right LISTENING port (although the setting of SQL Express still points to 1433). To check the port, run cmd.exe and look at the result of "netstat -ano".
Hopefully someone find this useful and don't need to spend days figuring this out.
|
|
|
|
|
Hi every one.
I have this message error each time I try to update my DB in my website.
System.Data.OleDb.OleDbException: Operation must use an updateable query
it works so fine on my PC but not on the remote server.
my code is :
this.Adapter.InsertCommand.ExecuteNonQuery();<br />
return returnValue;<br />
Can anybody help me please ?
Rédha.
|
|
|
|
|
Are you using Access? If so check this [^]
|
|
|
|
|
Hi,
Here`s my problem.I have 2 tables Employee and Department as follows
Employee
----------------------------------------------------------------
empid | fname | Lname | Grade | Designation | Dept_id |
----------------------------------------------------------------
Department
---------------------
Dept_id |Dept_name|
---------------------
I want to select empid,fname,lname,grade,designation,Dept_name
I know this is very simple but i am weak in joins.Pls suggest a query such that i can select the values.
|
|
|
|
|
Select empid, fname, LName, Grade, Desination, Dept_name from Employee JOIN department on Department.Dept_id = Employee.Dept_id
(you cqn use LEFT JOIN to get all employees, even those have Dept_Id=null.)
Rédha.
|
|
|
|
|
|
select empid,fname,lname,grade,designation,Dept_name from employee e join department d
on e.dept_id=d.dept_id
Gautham
|
|
|
|
|
Hi,
I merge a dataset into another dataset in my C# application.
While doing the dataset.Merge(myParamDataSet), the event 'position change' is raised from the 'bindingsource'.
Is there a way to stop all bindingsource events before the merge & after the merge?
Best regards,
Jens
|
|
|
|
|
i am new in database handling can any one explain me how to use Crosstab Query in MS Access to show the content of the veriable and not count or sum thanks in advance.
Sasmi
|
|
|
|
|
Hi all...
I m a CPP Developer.
started learning SQL while I was in high school
I know only "SELECT D.A,D.B,E.C FROM D,E WHERE D.id=E.id"
I need to know much more complex stuff and fast...
Does anyone know any good tutorials or books.
thanks alot.
|
|
|
|
|
There are so many available on net. What you need to do is to search them with google. 
Best Regards,
Apurva Kaushal
|
|
|
|
|
To learn sql refer msdn that is the best!!
Gautham
|
|
|
|
|
The_Server wrote: Does anyone know any good tutorials or books.
SQL Server Books Online has many examples of how to use each of the available commands and procedures.
|
|
|
|
|
hi,
I prefer http://sqlzoo.net
where one can learn by practising (online)
(explanation, examples and exercise)
|
|
|
|
|
Moving data from file database to sql and questions came up as to whether use of user defined types was best practice or not. What are pros and cons? Do you use them or not and why?
Will also be using these types as parameters from C# code. So welcome any specific comments regarding impact there.
Thanks,
Matt
|
|
|
|
|
I am trying to return only the first 10 records in sql code that has a where clause. here is my current code. Any help is appreciated.
sqlQuery = "SELECT TOP 10 PTXADDR, ZIP, X_COORD, Y_COORD, PARENT FROM JOB.PTXGIS WHERE"
sqlQuery = sqlQuery & " PTXADDR LIKE '" & addr & "%'"
|
|
|
|
|
lavell wrote: I am trying to return only the first 10 records in sql code that has a where clause. here is my current code. Any help is appreciated.
In terms of getting the first 10 rows, I don't see a problem with your code.
In terms of security, you have a great big hole waiting to be exploited. You would be better reading about SQL Injection Attacks and Tips on How to Prevent Them[^]
|
|
|
|
|
sqlQuery = "SELECT TOP 10 PTXADDR, ZIP, X_COORD, Y_COORD, PARENT FROM JOB.PTXGIS WHERE"
sqlQuery += " PTXADDR LIKE '" + addr & "%'"
I just have replaced the '&' to '+'
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
