|
You really have to use better names for things. Lable5 and TextBox3 really don't help anyone understand your code.
Next up: SQL Injection Attacks - You're code is vulnerable. You should learn about SQL Injection Attacks and Tips on How to Prevent Them[^]
Have you tried running the SQL Statment directly in SQL Server? What was the result?
Do you get any error messages? What were they?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Also, why are you puttin String.Empty inside quotes. This will display the string value "String.Empty", rather than the empty string ""...
|
|
|
|
|
Hi All,
I am trying to optimize a SP which involves cursor for the following DB structure and scenario.
DB Structure:
-------------
--Read it as TableName : (columnname(s))
TableBuyer : (BuyerName,BuyerType)
-- BuyerName is UniqueID
-- BuyerType can hold the value 1 or 2
TableCar : (BuyerName,Warranty,ChassisNumber)
-- BuyerName UniqueID
-- Warranty takes "DEF" as default value
TableCarDetails : (ChassisNumber, BuyerName)
Scenario:
---------
- If BuyerType is 2 then
1) Update warranty column with the value which is equal to corresponding
BuyerName(UniqueID) suffixed with / (forward slash)
2) A Corresponding entry has to be made (Insert Row) in TableCarDetails
with that chassis number and BuyerName
Existing Flow
-------------
The sp involves a temp table which stores the BuyerName where the BuyerType is 2
Based on the BuyerName in temp table all the updations are done thro cursor.
Can anyone throw somelight on how efficient I can tune this sp??
|
|
|
|
|
SwatCats wrote: Can anyone throw somelight on how efficient I can tune this sp??
Figure out a way of doing the same without using cursors. Relational databases are very good at doing set based operations (i.e. operations on large numbers of rows at the same time). The are not very good at working on one row at a time.
UPDATE statments can operate on many rows at once and I would imaging that the updates you want can be completed on large numbers of rows in one operation.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
hi brothers
iam working with Mysql DB on the web .. and i make timer to connection every minute to DB and check the table for last record has been added and show it in msgbox.
after one minute When it reconnection again .. i want it make sure the current result is not Unequalized previous result
iam tried but icant do it .. any one can help me plz ... thanx
---------------------------------------------------------------------------
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Dim mysqlstr As String
Dim mycomstr As String
Dim numm As Integer
mysqlstr = String.Format("server=localhost;user id=root;password=pass; database=mostashark; pooling=false;charset=utf8")
myconn = New MySqlConnection(mysqlstr)
mycomstr = "select T_Id,T_Exp_id,T_Text from twsyat order by T_Id DESC limit 1"
mycom = New MySqlCommand(mycomstr, myconn)
myconn.Open()
myreader = mycom.ExecuteReader()
Dim msg As New frmMessage
Do While (myreader.Read())
myreader.Read()
If myreader.Item("T_Id") > numm Then
dim MBoxText as string = myreader.Item("T_Text")
msgbox(MBoxText)
numm = myreader.Item("T_Id")
End If
Loop
End Sub
|
|
|
|
|
Do you have any kind of timestamp field in your table?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
no brother my table with this column : msg_id msg_user msg_txt
But i can add timestamp field if u have any solution with it
thanx
|
|
|
|
|
Dr.PHP wrote: But i can add timestamp field if u have any solution with it
Just do either one of these to add the timestamp field...
If you don't care about the timestamp being possibly null:
alter table {YourTableName} add ( msg_timestamp datetime )
or, if you do not want it to be null:
alter table {YourTableName} add ( msg_timestamp datetime NOT NULL )
{YourTableName} needs to be substituted with the actual name of the table.
Hope this helps,
Paul
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
i have one varchar variable @v which has value like '@p5','@p6' etc.
i have @p5, @p6 variables in my stored procedure.
when @v's value is '@p5', then value of @P5 will be used.
and so on.
how can i do that
|
|
|
|
|
if @v = '@p5'
Begin
--Use @p5 variable here
End
|
|
|
|
|
but i have @p1 to @p30 variables.
and i have to use this in loop
|
|
|
|
|
This sounds like you have a real design problem. I would suggest that you need to take a long hard look at your design here.
Plus, why are your variables called @p1 to @p30? What do they do? How can you tell what the purpose of these variables are? Out of the following, which do you think will help you to maintain your code in 6 months time:
declare @p1 nvarchar(20) or
@declare @first_name nvarchar(20)
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
my table have columns p1,p2,..p30,
in stored procedure using cursor this values are fetched in @p1, @p2..,@p30
|
|
|
|
|
Your table has columns that look like this? What on earth do they store? How can you tell that you've normalised the table?
I'm sorry, but how maintainable is this system going to be? Where do I put a value - is it in column P6 or is it in column P16. There is no cue to help me decide - and hence, there will likely be no standard.
Plus, why does your table have 30 columns? Have you denormalised it?
Please - take this opportunity to sort out the table. You will find that it pays dividends in the long run.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
From reading your other responses, I'm assuming you did NOT mean to capitilize P5 the one time...
This is basically just a table lookup problem--except that instead of storing the values in the database, you are storing them in programming variables. The basic idea is roughly the same, except that YOU get to write the lookup routine instead of letting the DB do it for you.
There are three basic approaches. In each example, I'll assume you have already retrieved the database value of @v into a local variable of the same name.
First, a simple linear scan of each variable name:
switch( @v )
{
case '@p1' : @p1
case '@p2' : @p2
case '@p3' : @p3
...
case '@p30' : @p30
default NULL
}
Second, ahead of time, put all the values into an associative array (essentially two arrays; one which contains the indices (names), and one which contains the values).
void initialize()
{ p_val(1)= @p1; p_val(2)= @p2; ...
p_ndx(1)='@p1'; p_ndx(2)='@p2' ...
}
then do a scan on the indices to find the value:
variant get_p_value( string p_name )
{ int curr_ndx = 1;
while( p_ndx( curr_ndx ) <> p_name ) curr_ndx++;
return p_val( curr_ndx );
}
A better approach would be to sort the values on p_ndx, and do a binary search instead of linear scan.
Third, if you don't know ahead of time all the possible names '@pX', you will have to use a language that has runtime evaluation ability, like Perl, or even VB.
return_value = eval( @v );
Of course one of the reasons we normally use database managements systems is to avoid all this nonsense.
SELECT val FROM myTable WHERE name=@v;
Hope this helps.
-- modified at 9:43 Friday 20th July, 2007
|
|
|
|
|
how can i use sequence number in insert comand so any time i click insert button a sequence number will fill up the id column
i am using vb.net and oracle
lets say i have a table with 2 colomn,
id integer
name string
what will be the insert comand?
please help. thanks
|
|
|
|
|
mindgameny wrote: a sequence number will fill up the id column
Are you looking for identity columns ? That will generate unique values for the column on each inserts
|
|
|
|
|
Oracle doesn't use an identity column.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Oops . I didn't notice it was for oracle.,
|
|
|
|
|
No problem. I was halfway through typing a response about using scope_identity when I noticed that. Perhaps he needs to learn that it's Oracle with a capital O. Much more noticable that way.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
One way of doing this in Oracle (and I believe it's probably the easiest) is to use a trigger. Suppose that you have the following table:
create table myTable
(
id number(10),
name varchar2(20);
)
alter table myTable
add (constraint mytable_pk primary key (id)); Create a sequence for this:
create sequence myTable_seq; Now, you write a pre-insert trigger:
create or replace trigger myTable_Insert
before insert on myTable
for each row
when (new.id is null)
begin
select myTable_seq.NEXTVAL
into :new.id
from dual;
end; To test this, enter:
INSERT INTO myTable(name) values ('Test');
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks a lot for your help
it helps me a lot for my project and i was able to do it in sql
but now i want to write code on vb.net for same purpose.
is that posible?
the data table is already in database (oracle)
and i cant change it.
so where and what i need to do in vb.net. please help
thanks a lot
|
|
|
|
|
Just because the table is in place doesn't mean that you can't use this technique. All you need to do is create the sequence table and the pre-insert trigger. They won't affect your table in the slightest.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
We often need quick access to our sql data. Often we open SQL Analyzer to grab the data when we know exactly what we want. Other non-SQL users wade through the application housing the data.
I just thought of creating a web application where users could "crawl" the data by choosing their database and table and being presented with the data. Then they could drill down in the data by clicking links that are based on the table's foreign keys.
In a perfect world there would be many features such as
* Having custom converters based on the data such as showing text values rather than foreign key ids that link to a lookup table, showing numbers in specified formats, displaying a picture rather than the path, etc.
* Allowing security based on passed credentials or a user entered username/password. Passed credentials would allow access to the data the user has initial rights to. Manual user credentials would be useful for giving access to specific areas not available to the user or testing access to user accounts that applications use.
* Searching
* ???
Before I go and create something like this (which would be fun) does anyone know of something similar? An application with the source code available would be preferable but I'd be willing to take a look at closed commercial apps as well.
Thanks!
Matt Penner
|
|
|
|
|