|
See if you can use command builder object to determine how many parameters the procedure accepts. or you may pass delimited text using which individual values can be seperated inside your SP.
Bhaskara
|
|
|
|
|
Just to clarify, I am using Access 2000 (no stored procedures).
|
|
|
|
|
Good day.
I create a product form that contains some textbox and combobox for the user to enter the data into a Product table in the SQL server database. The selection items in the combobox is SupplierName. When the user press add button to add the data to the Product table in SQL server. I want to store the SupplierNo instead of SupplierName into the Product Table in the SQL server. How do I retrieve the SupplierNo from the Supplier table by the selected item in the combobox and store it in the Product table?
Many thanks and best regards,
viv
|
|
|
|
|
Hi
SupplierName
For displaying purpose only You are using means you can bu that in between combo box and put value as SupplierNo. On click of add button if get the value of combo box by select1.value code, then it will automatically gives the SupplierNo..Send this value to SQL Query as variable
Happy Coding!!!
|
|
|
|
|
Hi,
I'm having a problem with an sql select statement from some c# code. The function is meant to count the number of entries in a table where the users dates of birth are between two dates. The two birthday ranges are taken from two integers userAgeFrom and userAgeTo. When I run the code I get the following error:
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
The weird thing is if I manually populate the strings userDOBFrom and userDOBTo with dates (eg userDOBTo = "12/09/1971") the select statement works and returns the number of users. When I try to populate the strings from a DateTime object, as below, the function fails.
Any help would be much appreciated!
Kevin
Here is the code:
<br />
int userAgeFrom = 18; <br />
int userAgeTo = 30; <br />
int numUsers; <br />
string selectStatement; <br />
string userDOBFrom; <br />
string userDOBTo; <br />
<br />
DateTime dtUserDOBFrom = DateTime.Now; <br />
DateTime dtUserDOBTo = DateTime.Now; <br />
<br />
userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToShortDateString(); <br />
<br />
userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); <br />
<br />
selectStatement = "select count(*) from USERS where date_of_birth between '" + userDOBFrom + "' and '"+ userDOBTo + "'"; <br />
<br />
sqlDBConnection.Open(); <br />
<br />
sqlDataAdapter.SelectCommand.CommandText = selectStatement; <br />
<br />
numUsers = (int)sqlDataAdapter.SelectCommand.ExecuteScalar(); <br />
<br />
sqlDBConnection.Close(); <br />
<br />
|
|
|
|
|
Hmm, better would be to use command parameters.
Otherwise though, I see nothing wrong with your code, other than the misplaced ".Date." in userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString();
|
|
|
|
|
Hi, thanks for the tip, I've tried it with and without the extra .Date and get the same error message.
Can you elaborate on the command parameters?
Cheers!
|
|
|
|
|
Sorry, don't have time for a full explanation. Its in the Help.
Basic idea is that instead of quoting the dates, you would put a placeholder there instead, like "... BETWEEN @P1 and @P2". Then you can create and add parameters to your command. When you do it this way, it has the benefit of strong-typing your parameters, which avoids nasty surprises like the one you are getting, as well as sneaky SQL-Injection attacks.
It sounds more complex than it is...
|
|
|
|
|
Try:
userDOBFrom.ToShortDateString()
userDOBTo.ToShortDateString()
when you build the SQL statement. The default ToString() method may generate the wrong syntax.
Normally I would insert:
MessageBox.Show(selectStatement);
To see what is going on in this case.
|
|
|
|
|
This might be a long shot, but we've had problems like this with different regional settings. The ToShortDateString() "uses formatting information derived from the current culture."
Another thing to try (even longer shot), is to make sure that the collation on your server is expecting your dates in the format that ToShortDateString() is returning.
|
|
|
|
|
I agree with the general consensus. Most likely ToShortDateString() is returning a string representation of a date that SQL Server can't parse.
I would use the formatted ToString() instead:
userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToString("yyyy-MM-dd"). This should put it in an ISO standard format that SQL Server should understand, regardless of culture/regional settings.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
I hope you can help me... I store an elapsed time value as a string (varchar) (mm:ss) in my SQL database. When I query the data from Excel, how can I calculate an average from this data? Would it help if I made the SQL datatype some kind of time value?
Thanks in advance for any advice you may provide.
'til next we type...
HAVE FUN!! -- Jesse
|
|
|
|
|
Jesse Evans wrote:
When I query the data from Excel, how can I calculate an average from this data?
Why are you storing it as a string? I would store it as an integer and if you needed in the format mm:ss a simple calculated field could be created for that function.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
check this
avg(cast(field as int))
|
|
|
|
|
Hi. I can't find the right category for my question so I decided to just post this here.
- Is it possible to use MS Access as a front-end to an Access database (as back-end) that is deployed on the Web Server? My initial reading on this topic seems to imply that I can have MDB as a back end to my front-end application (although this is not highly-recommended for huge applications). However, I need some straight answers to my questions so I posted on this forum.
To access an MDB file on the Web Server, I was told that the front-end application must use a middleware (such as ASP / ASP.NET). And also a capability to send HTTP requests. Does Access has this capability?
I really hope someone could help me with my questions. Thanks very much..
|
|
|
|
|
winpiglet wrote:
To access an MDB file on the Web Server, I was told that the front-end application must use a middleware (such as ASP / ASP.NET). And also a capability to send HTTP requests. Does Access has this capability?
this is true..you need ASP or ASP.NET
And access XP/2003 can help you export reports and forms to ASP.(try right click-export)
|
|
|
|
|
There seems to be a lot of headaches that become apparent on this message board when it comes to topics such as "enforcing database constraints" or "enforcing complex business rules in the database."
I work for a company called Dataphor with a new development tool that not only automates the application development process, but also allows for a declarative approach to enforcement of complex business rules, constraint enforcement, etc.
The product I am speaking of is Dataphor - an automated application environment. By combining the power of a businews rules engine, the automation of a meta-data repository, and the flexibility of a RAD environment, Dataphor provides an unprecendented level of prductivity and maintainability.
Dataphor is a product of Alphora. More information may be found at alphora.com.
-Brady
|
|
|
|
|
On my website I have 3 forms where people enter their contact details (name, email, address, phone and country) and some additional fields which are different for each form. Form 1 requires all the details to be entered, form 2 requires only name, email and country and form 3 requires name, email, address and country.
I initially designed my database with seperate tables for each of the forms since they require different contact details however was wondering whether removing the contact details to a seperate table and leaving some of the columns null would be better.
Can anyone advise me on this?
Thanks
SuzyB
If I had a better memory I would remember more.
|
|
|
|
|
If the common fields will have same data on all the forms, I would create one table for contact details which are nullable, and other individual data into seperate tables and enforce the constriants through application.
Bhaskara
|
|
|
|
|
Hi,
I want to know the primary keys of a given table. I s there any query or procedure which gives the primary keys of a given table in MS SQL.
Karteek
|
|
|
|
|
ADODB::Connection.OpenSchema will probably have something, or ADOX
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Try this
select *
from syscolumns
where id = ( select id from sysobjects where name = 'Table1' )
and colid in ( select distinct keyno from sysindexkeys where id = (select id from sysobjects where name = 'Table1' ) )
Where Table1 is the table name for which you are trying to find Primary Keys
Bhaskara
|
|
|
|
|
Or
If you are using VB.NET or some app, you can use SQLDMO components provided by MS SQL Server and manipulate DB objects, iterate through databases and tables and find out info about DB objects
Bhaskara
|
|
|
|
|
You should never read data straight from the sys... tables. The next service pack you install could completely change their schema/usage.
Instead, try these two queries. Once you see the data, you should be able to figure out how to combine them to meet your needs:
<br />
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS<br />
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE<br />
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Hi there!
I'd like to create a batch file (*.bat) into which I can place osql calls that will run a couple of stored procedures (in order to create a database or populate some tables, etc.). I have a couple of probs with it:
1. I'd like to only redirect errors into a file. How can this be done? If I use the /o param the complete inputfile gets written out! And how can I suppress those >1 >2 >3... (what do they represent anyway?)
2. Is there a way to do variables in a batch file, which later can be used? It like to create for example a variable holding the username at the top of the *.bat file which I later can reuse when running osql commands.
3. Any samples, better ideas, hints?
Thanks for reading,
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
