problem in inserting date in sql server 7 through insert query

hello myself avinash
i am developing on application having vb 6 as front end and sql server 7
as back end.
when i use insert query to insert data in table then the date value of
that query is going as 01/01/1900
my query is as follows

StrSql = "Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"
StrSql = StrSql & " Values(" & txtTransactionID.text & "," &
txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &
"'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),
2)
& "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &
val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),
2)
& ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &
vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &
vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),
2)
+ val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &
IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),
0)
& ")"

and its output is

Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)

in above query though i used cdate to voucherdate value still it save in
database as 01/01/1900 though here it shows right date
plz help me its a very big issue for me & i really just fed of this
problemYou might try running a Profiler trace to capture the actual statement
executed by SQL Server and check for any triggers that might change the
value. Note that SQL Server will interpret an empty string as 1900-01-01.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"avinash" <pawar_avinash@.rediffmail.com> wrote in message
news:9c11bb7aacaeeddcf230738468fd3b72@.localhost.ta lkaboutdatabases.com...
> hello myself avinash
> i am developing on application having vb 6 as front end and sql server 7
> as back end.
> when i use insert query to insert data in table then the date value of
> that query is going as 01/01/1900
> my query is as follows
> StrSql = "Insert Into
> SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"
> StrSql = StrSql & " Values(" & txtTransactionID.text & "," &
> txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &
> "'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),
> 2)
> & "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &
> val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),
> 2)
> & ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &
> vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &
> vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),
> 2)
> + val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &
> IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),
> 0)
> & ")"
> and its output is
> Insert Into
> SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
> Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)
> in above query though i used cdate to voucherdate value still it save in
> database as 01/01/1900 though here it shows right date
> plz help me its a very big issue for me & i really just fed of this
> problem|||hi avinash
i have come across such problems frequently. i would advise u
to set the date format within dtpicker control u are using. Check
properties for the control and set the format to custom. then set the
mask.
thats it.

Regards
Debashishsql

Problem executing a Oracle Procedure using Execute Sql Task

Hi All,

I am trying to execute a Oracle Procedure through Execute Sql Task but it keeps throwing back an error ORA:00900. Is it possible to execute the procedure from this object? Do I need to use the script task to execute this. If yes then how?

Any help would be appreciated.

Thanks in advance.

Amol

hi, I did some stuff related that from a Sql Task but from Sql Server 2000 (dts), an Oracle function:

DECLARE

RetVal NUMBER;

begin

ACTUALIZAMARCAEMPLEADO;

end;

|||

Hi Amol,

Could you provide a bit more info:

1. What is the SQL statement you are using to execute the stored procedure?

2. What type of connection are you using? (ADO, OLE DB, ODBC)

Thanks,

~Matt

Problem connecting to SQL Server via Query Analyzer

Hi there, I see this thread is back from 2004. I am having an issue connecting to a sql server located on another machine on my Intranet. I recieve the following error:
unable to connect to server ...
server: Msg 17, Level 16, State 1
Microsoft odbc sql server driver odbnetlib sql server does not exit or access
I tried to connect to other sql servers, and I got no problem, except for this particular server. any ideas?
From http://www.developmentnow.com/g/118_2004_1_0_0_468284/Problem-connecting-to-SQL-Server-via-Query-Analyzer.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
1. Server does exist (server name spellign corrcet)?
2. You sure you have permission to access it?
3. Network library compatible?
"Efrain Juarez" <stranger_tepa@.hotmail.com> wrote in message
news:1bf20371-0394-4c54-a975-764a23b202d5@.developmentnow.com...
> Hi there, I see this thread is back from 2004. I am having an issue
> connecting to a sql server located on another machine on my Intranet. I
> recieve the following error:
> unable to connect to server ...
> server: Msg 17, Level 16, State 1
> Microsoft odbc sql server driver odbnetlib sql server does not exit or
> access
> I tried to connect to other sql servers, and I got no problem, except for
> this particular server. any ideas?
> From
> http://www.developmentnow.com/g/118_2004_1_0_0_468284/Problem-connecting-to-SQL-Server-via-Query-Analyzer.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com