|
Can't you just check the value of the parameter inside the Stored Proc? Just assign it a value if the Parameter is empty. Or you can even assign a default value for the Parameter inside the Proc. Anyway, if the default value is set, then there is no need of this checking.
The name is Sandeep
|
|
|
|
|
Hi,
I'm 6 months new to C#, ADO coding and VS05 (Express still at this point).
My original DB was in MySql but due to the limitations of connection models in Express, I moved it to Access 2000 structure, to get a feel for the ADO and graphical concepts in VS05. So far it hasn't gone too badly, aside that the Access DB is behaving as "read only", if I try to reach it from OpenOffice and migration from MySql blew my PK/FK's and auto-numbers out the window. I was able to re-establish relations in VS no problem, however getting auto-num back for updates I'm still chasing.
I have succeeded in building the DataSet from Access in VS and was able to create the DataSet.xsd, establish relationships, build some integrated grids and see the simple linking relationships at work.
Questions:
The point I'm at now, is learning how to manage the ADO data, (assuming ADO can now copy the Database local to the program as needed?) and figuring out how to make combined table-source queries, parameter queries and perhaps something like views to work in the forms I'm using.
(1) The first problem I saw was that with a zipcode file of 47,000 text entries, opening large forms with the full dataset of contact details takes forever. I have a serial PK in the zipcode file which is indexed, but I'm wondering if and how I would add an index to the actual ascii zip code column in VS, or would I be better off compressing the ascii to binary and just use the PK index alone?
(2) Then, it seems like I should limit zip code lookups to data entry activities only and make a temporary scratch table of the actual referenced and used zip codes that have been pulled up into the user's working set. That seems to me to blow the 3N theory a little but then again it's a temporary table used to keep the performance working right? Is this an acceptable approach and how would I do that in VS, to "Derive" a temporary working subset list from the master set?
(3) Last, assuming I can create a crisp working set with a temporary, qualified, ZipCode scratch table, how does one go about creating parameterized queries in VS' visual designer of the dataset, so I can make combined-reference queries? For example I might want to join 3 related tables into one to use as my main indexing source in a primary/secondary form and then have the secondary appear as a grid from that first combined query. How do I create that master join set? Do I simply add query to the xsd graphic canvas and query builder the joins? If I "add query" to the regular data set tables to do this, it tells me my query does not match the original and refuses.
Well that's three roughly related questions to not muddy the thread so perhaps I can answer some specific replies that will help narrow down the approach, if some others would be willing to point me in the right direction.
Thanks in advance,
Nosliw
|
|
|
|
|
Below is a select statement that will be a stored procedure at some point.
I will only be passing @startdate and @enddate as user defined.
For the purpose of testing I set them to dates
Note:
I am trying to get the data for @totalatstart"total opened at start" and @totalatend "total opened at end" to be specifc to "Client"
declare @startdate datetime
declare @enddate datetime
declare @totalatstart int
declare @totalatend int
DECLARE @difference int
set @startdate ='03/01/2007'
set @enddate = '06/11/2007'
Set @totalatstart =(select count(*) from job_ticket where report_date > @startdate )
Set @totalatend =(select count(*) from job_ticket where report_date < @enddate)
set @difference =(@totalatstart-@totalatend)
select count(*) as 'Opened', @totalatstart 'Total Open at Start', @totalatend 'Total Open at End', @difference 'Total Closed', location_name 'Cient',
AVG(datediff(d,report_date,getdate() )) AS [Average
days open]
from job_ticket j
inner join location l on l.location_id = j.location_id
where (report_date between @startdate and @enddate)
group by l.location_name
Current resluts look like this:
Opened | Total Open at Start | Total opened at End | Total Closed | Client | ||||||| |||| AVG
47 |||||||| 341|||||| |||||||| 48 ||||||||||||||||||| 293 ||||||||| Alexandria, VA||||| 70
59|||||||||| 341|||||||||||| 48 ||||||||||||||| ||| 293 |||||||||| Austin, ||||||||||| 63
Obviously something is wrong with my set statment or my entire approach to this
Any suggestions or help in the right direction would be greatly appreciated.
I have been reading and can not seem to figure this out.
Regards,
Regards,
Hulicat
|
|
|
|
|
Well - part of your problem is that you only identify the total at start, difference and total at end once. These have no relation to the rest of your query. I would suggest that you put the logic for the creation of these values into the main query.
|
|
|
|
|
I am new to SQL, and have been accessing an MS SQL server database using various GUI tools.I want to rip a complete copy of this 9MB database onto my desktop computer, so I can alter it and "bench test" the changes I'm making to the overall application in VS2005.
I have tried to yank a copy of this database and its structure using programs like Visual Studio 2005, SQL Studio Manager Express, and EMS SQL Manager, none of which seem to offer a "Save As" option. While I can easily make changes to the database and its tables on the web host's server (sql.hostname.net), I can't figure out how to pull a copy off of their server and onto my machine...
Does this problem have a very simple solution that I am overlooking?
Thank you for any advice!
|
|
|
|
|
Presumably you’re running an instance of sql of your local machine?
I am not sure I fully understand what you’re trying to do.
If you’re in SQL management Studio right click on the database you want then select->Tasks->select either export, copy or backup.
It depends where you’re planning on hosting the database.
I hope this helps
Regards,
Hulicat
|
|
|
|
|
Using vb in an ActiveX part of a dts package I have created an excel sheet but now I need to add borders, lines, colors, etc.
Does anyone know how to do this?
All I could find is how to do a grid and a couple other things.
Set cell = oSheet.Range("A1:A6")
cell.Font.Bold = True
cell.ColumnWidth = 17.43
cell.borders.LineStyle = 10
Any help would be great.
Thanks
|
|
|
|
|
Hi,
Are there any issues using TOP clause in temp tables in stored procedure?
We are experiencing some difficulties for which we are unable to debug.
We are receiving two sets of different results, for same input parameters. When we run stored proc the first time versus when it is run 5 minutes later, the results are not consistent.
We are assuming using the TOP 1 clause may have some issues.
Thanks for your time.
|
|
|
|
|
Do you have an "order by" clause on your select statement?
|
|
|
|
|
|
Can you post the code for the stored procedure to this forum?
There are no issues with doing a "top 1" on temporary tables - apart from the fact the SQL-Server may possibly use a different query-plan each time you run it. However, your "order by" clause should have fixed that.
Is it possible that your temporary table somehow contains different data for each of the runs? I have had this caused by an update or delete statement using different query plans for different runs.
|
|
|
|
|
Here you go.
--------------
select top 1 @lastdrive = t1.datetimestamp
from #e t1
inner join #r r1 on r1.routestart <= t1.datetimestamp and
r1.routeend >= t1.datetimestamp
where t1.eventnum = 1108 and
t1.datetimestamp >= @stopstart and t1.datetimestamp <= @stopend and
r1.routedatekey = @routedatekey
and t1.driverkey = r1.driverkey
and r1.driverkey = @driverkey
order by t1.datetimestamp desc
--------------
This is run within a cursor.
Also - does it make a difference if we used something like -- select @lastdrive = max (t1.datetimestamp) -- instead?
Thanks.
Vani
|
|
|
|
|
I would use the @lastdrive = max(t1.datetimestamp) method. It may allow you to remove the cursor from your code.
|
|
|
|
|
You need to use ## for a global temporary table. A temporary table whose name begins with a single # is a local temporary table and is local to the connection.
SQL Server 2005 documentation says:
"There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server."
|
|
|
|
|
Hi good people.
I'm trying to update a table in mysql.
Here is the code:
For Each file12 In files<br />
<br />
array12 = System.IO.Path.GetFileNameWithoutExtension(file12).Trim<br />
<br />
<br />
Dim FileSize As UInt32<br />
Dim rawData() As Byte<br />
Dim fs As FileStream<br />
fs = New FileStream(cale12 & "\" & array12 & ".buf", FileMode.Open, FileAccess.Read)<br />
FileSize = fs.Length<br />
<br />
rawData = New Byte(FileSize) {}<br />
fs.Read(rawData, 0, FileSize)<br />
fs.Close()<br />
<br />
par.ParameterName = "?BinData"<br />
<br />
cmd.CommandText = "update notiz set Bindata=" & par.ParameterName & " where Nr=" & Chr(39) & array12 & Chr(39)<br />
<br />
par.Value = rawData<br />
<br />
cmd.Parameters.Add(par)<br />
cmd.ExecuteNonQuery()
It works for the first field but when I try to update the second field I have the error:
Parameter '?BinData' has already been defined.
Please help.
Thanks
|
|
|
|
|
Hi,
I think the problem is because you create parameter name on every cycle.
Why don't you try to create parameter name only once at first cycle
|
|
|
|
|
Hi,
Thanks for the idea.
It works.
Regards.
|
|
|
|
|
Is there a utility or simple way to determine the size (number of bytes) of records in a table?
I'm trying to determine what I would save in disk space if certain records were removed/archived out of a SQL Server 2000 DB.
I understand that record size depends on contents (VARCHAR(30) vs CHAR(30)).
Thanks.
|
|
|
|
|
How about running:
exec sp_spaceused 'MyTableName' That gives you the number of rows, and the amount of space taken-up by the data and indexes.
|
|
|
|
|
That's exactly what I needed, Andy. Thank you.
|
|
|
|
|
There is a code which displays results of SQL-query in dataGridView:
dataGridView1.DataSource = bindingSource1;<br />
dataAdapter = new OleDbDataAdapter("SQL-query", connectionString);<br />
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);<br />
DataTable table = new DataTable();<br />
dataAdapter.Fill(table);<br />
bindingSource1.DataSource = table;
Whether probably in a similar way to display results of SQL in the table in reportViewe?
Thanks.
|
|
|
|
|
I planned to update a set of values in a stored procedure and what the procedure will return if there is an error and how can i get it from C#
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
If you are using c# dot net catch a "SqlException" . In SQL procedure you can use RaisError function.
Regards,
Vimal
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
if <error_occurs> then
begin
RaisError('error....',16,1)
end
The name is Sandeep
|
|
|
|
|
Thank you
Best Regards,
M. J. Jaya Chitra
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
