The varbinary datatype does not differentiate the contents of the field, it's all just binary data as far as SQL is concerned. The samples you've found for images should apply equally to any type of binary object, it just seems that most examples are focues on image since most people want to store image data.
Mike
|||Thanks Mike. I will try those examples.hi all,
In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.
table 1:
tid--> int,identity,primary key
tname-->varchar(200)
table 2:
sid-->int,identity,primary key
tid-->fk (this tid is set as foreign key for the tid in table1)
now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..
pls help me..
thanks
swapna
Go For Stored Procedure for Insert...
SP Flow should be-
1. Start SQL Transaction
2. Insert into Table1
3. Get the Inserted INDENTITY value.
4.Insert into table 2
5. Commit Transaction Or Rollback transaction depending on the Error .
You can create a stored procedure. There use will insert the record in the table 1 first and fetch the latest generated id value in table 1 using scope_identity and store it in a variable. Then you will insert the corresponding record in table 2 using the variable value.
For help on how to call a stored procedure from code visit http://forums.asp.net/t/1165758.aspx.
Feel free to ask for more help on this issue.
Or you can create a trigger to insert the row into the second table.
It if must always happen the same way, the trigger would be a safer bet.
check out create trigger in the documentation.
sql
RyanCan we see the code and the exact error message?|||Have you tried executing the update manually? Also, you might want to update the timeout on the sql command to something longer, and see if it works.|||Instead of using the stored procedure I tried to execute a single update statement using c# code and the same one using query analyzer. The query analyzer one worked fine everytime with 2 different update statements but the code timed out on one. Both were just updating one field in one record. Both tables have triggers going on to, but Im using the same admin password in the code as Im using to login with query analyzer. The only other difference is that some stored procedures are encrypted and some are not But Im not sure all the procs and tables the triggers handle. I figured that wouldnt matter since im using an admin password. Does anyone know what is going on? Thanks
Ryan|||
ryanoc:
Does anyone know what is going on?
//SqlConnection connRDK = new SqlConnection("Server=my_server;Database=ad_xx_beta;Persist Security Info=False;user id=sa;Password=xxx");
String sql;
sql = "UPDATE VHSLSFIN SET AmtPriceVehicle = '116907' WHERE SlsId = 'V01001878'";
//sql = "UPDATE COEMP SET NameNick = 'Ryan C' WHERE empid = '163'";
try
{
SqlCommand commRDK = new SqlCommand(sql, connRDK);
connRDK.Open();
commRDK.ExecuteNonQuery();
}
catch (Exception ex)
{
string x = (ex.Message);
}
finally
{
//done
}|||I think your catch statement is "swallowing" any exceptions you are receiving. What are you doing with the string x? Are you displaying it anywhere?|||Not using it anywhere except in debug mode to view the exeption which is allway server timeout on one of the queries|||
You can check the log file of the database to see whether it is always full
|||Where do I find it? What does full mean?|||
Where do I find it? What does full mean?
Open your database because the Taskpad is context sensitive click on view at the top of Management Studio and you will see the Taskpad, click on it and you will see all the file allocation of the open database. You can increase the file size by changing from your existing size to something bigger. Hope this helps.
|||There is a big difference in the databases in question.Live database with no timeouts:
allocated: 12.37mb
used: 3mb
free: 9.3mb
Test database with some timouts:
allocated: .99mb
used: .49mb
free: .5mb
Does this major difference have to do with my timeout problem? If so, how do I increase the transaction log for the test database? thanks very much!
Ryan|||I increased the size and still get the timeout :(|||
Try increasing both files the MDF(Microsoft data file) and the LDF(log data file) by changing the size of the files. Hope this helps.
|||Its the same as the other files. One other thing I noticed is that my database was created using my username, but the other database was created using the admin username.I have created a wizard form to collect user information.When use click Finish button all the details added to the SQL server database.
That part is ok.
But my problem is this
Through my interface I am giving user to select the date and time.
(I have used AJAX datepicker and MaskedEdit control)
After user type the date (Normal format is - HH:MM:SS) and click finish button I check the database.
It automatically Inserted the date also (Jan 1 1900)
So I dont want this date part and I just want Time only.How do I do this ??
(I have used datatype as : nvarchar instead of datetime .Because of if i use datatime it automatically inserts both data and time values)
Values with thedatetimedata type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after thebase date, January 1, 1900. The base date is the system reference date. Values fordatetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
So in your case the first 4 bytes are not used. By default the value of Jan 1 1900 is displayed.
I do suggest that you continue to use the datetime value to store your time for filtering and sorting purposes but when you display the data to the end user you simply format the data to a time format.
Hope this helps!
|||
Hi,
you can convert your datetime with the CONVERT command, e.g. (in Query Analyzer):
Declare @.input As DateTime
Set @.input = Getdate()
Select Convert(nvarchar,@.input,108)
Regards
Marc André
Hi,
thanks for your reply.
Insert and Update trigger work fine when i have only one of them defined.
However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and has the same timestamp in the audit table.
Insert trigger goes as
CREATE TRIGGER InsRecord ON [dbo].[tableA]
AFTER INSERT
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Insert',GetDate() from inserted
Update trigger goes as
CREATE TRIGGER UpdRecord ON [dbo].[tableA]
FOR UPDATE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Update',GetDate() from inserted
Delete Trigger goes as
CREATE TRIGGER delRecord ON [dbo].[tableA]
FOR DELETE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Delete',GetDate() from deleted
Note:This tableA has relations with 2 other tables on 1 field each from each table but i don't think it should matter.
Please advise how to prevent it.CREATE TRIGGER alteredRecord ON [dbo].[tableA]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
...declare lngIns & lngDel
SELECT lngIns=count(col1)
from inserted
select lngDel=count(col1)
from deleted
IF lngIns>0 and lngDel=0
...inserted
else if lngIns>0 and lngDel>0
...updated
else if lngIns=0 and lngDel>0
...deleted
end
END
Dear All,
I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".
As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?
<script language="VB" runat="server" >
Sub Page_Load(Src As Object, e As EventArgs)
If Page.IsPostBack Then
Dim ConLath As SqlConnection
Dim comLath As SqlCommand
Dim insertcmd
conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password")
ConLath.Open()
insertcmd = "Insert into His_set values (@.t_d,@.s_p,@.p_s,@.v_oq,@.i_oq,@.v_qn,@.i_qn,@.v_qw,@.i_qw)"
comLath = New SqlCommand(insertcmd, ConLath)
comLath.Parameters.Add(New SqlParameter("@.t_d", SqlDbType.DateTime, 12))
comLath.Parameters("@.t_d").Value = trade_date.Text
comLath.Parameters.Add(New SqlParameter("@.s_p", SqlDbType.Decimal, 8))
comLath.Parameters("@.s_p").Value = sett_price.Text
comLath.Parameters.Add(New SqlParameter("@.p_s", SqlDbType.Decimal, 8))
comLath.Parameters("@.p_s").Value = post_close.Text
comLath.Parameters.Add(New SqlParameter("@.v_oq", SqlDbType.Int, 8))
comLath.Parameters("@.v_oq").Value = vol_oq.Text
comLath.Parameters.Add(New SqlParameter("@.i_oq", SqlDbType.Int, 8))
comLath.Parameters("@.i_oq").Value = oi_oq.Text
comLath.Parameters.Add(New SqlParameter("@.v_qn", SqlDbType.Int, 8))
comLath.Parameters("@.v_qn").Value = vol_qn.Text
comLath.Parameters.Add(New SqlParameter("@.v_qw", SqlDbType.Int, 8))
comLath.Parameters("@.v_qw").Value = vol_qw.Text
comLath.Parameters.Add(New SqlParameter("@.i_qn", SqlDbType.Int, 8))
comLath.Parameters("@.i_qn").Value = oi_qn.Text
comLath.Parameters.Add(New SqlParameter("@.i_qw", SqlDbType.Int, 8))
comLath.Parameters("@.i_qw").Value = oi_qw.Text
Try
comLath.ExecuteNonQuery()
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try
comLath.Dispose()
ConLath.Close()
End If
End Sub
</script>
I'm not surprised if it fails when you leave the mandatory field empty. But I assume that's not what you meant, right?
The problem here relates to casting. Your empty text box returns an empty string. This would be fine for a varchar column, but if you try this with a column of type int, it will fail. You need to explicitly insert a null value in this case.
You could try something like this:
comLath.Parameters("@.v_oq").Value = (vol_oq.Text =="" ? DBNull.Value : vol_oq.Text);|||thanks for your prompt reply.
apparently the conditional operator ? works if u are using C#.
I am using the language vb, this implies i will have to use the if and then conditonal block for each, right?
|||VB has the tertial operator IIF which is similar to ? operator, although it behaves somewhat differently (VB evaluates all parameters).
comLath.Parameters("@.v_oq").Value = IIF(vol_oq.Text =="", DBNull.Value, vol_oq.Text)|||
thanks so much it worked :)
however, the issue now is when i try to display the columns with null values, it reports an error -
i have explicitly casted these values with their corresponding data types to defualt value other than null. But the problem is like for eg, in case of any integer type it i set it to default of "0", for our business purpose its misleading as they would be expecting the sell of items for that day to be "0".
this is my code:
Public Function CheckDBNull(ByVal obj As Object, _
Optional ByVal ObjectType As enumObjectType = enumObjectType.StrType) As Object
Dim objReturn As Object
objReturn = obj
If ObjectType = enumObjectType.StrType And IsDBNull(obj) Then
objReturn = ""
ElseIf ObjectType = enumObjectType.IntType And IsDBNull(obj) Then
objReturn = 0
ElseIf ObjectType = enumObjectType.DblType And IsDBNull(obj) Then
objReturn = 0.0
End If
Return objReturn
End Function
Then what do you want to display if an integer column is null? If you want to leave that field blank, simply return an empty string...